DCount error

[Benjix1991]

Hi all,



Apologies if this has been asked, I have searched for a solution to no avail and I'm at hair pulling stage.

In a form, I have an unbound box [Forms]![main]![text21] (call it DOB) which has an input mask 00/00/0000 for a date.

I want to pull that date and use it in DCount along the lines of DCount("X", "Customer", "[birthDate] = '#' & Format(DOB, "DD/mm/yyyy") & " '#") or some combination

The real trouble I'm.having is formatting the date to be searched Format(DOB, "DD/mm/yyyy") as this generates an overflow 6 error and I can't figure for the life of me how to workaround. May be cause it's late and brain fog but I'd appreciate any help!

Thanks,
Ben

[John_G]

MS Access uses the American M-D-Y notation for dates when used in WHERE clauses (among other places).

Try using Format(DOB, "mm/DD/yyyy") in your Dcount.

Your DCount syntax isn't quite right, either. Try:

DCount("X", "Customer", "[birthDate] = #" & Format(DOB, "mm/DD/yyyy") & "#")

The date is delimited by the # characters; you didn't need the single quotes.

[Benjix1991]

MS Access uses the American M-D-Y notation for dates when used in WHERE clauses (among other places).

Try using Format(DOB, "mm/DD/yyyy") in your Dcount.

Your DCount syntax isn't quite right, either. Try:

DCount("X", "Customer", "[birthDate] = #" & Format(DOB, "mm/DD/yyyy") & "#")

The date is delimited by the # characters; you didn't need the single quotes.

Hi John,

I figured the quotes maybe weren't correct but when I input #29/07/1991# the DCount operation works fine. When I plop the field in, I get a type mismatch and when I try format the field to either "DD/mm/yyyy" or even when I tried "mm/DD/yyyy" I get the control flow error 6.

I worked back from this and it is the format operation that is causing the overflow error but I don't get why? I printed the field to the immediate window and the DOB displays as ddmmyyyy.

Honestly, so confused!

Thanks,
Ben

[isladogs]

I figured the quotes maybe weren't correct but when I input #29/07/1991# the DCount operation works fine. When I plop the field in, I get a type mismatch and when I try format the field to either "DD/mm/yyyy" or even when I tried "mm/DD/yyyy" I get the control flow error 6.

#29/07/1991# will work fine as the date is unambiguous
However using #12/07/1991# as 12 July 1991 will be read as 7 Dec 1991 by Access SQL
That's why you need the Format code to ensure its always read correctly

Overflow errors normally occur when a value is too large for a datatype.
For example when using e.g. integer number and the value is over the integer limit of 35000 or so. In such cases, changing to long integer fixes it.
It shouldn't be relevant for date fields
Your BirthDate field SHOULD be Date/Time datatype. If not change it!

Then the code below given by john_g should work without error

DCount("X", "Customer", "[BirthDate] = #" & Format(DOB, "mm/dd/yyyy") & "#")

If not, remove the input mask from your textbox & set its format property as Short Date

[June7]

What is your system setting for date style? See if this helps http://allenbrowne.com/ser-36.html

[Minty]

I would also remove the input mask, and set the format in the text box to short date, this will make sure a date in your locale windows setting is used. I suspect its storing it treating it as a string or wierd number.

[John_G]

It might be that it is neither the DCount(...) nor the Format(...) that is the problem. I have tried all sorts of combinations and I can't get either function to give me an Error 6 - overflow.

I'm thinking that maybe it is what you are doing with the result of the Dcount. How are you using the Dcount, i.e. in what context? The Error 6 suggests that maybe you are using the Dcount in the denominator (bottom) of a division somewhere, and if the DCount returns a 0, you will get Error 6 - overflow.

[isladogs]

Whilst you may be correct that its the next line after DLookup causing the issue, division by zero is error 11.
It also shouldn't be caused by an incorrect datatype as that's error 13.