Query Criteria and Function()

[CdRsKuLL]

Hi Peeps,

Really hoping someone can help.

I'm trying to create a simple search page. I have many fields and have coded it like this....

In a text box you type what you want to search for. This then populates a global variable.. ie.. searchtext = "big"

I then have a public function like..

Function SearchFun()
If searchtext = "" Then SearchFun = "*" Else SearchFun = searchtext
End Function


In my query criteria I have Like SearchFun().. Now this works great if the field has something in it, BUT its not allowing Null values. How can I have it if the searchtext is blank is lists everything including Null Values ?

Hope I've explained myself well enough

many thanks,

Steve

 

[Yarp]

I am not sure but it sounds like a "" vs Null issue to me. The following code change may sort your problem out:

Function SearchFun()
If searchtext Is Null Then SearchFun = "*" Else SearchFun = searchtext
End Function

I haven't tried this yet, but hopefully it works!

**EDIT** Wait, think I got the wrong end of the stick. Are you looking for Null's to be included in your search results and currently they aren't when the searchtext is blank??

 

[CdRsKuLL]

yep spot on m8..

I did try putting something like this in the Criteria but it didn't work...

IIf(SearchFun()="*",Is Null,Like SearchFun())
Thanks

Steve

 

[vbaInet]

Is Null will not be interpreted as the SQL Is Null when put inside an IIF() function. You would be better off building the sql string and applying a filter on the form or changing it's record source to the built sql statement. Have a look at this:

http://baldyweb.com/BuildSQL.htm

 

[Yarp]

I actually have a search form with a subquery. I didn't use VBA to define the subquery as I didn't know enough VBA to do it then. I have a query which defines the subquery content, and my criteria is

Like IIf([forms]![form1]![keyword] Is Null,"*","*" & [forms]![form1]![keyword] & "*")

[keyword] is the name of the text box where the filter is entered by the user. I have had no issues to date with this method. However, I do not have null values in the field that I am filtering. Any help??

 

[vbaInet]

I actually have a search form with a subquery. I didn't use VBA to define the subquery as I didn't know enough VBA to do it then. I have a query which defines the subquery content, and my criteria is

Like IIf([forms]![form1]![keyword] Is Null,"*","*" & [forms]![form1]![keyword] & "*")

[keyword] is the name of the text box where the filter is entered by the user. I have had no issues to date with this method. However, I do not have null values in the field that I am filtering. Any help??

Put IS NULL in the OR criteria box of that field.

 

[Yarp]

Of course. So simple and absolutely correct!

 

[CdRsKuLL]

Put IS NULL in the OR criteria box of that field.

Wouldn't this then show up all NULL values even when there is a value in the function ? I only want NULL values to show when nothing is in the search box..

Proper stumped :-!

Thanks for all the help mind

 

[Yarp]

I haven't tried it, but can you put "*" OR Is Null as the true condition of the IIF?

 

[vbaInet]

Wouldn't this then show up all NULL values even when there is a value in the function ? I only want NULL values to show when nothing is in the search box..

Proper stumped :-!

Thanks for all the help mind

That was for Yarp, not you. Have a look at the link I gave.

 

[vbaInet]

I haven't tried it, but can you put "*" OR Is Null as the true condition of the IIF?

"*" [FieldName] OR Is Null

And no not inside the IIF argument. Like I mentioned in post #4 it will not be evaluated in the True or False arguments.

 

[CdRsKuLL]

Hi fella,

Yep I'm already quite confident with vb coding the mysql line, just it's alot easier using the query box. Did alot ages ago with php and mysql.. very similar

Will give it a go and see what happens

cheers

Steve

 

[vbaInet]

Happy coding

Post a quick reply if you get stuck.

 

[CdRsKuLL]

just thought I would post how I managed it in the end..

I dropped into VBA and did the mysql statement in there...

Dim strSQL As String

'build sql up
strSQL = "SELECT Contracts.[Contract Number].....

If GetEst() <> "" Then strSQL = strSQL & " A....
If GetCounty() <> "" Then strSQL = strSQL & " A....
strSQL = strSQL & ") ORDER BY Contracts.[Contract Number] DESC;"

'load sql into form
Me.RecordSource = strSQL

'use sql
Me.Requery


Job done

thanks guys