how to search by lastname and firstname or just the lastname 1

[janag]

I want to make a form to search a table for the first and last name or just the last name if the first name is left blank. Currently I am taking the names from a form and putting them in query to find the names that match. The problem is when I leave the first name blank, the query searches for a blank string in the first name. If the first name is left blank I want the query to show all first names that go with the last name. Here is what I have as the criteria for the last name in the query:

[forms]![frmEnterSpeakerName]![SpeakerLast]

I have not been able to figure out what to put in for the criteria for the firstname in the query.

Thanks,
J

 

[mgolla]

Nz([forms]![frmEnterSpeakerName]![SpeakerLast],Like'*')

HTH
Mike

 

[mgolla]

Meant:

Nz([forms]![frmEnterSpeakerName]![SpeakerFirst],Like'*')

HTH
Mike

 

[janag]

Thanks for the tip, but it does not work if I do not enter the first name.

Thanks,

Jana

 

[mgolla]

Post your SQL from the query and I can take try to help out that way

HTH
Mike

 

[janag]

Thanks for your time. Here is my SQL code:

SELECT TAPEDATA.LIB, TAPEDATA.EVENT, TAPEDATA.TAPETITLE, TAPEDATA.SPKRLAST, TAPEDATA.FIRST_MI, TAPEDATA.RECORDDATE, TAPEDATA.[SentToSchultz's], TAPEDATA.TAPENOTE, TAPEDATA.TOM, TAPEDATA.DateTOM, TAPEDATA.SubscriptionTape, TAPEDATA.DateSubscription1, TAPEDATA.DateSubscription2
FROM TAPEDATA
WHERE (((TAPEDATA.SPKRLAST)=[forms]![frmEnterSpeakerName]![SpeakerLast]) AND ((TAPEDATA.FIRST_MI)=Nz([forms]![frmEnterSpeakerName]![SpeakerFirst],([TAPEDATA].[FIRST_MI]) Like '*')))
ORDER BY TAPEDATA.SPKRLAST DESC , TAPEDATA.FIRST_MI DESC;

Jana

 

[cghoga]

To leave the first name blank enter the following....

Like iif([forms]![frmEnterSpeakerName]![SpeakerFirst].value is null,"*",[forms]![frmEnterSpeakerFirst]![SpeakerLast].value)

If you wanted to leave the last name blank and just find the first name matches, enter this in the last name criteria....


Like iif([forms]![frmEnterSpeakerName]![SpeakerLast].value is null,"*",[forms]![frmEnterSpeakerLast]![SpeakerLast].value)

 

[janag]

Thanks for the help it works now. I used the Like IIF statement.

Jana