Using AS keyword for changing the name field

[orange]

Yes, Allan I was NOT making any comment on the poster's English.

I made a comment based on my best guess what you(the poster) intended. Sure there's lots of room for me to be wrong about WHAT you intended -- and that's my issue with telling us in plain English.

I gave you 2 examples of queries with Order By, and showed you these results that did WORK -- they sorted the result based on the parameters I gave.

Now, to say

Don't work

, I am confused. My examples work fine as written and described.

You are talking about a query, a combo box and a Filter for a Report.


You seem to be fixated on using "AS". I was showing an option that didn't required changing a Field/parameter/control name.

You have to be aware that a Report has its own Grouping and Sorting set up, and may not use the result of the recordsource query.

In any event as I have said, and as Allan has said -- we don't know What you are trying to do. Maybe we're just real slow. But in my view you have told us HOW you have set up something, and it isn't producing the result you want.

There may be options to achieve WHAT you want, but to offer such options we have to know WHAT you're trying to accomplish.

PS: Your English is not the issue. Your explanation is confusing us; and is unclear to us.

[rumenrs]

Hi,
OK, I don't know how more easy to explain and sorry for the reaction about my language.
Yes, you show me the results and works -> because you show me the query in query window, I want to use "AS" in RowSource field -> But it's impossible if is used then in Report.

I just don't want to see FirstName in combobox, I want to be replace with Name.

I resolve the problem in different and easy way -> I just change Table field name to "Name", then everywhere I actualize the new name field and now is OK.
I just wondered if I can use "AS" in RowSource field.

Ok, thanks for help.

[orange]

Are you getting a message, or are you using [Name] in your syntax? Name is a reserved word in Access and I'm surprised if you don't get some sort of message/warning.

[rumenrs]

Well, yes I'm using name, and there is no error message

[orange]

Can you post the latest version of your database? Remove anything confidential - use names like daffy duck or Joe Blow or whatever.
Also, can you provide specific instructions of what I should look at (Form/Report) , event code etc.?
What options should I try and what result should I get if it works the way you want.
If I'm suppose to select something from the combo --exactly what should I select....

[rumenrs]

Ok, I will try again to explain...

Open Form "frmOrderBy", then you can see there is comboboxes, if you select the first, the values are:

fnm
LastName
Address
Town
City
County

So, I don't like the word "fnm" because is understandable, so I want to replace this word "fnm" with word "FName" and when I go to combobox next time to is look like:

FName
LastName
Address
Town
City
County

But if Use use "AS" to rename the field in Record Source of combobox, when I press the button "Set sort order" the Access says Enter Parameter, if I leave the word "fnm" there is no problem.

[orange]

Your table design allows a student to be in 1 course only. Is this what you intended?
If a student enrolls/takes a second or third course, then your design requires you to replicate the row(s).

[orange]

Your table design allows a student to be in 1 course only. Is this what you intended?
If a student enrolls/takes a second or third course, then your design requires you to replicate the row(s).

You may want to consider

tblStudentInfo --->tblStudentTakesCourse<-- tblCourse

Your set up allows the situation shown in the jpg-- probably not what you want.

Also during some review of altering the Report sort order at runtime, I found this link
http://allenbrowne.com/ser-33.html

While testing your code and checking the Report properties, I noticed that even when you clear the Sort order via your button, the Order By property of the Report remains. I set it specifically [Town],[Lastname] and it was not cleared via the Clear Sort Order button. I have attached a jpg showing the Report properties after doing the Clear Sort.

I created a module as an attempt to get

Function ShowTableFields() As String
'
'This proc was an attempt to get the field names of your table into an array.
'The array would contain the name to show in a combobox's Value list and the second
' index would be the position of the field in the table and I allowed a third index
'to be used fo you to indicate whether that field show be displayed or not
'
' So you could build your value list based on the array contents.
' If you don't want StudentId to be in the list, then mark it False
' I have put Name in place of fnm in the array

'My thinking was you could use the position of the field 0,1,2 etc to be used in
' an Order By statement.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim x As Integer
Dim fldlist As String
Dim fldarray(11, 3) As Variant
Set db = CurrentDb

For Each tdf In db.TableDefs
   If Left(tdf.Name, 4) <> "MSys" Then  ' Don't enumerate the system tables
      For x = 0 To tdf.Fields.Count - 1
      'Debug.Print tdf.Name & "','" & tdf.Fields(x).Name
      fldlist = fldlist & tdf.Fields(x).Name & ";"
      fldarray(x, 1) = tdf.Fields(x).Name
      'check for your fnm field
         If tdf.Fields(x).Name = "fnm" Then fldarray(x, 1) = "Name"
      fldarray(x, 2) = x
      Debug.Print fldarray(x, 1) & " " & fldarray(x, 2) & " " & fldarray(x, 3)
      Next x
   End If
Next tdf
fldlist = Mid(fldlist, 1, Len(fldlist) - 1)
fldlist = Replace(fldlist, "fnm", "Name")
ShowTableFields = fldlist
Debug.Print ShowTableFields
End Function

The output from the debug.prints is

StudentID 0 
Name 1 
LastName 2 
Address 3 
Town 4 
City 5 
County 6 
PostCode 7 
Country 8 
Telephone 9 
CourseID 10 
StudentID;Name;LastName;Address;Town;City;County;PostCode;Country;Telephone;CourseID

But as I got into this and saw that a Report has its own sorting and grouping that is applied before anything in your query, I think you should look at another approach. I am leaving the code here since it may be of some value (possibly) to you.

It seems a long and convoluted route to change the string that appears in a combo.

Perhaps someone else has a technique that can be applied.

Good luck with your project.

[rumenrs]

Thanks for the big help.
I just change the table field

[orange]

Good stuff, glad you have it sorted out.