Query works in Access 2000 but close Access 2002 down

[JCAA]

I am currently experience something that is weird to me. A query is working fine for me in Access 2000 but when I or anybody else is trying to run it in Access 2002 it closes Access down completely. No warning no not just the query or the database but all of Access. Any suggestions?

thanks,

JCA

 

[JonFer]

Need more information. Can you...

1. Post the SQL.
2. Indicate whether the tables are linked or internal.
3. Show the datatypes of the fields being queried.

John

http://www.interautomateconsulting.com

 

[JCAA]

1.
SELECT [TTT - Temp File with Buy as Ord].*, IIf((Int([TTT - Temp File with Buy as Ord]![CreateSOQTY3]/6)*6)<[TTT - Temp File with Buy as Ord]![CreateSOQTY3],(Int([TTT - Temp File with Buy as Ord]![CreateSOQTY3]/6)+1)*6,Int([TTT - Temp File with Buy as Ord]![CreateSOQTY3]/6)*6) AS Rounding
FROM [TTT - Temp File with Buy as Ord]
ORDER BY [TTT - Temp File with Buy as Ord].CreateSOQTY3 DESC , IIf([DRPQty]>0,0,1), [TTT - Temp File with Buy as Ord].NOTE, [TTT - Temp File with Buy as Ord].ClientSoQty DESC , [TTT - Temp File with Buy as Ord].Request_Qty DESC , [TTT - Temp File with Buy as Ord].DRPQty DESC , IIf(([TTT - Temp File with Buy as Ord]![ClientSoQty]-[TTT - Temp File with Buy as Ord]![OH_Qty]-[TTT - Temp File with Buy as Ord]![InTransitQty]-[TTT - Temp File with Buy as Ord]![OpenSOQty])<0,0,[TTT - Temp File with Buy as Ord]![ClientSoQty]-[TTT - Temp File with Buy as Ord]![OH_Qty]-[TTT - Temp File with Buy as Ord]![InTransitQty]-[TTT - Temp File with Buy as Ord]![OpenSOQty]) DESC;

2.
There are linked tables in the database but the table it is taking data from is not linked it is an "internal" table.

3.
All the fields are number (double) except Item, ItemDescrip,
Trend Type, TrendDescrip, OBSOLETE, Phase or Spare, Sub Part (Y/N), Substitute Part, NOTE Which are all text fields.

I should also add that it is working in Access 2003.

I appreciate your help

 

[JonFer]

I would remove the Rounding field and the Order By clause and see if that runs okay. Then add back the Rounding field and the Order By clause one at a time to see what causes the problem. See if the problem is a field or a calculation on a field.

If the basic query doesn't run, the table has bad/corrupted data.

Nulls can cause problems so add Nz([number],0) or Nz([text],"") if you can identify a problem field.

Post back if you find out anything further.

John

http://www.interautomateconsulting.com

 

[JCAA]

John,

Thank you!
Thank you!
Thank you!

It is something in my Order By that is not working with Access 2k2 but is working in Access 2k and 2k3. It is running when I take the order by out. I have been trying to solve this for a long time and I grateful that someone was able to help me out.

 

[JonFer]

Now work on each expression in the ORDER BY clause to find the culprit.

John

http://www.interautomateconsulting.com