Date format for WHERE clause (1 Viewer)

matt_t

New member
Local time
Today, 12:37
Joined
Sep 7, 2016
Messages
9
I'm converting an Access project (adp) over to a linked table (accdb) front end with an ODBC connection to the SQL Server data and am having problems opening reports that have date criteria in their WHERE clause.

I've tried the following syntax in the WHERE clause:

ReportDate > #dd/mm/yyyy#
ReportDate > #yyyy/mm/dd#
ReportDate > #yyyy-mm-dd#

All three formats work on my development pc, but when I compile it and try to open the reports on a users workstation I get the following error for all three formats:

ODBC--call failed.
[Microsoft][SQL Native Client][SQL Server]Conversion failed when converting datetime from character string.(#241)

I'm using Access 2007 on Windows 7, connecting to SQL Server 2005.

Any ideas what the problem might be?

Matt
 

Minty

AWF VIP
Local time
Today, 19:37
Joined
Jul 26, 2013
Messages
10,355
Have the user workstations got the same sql clients loaded?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:37
Joined
Aug 30, 2003
Messages
36,118
Sounds like SQL Server is processing the request, which would mean the delimiter should be ' instead of #. I think the second format would work best.
 

matt_t

New member
Local time
Today, 12:37
Joined
Sep 7, 2016
Messages
9
Have the user workstations got the same sql clients loaded?

Sorry, by sql clients do you mean front ends? They are using the same front end, after compiling it and copying it to their machines as they use Access Runtime, connecting to the same sql database.

The compiled front end works on my pc too, under both Runtime and full Access.
 

matt_t

New member
Local time
Today, 12:37
Joined
Sep 7, 2016
Messages
9
Sounds like SQL Server is processing the request, which would mean the delimiter should be ' instead of #. I think the second format would work best.

I agree, it does sound like SQL is processing it somehow. I've tried it using apostrophes instead of the hash key, in all combinations of day, month and year, with slashes and hyphens. Whatever order/delimiter I use I always get the error:

Data type mismatch in criteria expression.
 

Minty

AWF VIP
Local time
Today, 19:37
Joined
Jul 26, 2013
Messages
10,355
Are your dates actually stored as dates or datetimes in the sql tables ?
 

Minty

AWF VIP
Local time
Today, 19:37
Joined
Jul 26, 2013
Messages
10,355
Can you actually view / update the tables on the users machines - does everything else work ?

My earlier comment about the SQL client was directed at the ODBC connection properties, are they hard coded or do you set up a DSN's on the client machine?
 

matt_t

New member
Local time
Today, 12:37
Joined
Sep 7, 2016
Messages
9
Can you actually view / update the tables on the users machines - does everything else work ?

Yes, the data is updatable and just about everything else works on the users machine. It's quite a large database with over 200 tables and 700 views, so I'd quickly notice if things were not right. I can open reports with other criteria in the where clause, it's just dates that are the problem.

My earlier comment about the SQL client was directed at the ODBC connection properties, are they hard coded or do you set up a DSN's on the client machine?

ODBC is where my knowledge isn't so great. I've always used projects in the past but am having to convert to ODBC in order to upgrade to a newer sql server version. I've installed the sql native client 9.0 driver on the workstation (the same as on my dev pc) and the linked tables keep the DSN settings I set when I linked the tables.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:37
Joined
Aug 30, 2003
Messages
36,118
Where exactly is this? In a query criteria, or VBA code wherecondition, or? What date are you using? The type mismatch with apostrophes puts us in Access. I work with SQL Server all the time, but I've never used an ADP.
 

matt_t

New member
Local time
Today, 12:37
Joined
Sep 7, 2016
Messages
9
Where exactly is this? In a query criteria, or VBA code wherecondition, or? What date are you using? The type mismatch with apostrophes puts us in Access. I work with SQL Server all the time, but I've never used an ADP.

It's in a VBA code where condition, as per:
DoCmd.OpenReport "ReportName", acViewPreview, , "SomeDateField > #29/08/2016#"
or
DoCmd.OpenReport "ReportName", acViewPreview, , "SomeDateField > #2016-08-29#"

It worked fine in the compiled adp, but not the accdb.
 

Minty

AWF VIP
Local time
Today, 19:37
Joined
Jul 26, 2013
Messages
10,355
That's very strange... You haven't got the SomeDateField in the reports underlying query formatted in some way have you ?
 

matt_t

New member
Local time
Today, 12:37
Joined
Sep 7, 2016
Messages
9
That's very strange... You haven't got the SomeDateField in the reports underlying query formatted in some way have you ?

I've solved it now thanks. Although I know how, I still don't know what the problem was. I installed the Office suite on the user pc and the reports magically worked. Weird!

Thank you very much for your assistance.

Matt
 

matt_t

New member
Local time
Today, 12:37
Joined
Sep 7, 2016
Messages
9
The first won't work properly in VBA:

You're right, it wasn't working properly, but there was no error. It switched the day and month around as the month wasn't valid. When I ran it for 07/09/2016 it was using the 9th day of July.

I can't think why installing the Office suite solved it, but it did.

Many thanks for your help.

Matt
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:37
Joined
Aug 30, 2003
Messages
36,118
That's because 7/9 is the 9th of July. :p

Glad you got it sorted.
 

Users who are viewing this thread

Top Bottom