[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

Access Query Name Parameter

I would like to create a query in Access that enables the user to enter the last name of a person and/or the first name and locates a record based on that criteria.

What I would like to happen is allow the user to enter a last name and a first name also if he/she wants to. For example, if the user enters a value for the last name, then the query will retrieve one or more records with a matching last name. However, if the user also enters a first name, then it will locate one or more records with both a matching last name and first name. The way the query is set up now, it will locate records that have both a matching last name as well as first name eventhough they may belong to different people. I do not want that. I realize I have to put the first name parameter in the criteria row next to the last name parameter, but if I did that, and the user did not enter any value for the first name, it won't return any records (because it is looking for a matching blank value for the first name).

How can I set it up so that it works the way I want it to?

Thanks.
Graduation-Query.jpg
0
geeta_m9
Asked:
geeta_m9
  • 4
  • 3
1 Solution
 
McOzCommented:
Try using the following in the criteria fields (keep on the same line)

Nz([Enter First Name],[First Name]) | Nz([Enter Last Name],[Last Name])

Cheers
0
 
geeta_m9Author Commented:
It does not like the "vertical bar", i.e., "|". So I changed it to an "OR", but now it is throwing up the following error (see attached).
0
 
geeta_m9Author Commented:
Ok, I changed the query to the following and I think it is working now. I need to test it some more.

Nz([Enter First Name],[qGradReview.First Name]) Or Nz([Enter Last Name],[qGradReview.Last Name])
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
McOzCommented:
Sorry for the confusion - the vertical bar was supposed to denote the dividing line between the columns!

So in the first name column, put Nz([Enter First Name],[First Name])

and in the last name column put Nz([Enter Last Name],[Last Name])
0
 
geeta_m9Author Commented:
It works great! Thank you!!!
0
 
geeta_m9Author Commented:
Thank you for a quick and effective solution!
0
 
McOzCommented:
No worries!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now