Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 822
  • Last Modified:

input mask for parameter queries

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
esu4236
Asked:
esu4236
  • 4
  • 4
  • 2
  • +2
1 Solution
 
PaurthsCommented:
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
 
esu4236Author Commented:
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
 
PaurthsCommented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
esu4236Author Commented:
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
 
PaurthsCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
esu4236Author Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
PaurthsCommented:
have u not received the database again? (audubon.mdb)
0
 
nico5038Commented:
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
 
esu4236Author Commented:
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
 
NetminderCommented:
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now