Is there a way to apply an input mask to a query parameter?

Is there a way to force the end user to enter a date parameter in a certain format when prompted via the query (e.g. 1/1/2014 (4-digit year)). I have users that accidentally enter 1/1/201 (forgetting the last digit of the year), and because 1/1/201 is a valid date, the query continues to run returning unexpected data results. Any thoughts? (PS: passing the date through a form is not an option for this project). Much thanks in advance!
jaguar5554Business AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
No. The best you can do is to create a small form for the user to enter the search criteria, then open the query using these.

Hamed NasrRetired IT ProfessionalCommented:
One idea, is to check for the last 4 digits, if less than 4, input a false date. The field to check for is [Start Date], the parameter is stdt. The false date is 1/1/1900.

SELECT ID, [Start Date]
FROM tbl
WHERE [start date]=IIF( Len(Right(stdt,LEN(stdt)-InStr(4,stdt,"/")))=4, stdt, #1/1/1900#);

Open in new window

jaguar5554Business AnalystAuthor Commented:
I had actually come up with a very similar solution -- using an IIF statement to check the length of the inputted date. Thank you!  Unfortunately, the query is still returning unexpected results. I'm thinking about using the IIF statement in the query, but perhaps adding a message box or something that alerts the user when running the report that the date range is an impossible one (e.g. more than 5 years, etc.). But I am still open to any other ideas.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Gustav BrockCIOCommented:
You can't win that battle. The tiny parameter box is for people like you, not end users.

Using a form, you can have proper error handling and communication with the user.

Hamed NasrRetired IT ProfessionalCommented:
Check this sample database. Open Query1 and see if you can make use of it.

tbl contains a date 1/1/1900 to use as error date entry.

SELECT tbl.ID, tbl.[Start Date], IIf(Len(Right([stdt],Len([stdt])-InStr(4,[stdt],"/")))=4,"","Date error entry") AS [""]
FROM tbl
WHERE (((tbl.[Start Date])=IIf(Len(Right([stdt],Len([stdt])-InStr(4,[stdt],"/")))=4,[stdt],#1/1/1900#)));

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jaguar5554Business AnalystAuthor Commented:
Very nice. Unfortunately, I can't use the WHERE clause. The date is user-defined and included in the dataset. To be more specific, the user is prompted for a date, and what is entered by the end user is then included in each record that is returned by the query. That date is then used later in other queries and reporting. I recognize that this issue could be better resolved using a form to pass the date to the query but that is not an option at this time. If there was only a way to apply an input mask on the query parameter, or force the user to re-enter the date if it's not a 4-digit year.
Gustav BrockCIOCommented:
If that was the case, you could win the battle. But, as said, you can't.
This is a well-known issue, and the proved workaround is well-known too.

Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
As others have said, query parameters just aren't meant for end users.  They're horrible.  I don't understand why a form isn't an option - it's the right way to do it, and would give you all the control you need.  It's like saying you're having trouble cutting tomatoes with a spoon, but a knife isn't an option.

If you do decide that a form is a possibility, then have a look at some examples I've posted of how to use forms to build Where clauses for reports.  It's a free download called Report Selection Techniques at

Armen Stein, Access MVP
J Street Technology
jaguar5554Business AnalystAuthor Commented:
Great analogy! LOL However, I was asked to assist on a co-worker's project and my recommendation to pass the date through a form was declined.

Thanks everyone for your feedback. I will try to adapt the IIF statement as provided by hnasr, as well as check out the downloads site as suggested by Armen -- both helpful.
Hamed NasrRetired IT ProfessionalCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.