Using AS keyword for changing the name field

[rumenrs]

Hi,
How I can use "AS" in Access. I want to change the name of the field in Combobox.
For example I'm using in combobox:



Select [FirstName] as NAME, [LastName], [Address],[Town], [City],[County] from tblStudentInformation

But after open the form choose NAME in combobox, and Access tells me: Enter parameter...
How I can rename "FirstName" to NAME, to be used in combobox?

I attach .mdb file with the problem.

P.S. I don't want to change the table field name, if this will propose me
report_sorting_2000.mdb

Thanks

[RuralGuy]

You do know that NAME is a Reserved Word in Access, right? http://www.allenbrowne.com/AppIssueBadWord.html#N

[rumenrs]

No, I didn't know. But if I choose other word, same situation

[RuralGuy]

Have you done a MsgBox on the value *before* trying to use it to see what you have?

[RuralGuy]

What you posted has a RecordSource of the table and *not* a query.

[rumenrs]

I didn't try with MsgBox, All what I want is: ComboBox To have other name, not FirstName, that's why I'm try to use AS

[RuralGuy]

Create a query and bit your report to that instead of the table.

[rumenrs]

Well, I try it with Query, there I'm using like that in the field:
Test: FirstName

but same situation...

[RuralGuy]

Your RowSource Type for the cbo should be a Query, not a List.

[rumenrs]

Well... now the combox shows all the name -> I'm still stuck:

[rzw0wr]

Look at this and see if this is what you are after.

report_sorting_2000.zip


Dale

[rumenrs]

Well this works but just half of the my problem, when you go to filter, and select Student Name (new name)-> Set Sort order there is again parameter message. How I can remove this message? I don't needed

P.S. Otherwise I have to change the field name of the table, but this will break all DB

[orange]

Perhaps you could tell us exactly what you are trying to do in plain English.

If you have this SQL (the query) as the recordsource of your Form/Report

SELECT tblStudentInformation.StudentID
, tblStudentInformation.FirstName 
, tblStudentInformation.LastName
, tblStudentInformation.Address
, tblStudentInformation.Town
, tblStudentInformation.City
, tblStudentInformation.County
FROM tblStudentInformation;

You could alter the recordsource with an Order By statement

The fields are "numbered 1 thru 7".

---To SORT by FirstName, you can alter the recordsource with

Order By 2 (since Firstname is field #2) by default you get ASCending

--To Sort by County DESCending , you could alter the recordsource with
Order By 7 DESC;

When you choose the field to Sort By in your Form, have an After Update event which alters the Recordsource as shown here, then requery the Form. (untested)

I used the version of the mdb posted by rzw0wr

Here's his query with my Order By

SELECT tblStudentInformation.StudentID, tblStudentInformation.FirstName AS [Student Name], tblStudentInformation.LastName, tblStudentInformation.Address, tblStudentInformation.Town, tblStudentInformation.City, tblStudentInformation.County
FROM tblStudentInformation order by 2;

Here is the Order by 7 DESC example

SELECT tblStudentInformation.StudentID, tblStudentInformation.FirstName AS [Student Name], tblStudentInformation.LastName, tblStudentInformation.Address, tblStudentInformation.Town, tblStudentInformation.City, tblStudentInformation.County
FROM tblStudentInformation order by 7 DESC;

see the corresponding jpgs for the query results

[rumenrs]

Hi, I don't know why you don't understand me and complain from my English, my English is much better that some guys here... whatever..

With this:
SELECT tblStudentInformation.StudentID, tblStudentInformation.FirstName AS [Student Name], tblStudentInformation.LastName, tblStudentInformation.Address, tblStudentInformation.Town, tblStudentInformation.City, tblStudentInformation.County FROM tblStudentInformation order by 2;

Don't work...

All what I need is to rename the table filed in the combobox, becase I have big DB and there is one table with field name: "dskm", so this "dskm" is not understandable in the combobox, that's why I want to rename with AS, like SQL query.
But if I use AS, when I choose this name (renamed name) from the combobox, Access says: Enter Parameter, and this is the problem... the message (like the images above).

I don't know how to enplane to you, I give also examples...
In your results, you show me the query, I don't want the query to be filtered, I want to use Filter in the report.


P.S. If any know how sort report results, using button -> this also will help me

[RuralGuy]

Hi, I don't know why you don't understand me and complain from my English, my English is much better that some guys here... whatever..

Orange was *not* complaining about your English. He was hoping you could explain it in more simplified terms so even us slow witted people could understand what you needed.