Syntax error with where criteria

[craig1988]

Count = DCount("Reference", "tblLog", "[DateLogged] Between """ & strDate & "'" AND "'" & endDate")



I am getting a syntax error when this line of code tries to run. Can anyone point out where the error is? The variables strDate & endDate are declared and set to txt box values on a form.

[pbaldy]

Try

Count = DCount("Reference", "tblLog", "[DateLogged] Between #" & strDate & "# AND #" & endDate & "#")

[cyanidem]

AFAIK you can't use between for dates. Try instead:
Count = DCount("Reference", "tblLog", "[DateLogged] >=#" & strdate & "# And [DateLogged] <=#" & enddate & "#")

[pbaldy]

AFAIK you can't use between for dates.

?DCount("*","Manual", "RevisionDate Between #1/1/2015# And #12/31/2015#")
404

[craig1988]

No issues with the between!!

The issue was with the placing of the #'s and the "'s

Works great now. Thanks

[cyanidem]

Yeah, I was wrong, but I swear I read somewhere that "between" works only for numeric values, not dates. Maybe it was about SQL...?
I'll see myself out

[pbaldy]

Happy to help!

cyanidem, you might be thinking of the fact that Between doesn't work in VBA code directly, like:

If Blah Between 1 And 5 Then

It works in this situation because the DCount() is passed to the database engine to process. That's in layman's terms, because I don't know the actual technical terms.

[cyanidem]

Yeah, could be that, or something completely different Nevertheless I should get my facts straight before advising others.
But, on the plus side, my solution would work too