Code data mismatch

Topic

New Reply

Trouble with code getting data mismatch. I can’t see it. Can you help?

SELECT tblCertificationDates.Date, tblCertificationTypes.Renewed, DateSerial(Year([tblCertificationDates.Date])+([Renewed]),Month([tblCertificatio
nDates.Date]),Day([tblCertificationDates.Date])) AS Due, tblCertificationDates.LearnerID, tblCertificationDates.Type
FROM tblCertificationTypes INNER JOIN tblCertificationDates ON tblCertificationTypes.Certifications = tblCertificationDates.Type
WHERE (((DateSerial(Year([tblCertificationDates.Date])+([Renewed]),Month([tblCertifica
tionDates.Date]),Day([tblCertificationDates.Date])))>=Now()));

Thank you. Fay

Reply | Quote

Replies

Chances are it has to do with the date calculation you are doing. You refer repeatedly to tblCertificationDates.Date – is that really specified as a Date field in the table design? If so your DateSerial calculation is essentially recreating the value of Date as a Date field. And a suggestion – rename Date to something else – Date is sort of a reserved word in Access becasue of the Date() function that is used to return the current date. I would name it dtCertificationDate or something like that. And the fact that your query has [tblCertificationDates.Date] suggests that something is amok in your SQL string. That should normally be [tblCertificationDates].[Date]
Hope this helps.

Reply | Quote

You’d also have a problem if there are records with a blank (null) date.

Reply | Quote