Multi-Field Search

[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

 

[tina]

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]

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

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

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

 

[John W. Vinson]

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

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