DLOOKUP with multiple date criteria one of which is function

[kdwoell]

Please..pls...pls someone fix this pls ...before I slit my wrists and gun down my co-workers......

Me.txtCurrentUsage = DLookup("[numUsers]", "tableSystemUsage", "[systemID] = " & Me![cboSelectSystem].Column(1) And DateValue([usageDate])=#7/15/2008#")

ERROR: "compile error - expect list separator or )"

table: tableSystemUsage
field: systemID (number)
field: usageDate (date/time)
field: numUser (number)

-systemID and usageDate are an Access composite key.
-I need to retrieve the number of system users for a given unique combo of systemID and MAX of usageDate
-I hard coded the date for now to simplify the stupid criteria expresssion.

signed...
"*#(*&jelkJ0934987&"

 

[kdwoell]

I'm persistent-this works. Key was to remove single quotes:

Broke: '"[systemID]=" & Me![cboSelectSystem].Column(1) & "'
Works: "[systemID]=" & Me![cboSelectSystem].Column(1) & "

Me.txtCurrentUsage = DLookup("[numUsers]", "tableSystemUsage", "[systemID]=" & Me![cboSelectSystem].Column(1) & " and DateValue([usageDate])=#7/15/2008#")

Now, If I can just return the max date for field "usageDate".

 

[CyberLynx]

Read the Access Help file with regards to the DMax function.

.