Multiple null values

[deejay_totoro]

Hello,

I am trying to make a "report" style screen that will allow users to call up a report based on the criteria they enter.

The query is passed the parameters from the controls on the form. This works fine.

Now in order to allow for user selection, it was suggested to use the following:

[Forms]![FormName]![controlName]

and in the criteria

Like [Forms]![FormName]![controlName]) Is Null

And this does indeed return all records even if nothing is entered.

The problem comes when I try to build another criteria. For example, say I want to generate a query that is within a certain date range and also by user name?

The above example does not seem to allow more than one use. If I use the above query example, it does indeed work with one column - but how can I get a similar result on multiple columns?

I want to make almost all the parameters in the underlying query dynamic, that is, the user can decide what they want.


thank you very much!

dj-T

 

[dcx693]

Here is a solution based on something that Jon K posted a while ago:

Use expressions like these in the Field: line of the QBE grid:
IIf(IsNull([forms]![formname]![control]),True,[field]=[forms]![formname]![control]), (where [field] is the name of the field from the table that you're comparing to the form control), uncheck the Show checkbox, and set the Criteria to <>False. Do that for every control that might have a Null value. Since you're not showing the result of that expression, you'll still need a column to display the results, so make sure you pull down the desired fields from your tables into your query.

I hope you got all that.

 

[deejay_totoro]

thanks and help!

Hello,

actually, there is no underlying table for the form - its just a collection of comboboxes. The user selects something (like name or company - which themselves come from a query). This is then passed to a control.

This control is then available to the the query.

I tried to use your example, but I couldn't get it to work. Maybe because there is no table?

Any ideas?

dj_T

 

[dcx693]

I tried to use your example, but I couldn't get it to work. Maybe because there is no table?

Each query needs to have at least one table or query that goes into it. I know you're using a query because you're asking about criteria and you said "The query is passed the parameters from the controls on the form."

 

[deejay_totoro]

Got it!

Ah...

I re-checked my typing and re-read your post.

And finally it worked!

I have created multiple columns and it works great.

Thank you very much!

dj_T