Nz/IIF Problem - Ms Access 97

[salisha]

I am creating this Parameter query and I need a little help. I have a couple fields such as lastname, firstname, address etc. In the query design mode, I am setting the criteria where the user will be prompted for a first
name and last name and get that particular name.
For the lastname field, I set it up so the user can type the first few letters of the last name, and get all entries that pertain to it. (I used the LIKE function) So for example, the user will type in SM, and get all
lastnames that start with SM. My problem is I need help setting up the criteria for the firstname field. The user doesn't necesssarily have to type in a firstname. So I need to write some sort of criteria that if the user does not type in firstname, it will only bring up all entries with that particular last name.
So far I have this code which doesn't work if the user does not type in a first name:

IIf(Nz([Enter First Name]="&quot,Like [Enter first few letters of last name:] & "*",[Enter first Name])

Like [Enter first few letters of last name:] & "*", - This is the criteria i have listed under the lastname field.

PLEASE HELP!!!!!!!!!!!!!!
thanks in advance.
salisha

 

[psemianonymous]

Just use the Nz function to put in a "*" if the first name parameter is null, like so:

Like (  """" & Nz([Enter First Name]) & "*" & """"  )

If it's null, it just gets -->LIKE &quot;*&quot;<--.
It it's filled in, say with JOHN, it gets: -->LIKE &quot;JOHN*&quot;<--


That should work.


--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[salisha]

hey foolio12,
thank you so much for your help!