Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

input mask for parameter queries

Posted on 2002-04-10
12
Medium Priority
?
815 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 58
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 58
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

609 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