Type mismatch with Date - SQL Server Backend

[samuelgreene]

HI all,

I'm getting 'Type mismatch in expression' in a new query I'm creating
in Access. We have a SQL server backend. I have an existing query in
access and i'm trying to join to a table which contains some random
dates for some statistical analysis(first it was a view, but now it's
just a table). The date in the query is formatted in this manner :
Format([Problems]![Date/Time],"d/m/yyyy")

The date in the sql server table is stored as date/time.

AssignedPerDay CompletedPerDay AssignedDate Assigned To dt
4 4 1/10/2005 Chris 1/12/2005

I noticed i had the access data formatted dd/mm/yy which was creating
leading zeros on the single digit numbers, but i changed that and it's
still causing an error. I've created the relation in the relationship
window as MS said to do - it accepts it without error there....that
evidently is not actually testing it there.

thanks
Sam

SQL Server 2000
MS Access 2003

 

[Douglas J Steele]

Is this a pass-through query, or are you simply trying to load into a linked
table that points to your SQL Server?

If the latter, even though you're writing to SQL Server, you're using Jet,
and Jet delimits dates with #. As well, with Jet the dates must be in
mm/dd/yyyy format, regardless of what your short date format is set to.

Try changing your format to

Format([Problems]![Date/Time],"\#mm\/dd\/yyyy\#")

 

[samuelgreene]

Hi Doug,
First, I've seen you around these forums - thanks for helping, you seem
to be the man of a thousand answers.

I have a query which is made up of 2 other queries, which are based on
linked tables. I'm trying to join another linked table(eventually a
view) to this query.


I tried formatting all the dates with the #'s outside them - no luck,
still getting mismatch.
This one has me perplexed - normally type mismatches are easy.

Noticed i had smalldate types and changed them to datetime. Same
result.

Sam

 

[RoyVidar]

(e-mail address removed) wrote in message

Hi Doug,
First, I've seen you around these forums - thanks for helping, you seem
to be the man of a thousand answers.

I have a query which is made up of 2 other queries, which are based on
linked tables. I'm trying to join another linked table(eventually a
view) to this query.


I tried formatting all the dates with the #'s outside them - no luck,
still getting mismatch.
This one has me perplexed - normally type mismatches are easy.

Noticed i had smalldate types and changed them to datetime. Same
result.

Sam

It is hard to advice with only you telling what is happening. Could
you give us the code you're running, and a debug.print of the actual
SQL string?

 

[Douglas J Steele]

(e-mail address removed) wrote in message


It is hard to advice with only you telling what is happening. Could
you give us the code you're running, and a debug.print of the actual
SQL string?

That, and details about the fields involved.

 

[samuelgreene]

Here is the SQL:

SELECT AssignedPerDay.CountOfProblemID AS AssignedPerDay,
CompletedPerDay.CountOfProblemID AS CompletedPerDay,
AssignedPerDay.AssignedDate, AssignedPerDay.[Assigned To],
AssignedPerDay.AssignedDate, CompletedPerDay.CompletedDate
FROM dbo_calendarrandom INNER JOIN (AssignedPerDay INNER JOIN
CompletedPerDay
ON (AssignedPerDay.[Assigned To] = CompletedPerDay.[Assigned To])
AND (AssignedPerDay.AssignedDate = CompletedPerDay.CompletedDate))
ON dbo_calendarrandom.dt = CompletedPerDay.CompletedDate
ORDER BY AssignedPerDay.AssignedDate, AssignedPerDay.[Assigned To];

I don't know what you mean by debug.print - i'm in design view and just
trying to run it. I don't know how to do that besides in vba in a
form. If there is some kind of additional debugging info i could get,
I'd love to know

the Fields involved in the joins you see above are:

AssignedPerDay.AssignedDate (actually Problems.[Date/Time]) datetime
CompletedPerDay.CompletedDate (actually Problems.CompletedDate)
smalldatetime
dbo_calendarrandom.dt datetime

 

[Douglas J. Steele]

What happens if you remove dbo_calendarrandom from the query: does it work
then?

 

[samuelgreene]

yes - if i remove the join...if not it does a cross product of course