• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 931
  • Last Modified:

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
0
ceramicpc
Asked:
ceramicpc
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
 
ceramicpcAuthor Commented:
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
0
 
Helen FeddemaCommented:
Perhaps you could use a totals query instead, with a Count on the field, instead of DCount.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JVWCCommented:
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, :-)
0
 
ceramicpcAuthor Commented:
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
0
 
ceramicpcAuthor Commented:
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.

0
 
JVWCCommented:
Hi CeramicPC,
In Access, SQL only recognises US date format, which is why I always throw it into a variable in code.
UK format (and in my case, AU) works almost everywhere else in Access according to your locale, but not in SQL.
Another tip is to include the # when you assign the date to the variable. This makes for prettier code when you you use it later...

0
 
JVWCCommented:
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
0
 
JVWCCommented:
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
0
 
ceramicpcAuthor Commented:
Hi JVWC,

Many thanks for you comments, seems this is one of those Microsoft got ya's that I'll put down to experence!
0
 
JVWCCommented:
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.

0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now