Solved

input mask for parameter queries

Posted on 2002-04-10
12
785 Views
Last Modified: 2012-05-04
Is there a way to create an input mask for parameter queries?  For example, if I want to query a date field, I want it to automatically fill in the slashes (/) for me as I type in the date for my parameter.  Or if I were to query a telephone number field, I want it to automatically type in the dashes for me.  Is there a way to do this?  I'm not an SQL person, so please explain in detail if I need to do something in code.
0
Comment
Question by:esu4236
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 12

Expert Comment

by:Paurths
ID: 6932189
hi esu4236,

not that i know of... but some other expert might come along and have a solution for a parameter query.

Anyway,

if u want to be able to format, then i would create another form (YourForm), and e.g. put a textbox on it (txtDate), and use this in your query.

in the 'input mask-property of the textbox : 99/99/9999;9;*

in your query u can refer to the textbox like this (in the criteria row)
[Forms]![yourform]![txtDate]


cheers
Ricky
0
 

Author Comment

by:esu4236
ID: 6933961
I set up a form as you said and then placed in the criteria row what you suggested.  However, the input mask is still not coming up when I run the query.  It works fine in the form, but not for the query.  I still have to type in the / when I run the query.
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6933986
u have to remove the parameter from the query.
U are now using the value which is typed in the textbox on the form.
by putting : [Forms]![yourform]![txtDate]
in your criteria in the query, the value in txtDate on form YourForm is used to filter.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:esu4236
ID: 6934944
That's what I did.  I only had [Forms]![yourform]![txtDate] in the criteria line of my query -- nothing else.  I still had to type in the dashes for my date.  I set up the input mask on the form.  Still not working.
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6935042
is it possible for u to email me the database?
or at least a mdb with the form, table, query and report in it?
ricky.thijs@pandora.be
0
 
LVL 57
ID: 6936307
<<I set up a form as you said and then placed in the criteria row what you suggested.  However, the input
mask is still not coming up when I run the query.  It works fine in the form, but not for the query.
 I still have to type in the / when I run the query. >>

  Yes, that will be true.  The only way you can have an input mask is through a form control.  When you run the query, the form must be open or you will get prompted.

  Typically, users won't run queries on thier own.

Also, for dates, it's critical that you explicitly type the control in query design.  Open the query in design view, click query/parameters.  Enter the name of the parameter and indicate the data type. In this case it would be:

[Forms]![yourform]![txtDate]    with a type of Date/Time.

  You should do this for all parameter references.

HTH,
Jim.
0
 

Author Comment

by:esu4236
ID: 6936690
JDettman,

Your suggestions didn't work for me either.  I'm confused -- am I supposed to be putting the criteria for my parameter in the Criteria row or under Query, Parameter?  Either way, it's not working for me.  And I don't understand what you mean by having the form open as I run the query - if I have the form open as I run the query, I don't get prompted at all for the date.  I want the query to act as a parameter query; however, when I type in my date, I want it to automatically fill in the dashes for me.
0
 
LVL 57
ID: 6936965
<<Your suggestions didn't work for me either.  I'm confused -- am I supposed to be putting the criteria
for my parameter in the Criteria row>>

  Yes.  Your criteria check would look something like this:

 =Forms![myFormName]![myDateControlName]

  With this, your telling the query to look at a form called "myFormName" and get the value for the parameter from the control on that form called "myDateControlName"

  However the query doesn't know that it's a date in there (dates are stored as a double).  To tell the query to expect a date in that control, you click query/parameters.  Then enter:

Forms![myFormName]![myDateControlName]  for the parameter name

and select Date/Time for the type.

Now with the form open, run the query.  It won't prompt you for the parameter, but instead get it from the form control.

<< And I don't understand what you mean by having the form open as I run the query - if I have the form
open as I run the query, I don't get prompted at all for the date.>>

  That's what is supposed to happen

<<I want the query to act as a parameter
query; however, when I type in my date, I want it to automatically fill in the dashes for me.>>

  The only way to do that is through a form control, which has an input mask set.  A query can't do what you want on it's own.  A typical application will have many such forms, which generally have a command button on them to actually execute the query, or open another form that is based on the parameter query.

Jim.
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6937098
have u not received the database again? (audubon.mdb)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7103226
for esu4236

No comment has been added for the last two months.
So it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 

Author Comment

by:esu4236
ID: 7103623
I never did get any of the suggestions to work for me.  This question can be deleted as far as I'm concerned.  Thank you.
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7128909
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question