Dsum with Syntax Error

[Vasago]

I have a Dsum that worked until I added the " And [Trip Sign Off] <=8:45:00 AM" . I am now getting this error:

Syntax error (missing operator) in query expression '[Week] = 6 And [Trip Sign Off] <= 8:45:00 AM and DatePart('d',[Date]) <= 3 and [Employee]='Batson, Leroy''.

DSum("[time]","Profit Breakdown 1","[Week] = " & [week number] & " And [Trip Sign Off] <= " & [TTime] & " And DatePart('d', [Date]) <= " & [ddate] & " And [Employee]='" & [Employee1] & "'")

Thanks for your help,
Vasago

[jimatqsi]

Vasago, try DateValue("8:45 :00 AM") for your time.

I'm not 100% sure the " AM" works in that context but I think so.

Jim

[Luk3r]

Also, adding to what jimatqsi said, I believe (at least in regular SQL queries) if your column is not suited for an integer you would have to use "'" & ColumnValue & "'", to treat it as a string.

[zmbd]

I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.
So to use your code:

DIM strSQL as string
strSQL = "[Week] = " & [week number] & _
   " And [Trip Sign Off] <= " & [TTime] & _
   " And DatePart('d', [Date]) <= " & _
   [ddate] & _
   " And [Employee]='" & [Employee1] & "'"
'
'now you can insert a debug print here for troubleshooting
' - press <ctrl><g> to open the immediate window
' - you can now cut and paste this information for review!
'
debug.print "Your criteria = " & strSQL
'
'now use the string in your code:
DSum("[time]","Profit Breakdown 1",StrSQL)

Next using the reserved token "Date" and "Week" as field names:
-- It is always preferable to use only alphanumeric characters, no spaces, and the underscore when naming fields and to avoid using any reserved/token/keywords in the fields:
-- Access 2007 reserved words and symbols
-- AllenBrowne- Problem names and reserved words in Access