Paramater Query using Date or Null (1 Viewer)

T

tucker61

Registered User.

Local time

Today, 14:20

Joined

Jan 13, 2008

Messages

321

• Dec 29, 2016

• #1

I want my query to return records between specific dates, or if the date fields are left null then bring back all records. The first part works, but cant get my head round how to bring back all records ( Or should I set the default values as min and max dates ?)

Between [Forms]![frmsupplier]![cboxstart] And [Forms]![frmsupplier]![cboxEnd]

 

Frothingslosh

Premier Pale Stale Ale

Local time

Today, 17:20

Joined

Oct 17, 2012

Messages

3,276

• Dec 29, 2016

• #2

I'm afraid NULL doesn't play nice with Between - it will never return records when used that way.

What you'll need to do is use two different queries and choose between them. Easiest would probably be a DCount on the query with date criteria, and opening that query if there are records, and a different query with no Between criteria if there are none.

 

J

JHB

Have been here a while

Local time

Today, 23:20

Joined

Jun 17, 2012

Messages

7,732

• Dec 29, 2016

• #3

tucker61 said:

..or if the date fields are left null then bring back all records.

Click to expand...

Do you mean the controls, ([cboxstart]/[cboxend]) in the form or in the table?

 

Last edited: Dec 29, 2016

Frothingslosh

Premier Pale Stale Ale

Local time

Today, 17:20

Joined

Oct 17, 2012

Messages

3,276

• Dec 29, 2016

• #4

JHB said:

Do you mean the controls, ([cboxstart]/[cboxend]) in the form or in the table?

Click to expand...

Oh, good catch. That's what I get for trying to answer this at 5 in the morning!