Search Query from a form.... HOW??? 1

[DrDan1]

Hey all. I'm getting a bit frustrated here. Every time I think I've solved the problem, another pops up. I'm trying to run a Query that will look for a string (In a selected field) which it gets from a text box (Called Title) in a form (called 'FindIt Form'). At first I tried this in the Criteria box of the field in the query:
--[Forms]![FindIt Form]![Title]--
This would only find records that matched the data in the text box exactly. e.g. if you type in "Box" it wouldn't find "Box 1." No Next I tried this:
--Like[Forms]![FindIt Form]![Title] & "*"--
This seemed to work but it wouldn't return Null values if the text box was left empty. This is important as there are more than one text boxes it will search from at once. So now I'm trying this:
--IIf([Forms]![FindIt Form]![Title] Is Null,"","Like [Forms]![FindIt Form]![Title]& "*" &quot

BUT IT WON'T WORK!!!! Hmph. If anyone can help, you'll make me a happy man... or just less annoyed. Thanks.

 

[DrDan1]

Oh yeah. Sorry. I forgot to mention I'm using Access 2000.

 

[wyld]

Try this...

IIf(isnull([Forms]![FindIt Form]![Title]),"","Like [Forms]![FindIt Form]![Title]& "*" &quot

 

[DrDan1]

Nope. The same thing happens. I get an error box popping up that says,

"This expression is typed incorractly, or it is too compicated to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

 

[DrDan1]

Is there somethime I could maybe put in the Field name of the query that would help? What exactly does Nz do and could it be used? I've tried this in the Field Name, " Nz([Title])"

It seemed to do something, but now returns blank cells instead of all the cells if the forms textbox is left empty. This is puzzle. One that needs solving.

 

[DrDan1]

The formula I'm currently using is:

IIf(IsNull([Forms]![FindIt Form]![Title]),"",[Forms]![FindIt Form]![Title])

Which as I understand, should work. This works when I type something in but not when the txt box is blank. If, however, I put text between the empty "'s then it will look for that text if the txt box is blank. I thought "" would return all records in the field. I've tried "*" and like "*" as well. no cigar!

 

[wyld]

I didn't notice you were using Access 2000. Try

IIf([Forms]![FindIt Form]![Title] = nothing,"","Like [Forms]![FindIt Form]![Title]& "*" &quot

 

[DrDan1]

No luck. It says I have entered an operator (Such as a +) in an expression without a corrosponding opperand.

The IsNull method you mentioned earlier seems to work. It's just that I have problem either with wildcards or getting it to return all records when the TxtBox is blank.

 

[wyld]

Okay...how about?

IIf(isnull([Forms]![FindIt Form]![Title]) or [Forms]![FindIt Form]![Title] = "" ,"","Like [Forms]![FindIt Form]![Title]& "*" &quot

 

[DrDan1]

Once again... no luck. I'm stumped.

 

[DrDan1]

I'm a step closer now. I've tried this and it works:

IIf(IsNull([Forms]![FindIt Form]![Title]),[Mega Sheet].[Title],[Forms]![FindIt Form]![Title])

Mega Sheet = The table that hols the data
Title = The relevant Field in the table.

So now if the txt box is null, it retrieves all the data from the Title field in the Table. Now all I would like it to do is add a wildcard to whatever is typed in the txt box.

E.g. if "King" is type in I would like it to find "Kings Cross" as well as anything else with "King" in it.

Getting closer. Thanks for the help on the IsNull thing Wyld. Wouldn't have got this far without it.