?
Solved

input mask for parameter queries

Posted on 2002-04-10
12
Medium Priority
?
804 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

771 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