asked on

Access VBA DCount() on SQL 2008 server table

I'm having a weird problem when using a DCount() function in Access 2003 VBA.

I'm trying to use a DCount() to check whether there are related records in a table but for dates > 01/01/2010 DCount() function just returns a 0.

For dates <= 01/01/2010 it works fine.

I'm using MS Access 2003 SP3 with a linked table to a SQL Server 2008 Express

The data type in SQL server is dateTime

I've also created a test table with just a few dates in 2009 and a few in 2010 and DCount() exhibits exactly the same behaviour.

Just to confirm that I'm using the correct syntext my VBA code is;

MsgBox DCount("[TestDate]", "dbo_tbltest", "[TestDate] = #02/01/2010#")

Can anyone else confirm this or offer some suggestions?

Thanks for looking

Scott McDaniel (EE MVE )

Your syntax is wrong for SQL Server. It should be:

MsgBox DCount("[TestDate]", "dbo_tbltest", "[TestDate] = '02/01/2010'")

Access uses the hash marks (#), while SQL Server uses single quotes

ceramicpc

ASKER

Thanks for your reply.

I tried the revised syntext but I get the error;

'Data type mismatch in criteria expression. (Error 3464)'

If the syntax is incorrect why does it work ok with dates before 02/01/2010 ?

BTW I've also tried

MsgBox DCount("[TestDate]", "dbo_tbltest", "[TestDate] = 02/01/2010")

which runs but has the same issue as the code with the # marks

Helen Feddema

Perhaps you could use a totals query instead, with a Count on the field, instead of DCount.

JVWC

Instead of
MsgBox DCount("[TestDate]", "dbo_tbltest", "[TestDate] = #02/01/2010#")
try MsgBox DCount("[TestDate]", "dbo_tbltest", "[TestDate] > #01/01/2010#")

Also try declaring a variable and assigning the date to be tested, then use ...[Testdate] > vardate...
If you are from Australia, like me, you should also assign the date using format().
Otherwise the function does not know if that was 1 Feb 2010 or 2 Jan 2010, :-)

ceramicpc

ASKER

Good suggestion,
This is what I tried;

Dim dteTestDate As Date
dteTestDate = Format(CDate("11/01/2010"), "dd/MM/yyyy")
MsgBox dteTestDate
'MessageBox result = 11/01/2010
MsgBox DCount("[TestDate]", "dbo_tblTest", "[TestDate] = #" & dteTestDate & "#")
'MessageBox result = 0
MsgBox DCount("[TestDate]", "dbo_tblTest", "[TestDate] = " & dteTestDate)
'MessageBox result = 0
*******
If I use a date though before 2010 then;

Dim dteTestDate As Date
dteTestDate = Format(CDate("23/12/2009"), "dd/MM/yyyy")
MsgBox dteTestDate
'MessageBox result = 23/12/2009
MsgBox DCount("[TestDate]", "dbo_tblTest", "[TestDate] = #" & dteTestDate & "#")
'MessageBox result = 3
MsgBox DCount("[TestDate]", "dbo_tblTest", "[TestDate] = " & dteTestDate)
'MessageBox result = 3
I attach my test data if you're intrested.
BTW I also moved the data from SQL server to a local table in access but the results were the same.

Helen_Feddema "Perhaps you could use a totals query instead, with a Count on the field, instead of DCount."
I guess I could but I would rather avoid creating a Access query just for this operation. Unless I could write one in VBA / SQL ? My SQL coding not that good ;-)

testdb.xls

ceramicpc

ASKER

Ok, i've found that if I put the date in the US format i.e MM/dd/yyy then it works;

The following works ok;

Dim dteTestDate As Date

dteTestDate = Format(CDate("01/11/2010"), "dd/MM/yyyy")

MsgBox dteTestDate

'MessageBox result = 01/11/2010

MsgBox DCount("[TestDate]", "LocaltblTest", "[TestDate] = #" & dteTestDate & "#")

'MessageBox result = 3

Anyone got a suggestion why this is happening? I've checked the regional settings in Windows and they are all UK English.

ASKER CERTIFIED SOLUTION

JVWC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JVWC

Just had a peek at your data...
Try adding (say) 2x 2/1/2010 as well as the 3x 1/2/2010 without the US format and see what happens :-)
Cheers

JVWC

Something to look at:
http://support.microsoft.com/kb/142009
http://support.microsoft.com/kb/149095/EN-US/

The second one is very handy if you use dates a lot.
Cheers

ceramicpc

ASKER

Hi JVWC,

Many thanks for you comments, seems this is one of those Microsoft got ya's that I'll put down to experence!

JVWC

Yep,
And just to add insult to injury, the MSGBOX results you relied on: probably used Locale to display your coded date...
Dates are fun if you're not in the US, eh?
Cheers.