Data Validation for Parameter Query...

[cheuschober]

Does anyone know if there is a way to have Data Validation for a Parameter Query? I tried searching today on the issue but I couldn't quite find an answer. Of course, my jargon isn't quite perfect and so I might just not be searching for the right terms.

Basically I wish to review my business deductible expenses in a tax-year overview where I am asked for the tax year to show but I would like to be able to limit it to 'acceptable' tax years, those being the year of the first entry in the database and that of the last year in the database.

I have the parameters working fine, but the data validation has me a bit confused.

If anyone could point me in the right direction I'd be appreciative!
~Chad

 

[RoyVidar]

Do you mean parameters as in those "awful thingies" popping up when you run the query?

If so, consider using a form for the parameters. Let the users enter the data in text controls on the form, and use different means of validation (combobox is one possibility, validation rules, some validating code...), then reference the control(s) in the query criteria row with something like this (use the expression builder to get the syntax right, especially if it's on a subform)

forms!frmYourForm!txtNameOfControl

 

[cheuschober]

Yes, yes, I know they're horrific things but this is not a query for data entry purposes, it's solely for a report. That said, I just want, when the report is run, to be asked for a singular tax year to display the results for.

Thanks the same, though,
~Chad

 

[RoyVidar]

Awful, because amongst other, they do not allow any data validation... - so use form controls if you need validation

Is you'r enquiry how to produce them? Use some text within square brackets in the criteria row as below

[Enter Tax Year]

 

[cheuschober]

Currently yes... but right now there is no form for this query nor is there any need or desire for one???


<blah blah SELECT and FROM statement>
WHERE Year([TaxLogs].[Date])=[Please Enter Tax Year]

... so confused...

~Chad

 

[RoyVidar]

Yes, doesn't that work? If not, I'd first have a look at field names. Date is a function, therefore a reserwed word. Using [brackets] around it usually works.

 

[cheuschober]

Yup... that works...

But not to the full extent that I want... I would like to expand upon the statment to include a 'Like "*"' part of the statement to display ALL records in the case of a null entry

and data validation for only existing dates would be nice too, but this ALL part of the statement is more important to me

Any ideas?

 

[RoyVidar]

When I work, I have a set of "rules" to guide me. Among those are
* let the users have access to forms and reports for manipulating and viewing data, not anything else. Forms and reports are built for that purpose
* avoid "pop up parameters"

Per the first, one often uses naming conventions on fields and objects which makes them unsuitable for user consumption. Also queries and tables will often allow more tweaking by the user than what's needed (or rather, allowed).

Per the second, to repeat, those "awfult thingies" do not allow any datavalidation. I do not want any queries to fail because users enters data in a wrong format, not enough information... I want to control the input, validate before I run the query - i e have control over the whole process. You cannot have that with those parameters!

1 - create a report to display the information you want to display (some of the other possibilities, use a continuous form or a listbox)
2 - use a form to enter the information (parameters)
3 - use some code verifying/validating the inputted data, then build either the whole SQL, or just the Where clause (the where condition of the openform/openreport method) dynamicly in the code opening the report, opening the form, creating and assigning the recordsource of a continuous form/rowsource of a listbox control.

Some more hints - the Like operator works for text, not dates.

Should you still wish to follow the "parameter path", I'm sorry, I'm unable to assist.