Search querry

[sdc1234]

I have 3 columns; last, first and mi; my code I am using for the search is (Like "*" & [Forms]![SearchF]![Last] & "*"), the only problem is it only searches by last name. I want to do a search like search last, first and MI; thank you for your help


-novice...

[rzw0wr]

Try putting Like "*" & [Forms]![SearchF]![Last] & "*" in last field.
(Like "*" & [Forms]![SearchF]![first] & "*") in first field.
(Like "*" & [Forms]![SearchF]![mi] & "*") in mi field.

In your query.

Dale

[sdc1234]

Try putting Like "*" & [Forms]![SearchF]![Last] & "*" in last field.
(Like "*" & [Forms]![SearchF]![first] & "*") in first field.
(Like "*" & [Forms]![SearchF]![mi] & "*") in mi field.

In your query.

Dale

thank you that worked, however I am still having problems generating reports for people without middle names... ex: I have a jon doe and a jon a. doe, I put in the jon a doe and I get dates and dates remaining, but when I just put in jon doe I get a blank report... Is there another way I can do my search?

[rzw0wr]

Copy and Paste your SQL.

Dale

[orange]

You could see if there is anything in the MI field.

If len(Mi) & "" = 0 then >>>>>No middle initial

[rzw0wr]

orange got me thinking.
If I remember correctly that if the field held a null this did not work.
Something had to be in every field in the record.

Dale

[sdc1234]

orange got me thinking.
If I remember correctly that if the field held a null this did not work.
Something had to be in every field in the record.

Dale

I have on this querry: ID, LastName, First,Name, MI,(under critera of these three I have the code that you provided for searching), StartDate, EncdDate, then I have an expression
sdays: Sum(IIf([x-test1]![StartDate]=[x-test1A]![StartDate],[x-test1A]![chk],IIf([x-test1]![StartDate]=[x-test2A]![StartDate],[x-test2A]![chk],IIf([x-test1]![StartDate]=[x-test3A]![StartDate],[x-test3A]![chk],0)))) (to calculate the # of days remaining)

[sdc1234]

I have on this querry: ID, LastName, First,Name, MI,(under critera of these three I have the code that you provided for searching), StartDate, EncdDate, then I have an expression
sdays: Sum(IIf([x-test1]![StartDate]=[x-test1A]![StartDate],[x-test1A]![chk],IIf([x-test1]![StartDate]=[x-test2A]![StartDate],[x-test2A]![chk],IIf([x-test1]![StartDate]=[x-test3A]![StartDate],[x-test3A]![chk],0)))) (to calculate the # of days remaining)

have I provided enough info?

[rzw0wr]

Do you have the same problem or another one.

If you have another problem you will get better coverage if you start another thread.

Dale

[orange]

The query shown in post #7 has an AND condition on Firstname, Lastname and MI.
That means they all must meet your criteria. My guess is they can NOT all meet your
Search condition simultaneously.

[sdc1234]

The query shown in post #7 has an AND condition on Firstname, Lastname and MI.
That means they all must meet your criteria. My guess is they can NOT all meet your
Search condition simultaneously.

I do not understand, I am searching for a john doe and report shows up blank, my search criteria is set to search for a first, last and middle. when I run the report it searches last name: Doe, First Name: Jon, MI:J, the report comes out how I want with the dates of vacation, however if there is another Jon Doe without a middle name it comes up blank, the code If len(Mi) & "" = 0 then >>>>>No middle initial, where would it go under in my querry. I tried to enter it under MI field but came up with errors.

[orange]

Tell us explicitly What you want to happen, under what conditions, and I'm sure someone will have ideas.
How does the reporting of people with and without middle initials fit in with your search?
Are you searching for a specific record(s), or are you trying to run a report with conditions?

Can you give a clear example showing us what you want as the result when you Search for X?

[sdc1234]

Tell us explicitly What you want to happen, under what conditions, and I'm sure someone will have ideas.
How does the reporting of people with and without middle initials fit in with your search?
Are you searching for a specific record(s), or are you trying to run a report with conditions?

Can you give a clear example showing us what you want as the result when you Search for X?

I have a database of about 1500 employees, I want to run a report on a specific employee and send them a confirmation of their total/allowed vacation day I have jon j doe and jon doe in my database, In a querry I did a search last, first and MI, the report pulls people with middle names and when I just enter a last and first name, the report comes up blank, (see attachment) the blank report is the jon doe but I have dates for him as well. I hope this helps

-novice

[orange]

What is the Primary key of your Employee table?

You need a means to uniquely identify John Doe vs Jon Doe vs Jon J Doe... Gion Dough vs Juan Dowe

Surely with 1500 employees and these types of calculations, you're not relying on Names and whether or not you get the proper middle initial.

You might want to consider a different search approach. Search via Lastname against a combo of Distinct Lastnames; cascading combo to Distinct Firstnames within Lastname selected; etc.

Good luck with your project.

[sdc1234]

What is the Primary key of your Employee table?

You need a means to uniquely identify John Doe vs Jon Doe vs Jon J Doe... Gion Dough vs Juan Dowe

Surely with 1500 employees and these types of calculations, you're not relying on Names and whether or not you get the proper middle initial.

You might want to consider a different search approach. Search via Lastname against a combo of Distinct Lastnames; cascading combo to Distinct Firstnames within Lastname selected; etc.

Good luck with your project.

Primary Key is ID.... do you think it would work if I did the search by email address instead of last, first and mi?