Solved

input mask for parameter queries

Posted on 2002-04-10
12
790 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

756 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