Query dates (Access03)

Topic

New Reply

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

Reply | Quote

Replies

How about:

ClassDate > = DateAdd(“yyyy”,-2, Date())

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Do you actually have a field that is named Date?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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())))”

Reply | Quote