Criteria in Query grid.....

[spalmateer]

Hi,
I just found out that the search form we've been using for the last two years was fundamentally flawed- only showing records that were not null in certain fields. I guess there is ALOT of value in extensively testing before deploying!! Anyway- I have a query that receives its criteria from a value in a textbox on a form. Here is the formula I'm using:

Like IIf(IsNull([Forms]![frmSearch]![txtLastName]),"*",[Forms]![frmSearch]![txtLastName] & "*")

What I would like it to do is if the value on the textbox is null, then I don't want any value in the criteria, therefore it would show all records, null or not. I've come to find out the problem is with the "LIKE" expression. If there is nothing in the textbox, then the expression returns Like * and then it only shows records that contain a value. So I tried the following formula to try to eliminate the like unless there is a value in the textbox:

=IIf(IsNull([Forms]![frmSearch]![txtLastName]),"","Like " & [Forms]![frmSearch]![txtLastName] & "*")

This didn't work either. Is there a way I can pull a value from a textbox for the criteria of a query but only if there is a value in the textbox. If there isn't then it should have nothing in the criteria. "" doesn't seem to work. Any help with this would be much appreciated!! Thanks!
Scott


[This message has been edited by spalmateer (edited 04-21-2002).]

 

[spalmateer]

Hi Pat,
That criteria works but I didn't explain my situation thoroughly enough. On my form I have a textbox where they enter the last name (cboLastName). After update of the textbox I have the following code:

Private Sub cboLastName_AfterUpdate()
If IsNull(Me![cboLastName]) Then
Me![txtLastName] = Null
Else: Me![txtLastName] = Me![cboLastName] & "*"
End If
Me.frmSubSearch.Requery
Me.txtRecord.Requery
End Sub

This takes the entry and if it is not null, puts an asterisk after it- therefore park becomes park*. Your code does what it should because previously if there were nothing in the textbox, it would only show the records that contained a last name value. Now it shows them all. But in my situation that code is looking for a record that actually matches park*. It won't let me use the asterisk as a wildcard. Is there another way to make this work? Can it work for multiple fields? Thank You!!
Scott


[This message has been edited by spalmateer (edited 04-22-2002).]

 

[spalmateer]

Finally got it to work. I used Pat's suggestion but instead of = I used LIKE.

Where LastName LIKE [Forms]![frmSearch]![txtLastName] or [Forms]![frmSearch]![txtLastName] Is Null;

Then I had to update/save the record before it would pull the value from the second textbox.