QBF criteria

[wegendereze]

Hello,

I've developed a QBF arrangement using one table, one query, and one form.

The table is a list of records from a library database. The form has several text boxes specifying several individual fields from that table. There is one button in the form running a macro that runs a query.

This query has all the text box fields from the form, each on in a column with the following criteria:

Like [Forms]![FormName]![TextBoxName] & "*" Or Is Null


This returns the records needed, but also obviously includes any NULL values. What I need is criteria that returns the NULL values only when the text box field is blank, and returns all related values when the text box field is filled out. I've tried an iif statement along the lines of:

iif (isnull([Forms]![FormName]![TextBoxName]),NULL, [Forms]![FormName]![TextBoxName] & "*" )

However this yields no results. I'm new to SQL so any help is appreciated.

Thanks,
Jay

[nico5038]

Hi Jay,

You can't switch between Null or a value in a query dynamically. When a LIKE is defined, all Null's will be skipped...

When you have a field on the form you can add a button to execute the filtering needed and use the form's .Filter property to get the needed result.

The needed code for the button's OnClick event would look like:

Private Sub Command17_Click()
If Len(Nz(Me.TextBoxName)) > 0 Then
    Me.Filter = "<Fieldname from table> like '*" & Me.TextBoxName & "*'"
Else
    Me.Filter = "<Fieldname from table> Is Null"
End If
Me.FilterOn = True
End Sub
 

Just replace the "<Fieldname from table>" with the proper fieldname.

Nic;o)