Parameter issue

[mama16]

How can I add a list of things in a parameter box for comparison purposes. For example, from A-Z, when a user clicks on the report button, a parameter box pops up asking for [What letter please] User selects D, [What letter please] User selects C, [What letter please] Z
Then, it pulls information on those three letters. All that information exist within a table.

Any ideas?

Thanks

 

[GingerR]

You will want to change the record source of your report.

If you are searching in a single field, you'd put criteria in that field in the query grid, something like

Like "*" & [Enter First Letter:] & "*" Or Like "*" & [Enter Second Letter:] & "*" Or Like "*" & [Enter Third Letter:] & "*"

If you didn't already know, the asterics are wildcards.

If you are searching on three different fields, just put one of each of those in the above code as the criteria for each of the three fields.

hope this helps--g

 

[mama16]

When I run the query with the new criteria, It ask me three times for information but no data shows. Why am I doing wrong?

 

[mama16]

Any ideas?

 

[dhookom]

Please post your SQL view of your query? What happens if you remove the criteria one at a time?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[mama16]

When I run this query, I do not get anything back, WHY?

SELECT Deployers.Deployers, Statistics.Tot_calls_by_Month, Statistics.Peak_call_day
FROM Deployers LEFT JOIN Statistics ON Deployers.Deployers = Statistics.Deployers
WHERE (((Deployers.Deployers) Like "*" & [Wha't the Letter] & "*" And (Deployers.Deployers)="*" & [What's the next] & "*"));

I want the user at report time, to be able to select multiple deployers.deployers for comparison purposes.
If I use the "OR" I only get data for one value. That's not what I want. I'm looking for whatever values I choose.

Any ideas?

 

[dhookom]

I think you mis-read GingerR's response. Her suggestion contained 3 "Like"s and 2 "Or"s.
[blue]Like ... Or Like ... Or Like ...[/blue]

Your SQL contains 1 "Like", 1 "=", and 1 "And".
[blue]Like ... And = ...[/blue]


Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[mama16]

It worked but what if the user only wants to compare two values instead of three. The criteria will ask you for three values

Like "*" & [Enter First Letter:] & "*" Or Like "*" & [Enter Second Letter:] & "*" Or Like "*" & [Enter Third Letter:] & "*"

Can the user ignore the last parameter box by not putting any data in it and clicking ok?

I did that and it gives me all the values that are in the DB

Any ideas,

Thanks.

 

[dhookom]

Like "*" & Nz([Enter First Letter:],"~") & "*" Or Like "*" & Nz([Enter Second Letter:],"~") & "*" Or Like "*" & Nz([Enter Third Letter:],"~") & "*"


Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[mama16]

Thanks, It worked but I still have a problem. When I choose AZ, NC , and KY. I get those results but I also get San Francisco. This is because "NC" appears in Fra"nc"isco.
Also, if I choose "UT" for Utah, Access pulls the info for UT and also for FL-So"ut"h East.
Is there a way to stop this from happening?

Thanks a bunch,

 

[dhookom]

Your primary issue is that you are storing multiple types of information in the same field. Each item of information should be in its own field.

In addition, I NEVER use parameter queries. It is much better practice to set criteria by making selections or entering values in forms.

Are you attempting to open a form or report based on this query?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[mama16]

I'm trying to open a report. It works but like I said it is pulling records that I don't want.
I have a field called Deployers(PK). In that field I have data such as
AZ,
FL-Orlando,
CA-Sacramento
NC,
UT,
FL-South East,
CA-San Francisco
IA,
MT,
Etccc....
I have a total of 24 states.
When I run a report to check for the statistics of those Deployers, I want to be able to compare some of the states.
The query that runs, it's doing the job well except for what I mentioned before, its picking up some states that I don't want. For example, if I want info on NC,AZ,UT. It will give me info on NC,AZ,UT plus CA-San Francisco, FL-South East.

Thanks Duane

 

[dhookom]

Think about it. It looks like your state is ALWAYS listed FIRST. Your criteria prompts match the values ANYWHERE in the field.
[blue][tt]Like "*" & Nz([Enter First Letter:],"~") & "*" Or Like "*" & Nz([Enter Second Letter:],"~") & "*" Or Like "*" & Nz([Enter Third Letter:],"~") & "*"[/tt][/blue]

There are other string functions like Left(), Right(), Instr(), ...

Please check these out on your own and come back if you can't figure this out. You are creating a moving target and asking for help on every step along the way. You need to be able to troubleshoot some of this on your own. No one is really helping you if they simply provide a solution. You need to "learn how to fish".

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]