Prompt for a value to look up or return the largest value if no number is entered

[BudMan]

I have a table where I keep track of my home inspections.


The fields include ID (Auto Number)
Insp_Num (The number is generated using the DMAX function)
Insp_Date
Name
Address
Notes


I am trying to craft a query that prompts an Insp_Num to look up or display the largest number in the field if I do not enter a number at the prompt, along with the other fields.


I have tried Googling but cannot find what I am looking for. Is it ever possible?

[madpiet]

prompts an Insp_Num to look up or display the largest number in the field if I do not enter a number at the prompt

The only way I can think of doing it is with a button, so on a FORM. (Nobody should have direct access to your tables anyway!) Then you'd do something like prompt the user for an Inspection Number (just use a combobox). Then you can run one query if the combobox has a value selected and another if it doesn't.

If all you want is the most recent record, I suppose you could do something like

SELECT TOP (1) Insp_Num
FROM InspectionsTable
ORDER BY InspectionDate DESC;

[June7]

Query prompts are annoying and I don't use. There is no validation of input and then have to deal with case of no input. I use VBA to construct filter/sort criteria and apply to form or report.

[jojowhite]

mostly, I will want to do this on a Form and use the Form's textbox as criteria,
but for query, you add Parameter to it so it will prompt you.
if nothing is inputted use the max value of the field.
this query will prompt for the Inspection Number or non is inputted, the max will return:

SELECT InspectionTable.Insp_Num, InspectionTable.Insp_Date, InspectionTable.Name, InspectionTable.Address
FROM InspectionTable 
WHERE InspectionTable.Insp_Num=IIf(IsNull([Enter Inspection Number]),DMax("Insp_Num","InspectionTable"),[Enter Inspection Number]);

[BudMan]

Thanks jojowhite. That worked exactly like I wanted it to.