Show all records of no criteria value enter (1 Viewer)

moi

Member
Local time
Today, 10:35
Joined
Jan 10, 2024
Messages
273
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
 

Attachments

  • Screenshot (59).png
    Screenshot (59).png
    25.3 KB · Views: 104
change to:

IIF(IsNull([Forms]![frmPrintMgr]![txtPropertyID]), [propID], [Forms]![frmPrintMgr]![txtPropertyID])
 
  • Like
Reactions: 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".
 
If you change your query to SQL view you can make the WHERE clause like:
SQL:
...
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)
 
  • Like
Reactions: moi
Pay close attention to the parentheses in @cheekybuddha 's reply. They are critical when your criteria combines AND's and OR's.
 
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:
Code:
Nz([dateField],1)

the criteria would look like this:
Code:
WHERE (((Nz([dateField],1)) Between Nz([Forms]![frmPrintMgr]![txtstartdate],#12/31/1899#) And Nz([Forms]![frmPrintMgr]![txtenddate],#12/31/9999#)));
 
  • Like
Reactions: moi
If you change your query to SQL view you can make the WHERE clause like:
SQL:
...
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)..
 
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:
Code:
Nz([dateField],1)

the criteria would look like this:
Code:
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.
 
these 2 dates are "replacement" when the two textbox dates are blank.
 
remove the "Where" clause, put this as criteria:

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

instead of Group By, change it as Where
 
remove the "Where" clause, put this as criteria:

Code:
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..
 
2024-03-30 16 01 19.png
 
Last edited:
it shoul be: #12/31/... and not #31/12...

also, check the Correct name of your Textboxes and use it in the query.
 
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..
 
Look at your form textbox references in your expression:

[startdate] vs [txtstartdate]
 

Users who are viewing this thread

Back
Top Bottom