Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DEFAULT VALUES IN PARAMETER QUERIES

Posted on 2000-03-21
21
Medium Priority
?
444 Views
Last Modified: 2008-03-04
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:"",""title"",""defaultvalue"")")

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
0
Comment
Question by:TK421
[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
  • 11
  • 5
  • 5
21 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 2640642
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!
0
 

Author Comment

by:TK421
ID: 2640734
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.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2640735
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("InputBox(""Enter customer name:"",""Choose customer"",""TK421"")");

Regards,
Paasky
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 54

Expert Comment

by:nico5038
ID: 2641334
Place in the query criteria cell below the ChargeCode:

Eval("InputBox(""ChargeCode:"",""Form for inputting ChargeCode"",""12345"")")

""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.
0
 

Author Comment

by:TK421
ID: 2641390
Looks like it's working. Sort of. I need to be able to wildcard (*)

Heres's my criteria now:

Eval("InputBox(""ChargeCode:"",""Status by Charge CodeForm"",""8FBAJ"")")

How can I get results like:

8fbaj-lt1
000-8fbaj-lt2
05-8fbaj

Right now I only get "8fbaj". Thanks!
0
 
LVL 10

Expert Comment

by:paasky
ID: 2641410
SELECT *
FROM table1
WHERE ChargeCode LIKE Eval("InputBox(""ChargeCode:"",""Status by Charge CodeForm"",""*8FBAJ*"")");

0
 
LVL 54

Expert Comment

by:nico5038
ID: 2641557
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.
0
 

Author Comment

by:TK421
ID: 2641584
Any ideas paasky?
0
 

Author Comment

by:TK421
ID: 2641602
I get no results when I try this:

Eval("InputBox(""ChargeCode:"",""Status by Charge CodeForm"",""*8FBAJ*"")")


OR when I try and wildcard in the prompt.....    :..(

TK421
0
 

Author Comment

by:TK421
ID: 2641653
I get no results when I try this:

Eval("InputBox(""ChargeCode:"",""Status by Charge CodeForm"",""*8FBAJ*"")")


OR when I try and wildcard in the prompt.....    :..(

TK421
0
 

Author Comment

by:TK421
ID: 2641693
Adjusted points from 30 to 60
0
 

Author Comment

by:TK421
ID: 2641694
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
0
 
LVL 10

Expert Comment

by:paasky
ID: 2641748
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
0
 

Author Comment

by:TK421
ID: 2649144
This function is placed in the query criteria. This DOES NOT return any records:

Eval("InputBox(""ChargeCode:"",""Status by Charge CodeForm"",""*8FBAJ*"")")

But this does:

Eval("InputBox(""ChargeCode:"",""Status by Charge CodeForm"",""8FBAJ"")")

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!
0
 
LVL 10

Accepted Solution

by:
paasky earned 240 total points
ID: 2650014
You should use operator LIKE. Put this into query criteria:

Like Eval("InputBox(""ChargeCode:"",""Status by Charge CodeForm"",""*8FBAJ*"")")

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
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2650112
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!
0
 

Author Comment

by:TK421
ID: 2650425
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!
0
 
LVL 10

Expert Comment

by:paasky
ID: 2650459
Happy to help you TK.

Best regards,
Arto
0
 

Author Comment

by:TK421
ID: 2650475
paasky,

It runs fine. But when I click the "cancel" button, it runs and returns no records. Why? aaahhhh!
0
 

Author Comment

by:TK421
ID: 2650592
paasky,

It runs fine. But when I click the "cancel" button, it runs and returns no records. Why? aaahhhh!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2651246
Sorry TK421, I don't get it.

The EVAL gives an input box, thus the user can type what he wants with or without "*" ?!
shouldn't it look like:
Like '*" & Eval("InputBox(""ChargeCode:"",""Status by Charge CodeForm"",""8FBAJ"")") & "*'"

To force to have always "*"'s around the string?
 


0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

670 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