Multi-Field Search

  • Thread starter Thread starter Sharkbyte
  • Start date Start date
S

Sharkbyte

I am trying to put together a search page. There are a total of 4 combo
boxes and 5 text boxes. 4 of the text boxes are used as Start/End dates for
2 date fields.

I have used Like "*" & [forms]![formname]![control1] & "*", with multiple
fields, successfully before. However, no matter what I do this time, I am
losing records that contain NULLs; and I haven't even gotten to try and work
in the date fields...

I am using Access2003 FE and SQL-Server2005 BE. My final query does link 4
tables; however, I did test it without any linked tables, with the same
results.

Any suggestions what I may be missing?

TIA

Sharkbyte
 
hmm, well, your info is a little sketchy, but i don't think

Like "*" & [forms]![formname]![control1] & "*"

is going to get you Null values. have you tried

Like "*" & [forms]![formname]![control1] & "*" Or
[forms]![formname]![control1] Is Null

?

hth
 
But if I do that, then when I am searching for a specific value for Control1,
the NULLS aren't filtered out...


tina said:
hmm, well, your info is a little sketchy, but i don't think

Like "*" & [forms]![formname]![control1] & "*"

is going to get you Null values. have you tried

Like "*" & [forms]![formname]![control1] & "*" Or
[forms]![formname]![control1] Is Null

?

hth


Sharkbyte said:
I am trying to put together a search page. There are a total of 4 combo
boxes and 5 text boxes. 4 of the text boxes are used as Start/End dates for
2 date fields.

I have used Like "*" & [forms]![formname]![control1] & "*", with multiple
fields, successfully before. However, no matter what I do this time, I am
losing records that contain NULLs; and I haven't even gotten to try and work
in the date fields...

I am using Access2003 FE and SQL-Server2005 BE. My final query does link 4
tables; however, I did test it without any linked tables, with the same
results.

Any suggestions what I may be missing?

TIA

Sharkbyte
Click to expand...
Click to expand...
 
But if I do that, then when I am searching for a specific value for Control1,
the NULLS aren't filtered out...
Click to expand...

Actually not so: the criterion

Like "*" & [forms]![formname]![control1] & "*" Or
[forms]![formname]![control1] Is Null

will be True if a) there is some value in control1 and it matches some
substring of the field or b) if control1 is empty and you're NOT searching
that field.

Actually I'd use an explicit match rather than the wildcards:

= [forms]![formname]![control1] Or [forms]![formname]![control1] Is Null
 
Back
Top