SQL ORDER BY not working properly?

[braktoon]

hello, in one of my projects i run a query on a database and place the result in a new table. the result is supposed to be sorted by one of two fields, my code looks like:
sSQL = "SELECT * INTO T4RR FROM T4R"
sSQL = sSQL & strRetNo & "ORDER BY T4R."
if optBuilt.value = true then
sSQL = sSQL & "ShippedWeek;"
else
sSQL = sSQL & "BuiltWeek;"
end if

when i run it for the built week, it sorts fine, but when i run it for the shipped week, the results look like someone took the correctly sorted list, cut it in half and pasted the first half after the second half. if i had a list of 8 strings it would look like this {e,f,g,h,a,b,c,d}. i would appreciate any guidance as to how to fix this problem.

thank you for your time
Justin Loehlein

 

[jrbarnett]

Justin,

The way that relational databases work in general is governed by set theory, and as such it doesn't matter which order records go into the table. Likewise, with an ordinary select query you cannot guarantee the order that the results will be returned - unless you force Access to sort the results with an ORDER BY on the SQL code.

John

 

[NorthNone]

Justin, I couldn't reproduce your problem. I didn't know what strRetNo was, I omitted it. Don't know if that had any impact. Anyway, here's the code that worked for me based off of your code. OptBuilt was a checkbox on my form.
==========================
Private Sub cmdAddRecs_Click()
On Error GoTo Err_cmdAddRecs_Click
Dim sSQL As String
sSQL = "SELECT * INTO T4RR FROM T4R "
sSQL = sSQL & "ORDER BY "
If optBuilt.Value = True Then
sSQL = sSQL & "BuiltWeek;"
Else
sSQL = sSQL & "ShippedWeek;"
End If
DoCmd.RunSQL sSQL
Exit_cmdAddRecs_Click:
Exit Sub
Err_cmdAddRecs_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecs_Click
End Sub

 

[braktoon]

its strange, that looks like my code, i have never seen this problem before, and i have run plenty of similar queries, like i said, it works for BuiltWeek, but not ShippedWeek which would seem to indicate a problem in the data, but when i sort the data by hand in the table, it comes out correct. the final SQL statement look something like:
"SELECT * INTO T4RR FROM T4R WHERE ReturnNo = 1 ORDER BY BuiltWeek;"
that works, but for some reason if instead of BuiltWeek i substitute ShippedWeek it doesnt