LIKE in embedded if statement?

[lulugrant_is_a_dog]

I am trying to run a query based on the values in text boxes on a form
(Advanced_Search).

I have worked out the following embedded if statement to use as the
criteria:
IIf(IsNull([Forms]![Advanced_Search]![txtAlbumno]),[Album No],Like "*"
& [Forms]![Advanced_Search]![txtAlbumno] & "*")

The 'Like' part is where I have gone wrong because it doesn't work.

What have I done wrong?
Can anyone help me?

 

[Allen Browne]

Switch the query to SQL View (View menu), and adjust the WHERE clause like
this:

WHERE (([Forms]![Advanced_Search]![txtAlbumno] Is Null)
OR ([Field1] = [Forms]![Advanced_Search]![txtAlbumno]))

Replace "Field1" with the name of your field.

If you are interested in building your own search form using code to avoid
these convoluted clauses, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[fredg]

I am trying to run a query based on the values in text boxes on a form
(Advanced_Search).

I have worked out the following embedded if statement to use as the
criteria:
IIf(IsNull([Forms]![Advanced_Search]![txtAlbumno]),[Album No],Like "*"
& [Forms]![Advanced_Search]![txtAlbumno] & "*")

The 'Like' part is where I have gone wrong because it doesn't work.

What have I done wrong?
Can anyone help me?

I can't really figure out from your syntax what it is that you wish to
occur.

As written above, if the form's [txtAlbuno] is null, you wish to
display all records that have that [Album No] in the field. However if
the form's [txtAlbumno] is null there is no Album No to return.

I'll guess that if the form control is null you wish to return all the
records and if the form control is not null, just return the record
that matches the value in the form control.

Like IIf(IsNull([Forms]![Advanced_Search]![txtAlbumno]),"*",
[Forms]![Advanced_Search]![txtAlbumno])

 

[Allen Browne]

Fred, the suggestion you made is used by thousands of people, but are you
aware that it does not actually doesn't return all records?

The criterion:
Like "*"
has the effect of culling the nulls.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to run a query based on the values in text boxes on a form
(Advanced_Search).

I have worked out the following embedded if statement to use as the
criteria:
IIf(IsNull([Forms]![Advanced_Search]![txtAlbumno]),[Album No],Like "*"
& [Forms]![Advanced_Search]![txtAlbumno] & "*")

The 'Like' part is where I have gone wrong because it doesn't work.

What have I done wrong?
Can anyone help me?

I can't really figure out from your syntax what it is that you wish to
occur.

As written above, if the form's [txtAlbuno] is null, you wish to
display all records that have that [Album No] in the field. However if
the form's [txtAlbumno] is null there is no Album No to return.

I'll guess that if the form control is null you wish to return all the
records and if the form control is not null, just return the record
that matches the value in the form control.

Like IIf(IsNull([Forms]![Advanced_Search]![txtAlbumno]),"*",
[Forms]![Advanced_Search]![txtAlbumno])