Strange Error - Result DCount

[Nill.GO]

It's returning me every day the value = 3 instead of being "0" (zero) if you don't have any output for the current day.


Function load_exit()
Dim IntS As Integer

IntS = DCount("[exitTypeID]", "[tbCustomers]", "Format([exitDate],'dd/mm/yyyy') = Format(Date(),'dd/mm/yyyy') And [exitTypeID] = 2 or [exitTypeID] = 3 or [exitTypeID] = 5")
Forms![FrmMenu].lblexitTotal.Caption = IntS

End Function

 

[theDBguy]

Hi. Welcome to AWF!

Try using:

Format(FieldName or Date(), 'yyyy-mm-dd')

 

[moke123]

your criteria should read like "Where [ExitDate] = 1/5/2023"

"[exitDate] = #" & Format(Date(),'yyyy/mm/dd') & "#"

 

[CJ_London]

you need some brackets to split the and's and or's

"Format([exitDate],'dd/mm/yyyy') = Format(Date(),'dd/mm/yyyy') And ([exitTypeID] = 2 or [exitTypeID] = 3 or [exitTypeID] = 5)"

you coud simplify your code

"Format([exitDate],'dd/mm/yyyy') = Format(Date(),'dd/mm/yyyy') And [exitTypeID] in (2,3,5)"

agree with moke's suggestion but pretty sure

"[exitDate]=date() and [exitTypeID] in (2,3,5)"

would work as well

 

[Pat Hartman]

There is no "WHERE" in the criteria part of a domain function.

 

[moke123]

There is no "WHERE" in the criteria part of a domain function.

Yea, that doesn't read the way I meant it. Meaning the criteria portion should read like a where clause , not contain "where" Thought the example was clear.

 

[cheekybuddha]

but pretty sure

"[exitDate]=date() and [exitTypeID] in (2,3,5)"

I'm guessing that all the Format()'ing is to try and deal with the time portion of [exitDate]

So you could use:

"Int([exitDate])=date() and [exitTypeID] in (2,3,5)"

Or for more efficient searching if [exitDate] is indexed:

"([exitDate] >= Date() And [exitDate] < Date() + 1) AND [exitTypeID] IN (2,3,5)"