Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'd also have a problem if there are records with a blank (null) date.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •