Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query dates (Access03)

    Trying to get a query to report a class date only if it is within the last two years. My criteria is

    >=Now()-(DateSerial(Year([tblSession.DateOfClassStart])-2,Month([tblSession.DateOfClassStart]),Day([tblSession.DateOfClassStart])))

    But I am getting dates of 03 etc.

    What am I missing?

    Thank you. Fay

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query dates (Access03)

    How about:

    ClassDate > = DateAdd("yyyy",-2, Date())
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query dates (Access03)

    Thank you so much, it worked. I haven't used that expression before. Fay

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query dates (Access03)

    I converted the query into SQL, but there is an error in the following code that I can't find. When I run the code the first section creates the table, but.... It creates the CPR field but not the ACLS field. The ACLS correct date is shown in the CPR field. If there is no ACLS date the following shows up.

    ) AS ACLS, IIf(([ClassName] Like 'BLS*'),[tblSession].[DateOfClassStart],

    I then get an error message about not having equal fields at the designation when it gets to the second part of the code below. That is because there isn't a ACLS filed created.

    Thank you for your help. Fay

    ' SQL for make-table query
    strSQL = " SELECT tblLearners.LearnerID, tblLearners.LastName, tblLearners.NickName, tblClasses.ClassName, " & _
    "tblSession.DateOfClassStart, IIf(([ClassName] Like 'ACLS*'),[tblSession].[DateOfClassStart],"") AS ACLS, " & _
    "IIf(([ClassName] Like 'BLS*'),[tblSession].[DateOfClassStart],"") AS CPR INTO tblACLSClassPrerequisites " & _
    "FROM tblLearners INNER JOIN ((tblClasses INNER JOIN tblSession ON tblClasses.ClassID = tblSession.ClassID) " & _
    "INNER JOIN tblRegistrationLearner ON tblSession.ClassNumber = tblRegistrationLearner.ClassNumber) " & _
    "ON tblLearners.LearnerID = tblRegistrationLearner.LearnerID " & _
    "WHERE (((tblClasses.ClassName) Like 'ACLS*') AND ((tblSession.DateOfClassStart)>=DateAdd('yyyy',-2,Date()))) OR (((tblClasses.ClassName) Like 'BLS*') " & _
    "AND ((tblSession.DateOfClassStart)>=DateAdd('yyyy',-2,Date()))) "

    ' Execute it
    DoCmd.RunSQL strSQL

    ' SQL for append query

    strSQL = "INSERT INTO tblACLSClassPrerequisites ( LearnerID, LastName, NickName, ClassName, DateOfClassStart, ACLS, CPR )" & _
    "SELECT tblCertificationDates.LearnerID, tblLearners.LastName, tblLearners.NickName, tblCertificationDates.Type AS ClassName," & _
    "tblCertificationDates.Date AS DateOfClassStart, IIf(([ClassName] Like 'ACLS*'),[Date],"") AS ACLS, " & _
    "IIf(([ClassName] Like 'BLS*'),[Date],"") AS CPR" & _
    "FROM tblCertificationDates INNER JOIN tblLearners ON tblCertificationDates.LearnerID = tblLearners.LearnerID" & _
    "WHERE (((tblCertificationDates.Type) Like 'ACLS*') And ((tblCertificationDates.Date) >= DateAdd('yyyy', -2, Date)))" & _
    "or (((tblCertificationDates.Type) Like 'BLS*') And ((tblCertificationDates.Date) >= DateAdd('yyyy', -2, Date)))"

    ' Execute it
    DoCmd.RunSQL strSQL

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query dates (Access03)

    If ACLS is a date field, then this bit of your code is causing the error for 2 reasons:

    IIf(([ClassName] Like 'ACLS*'),[Date],"") AS ACLS

    First of all, since this bit of code is embedded in a string already delinated by double=quotes, you must use single quotes to represent a zero-length string.

    Secondly, it appears ACLS is a date field, and therefore won't accept a zero-length string anyway! Use Null instead.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query dates (Access03)

    Thanks Mark the first part of the code works correctly. I put Null in place of the "" in pot parts of the code. Now I am getting a 3067 error message "Query input must contain at least one table or query" with the second part of the the code. I have both a location, a table, and a from location in the code.

    strSQL = "INSERT INTO tblACLSClassPrerequisites ( LearnerID, LastName, NickName, ClassName, DateOfClassStart, ACLS, CPR )" & _
    "SELECT tblCertificationDates.LearnerID, tblLearners.LastName, tblLearners.NickName, tblCertificationDates.Type AS ClassName," & _
    "tblCertificationDates.Date AS DateOfClassStart, IIf(([ClassName] Like 'ACLS*'),[Date],Null) AS ACLS, " & _
    "IIf(([ClassName] Like 'BLS*'),[Date],Null) AS CPR" & _
    "FROM tblCertificationDates INNER JOIN tblLearners ON tblCertificationDates.LearnerID = tblLearners.LearnerID" & _
    "WHERE (((tblClasses.ClassName) Like 'ACLS*') AND (Not (tblSession.DateOfClassStart)>Now() And (tblSession.DateOfClassStart)>=DateAdd('yyyy',-2,Date()))) " & _
    "OR (((tblClasses.ClassName) Like 'BLS*') AND (Not (tblSession.DateOfClassStart)>Now() And (tblSession.DateOfClassStart)>=DateAdd('yyyy',-2,Date())))"


    Thanks for the help.Fay

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query dates (Access03)

    Do you actually have a field that is named Date?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query dates (Access03)

    The field is called DateOfClassStart in the first second of code. It is officially called Date in the table that the second part of the code comes from. I put this in the second piece of code "tblCertificationDates.Date AS DateOfClassStart" seen in the previous message. It works when I run it as a query using the grid.

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query dates (Access03)

    Put this line of code immediately after you fill strSQL:

    debug.print strSQL

    This will appear in the immediate window of the code window. Look at it to see if you have any apparent problems. If you can't see anything, then create a new query and copy this string to it and try to run it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query dates (Access03)

    Your SQL string is lacking some spaces in important places. Insert a space before FROM and before WHERE:

    strSQL = "INSERT INTO tblACLSClassPrerequisites ( LearnerID, LastName, NickName, ClassName, DateOfClassStart, ACLS, CPR )" & _
    "SELECT tblCertificationDates.LearnerID, tblLearners.LastName, tblLearners.NickName, tblCertificationDates.Type AS ClassName," & _
    "tblCertificationDates.Date AS DateOfClassStart, IIf(([ClassName] Like 'ACLS*'),[Date],Null) AS ACLS, " & _
    "IIf(([ClassName] Like 'BLS*'),[Date],Null) AS CPR" & _
    " FROM tblCertificationDates INNER JOIN tblLearners ON tblCertificationDates.LearnerID = tblLearners.LearnerID" & _
    " WHERE (((tblClasses.ClassName) Like 'ACLS*') AND (Not (tblSession.DateOfClassStart)>Now() And (tblSession.DateOfClassStart)>=DateAdd('yyyy',-2,Date()))) " & _
    "OR (((tblClasses.ClassName) Like 'BLS*') AND (Not (tblSession.DateOfClassStart)>Now() And (tblSession.DateOfClassStart)>=DateAdd('yyyy',-2,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
  •