Input Parameter for Query

[Proaccesspro]

I know you can allow a user to type in a parameter during a query. However, how can one limit the choices by using a drop down or list box?

Here is an example of what I am trying to do: In the Country field of the customers table I can have the following in the criteria section:

[Enter a Country]

When this query executes, the user enters a country and the results are shown.
Is there a way to make this a drop down box or list box with pre-defined choices instead of allowing the user to freely type a parameter?

[Rabbit]

Not with the parameter request box. Usually, we'll do this by having an intermediary form.

[Proaccesspro]

Not with the parameter request box. Usually, we'll do this by having an intermediary form.

Can you provide a link to some examples??

[Denburt]

Try utilizing a search form then you can use that to open the record you are requesting...

http://www.thescripts.com/forum/thread590551.html

Let me know if that helps. I have a couple of functions I use for rebuilding queries but I don't think you want to go that route it can get a bit complex.

[Proaccesspro]

Try utilizing a search form then you can use that to open the record you are requesting...

http://www.thescripts.com/forum/thread590551.html

Let me know if that helps. I have a couple of functions I use for rebuilding queries but I don't think you want to go that route it can get a bit complex.

I'll check it out, Thanks.....

Is there a parameter function I can use to determine what "orders" are X number of days old??? In other words, the user would inut a number, say 45, and the query would then list all of the corresponding order that are 0 to 45 days old.

[Proaccesspro]

I'll check it out, Thanks.....

Is there a parameter function I can use to determine what "orders" are X number of days old??? In other words, the user would inut a number, say 45, and the query would then list all of the corresponding order that are 0 to 45 days old.

Let me clarify further. I have a date field in a table. I want to allow the user to input a number and the query use that number to determine how many order are up to that many days old....

the following code in the criteria line of a query will prompt the user for a beginning and end date...I want to do something similar, but based on a single input for number of days.

between [enter a start date] AND [enter an end date]

[Denburt]

Yeah you will need to write some code to determine what you are looking for.

If DateDiff("d", Now, TheDate) < 45 then
showRecords()
End if

[Denburt]

Let me clarify further. I have a date field in a table. I want to allow the user to input a number and the query use that number to determine how many order are up to that many days old....

the following code in the criteria line of a query will prompt the user for a beginning and end date...I want to do something similar, but based on a single input for number of days.

between [enter a start date] AND [enter an end date]

O.K. then do this

in a field name in the Query add this
DaysOld:DateDif f("d", Now, TheDate)

Then in the criteria of that field you can ask for the number of days [Number Of Days] or just specify <45

[Proaccesspro]

O.K. then do this

in a field name in the Query add this
DaysOld:DateDif f("d", Now, TheDate)

Then in the criteria of that field you can ask for the number of days [Number Of Days] or just specify <45

Almost...can I substitute a field in my table (called date recvd) for Thedate?? I actually want to calculate the number of days the user inputs minus the field Date recvd. Then grab the records of those that meet that criteria.

[Denburt]

O.K. then do this

in a field name in the Query add this
DaysOld:DateDif f("d", Now, [date recvd])

That will give you a column of numbers thus calculating the days before the users input then when the person inputs a number you control how it is used < for less than > greater than etc.

In the criteria of that field you can ask for the number of days
You can specify <[Number Of Days] and get the prompt then they input the number of days or an = sign etc. or just specify <45 or something along those lines.


I don't want to confuse you but another method would be to use the field you have called [date recvd] in the criteria section add the following (this one is off the top of my head should work though).

dateadd("d",-[How many Days],Date())

Good luck and goodnight for now.

[Proaccesspro]

O.K. then do this

in a field name in the Query add this
DaysOld:DateDif f("d", Now, [date recvd])

That will give you a column of numbers thus calculating the days before the users input then when the person inputs a number you control how it is used < for less than > greater than etc.

In the criteria of that field you can ask for the number of days
You can specify <[Number Of Days] and get the prompt then they input the number of days or an = sign etc. or just specify <45 or something along those lines.


I don't want to confuse you but another method would be to use the field you have called [date recvd] in the criteria section add the following (this one is off the top of my head should work though).

dateadd("d",-[How many Days],Date())

Good luck and goodnight for now.

Both ways execute, but neither produces any records!!!!

Am I correct in thinking that both ways are looking for dates that will MATCH the current date minus the number input by the user?? In other words, today is the 30th...so if someone types in 5 as the parameter, does the function then go out and look for orders received 5 days prior to today? If so, that is not what I want to do. Instead, I would want to see ALL orders received over the last 5 days (the number typed in by the user).

[Denburt]

O.K. My brain isn't what it used to be. I ran a few tests and my conclusion is that in the field [date recvd] you should add the following criteria and it should work as expected..

>=DateAdd("d" ,-[How many Days],Date())

[Proaccesspro]

O.K. My brain isn't what it used to be. I ran a few tests and my conclusion is that in the field [date recvd] you should add the following criteria and it should work as expected..

>=DateAdd("d" ,-[How many Days],Date())

Works like a top!! Thanks!!

[Denburt]

Works like a top!! Thanks!!

Glad we could help, have a nice day.