SQL "Order By" in VBA error 1

[0212]

Hi, I am trying to use sql "order by" in vba. I get a syntax error. Can anyone help?
strWhere = strWhere & " ORDER BY [RADue] ". Thanks for any help!!

Code section:

Select Case Me.RptType
Case 1
'For Case Ovrdue Rpts in dialogue Box, find all dates for RADue <31 days overdue from today, > 30 days and'
'all null due dates'
Select Case Me.OvrDue

Case 1
'StrWhere = strWhere + "SQL Statement today's date & _ (line continuation)'
strWhere = strWhere & " AND [RADue] >= (Date()-30)" & _
"AND [RADue]< Date()" & _
"AND [RAComp]Is Null "
Case 2
strWhere = strWhere & " AND [RADue] < (Date()-30)" & _
" AND [RAComp]Is Null "
Case 3
strWhere = strWhere & " AND [RADue] Is Null" & _
" AND [RAComp]Is Null "
End Select
strWhere = strWhere & " ORDER BY [RADue]

 

[PHV]

Which line of code is highlighted when the error raises ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[0212]

Thanks, PHV, for your help. I didn't show the line that was highlighted. There are three CASEs. At the end of the procedure, I open a form with the following:

DoCmd.OpenForm " FrmTrackRptDatesODR ", acNormal, acEdit, strWhere

This is the line highlighted with a syntax error for the sql statement. Thanks!!

 

[Docshop]

Do you need the semicolon to complete the sql statement?
"ORDER BY [RAdue];

 

[PHV]

You CAN'T use an ORDER BY clause in the WhereCondition of the OpenForm method.
You may try this:
...
End Select
strOrder = "[RADue]"
...
DoCmd.OpenForm "FrmTrackRptDatesODR", acNormal, acEdit, strWhere
With Forms!FrmTrackRptDatesODR
.OrderBy = strOrder
.OderByOn = True
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[0212]

DocShop, thanks for your response. The sql statement is in vba. I tried adding the semicolon, but it didn't help. I don't think you need semicolins in vba - do you?

 

[Docshop]

can you add a breakpoint and a watch on strWhere and show me the sql statement that strWhere has jus prior to opening the form?

 

[0212]

Thanks again, PHV!!! It worked like a charm! Do you know of any good referenced for my endeavors with VBA/SQL? Thanks, again!

Docshop, PHV helped me. Thanks for you help!