iif statement 3

[handlewithcare535]

I am trying to make a query which involves having the option of typing in numerous parameters (sort of like a search function), but I can't work out how to stop it from bringing back only empty fields if I don't type in a value for one of the parameters. For example, one of the parameters is ID number, but if they don't know that, then I want all ID numbers to be included in the search. And this might be getting a bit too hard for me, but I would also like to have the option of, if I type in the ID number, then there's obviously only one record for this, but I don't want to have to go through all the rest of the options!

Hope this makes sense to someone who can help me!!

Many thanks,

hwc535

 

[lars7]

Maybe something like this could help:

http://www.fontstuff.com/access/acctut17.htm

 

[dhookom]

You can set the criteria under a field in a query grid to something like:
Forms!frmA!txtID or Forms!frmA!txtID Is 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]

 

[handlewithcare535]

Thanks, I've managed to do that now!! My customer is getting even harder to pleaase now though! Any ideas on how to filter the comboboxes depending on selections already made on the form.

e.g. If I were to have two comboboxes on a form, first name and last name for instance. If I selected the first name, is there a way to filer the last name combobox so that only last names which go with the first name appear?

Thanks

hwc535

 

[dhookom]

Roger Carlson has a sample of how to cascade combo boxes at

http://www.rogersaccesslibrary.com/TableOfContents3.asp.

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]

 

[randy700]

Add a WHERE clause to the row source of the FirstName combo box. Something like...

=SELECT FirstName FROM myTable WHERE LastName = cboLastName

Randy

 

[Rick4077]

Okay, what about this angle . . .

SELECT C.OfcName.

C represents the tblName and OfcName is the only
field in that table. (See the FROM clause at the bottom of the query)

Heck, it's the only field in ALL of my client tables.

There must be a way to
1.) produce a list of client tables.
2.) Select one table name from that list
3.) Use that table name as “C” before I run the below query.

This way, I can use the below query for ALL of my clients tables in lieu of having a separate query and report for each client! It’s madness I say.

There must be a simple way to do this? (Parameter Query???)

SELECT C.OfcName, 

Sum(IIf(S.ListName Like C.OfcName & "*" And S.SellName Like C.OfcName & "*",1,0))*2 AS WSO, 
Sum(IIf(S.ListName Like C.OfcName & "*" And S.SellName Like C.OfcName & "*",S.SalePrice,0))*2 AS WSODV, Sum(IIf(S.ListName Like C.OfcName & "*" And Nz(S.SellName) Not Like C.OfcName & "*",1,0)) AS TSO, Sum(IIf(S.ListName Like C.OfcName & "*" And Nz(S.SellName) Not Like C.OfcName & "*",S.SalePrice,0)) AS TSODV, Sum(IIf(S.ListName Not Like C.OfcName & "*" And S.SellName Like C.OfcName & "*",1,0)) AS WST, Sum(IIf(S.ListName Not Like C.OfcName & "*" And S.SellName Like C.OfcName & "*",S.SalePrice,0)) AS WSTDV, 

WSO+TSO+WST AS [Total Transactions], Sum(IIf(S.ListName Like C.OfcName & "*",S.SalePrice,0)) AS [Listing Dollar Volume], WSODV+TSODV+WSTDV AS [Listing and Sales Dollar Volume]

FROM CBAll AS C, SCMLS AS S
GROUP BY C.OfcName
ORDER BY 10;

Thanks . . . Rick

 

[dhookom]

I think Rick4077 is very confused. Looks like his post should be in a different thread.

Randy700,
Did you actually try to implement you solution in a test environment?

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]