Query criteria returns blank and OR statement does not work (1 Viewer)

Hi everyone. Stuck here with some query critera.
Some help would be much appreciated

I have query that filters between 2 given dates.
However when input no dates (if just want all the date) it returns blank.

So i tried to fix this using Nz, which doesnt seem to work at all.

I tried "Or like...year" which does work, if i leave it empty it gives me everything. But the "Between" code doesnt work anymore.

Here's my criteria in my query:

Between Nz([Forms]![avs_filter_form]![startdate]) And Nz([Forms]![avs_filter_form]![enddate]) Or Like "*" & [Forms]![avs_filter_form]![yearselect] & "*"

No clue why this doesn't work. Please help. Tried so many things.

Please post the full SQL for the query.

If you have a sample that works and one that doesn't, then please post the sql for each.

Thanks for the reply.

I have alot of fields in this query for later use as well. It's sort of like a master query. But what im trying to do is just based on the "iteminbounddate".


SELECT inboundreturns_items.item_ID, inboundreturns_items.returnID, inboundreturns_items.ireq, inboundreturns_items.item, inboundreturns_items.serial, inboundreturns_items.tasknumber, inboundreturns_items.country, inboundreturns_items.engineername, inboundreturns_items.dealer, inboundreturns_items.status, inboundreturns_items.itemcomments, inboundreturns_items.connote, inboundreturns_items.iteminbounddate, inboundreturns_items.iteminboundtime, inboundreturns_items.user_id, inboundreturns_items.statusxsl, inboundreturns_items.whslocation, inboundreturns_items.problemcolorlabel, inboundreturns_items.problemdoa, inboundreturns_items.problemboxopen, inboundreturns_items.problemengsign, inboundreturns_items.userid, inboundreturns_items.reportref, inboundreturns_items.commercial, inboundreturns_items.t1, inboundreturns_items.inslagnummer, inboundreturns_items.itemfinishtime, inboundreturns_items.hidefinishbutton, inboundreturns_items.irissolution, inboundreturns_items.tasknumber, inboundreturns_items.mrn, Count(inboundreturns_items.item_ID)

AS countitem, Sum(inboundreturns_items.item_ID) AS totalitem
FROM inboundreturns_items

GROUP BY inboundreturns_items.item_ID, inboundreturns_items.returnID, inboundreturns_items.ireq, inboundreturns_items.item, inboundreturns_items.serial, inboundreturns_items.country, inboundreturns_items.engineername, inboundreturns_items.dealer, inboundreturns_items.status, inboundreturns_items.itemcomments, inboundreturns_items.connote, inboundreturns_items.iteminbounddate, inboundreturns_items.iteminboundtime, inboundreturns_items.user_id, inboundreturns_items.statusxsl, inboundreturns_items.whslocation, inboundreturns_items.problemcolorlabel, inboundreturns_items.problemdoa, inboundreturns_items.problemboxopen, inboundreturns_items.problemengsign, inboundreturns_items.userid, inboundreturns_items.reportref, inboundreturns_items.commercial, inboundreturns_items.t1, inboundreturns_items.inslagnummer, inboundreturns_items.itemfinishtime, inboundreturns_items.hidefinishbutton, inboundreturns_items.irissolution, inboundreturns_items.tasknumber, inboundreturns_items.mrn, inboundreturns_items.tasknumber

HAVING (((inboundreturns_items.iteminbounddate) Between Nz([Forms]![avs_filter_form]![startdate]) And Nz([Forms]![avs_filter_form]![enddate]) Or (inboundreturns_items.iteminbounddate) Like "*" & [Forms]![avs_filter_form]![yearselect] & "*"));

It is often better to "divide and conquer". That is start with something small, that you can test and ensure it works. Gradually add more detail.

alright! indeed, let me get the basics right. Ill get back. Thanks

Good stuff. Did you see my response to your other post re Averages?

Ok, so i tried again with a clean new query with just 3 fields.

When i use

Between Nz([Forms]![avs_filter_form]![startdate]) And Nz([Forms]![avs_filter_form]![enddate])

It works fine!

When i use

Between Nz([Forms]![avs_filter_form]![startdate]) And Nz([Forms]![avs_filter_form]![enddate]) Or Like "*" & [Forms]![avs_filter_form]![yearselect] & "*"

Only the OR statement works (the year).

My main problem is:
When i ONLY use the date range that would be fine! I dont really need the year. But if i do that, when i open my form (where i have my date fields) it opens up totally blank. It returns null because i havent had the chance to give the date range yet. That does NOT happen when i use the OR statement, but then it doesnt work either because it gives me ALL the records instead. (when i put in the year, it does work)

1. You have no default value for the Nz() functions. What date do you want to use if one isn't provided? Personally, I never use Nz() at all. If a date is required, I validate the date range on the form.
2. LIKE is a string operation and dates are NOT STRINGS!!!! Dates are stored internally as double precision numbers with the integer being the number of days since Dec 30, 1899 (For Jet and ACE, SQL Server, Excel, and Word, etc all use the same concept but they use a different origin date but the ODBC driver handles the conversion). The decimal portion represents the fraction of a day since midnight.
3. The criteria should be in a WHERE clause rather than a HAVING. The WHERE is applied before data is aggregated so it is used to eliminate data before the query engine performs the aggregation. The HAVING is used ONLY on aggregated data. So you would use it to select customers with an outstanding balance > 0 or students who are carrying too many credits, etc.

See if this works for you

WHERE (inboundreturns_items.iteminbounddate Between [Forms]![avs_filter_form]![startdate] And [Forms]![avs_filter_form]![enddate] Or [Forms]![avs_filter_form]![yearselect] Is Null);