Query for expiring items

[Lmartinrn]

I am a bit confused about what I'm sure is a very basic question.

I have created a database that lists employee first name, employee last name, BLS expiration date, TNCC expiration date and several other credential expiration dates. Each is unique to the employee listed on the form and the expiration dates are in short date/time format.



I need a query that will show me every employee who has credentials (regardless of type i.e. BLS, TNCC, etc) that will expire sometime in the next 6 months.

Is this possible?

[June7]

Try:

SELECT * FROM Table1
WHERE BLSexp Between Date() And DateAdd("m",6,Date()) OR TNCCexp Between Date() And DateAdd("m",6,Date());

In the query designer grid, include the BETWEEN AND date criteria under each expiration date field, each on a different criteria row so the OR operator will apply.

[Lmartinrn]

Well, after entering, I received a syntax error (missing operator) in query expression BLS Expiration Date...etc. I hope it can be figured out how to make it work. Thank you for your quick reply

[June7]

Post the SQL statement you constructed so can analyse.

Don't know why there would be a missing operator. In query designer copy/paste the criteria under each date field, each on a separate criteria row of the grid.

[Lmartinrn]

Okay, I got it to work... Thank you so very much! I hate to bother but this should be my last question. How should it read if I want them to enter a date instead of a default 6 month timeframe?

[June7]

Have unbound textbox on form for input of date criteria then refer to that textbox.

Between Date() And Forms!formnamehere!textboxnamehere

[Lmartinrn]

I did as instructed and it says, "The expression you entered contains an invalid syntax". "You may have entered an operand without an operator". Please help! This has been very frustrating for me. I showed it to my boss who really wants to be able to have the user input the number of months instead of defaulting to six.

Also, one more thing.... I have a query with 5 fields...Last Name, First Name, EKG Number, Active, Location. For some reason, I want to be able to query by [Enter Last Name], Location ="S", and =True (for active as it is a checkbox). My problem with the query is that if I decide to leave the [Enter Last Name] blank, it returns a blank query. I want it to default to all records that are active at the S location when [Enter Last Name] is left blank. Is this possible?

[pbaldy]

I changed the title of your thread from the not-very-informative "Query Issue" to "Query for expiring items", which hopefully will help people searching in the future.

[Lmartinrn]

Thank you. I didn't even think about that.

[June7]

Oh, you want user to enter number of months, not an actual date?

Between Date() And DateAdd("m", Nz(Forms!formnamehere!textboxnamehere,99), Date())

Use LIKE and wildcard with name parameter: LIKE [Enter Last Name] & "*"

[Lmartinrn]

Well, I got it to work for my query... here is my dilemma at this point:
When you run the query, it asks for user input and it reads as such "Forms!frmEmplInfo!Label104". The user will not have any idea how to deal with this as an entry. Even if I change the label name, the remainder of the string will read that way. How do I fix it?

Also, what do I do with the unbound Text Box in the form? Can I just make it not "visible"?

[June7]

Why is the parameter referring to a label? Should be referring to a data control like a textbox or combobox or listbox as shown in the example I gave. Probably the unbound textbox you want to hide. User inputs month value into textbox. Query refers to that textbox for the filter criteria.

[Lmartinrn]

The query works just fine by entering the actual number of months (i.e. type in 1 to obtain data for exp within the next month). The prompt that wants you to enter the number is what reads this string of stuff that the user won't be able to understand. It is referring to the "name" of the label for the textbox which could be named anything. In this case, it is named Label104. The caption reads as you would want information to be entered but it does not do anything on the form when you enter a number in it. It works in the query but not in the form.

My question is how to get the query to read so that a user can understand what data it is asking for. I can hide the unbound text box in the form if it isn't supposed to do anything other than be a text box to make the query work.

[June7]

My suggestion is that the query parameters be references to the textbox on form, not a prompt from the query that user must respond to. I NEVER use query input parameter prompts. The input cannot be validated and if user does not enter valid criteria the query will still run but results will be unexpected and everyone is frustrated.

If you must use prompts (no references to the form or its controls), then try:

[Enter number of months]

and the suggestion I gave earlier: LIKE [Enter Last Name] & "*"

[Lmartinrn]

The way you have me entering it as the first response above is that it is to run a query of BLS, ACLS, TNCC, etc expiration dates that will expire in a certain time frame from a query.
The unbound text box I created, as you mentioned above, in the form will only allow me enter a date into the unbound text box in the form but doesn't do anything with it. It shows the date I type into the text box and that is all. I am not sure how that helps me. When I link the query to the unbound text box in the form, it works to generate the information I need but it does it through the query and not the form.
When I enter [Enter number of months] in the query as you mentioned above, it does not show me every item that will expire on that query over the course of the next "x" number of months.

Here is what I need and I really hope you can help me:
From my table, I need to run a report that allows a user to enter specific to each employee (employee first name, employee last name, every certification (TNCC, BLS, ENPC, etc) that will expire within a user defined set of time (i.e. next 2 months or next 6 months, etc.).
I have created a query that does that which will then generate a report. When I enter into the string into the query as you stated above, it works to produce the data. My problem is that when you run the report (or query), the entering prompt is the string of words and not anything a user will understand.
Please help. Maybe, I'm thinking about this all wrong.