help w/ access 2k3 query criteria -user prompt for last name &/or

[sj]

greetz and thanks in advance for assistance with what i thought should be a
pretty simple task......

i am helping someone who has a single table (may eventually move to
normalized tables but currently still just one table) with fields such as
lastname, firstname, ssn, age, phone, etc. i am using the query criteria
field to prompt the user for input for lastname and also a prompt for ssn. i
am using the following syntax:
Like "*" & [Please enter last name:] & "*"
Like "*" & [Please enter SSN:] & "*"
but unfortunately am not getting all and/or correct records returned.

my goal is to allow the user to input either all or partial last name and/or
all or partial SSN and return records that match appropriately. is there a
way to successfully do this? if so, what should the criteria field contain
to be able to do so. if this can't be done then i would be satisfied with
the user being able to enter either the last name OR the SSN (leaving one of
the prompts blank) and pull up records that match which ever one they enter
(this doesn't seem to work correctly either).

this seemed/seems like a really easy task but i unfortunately can't get it
to work...... any help you can provide is much appreciated! thanks again!

 

[John Vinson]

i am helping someone who has a single table (may eventually move to
normalized tables but currently still just one table) with fields such as
lastname, firstname, ssn, age, phone, etc. i am using the query criteria
field to prompt the user for input for lastname and also a prompt for ssn. i
am using the following syntax:
Like "*" & [Please enter last name:] & "*"
Like "*" & [Please enter SSN:] & "*"
but unfortunately am not getting all and/or correct records returned.

Where are you entering these criteria? Are they on the same or
different lines in the query grid (they should be on the same line)?
WHat is the datatype of SSN (it's not Number I hope?) Does the stored
SSN contain hyphens, or just digits?

Can you give an example of an incorrect retrieval?

John W. Vinson[MVP]

 

[sj]

hello john et al,

thanks for the response. i have tried the like clauses on the same query
grid line as well as different lines (i.e. - or condition). i would like for
the user to be able to input one of two scenarios: a) enter all or portion of
a last name and leave the ssn blank or b) leave the last name blank and enter
all or a portion of the ssn. SSN is a text field and the stored value does
contain hyphens (which are also input in the user prompt when the ssn is
entered).

an example of an incorrect retrieval follows....

i have a real record in the db with last name = testerman and test records
in the db with last name = 1) test 2) test2 3) test3 when i enter the text:
test into the user prompt i retrieve records testerman and test but not test2
or test3. if i leave last name blank and enter a partial ssn (such as 123-
which does exist in the db) i get all records retrieved.

i hope this helps clarify and once again thanks in advance for any help you
can provide...... sj

i am helping someone who has a single table (may eventually move to
normalized tables but currently still just one table) with fields such as
lastname, firstname, ssn, age, phone, etc. i am using the query criteria
field to prompt the user for input for lastname and also a prompt for ssn. i
am using the following syntax:
Like "*" & [Please enter last name:] & "*"
Like "*" & [Please enter SSN:] & "*"
but unfortunately am not getting all and/or correct records returned.

Where are you entering these criteria? Are they on the same or
different lines in the query grid (they should be on the same line)?
WHat is the datatype of SSN (it's not Number I hope?) Does the stored
SSN contain hyphens, or just digits?

Can you give an example of an incorrect retrieval?

John W. Vinson[MVP]