Dcount with date and user criteria

[mcktigger]

Hi All

I have a continuous form which sale staff use to view and schedule calls. This works fine but I now need to limit the number of calls a member of staff can schedule for a particular date to 15. If someone tries to schedule a call and they already have 15 calls for that day a msgbox needs to be displayed and the change cancelled. I thought a Dcount similiar to below would do the job but I can't get this to work.

If I do the Dcount using just the date I get an unexpected number returned not the number of records in the query.

intcount = DCount("*", "QryViewCalls", "[NextCall] = [Forms]![FrmViewCalls]![NextCall] and [CreatedBy] = CurrentUser()")
If intcount => 15 Then
MsgBox "xxxxxx"
DoCmd.CancelEvent

I'd really appreciate any assistance offered.

Thanks

 

[jzwp22]

Since the form value and the user are variables, they cannot be included in the double quotes. Assuming that nextcall is a date field, the value from the form has to be delimited by # signs. Similarly, if the CreatedBy field is text, the value supplied via CurrentUser() has to be delimited by single quotes.

intcount = DCount("*", "QryViewCalls", "[NextCall] =#" & [Forms]![FrmViewCalls]![NextCall] & "# and [CreatedBy] ='" & CurrentUser() & "'")

If the CreatedBy field is numeric, no delimiters would be needed.

You might also add a debug.print statement so that you can verify the value of intcount. This prints the value of intcount to the VBA immediate window


intcount = DCount("*", "QryViewCalls", "[NextCall] =#" & [Forms]![FrmViewCalls]![NextCall] & "# and [CreatedBy] ='" & CurrentUser() & "'")
debug.print intcount
If intcount => 15 Then
MsgBox "xxxxxx"
DoCmd.CancelEvent

 

[mcktigger]

Hi

Thanks for the reply worked perfectly. One day I'll get my head around how the syntax works.

Thanks again

 

[jzwp22]

You're welcome. The domain aggregate functions are a little tricky; I still mess them up from time to time.