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

 

[Brendan Reynolds]

One possibility is Null values in the fields dbo_calendarrandom.dt or
CompletedPerDay.CompletedDate.

 

[samuelgreene]

Yes, there are definitely null values in the completedDate field.

 

[samuelgreene]

I got rid of null dates and did the join on the created date - which
shouldn't have had nulls. It had 5 in there for some reason. Still
getting the error.

 

[Brendan Reynolds]

'CompletedPerDay' is a query, not a table, right? Possibly there might be
something in the SQL for that query that is causing Access to misinterpret
the data type. Could you post the SQL for that query?

 

[samuelgreene]

'CompletedPerDay' is a query, not a table, right? Possibly there might be
something in the SQL for that query that is causing Access to misinterpret
the data type. Could you post the SQL for that query?

yes, it is a query, not a table. Here is the SQL for CompletedPerDay

SELECT Count(Problems.ProblemID) AS CountOfProblemID,
Problems.[Assigned To], Format([Problems]![Date
Completed],"\#mm\/dd\/yyyy\#") AS CompletedDate
FROM Technician INNER JOIN Problems ON Technician.[Tech Name] =
Problems.[Completed By]
WHERE (((Technician.HWtech)<>0) AND ((Problems.[Date
Completed])>[beginning date] And (Problems.[Date Completed])<[end
date]) AND ((Problems.Customers_Pending)=2))
GROUP BY Problems.[Assigned To], Format([Problems]![Date
Completed],"\#mm\/dd\/yyyy\#");


Assigned per day

SELECT Count(Problems.ProblemID) AS CountOfProblemID,
Problems.[Assigned To],
Format([Problems]![Date/Time],"\#mm\/dd\/yyyy\#") AS AssignedDate
FROM Problems INNER JOIN Technician ON Problems.[Assigned To] =
Technician.[Tech Name]
WHERE (((Technician.HWtech)<>0) AND ((Problems.[Date/Time])>[beginning
date] And (Problems.[Date/Time])<[end date]))
GROUP BY Problems.[Assigned To],
Format([Problems]![Date/Time],"\#mm\/dd\/yyyy\#"),
Problems.Customers_Pending
HAVING (((Problems.Customers_Pending)=2))
ORDER BY Problems.[Assigned To],
Format([Problems]![Date/Time],"\#mm\/dd\/yyyy\#");


and the two combined and joined to calendarrandom - Assigned and
Completed:

SELECT AssignedPerDay.CountOfProblemID AS AssignedPerDay,
CompletedPerDay.CountOfProblemID AS CompletedPerDay,
AssignedPerDay.AssignedDate, AssignedPerDay.[Assigned To],
Format([dbo_calendarrandom]![dt],"\#mm\/dd\/yyyy\#") AS mydt
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 =
AssignedPerDay.AssignedDate
ORDER BY AssignedPerDay.AssignedDate, AssignedPerDay.[Assigned To];


Thanks Again!
Sam

 

[Brendan Reynolds]

There you go. The use of the Format function is causing the database engine
to see CompletedDate and AssignedDate as text instead of as dates.

--
Brendan Reynolds
Access MVP

'CompletedPerDay' is a query, not a table, right? Possibly there might be
something in the SQL for that query that is causing Access to
misinterpret
the data type. Could you post the SQL for that query?

yes, it is a query, not a table. Here is the SQL for CompletedPerDay

SELECT Count(Problems.ProblemID) AS CountOfProblemID,
Problems.[Assigned To], Format([Problems]![Date
Completed],"\#mm\/dd\/yyyy\#") AS CompletedDate
FROM Technician INNER JOIN Problems ON Technician.[Tech Name] =
Problems.[Completed By]
WHERE (((Technician.HWtech)<>0) AND ((Problems.[Date
Completed])>[beginning date] And (Problems.[Date Completed])<[end
date]) AND ((Problems.Customers_Pending)=2))
GROUP BY Problems.[Assigned To], Format([Problems]![Date
Completed],"\#mm\/dd\/yyyy\#");


Assigned per day

SELECT Count(Problems.ProblemID) AS CountOfProblemID,
Problems.[Assigned To],
Format([Problems]![Date/Time],"\#mm\/dd\/yyyy\#") AS AssignedDate
FROM Problems INNER JOIN Technician ON Problems.[Assigned To] =
Technician.[Tech Name]
WHERE (((Technician.HWtech)<>0) AND ((Problems.[Date/Time])>[beginning
date] And (Problems.[Date/Time])<[end date]))
GROUP BY Problems.[Assigned To],
Format([Problems]![Date/Time],"\#mm\/dd\/yyyy\#"),
Problems.Customers_Pending
HAVING (((Problems.Customers_Pending)=2))
ORDER BY Problems.[Assigned To],
Format([Problems]![Date/Time],"\#mm\/dd\/yyyy\#");


and the two combined and joined to calendarrandom - Assigned and
Completed:

SELECT AssignedPerDay.CountOfProblemID AS AssignedPerDay,
CompletedPerDay.CountOfProblemID AS CompletedPerDay,
AssignedPerDay.AssignedDate, AssignedPerDay.[Assigned To],
Format([dbo_calendarrandom]![dt],"\#mm\/dd\/yyyy\#") AS mydt
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 =
AssignedPerDay.AssignedDate
ORDER BY AssignedPerDay.AssignedDate, AssignedPerDay.[Assigned To];


Thanks Again!
Sam