Dcount using date comparison (1 Viewer)

tfaiers · Mar 6, 2015

Hi Everyone,

I'm trying reference a dcount on two fields, one is a string and works fine, the other is on a date which does not.

I've investigated the problem and found a couple of references to making sure I use a # symbol to reference the date but I think the thing that's throwing the comparison out is that the date field stores time as well, even if you can't see it?

So my code:

Code:

MsgBox DCount("*", "[healthcheck]", "[check_date]=#" & Format([Forms]![MAINTENANCE_FRM]![MAINTENANCE_DETAIL_TBL subform].[Form]![FIRST_HEALTH]) & "#")

I thought should count the records in the healthcheck table that match the check_date field against the first_health field on the current form, but I keep getting a 0 count even though the dates seem to match.

Any thoughts?

plog · Mar 6, 2015

Your missing a #. Those need to go before and after the date variable. So after you reference your date variable you need to add this:

& "#"

tfaiers · Mar 6, 2015

Sorry Plog, I realised I'd missed the # at the end of the line when I pasted the code from the VBA screen, but the function still returns 0.

It also still returns 0 if the comparison =#02/03/2015# (which I tested as that's the date stored in the healthcheck, check_date field.

spikepl · Mar 6, 2015

I think the thing that's throwing the comparison out is that the date field stores time as well, even if you can't see it?

You are asking or you are stating? Because if one date contains the time element and the other doesn't then they'll not be equal, so yuo need to extract the date using DateValue

tfaiers · Mar 9, 2015

I meant that I'd read that date fields store the time even if you can't see it, but I'm not sure if it's true or not.

I've changed the coding again and added a couple of visual references to show me what's being found, with:

Code:

MsgBox DLookup("[check_date]", "[healthcheck]")
MsgBox [Forms]![MAINTENANCE_FRM]![MAINTENANCE_DETAIL_TBL subform].[Form]![FIRST_HEALTH]
MsgBox DCount("*", "[healthcheck]", "[check_date]=#" & DateValue([Forms]![MAINTENANCE_FRM]![MAINTENANCE_DETAIL_TBL subform].[Form]![FIRST_HEALTH]) & "#")

The first messagebox returns 02/03/2015
The second messagebox returns 02/03/2015
The Dcount returns 0

The logic doesn't make sense and I'm still scratching me head.

I've also tried:

Code:

MsgBox DCount("*", "[healthcheck]", "datevalue([check_date])=#" & DateValue([Forms]![MAINTENANCE_FRM]![MAINTENANCE_DETAIL_TBL subform].[Form]![FIRST_HEALTH]) & "#")

which still gives a dcount of 0, I must be missing something obvious, but I just can't see it.

edit:

I've just put this extra code in to store the dates as data1 and data2, which results in a positive match and the last messagebox shows a 1

Code:

Dim data1 As Date
Dim data2 As Date
MsgBox DLookup("[check_date]", "[healthcheck]")
data1 = DLookup("[check_date]", "[healthcheck]")
MsgBox [Forms]![MAINTENANCE_FRM]![MAINTENANCE_DETAIL_TBL subform].[Form]![FIRST_HEALTH]
data2 = [Forms]![MAINTENANCE_FRM]![MAINTENANCE_DETAIL_TBL subform].[Form]![FIRST_HEALTH]
MsgBox DCount("*", "[healthcheck]", "([check_date])=#" & DateValue([Forms]![MAINTENANCE_FRM]![MAINTENANCE_DETAIL_TBL subform].[Form]![FIRST_HEALTH]) & "#")
If data1 = data2 Then
MsgBox "1"
Else
MsgBox "0"
End If

tfaiers · Mar 9, 2015

Just to let you all know, I was unable to use the dates for comparison within the VBA code, in the end I've had to refer to queries instead which work fine.

I've got no explanation as to why the VBA doesn't work, but the problem has been worked around.

Many thanks for your help.

Solo712 · Mar 9, 2015

tfaiers said:
Just to let you all know, I was unable to use the dates for comparison within the VBA code, in the end I've had to refer to queries instead which work fine.

I've got no explanation as to why the VBA doesn't work, but the problem has been worked around.

Many thanks for your help.

What regional setting are you using on your Windows ? Access ecpects the date format to be #mm/dd/yyyy# .Best,Jiri

Dcount using date comparison (1 Viewer)

tfaiers

Registered User.

plog

Banishment Pending

tfaiers

Registered User.

spikepl

Eledittingent Beliped

tfaiers

Registered User.

tfaiers

Registered User.

Solo712

Registered User.

Similar threads

Users who are viewing this thread