Show all records of no criteria value enter

[moi]

i have a query to filter report record..(Enter PropertyID), see attached.. What should i add to the criteria so that if there is no criteria entered, it will show all records..

Pls consider the changes, It will be change to "[Forms]![frmPrintMgr]![txtPropertyID]". What would be the expression so that it will show all records if no criteria.

Thank you in advance.. any help would be appreciated.

Moi Ubias

 

[arnelgp]

change to:

IIF(IsNull([Forms]![frmPrintMgr]![txtPropertyID]), [propID], [Forms]![frmPrintMgr]![txtPropertyID])

 

[moi]

change to:

IIF(IsNull([Forms]![frmPrintMgr]![txtPropertyID]), [propID], [Forms]![frmPrintMgr]![txtPropertyID])

Many thanks arnelgp for picking up my post, you help me lot on my first project..

What about if my criteria is a date, ie. "txtstartdate" and "txtenddate".. at present i used "Between" "And".

 

[cheekybuddha]

If you change your query to SQL view you can make the WHERE clause like:

...
WHERE (propID = [Forms]![frmPrintMgr]![txtPropertyID] OR [Forms]![frmPrintMgr]![txtPropertyID] IS NULL)
  AND (YourDateField >= [Forms]![frmPrintMgr]![txtstartdate] OR [Forms]![frmPrintMgr]![txtstartdate] IS NULL)
  AND (YourDateField < [Forms]![frmPrintMgr]![txtenddate] + 1 OR [Forms]![frmPrintMgr]![txtenddate] IS NULL)

 

[arnelgp]

you can add an Expression to your Query, but do not Include then (dont put tick mark).
the Expression is for Criteria purpose only:

the column:

Nz([dateField],1)

the criteria would look like this:

WHERE (((Nz([dateField],1)) Between Nz([Forms]![frmPrintMgr]![txtstartdate],#12/31/1899#) And Nz([Forms]![frmPrintMgr]![txtenddate],#12/31/9999#)));

 

[moi]

If you change your query to SQL view you can make the WHERE clause like:

...
WHERE (propID = [Forms]![frmPrintMgr]![txtPropertyID] OR [Forms]![frmPrintMgr]![txtPropertyID] IS NULL)
  AND (YourDateField >= [Forms]![frmPrintMgr]![txtstartdate] OR [Forms]![frmPrintMgr]![txtstartdate] IS NULL)
  AND (YourDateField < [Forms]![frmPrintMgr]![txtenddate] + 1 OR [Forms]![frmPrintMgr]![txtenddate] IS NULL)

Many thanks cheekybuddha (David)..

 

[moi]

Pay close attention to the parentheses in @cheekybuddha 's reply. They are critical when your criteria combines AND's and OR's.

Hi Pat, Thanks for the reminder.. will take note of that..

 

[moi]

you can add an Expression to your Query, but do not Include then (dont put tick mark).
the Expression is for Criteria purpose only:

the column:

Nz([dateField],1)

the criteria would look like this:

WHERE (((Nz([dateField],1)) Between Nz([Forms]![frmPrintMgr]![txtstartdate],#12/31/1899#) And Nz([Forms]![frmPrintMgr]![txtenddate],#12/31/9999#)));

Hi arnelgp,

Thank you, just a clarification, should i replace the date value on the expression (12/31/1899 and 12/31/9999) ? for what these dates for..

Thank you.

 

[arnelgp]

these 2 dates are "replacement" when the two textbox dates are blank.

 

[moi]

these 2 dates are "replacement" when the two textbox dates are blank.

Hi arnelgp,

I got this error see attached.

 

[moi]

these 2 dates are "replacement" when the two textbox dates are blank.

this is what i put on my query..

 

[arnelgp]

remove the "Where" clause, put this as criteria:

Between Nz([Forms]![frmPrintMgr]![txtstartdate],#12/31/1899#) And Nz([Forms]![frmPrintMgr]![txtenddate],#12/31/9999#)

instead of Group By, change it as Where

 

[moi]

remove the "Where" clause, put this as criteria:

Between Nz([Forms]![frmPrintMgr]![txtstartdate],#12/31/1899#) And Nz([Forms]![frmPrintMgr]![txtenddate],#12/31/9999#)

instead of Group By, change it as Where

Hi,

Same error "unrecognize" field name or expression..

 

[arnelgp]

 

[moi]

View attachment 113362

Hi arnelgp,

It gives me same error..

 

[arnelgp]

it shoul be: #12/31/... and not #31/12...

also, check the Correct name of your Textboxes and use it in the query.

 

[moi]

it shoul be: #12/31/... and not #31/12...

also, check the Correct name of your Textboxes and use it in the query.

Hi,

31/12 - may date field (pdate) is a medium date format.

textboxes are correct..

still, it gives same error.. what am i doing wrong here.. Any suggestions please..

 

[cheekybuddha]

Look at your form textbox references in your expression:

[startdate] vs [txtstartdate]

 

[moi]

Look at your form textbox references in your expression:

[startdate] vs [txtstartdate]

HI David,

Yes i did that, i change it..

 

[cheekybuddha]

Copy and paste the SQL view of the query here.