And/Or

[keeblerjp]

I have a form that pulls from a querie. The queries gives the end user different fields to choose from.

txtbox1 Hit or Miss
txtbox2 Yes or No
txtbox3 Service, Flawless 5, STAR, Finance

ok, if you right this as an "or" function in a queries, it will give you either one, i want it to be like the "and" function, but sometimes people wont choose a box, so if you leave the box blank it returns no results. This is the way i have it written now:

SELECT EntryTable.EntryID, EntryTableMain.EntryTableMainID, EntryTableMain.EntryDate, EntryTableMain.GroupName, EntryTableMain.YourName, EntryTable.Flawless5, EntryTable.SubjectFab, EntryTable.Action, EntryTable.LocationSpecificArea, EntryTable.Department, EntryTable.HitorMiss, EntryTable.ExternallInternall, EntryTable.DetailComment, EntryTable.AdditionalInformation
FROM EntryTable LEFT JOIN EntryTableMain ON EntryTable.EntryLink = EntryTableMain.EntryTableMainID
WHERE (((EntryTable.Flawless5)=[Forms]![Searchfrm]![Flawless5])) OR (((EntryTable.SubjectFab)=[Forms]![Searchfrm]![SubjectFab])) OR (((EntryTable.Action)=[Forms]![Searchfrm]![Action])) OR (((EntryTable.LocationSpecificArea)=[Forms]![Searchfrm]![LocationSpecificArea])) OR (((EntryTable.Department)=[Forms]![Searchfrm]![Department])) OR (((EntryTable.HitorMiss)=[Forms]![Searchfrm]![HitorMiss])) OR (((EntryTable.ExternallInternall)=[Forms]![Searchfrm]![ExternallInternall]))
ORDER BY EntryTableMain.EntryDate;

 

[dhookom]

I don't see any txtbox1 or 2 or 3 in you SQL. Did you provide generic names and then SQL with actual names? If so, it only serves to confuse.

I use Nz() to provide a default value if none is provided:
WHERE EntryTable.Flawless5 = Nz([Forms]![Searchfrm]![Flawless5],EntryTable.Flawless5)

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]

 

[keeblerjp]

Ok, sorry about the generic txtbox and yes it was supposed to represent the SQL

Ok, I changed my SQL to look like this but its still pulling zero records, have a written something wrong, and if i forget to say it THANK YOU!

Heres the SQL:

SELECT EntryTable.EntryID, EntryTableMain.EntryTableMainID, EntryTableMain.EntryDate, EntryTableMain.GroupName, EntryTableMain.YourName, EntryTable.Flawless5, EntryTable.SubjectFab, EntryTable.Action, EntryTable.LocationSpecificArea, EntryTable.Department, EntryTable.HitorMiss, EntryTable.ExternallInternall, EntryTable.DetailComment, EntryTable.AdditionalInformation
FROM EntryTable LEFT JOIN EntryTableMain ON EntryTable.EntryLink=EntryTableMain.EntryTableMainID
WHERE ((((((EntryTable.Flawless5)=Nz(Forms!Searchfrm!Flawless5)) And ((EntryTable.SubjectFab)=Nz(Forms!Searchfrm!SubjectFab))) And (((EntryTable.Action)=Nz(Forms!Searchfrm!Action))) And (((EntryTable.LocationSpecificArea)=Nz(Forms!Searchfrm!LocationSpecificArea))) And (((EntryTable.Department)=Nz(Forms!Searchfrm!Department))) And (((EntryTable.HitorMiss)=Nz(Forms!Searchfrm!HitorMiss)))) And (((EntryTable.ExternallInternall)=Nz(Forms!Searchfrm!ExternallInternall)))))
ORDER BY EntryTableMain.EntryDate;

 

[r937]

i don't use the Nz function myself, but doesn't it require two parameters?

in other words, if one of the form fields in the WHERE clause is empty, what value will Nz() return?

if it returns null, then the entire WHERE clause will be false, because of all the ANDs

rudy

http://r937.com/

SQL Consulting

 

[dhookom]

keeblerjp,
You missed my syntax:
EntryTable.HitorMiss=Nz(Forms!Searchfrm!HitorMiss)
should be:
EntryTable.HitorMiss=Nz(Forms!Searchfrm!HitorMiss, EntryTable.HitorMiss)
If your control is null then the expression results in:
EntryTable.HitorMiss=EntryTable.HitorMiss
which always results in true (as long as EntryTable.HitorMiss is not null).

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]

 

[keeblerjp]

If i could shake your hand right now, i would probably never let it go, IT WORKS!!!!! Thank You so much.

Just like a man, i forget to read all the way before doing something.

Again, you just made my weekend