How do I create a effective form to search a subform/query 1

[Plumes]

Hi All

I'm having trouble creating a suitable form to search the data in a query.

I have created a form with a subform within it and have created controls in the main form, mainly combo boxes, for all the fields in the sub form and attached a macro to requery when a control in the main form is updated. This works ok except I wanted the query to ignore null values in any of the controls in the main form so all records are displayed before any updates and so I can search by one field alone.

I have used:

[control1] Or Like [control1] Is Null

in the criteria of the fields and this works except there are at least 20 fields in the query and when this rule is applied to all it fills lines and lines of criteria and the query slows to a halt.

If anyone knows a way around this or a better method of doing this then I be very grateful if you could help.

Matt

 

[Blorf]

I dunno if this will speed it up much, but try

Like "*" & [Contro] & "*"

This unfortunately allow wild cards, but if you are using combo boxes, will probably never happen.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[Plumes]

Thanks ChaZ

That worked great. I also have some date fields where I've created Date_From and Date_To controls and instead of null values I've set default values. To show all records I have a date before any records were created as one and Date() as the other, this can't be applied to a date field which has dates in the future though. Is there a way of applying the above to date values?

Matt

 

[Blorf]

If you forced the from date to be earlier than any begening dates, and left the to date blank or null, maybe something like this would work

>=from_date and <=(iif (to_date is null, date()+10000, date())

This would be the early date to today plus 100000 days if the todate is null, or early date to date entered if not null.

ChaZ


Ascii dumb question, get a dumb Ansi