?
Solved

Access VBA DCount() on SQL 2008 server table

Posted on 2010-01-11
11
Medium Priority
?
905 Views
Last Modified: 2013-11-27
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
Comment
Question by:ceramicpc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 85
ID: 26285921
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
 

Author Comment

by:ceramicpc
ID: 26286486
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26287664
Perhaps you could use a totals query instead, with a Count on the field, instead of DCount.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 6

Expert Comment

by:JVWC
ID: 26291231
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
 

Author Comment

by:ceramicpc
ID: 26292610
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
 

Author Comment

by:ceramicpc
ID: 26294046
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
 
LVL 6

Accepted Solution

by:
JVWC earned 2000 total points
ID: 26301140
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
 
LVL 6

Expert Comment

by:JVWC
ID: 26301181
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
 
LVL 6

Expert Comment

by:JVWC
ID: 26301243
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
 

Author Comment

by:ceramicpc
ID: 26301404
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
 
LVL 6

Expert Comment

by:JVWC
ID: 26301592
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question