Query for expiring items

[June7]

Whether you use a prompt or reference to textbox, the same parameter has to be under each date field on separate criteria rows as I originally described.



Why does the unbound textbox require a date value? Does it have formatting that requires a date and not just a number?

If you want the query results to show on form make the query the RecordSource for the form. Review this tutorial to see one way to build filter into a form: http://datapigtechnologies.com/flash...tomfilter.html

At this point, I don't think I will be able to help unless you provide the project so I can see what you have done. Make copy, remove confidential data, run Compact & Repair, zip if large, attach to post or upload to fileshare site such as box.com and post link.

[Lmartinrn]

http://www.box.com/s/4c58a03a71e5ed80fdc0

I have uploaded to Box.com

Any help you can provide will be greatly appreciated!

[June7]

Which objects are involved - which query, form, report?

[Lmartinrn]

qryEmplCredExpEnterParameter

[Lmartinrn]

Once I get the query working, I will make a report from this query the user will use.

[June7]

The query includes employee name in the query filter parameters. The employee name is unique in the source table. Only one record will return. Might as well just search only on the employee name criteria and eliminate all the others. If you want this query to return all employees with expiring dates, eliminate the employee name parameter. When I remove the employee name criteria, 43 out of 107 are returned.

SELECT tblEmplInfo.Location, tblEmplInfo.[BLS Expiration Date], tblEmplInfo.[ACLS Expiration Date], tblEmplInfo.[TNCC Expiration Date], tblEmplInfo.[PALS Expiration Date], tblEmplInfo.[ENPC Expiration Date], tblEmplInfo.[HAZMAT Awareness], tblEmplInfo.[CEN Expiration Date], tblEmplInfo.[TB Spot Test Due], tblEmplInfo.Active
FROM tblEmplInfo
WHERE tblEmplInfo.Location Like [Enter Location] & "*" And tblEmplInfo.[Employee Last Name] Like [Enter Last Name] & "*" And tblEmplInfo.Active=True
And (tblEmplInfo.[BLS Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or tblEmplInfo.[ACLS Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or tblEmplInfo.[TNCC Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or tblEmplInfo.[PALS Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or tblEmplInfo.[ENPC Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or tblEmplInfo.[HAZMAT Awareness] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or tblEmplInfo.[CEN Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or tblEmplInfo.[TB Spot Test Due] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ()))
ORDER BY tblEmplInfo.[Employee Last Name];

[Lmartinrn]

The user needs to have the option to either pull up one person with expiring credentials or everyone at a specific location with upcoming expiring credentials which is where I'm running into problems

[June7]

Paste this into the SQL View editor:

SELECT * FROM tblEmplInfo
WHERE tblEmplInfo.Location Like [Enter Location] & "*" And [ID] Like [Enter ID] & "*" And Active=True
And ([BLS Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or [ACLS Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or [TNCC Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or [PALS Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or [ENPC Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or [HAZMAT Awareness] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or [CEN Expiration Date] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ())
Or [TB Spot Test Due] Between Date() And DateAdd("m",Nz(Forms!frmEmplInfo!Label104,99),Date ()))
ORDER BY [Employee Last Name], [Employee First Name];

Then I suggest instead of the query input parameter prompts (I never use them), have unbound controls on the form as input for the Location and ID, right next to the date criteria box. The employee ID could be multi-column combobox. Users would see and pick name but the search would be on the ID.