VBA dcount problem

[SmallTime]

Hi,

I can't seem to understand what's amiss here and would be grateful if someone could cast an eye over this and perhaps see what I can't.

I'm getting two different results when using a select query with totals and DCount in vba when I thought I should get the same result.

The query returns 19 which is what I expected and going through the source data is correct

SELECT Count([All Referrals].Received) AS CountOfReceived
FROM [All Referrals]
WHERE ((([All Referrals].Received) Between [Forms]![FrmWhatHappened]![TxtStartDate] And [Forms]![FrmWhatHappened]![TxtEndDate]));

VBA which returns 21 and is wrong

Dim NewCaseCount As Integer

NewCaseCount = DCount("[ReferralID]", "[All Referrals]", "[Received] between #" & Forms![FrmWhatHappened]![TxtStartDate] & "# AND #" & Forms![FrmWhatHappened]![TxtEndDate] & "#")

What am I missing in the VBA that's giving me the wrong count, I just can't figure it out.


SmallTime

 

[Isskint]

Could this be to do with local settings? When you interpret dates in Query I have noticed they follow the PCs local date/time settings BUT when i go through VBA they follow Access default (American i guess). Check for a value of 2 in your data and see if the date can be interpretted as Month/Day OR Day/Month. EG 2 may is 05/02 in US but in UK is 02/05

 

[SmallTime]

Good thought, Just tested 08/07/2012 and got 08 July 2012 which is what it should be (no american day\month mix-up). Worth a shot but still a little baffled.

Cheers

 

[bob fitz]

Try:

Dim NewCaseCount As Integer
 
NewCaseCount = DCount("[ReferralID]", "[All Referrals]", "[Received] between #" & Format(Forms![FrmWhatHappened]![TxtStartDate],"mm/dd/yyyy") & "# AND #" & Format(Forms![FrmWhatHappened]![TxtEndDate],"mm/dd/yyyy") & "#")

 

[SmallTime]

Hmmm, I say Hmmm as I scratch my head.

Now why did VBA return the correct date following Isskint's suggestion when I tested for dd/mm against mm/dd. However, your suggestion does the trick. formatting in mm/dd/yyyy returned the correct count of 19, yet formatting in dd/mm/yyyy doesn't.

Just when I thought I was getting to grips with things nothing makes sense anymore.

Many thanks for the help guys.
SmallTime

 

[bob fitz]

You don't say exactly what test you did.
Take a look at the link below, in particular "2. Wrong Formatting in Code" :

http://allenbrowne.com/ser-36.html

 

[SmallTime]

The testing, which at the time I thought was reasonable was a number of variations on

Dim mydate As Date
mydate = DLookup("[Received]", "[All Referrals]", "[ReferralID] = 98")
MsgBox Format(mydate, "dd mmm yyyy")

(ReferralID 98 is shown in 'All Referrals' as 08/07/2012)

Just reading through the link you sent, and yes although I was mildly aware of the variations in date formats across the world and I assumed (wrongly) that if my system date format is set to UK Access would interpret dates in the correct format dd/mm. Or Maybe I was tricked into believing this to be the case since using the 'between' operator in expression builder without formatting the dates has been returning the correct results.

Oh well, the more I learn the the less I know and the harder it seems to get. Ignorance is sometimes bliss.


Many thanks
SmallTime

 

[bob fitz]

Oh well, the more I learn the the less I know and the harder it seems to get. Ignorance is sometimes bliss.

I've known this feeling so many times.