Query issue

[gianina]

Hi All,

I have a problem with my query and I hope I can find some answers here.

I created a query which shows all "new" ID numbers created.
We got to ID number 99 and then we created ID number 100 it didn't show right after number 100 in my query. Instead ID 100 got placed after ID number 10.

Also, I created ID number 200 and got placed after ID number 20.

Is there a way I can make ID number 100 to be shown after ID number 99 and so forth...

Thank you for your help.

 

[lespaul]

Is the ID field numeric or text? seems like it's sorting as a text field....if it is text, then it's sorting correctly. If you need to sort numerically try:

ORDER BY VAL(ID)

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[gianina]

Hi Lespaul,

Thank you for your reply.

I am not an expert of Access, therefore I need to ask you to tell me where should I put this:

"ORDER BY VAL(ID)

HTH "

Thanks again...

 

[ironj32]

go into the SQL view of your query
and it should look like this

SELECT Table1.ID
FROM Table1
ORDER by Val( id);

 

[gianina]

My table is called "NCR Database" and the field where the ID's are stored is called "NCR #"

So I did something like this:

SELECT [NCR Database].[NCR #]
FROM [NCR Database]
ORDER by Val(NCR #);

and an error poped up: "Syntax error in date in query expression 'Val(NCR #)'

What should I do ?

Thanks.

 

[traingamer]

try

ORDER by Val([NCR #]);

 

[PaulBricker]

And if at all possible stay away from using # signs and other delimiting/concatenating characters like ", ', &, * as part of a field name. As you can see by the error message you were getting, Access was looking for a date because the # sign is used to delimit date values.

Paul

 

[gianina]

Thank you all..it worked.
I appreciate it.

G.