I have a problem with dates in queries.

  • Thread starter Thread starter P Van Veen
  • Start date Start date
P

P Van Veen

SELECT Schedule.ScheduleDate, Customers.ContactLastName,
Customers.BillingAddress, Customers.City, Customers.PhoneNumber,
Workorders.Make, Workorders.Model, Workorders.ProblemDescription,
nz(DateValue([ScheduleDate])) AS DateCheck, Weekday([ScheduleDate],2) AS
WeekDay, Schedule.WorkorderID, Schedule.EmployeeID, Schedule.Notes,
Customers.Ext, nz(DLookUp("ScheduleDate","Date Check"),Date()-1) AS
LastSched
FROM Customers INNER JOIN (Schedule LEFT JOIN Workorders ON
Schedule.WorkorderID = Workorders.WorkorderID) ON Customers.CustomerID =
Schedule.CustomerNum
WHERE (((nz(DateValue([ScheduleDate])))=DateValue(Date())) AND
((nz(DLookUp("ScheduleDate","Date Check"),Date()-1))<Date()))
ORDER BY Schedule.ScheduleDate;

When I run this query I get a Data Type Mismatch Error. If I remove this
portion of query it runs
OK --------------------------------------------WHERE
(((nz(DateValue([ScheduleDate])))=DateValue(Date()))

Thing is it was running just fine for last 3 years on multiple machines. I
have exported database objects to a fresh database and same problem. I have
also tried running on another machine where and it still fails???

Any help would certainly be greatly appreciated!

Pete Van Veen
 
Presumably you are using DateValue() because [ScheduleDate] contains a time
component you need to get rid of. And presumably you are using Nz() because
ScheduleDate may be null, and DateValue() can't handle Nulls.

The Nz() must be applied *before* the call to DateValue(), and it needs to
supply some actual date to prevent the Type Mismatch. Try:

WHERE DateValue(Nz([ScheduleDate], #1/1/9999#)) = Date()
 
Back
Top