Trouble with code in query

[Johnthelleader]

I am having trouble with some code I am using on a query. Part of the query comes from two fields in a form. The rest comes from a table. The query then populates a report. The code I have written works fine. The trouble starts when I try and enter in some code so that if some of the fields in the form is left blank, it just returns values based on the fileds that has data, so ignores any blank fields.
I have tried to use examples illustrated in this Forum, but it does not seem to like it. I keep on getting the request to fill in the parameter value. I am sure it is something simple!

This is the code I have entered for the Invoice Date Field:
Between [Forms]![Invoices]![Invoice date 1] And [Forms]![Invoices]![Invoice date 2]
(this is so the user can enter a date range with the two fields)

 

[boblarson]

This is the code I have entered for the Invoice Date Field:
Between [Forms]![Invoices]![Invoice date 1] And [Forms]![Invoices]![Invoice date 2]
(this is so the user can enter a date range with the two fields)

This goes in the criteria under your date field and not as a field itself.

 

[Johnthelleader]

Thanks for coming back.
I am sorry but the code was in the Criteria section and not field name. Any ideas on how I sort out the null value issue?

 

[rainman89]

NZ() function
Edit: wait by ignores blank fields u mean skip that entry completely?

in the criteria of the field u want skipped if its empty you can try
<>"" or Null

 

[Johnthelleader]

I have tried the following:

Criteria: Between [Forms]![Invoices]![Invoice date 1] And [Forms]![Invoices]![Invoice date 2]

Or: Is Null

But this does not work, it just returns blanks!

 

[boblarson]

I think you would want:

Between Nz([Forms]![Invoices]![Invoice date 1]) And Nz([Forms]![Invoices]![Invoice date 2])

But, for a date you might have to supply a default for the NZ if it is null.

 

[rainman89]

I think you would want:

Between Nz([Forms]![Invoices]![Invoice date 1]) And Nz([Forms]![Invoices]![Invoice date 2])

But, for a date you might have to supply a default for the NZ if it is null.

I think he means if another field is blank to skip that record.... am i right?

 

[Johnthelleader]

Yes that is right rainman89. So should it be something different?

 

[rainman89]

which field cannot be blank?

 

[Johnthelleader]

There are three fields in total; a name field and the two date fields. If the name field is left blank but a date range is entered then the report brings up for the date range. If a name is entered and not a date range then all data is returned associated with that date.
So far if a name is entered and a date range that is fine. It is just if one of the fileds is ignored, no data is returned. I think there should be some code that tells it to ignore empty fields.

 

[rainman89]

ok so for your query youd want a

thename:NZ([yournamefield])

and also do what boB has shown with the

between NZ(etc) and NZ(etc)

heres a quick sloppy sample of what i think u mean

 

[Johnthelleader]

Thanks for going to all that trouble with the sample database. I have done everything you have mentioned but the report is still blank when one of the fields on the form is blank. Any other possible suggestions? I don't have a file compressor so cannot convert the query into Zip format so you can view here.

 

[boblarson]

What version of Windows are you running?

 

[Johnthelleader]

Windows XP

 

[boblarson]

Then you DO have a zip utility at your command. You just didn't know it. If you go to the file in question and right-click you can select SEND TO and select COMPRESSED FOLDER.

 

[Johnthelleader]

Here she is!

View attachment Blank Database.zip

 

[boblarson]

Where's the form? You reference a form in the query but didn't include a form.

 

[Johnthelleader]

Good point!
I have now added the Form and the report

View attachment Blank Database.zip