Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
Solved
DEFAULT VALUES IN PARAMETER QUERIES
Posted on 2000-03-21
i got this tip in my email, but I do not know where to add this code. In the form? Query? help!
And now for today's tip...
DEFAULT VALUES IN PARAMETER QUERIES
Paul Fantacci of Maryland sent a great tip for using default values in
parameter queries. In general, you can't specify a default value for a
parameter in query. For instance, let's suppose you use the same
parameter query to check sales by region. When you run the query, the
parameter expression prompts you for a region. You enter a region, and
Access then displays the results for that region.
Let's suppose you run this query for one particular region most of the
time. In that case, it would be nice if your parameter query would
default to that region. Then you could run it by accepting the
default--changing it only when you need to see the sales totals for
another region.
Paul Fantacci explains that if you use the InputBox function in your
parameter expression in the form
Eval("InputBox(""prompt:""
Access displays an input box instead of the normal parameter prompt.
If you click OK, Access runs the query using defaultvalue as the
query's criteria. Or you can enter a new value, then click OK to run
the query.
TK
And now for today's tip...
DEFAULT VALUES IN PARAMETER QUERIES
Paul Fantacci of Maryland sent a great tip for using default values in
parameter queries. In general, you can't specify a default value for a
parameter in query. For instance, let's suppose you use the same
parameter query to check sales by region. When you run the query, the
parameter expression prompts you for a region. You enter a region, and
Access then displays the results for that region.
Let's suppose you run this query for one particular region most of the
time. In that case, it would be nice if your parameter query would
default to that region. Then you could run it by accepting the
default--changing it only when you need to see the sales totals for
another region.
Paul Fantacci explains that if you use the InputBox function in your
parameter expression in the form
Eval("InputBox(""prompt:""
Access displays an input box instead of the normal parameter prompt.
If you click OK, Access runs the query using defaultvalue as the
query's criteria. Or you can enter a new value, then click OK to run
the query.
TK
-
11
-
5
-
5
21 Comments
Should work in both form rowsource and in query criteria, as both support functions!
But why doesn't give it a try?
On the other hand, normally I don't want this as I like to fill the values from my form, thus not bothering the user with (a lot of) propmts, that he has to OK or Cancel!
But why doesn't give it a try?
On the other hand, normally I don't want this as I like to fill the values from my form, thus not bothering the user with (a lot of) propmts, that he has to OK or Cancel!
I imagine I need to replace some of the text in the line, but what text?
I have a query prompt me for a "ChargeCode". Where would I enter that in this line of text? Once I get the code correct, I'll try and place it in the Row Source. Thx.
I have a query prompt me for a "ChargeCode". Where would I enter that in this line of text? Once I get the code correct, I'll try and place it in the Row Source. Thx.
Hello TK421,
As nico5038 said, you can use this in your queries, which you can use as Recordsource with your reports and forms and queries which update or delete data.
For eg. this query would be used with your report or form (written in Query SQL view window):
SELECT *
FROM tblCustomer
WHERE CustomerName=Eval("InputBo
Regards,
Paasky
As nico5038 said, you can use this in your queries, which you can use as Recordsource with your reports and forms and queries which update or delete data.
For eg. this query would be used with your report or form (written in Query SQL view window):
SELECT *
FROM tblCustomer
WHERE CustomerName=Eval("InputBo
Regards,
Paasky
Place in the query criteria cell below the ChargeCode:
Eval("InputBox(""ChargeCod
""ChargeCode:"" is the text in front of the input field
""Form for inputting ChargeCode"" is the title in the blue title bar of the form
""12345"" is a default value that you can set to support the user by giving him the most used value.
Eval("InputBox(""ChargeCod
""ChargeCode:"" is the text in front of the input field
""Form for inputting ChargeCode"" is the title in the blue title bar of the form
""12345"" is a default value that you can set to support the user by giving him the most used value.
Looks like it's working. Sort of. I need to be able to wildcard (*)
Heres's my criteria now:
Eval("InputBox(""ChargeCod
How can I get results like:
8fbaj-lt1
000-8fbaj-lt2
05-8fbaj
Right now I only get "8fbaj". Thanks!
Heres's my criteria now:
Eval("InputBox(""ChargeCod
How can I get results like:
8fbaj-lt1
000-8fbaj-lt2
05-8fbaj
Right now I only get "8fbaj". Thanks!
SELECT *
FROM table1
WHERE ChargeCode LIKE Eval("InputBox(""ChargeCod
FROM table1
WHERE ChargeCode LIKE Eval("InputBox(""ChargeCod
Just take the wild-characters section from the help file and put in (under a help-button?) on the creen for your user(s).
Not only the "*" but also the "?" and the "[1-4]" examples are usefull.
Not only the "*" but also the "?" and the "[1-4]" examples are usefull.
I get no results when I try this:
Eval("InputBox(""ChargeCod
OR when I try and wildcard in the prompt..... :..(
TK421
Eval("InputBox(""ChargeCod
OR when I try and wildcard in the prompt..... :..(
TK421
I get no results when I try this:
Eval("InputBox(""ChargeCod
OR when I try and wildcard in the prompt..... :..(
TK421
Eval("InputBox(""ChargeCod
OR when I try and wildcard in the prompt..... :..(
TK421
Pts raised to 60.
Let's kick it up a notch! Again, I can not get the wildcard in the code or when entering it manually. Any ideas anyone? Thanks for all of the great advice.
TK421
Let's kick it up a notch! Again, I can not get the wildcard in the code or when entering it manually. Any ideas anyone? Thanks for all of the great advice.
TK421
dear TK421,
Where/how are you using the function? I tried exactly same query that I posted here and got just the records what should come.
If you have a query there would you please post it here and I'll look what's the problem. Also you should modify your query in SQL view instead of Design view.
And you can always email part of your db to me... ;-)
Regards,
Paasky
Where/how are you using the function? I tried exactly same query that I posted here and got just the records what should come.
If you have a query there would you please post it here and I'll look what's the problem. Also you should modify your query in SQL view instead of Design view.
And you can always email part of your db to me... ;-)
Regards,
Paasky
This function is placed in the query criteria. This DOES NOT return any records:
Eval("InputBox(""ChargeCod
But this does:
Eval("InputBox(""ChargeCod
I also tried to edit the SQL and I get the same results. ANy ideas?
Is there another place I can try this code? I have a form (in datasheet view). Would it work there?
Thanks!
Eval("InputBox(""ChargeCod
But this does:
Eval("InputBox(""ChargeCod
I also tried to edit the SQL and I get the same results. ANy ideas?
Is there another place I can try this code? I have a form (in datasheet view). Would it work there?
Thanks!
You should use operator LIKE. Put this into query criteria:
Like Eval("InputBox(""ChargeCod
and it should work.
This is from Access help:
You can use the Like operator to find values in a field that match the pattern you specify. For pattern, you can specify the complete value (for example, Like "Smith"), or you can use wildcard characters to find a range of values (for example, Like "Sm*").
Paasky
Like Eval("InputBox(""ChargeCod
and it should work.
This is from Access help:
You can use the Like operator to find values in a field that match the pattern you specify. For pattern, you can specify the complete value (for example, Like "Smith"), or you can use wildcard characters to find a range of values (for example, Like "Sm*").
Paasky
Still asking myself why you want the values entered this way. When a user enters the string in a text field on your form you can add the "*"'s by code and the user can't mess it up ?!
Also multiple fields are posible from the form, with EVAL you will have to press OK for each variable as on the form just once!
Also multiple fields are posible from the form, with EVAL you will have to press OK for each variable as on the form just once!
Yes! That's exactly what I want. Thank you Paasky. AGAIN!
As far as why I want this:
I want the DB to return all instances of fields containing *xxx*. I do not want to leave it to the user to enter the (*) manually.
Thanks paasky!
As far as why I want this:
I want the DB to return all instances of fields containing *xxx*. I do not want to leave it to the user to enter the (*) manually.
Thanks paasky!
paasky,
It runs fine. But when I click the "cancel" button, it runs and returns no records. Why? aaahhhh!
It runs fine. But when I click the "cancel" button, it runs and returns no records. Why? aaahhhh!
paasky,
It runs fine. But when I click the "cancel" button, it runs and returns no records. Why? aaahhhh!
It runs fine. But when I click the "cancel" button, it runs and returns no records. Why? aaahhhh!
Featured Post
Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data.
A Microsoft Access subform is used to show relevant calcul…
With just a little bit of SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form. If you have never written code or looked at an SQL statement before, no problem! ... give i…
Suggested Courses
Course of the Month11 days, 16 hours left to enroll
606 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.