none
Query Parameter syntax RRS feed

  • Question

  • Good Morning Access Family,

    Looking for assistance with syntax in a Query with a popup parameter.

    Sometimes used is something like; Between [Begin Date] And [End Date]

    In this case if a date is not entered we need all records returned

    Sometimes used something like this to return all records on text field; Like [Enter Company Name * are wild]

    * doesn't work here.

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, February 19, 2016 3:30 PM

Answers

  • Hi Chris,

    If you want all records only in case that none of the two dates is entered:

    Between [Begin Date] And [End Date] Or [Begin Date] Is Null And [End Date] Is Null

    If you want all records if one or all parameters are missing:

    Between [Begin Date] And [End Date] Or [Begin Date] Is Null Or [End Date] Is Null

    I usually want something like this:

        ([DateField]>= [Begin Date] OR [Begin Date] Is Null) AND ([DateField]<= [End Date] OR [End Date] Is Null)

    So if the [Begin Date] is Null, there's no criterion on begin date, but [End Date] can still specify the latest acceptable date; similarly, if the [End Date] is Null, there's no criterion on end date, but [Begin Date] can still specify the earliest acceptable date.  And if both date criteria are Null, then there's no limit on acceptable dates.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by KCDW Tuesday, February 23, 2016 2:26 PM
    Friday, February 19, 2016 5:28 PM
  • I'd recommend a small refinement to Dirk's solution:

    ([DateField]>= [Begin Date] OR [Begin Date] IS NULL) AND ([DateField]< [End Date]+1 OR [End Date] IS NULL)

    It's not only more flexible than a BETWEEN…..AND operation, but more importantly, it's more reliable as it caters for rows with a date on the last day of the range where the time of day element is greater than zero.  Unless specific steps have been taken in the table definition, i.e. an appropriate ValidationRule property, it cannot be assumed with complete confidence that such rows do not exist, in which case a BETWEEN…..AND operation would not return the rows with those values in the column.

    Ken Sheridan, Stafford, England

    • Marked as answer by KCDW Tuesday, February 23, 2016 2:27 PM
    Friday, February 19, 2016 6:16 PM
  • I'd recommend a small refinement to Dirk's solution:

    ([DateField]>= [Begin Date] OR [Begin Date] IS NULL) AND ([DateField]< [End Date]+1 OR [End Date] IS NULL)

    I agree with this amendment.  If there's any chance the date field might include a time, you need to check for [DateField] < ([EndDate] + 1).


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by KCDW Tuesday, February 23, 2016 2:27 PM
    Friday, February 19, 2016 7:06 PM
  • If we remove the unecessary garbage which the Access query designer throws in, and rationalize the logic we are left with the following as the query's WHERE clause:

    WHERE Jurisdiction.JurisdictionID = 1
    AND Products.MSDSARListPrimary IN (27,44,45,47,48)
    AND BalancePaymentInformation.SumOfPayment <> 0
    AND ([BoardDate] >= [Begin Date] OR [Begin Date] IS NULL)
    AND ([BoardDate] < [End Date]+1 OR [End Date] IS NULL)

    However, you appear to be restricting the query on columns on the left side of RIGHT OUTER JOINs, which will in effect turn them into INNER JOINs, so unless the outer joins are unnecessary and can be replaced by inner joins, I'd be doubtful about the query returning the correct result set.


    Ken Sheridan, Stafford, England

    • Marked as answer by KCDW Tuesday, February 23, 2016 2:27 PM
    Friday, February 19, 2016 10:49 PM
  • I'll bet that if you explicitly declare the data types of the parameters, Ken's query will work.  Either use the Query Parameters dialog (from the Show/Hide group in the query Design tab) to specify that both parameters, [Begin Date] and [End Date], are Date/Time, or else in SQL view prefix the original SQL with a PARAMETERS statement, like this:

    PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
    SELECT Products.PEBNumber, Products.ProductName, Products.ProductDescription, Jurisdiction.JurisdictionID, Jurisdiction.Section, Products.SubmittalDate, Products.BoardDate, Products.MSDSARListPrimary, Products.MSDSARListSecondary, Products.DocumentsReceived, Products.[AssignedBureaus/Divisions], ([BalanceTestFees.SumOfTestFees]+[BalanceTrafficFees].[SumOfTrafficFees]+[BalanceManualFee].[SumOfManualFee]-[BalancePaymentInformation].[SumOfPayment]) AS SumofSums, BalancePaymentInformation.SumOfPayment, [BalanceTrafficFees.SumofTrafficFees]+[BalanceTestFees.SumOfTestFees]+[BalanceManualFee.SumOfManualFee] AS SumofFees
    FROM Jurisdiction RIGHT JOIN (BalanceTrafficFees RIGHT JOIN (BalanceTestFees RIGHT JOIN (BalancePaymentInformation RIGHT JOIN (Products LEFT JOIN BalanceManualFee ON Products.PEBNumber = BalanceManualFee.PEBNumber) ON BalancePaymentInformation.Products.PEBNumber = Products.PEBNumber) ON BalanceTestFees.Products.PEBNumber = Products.PEBNumber) ON BalanceTrafficFees.Products.PEBNumber = Products.PEBNumber) ON Jurisdiction.JurisdictionID = Products.Jurisdiction.Value
    WHERE (((Jurisdiction.JurisdictionID)=1) AND (([BoardDate]>=[Begin Date] Or [Begin Date] Is Null) And ([BoardDate]<[End Date]+1 Or [End Date] Is Null)) AND ((Products.MSDSARListPrimary) In (27,44,45,47,48)) AND ((BalancePaymentInformation.SumOfPayment)<>0)) OR (((Jurisdiction.JurisdictionID)=1) AND (([BoardDate]>=[Begin Date] Or [Begin Date] Is Null) And ([BoardDate]<[End Date]+1 Or [End Date] Is Null)) AND ((Products.MSDSARListSecondary) In (27,44,45,47,48)) AND ((BalancePaymentInformation.SumOfPayment)<>0))
    ORDER BY Products.BoardDate DESC;


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    • Edited by Dirk Goldgar Sunday, February 21, 2016 10:33 PM
    • Marked as answer by KCDW Tuesday, February 23, 2016 2:26 PM
    Sunday, February 21, 2016 10:33 PM
  • If you are sure that the query is returning the  correct rows, with no omissions, then don't attempt to fix what ain't broke.  Let me explain the reasoning behind what I said, though:

    An OUTER JOIN, be it RIGHT or LEFT returns rows from the table on one side of the JOIN regardless of there being a matching row in the table on the other side;  the columns from the other side would return Nulls if there is no matching row.  If you restrict the query on a column on the other side, however, then you are requiring the query to return rows from the other side with a value in the column in question, so the column cannot be Null, i.e. there must be a matching rows in the tables on both sides of the JOIN, i.e. it effectively  becomes an INNER JOIN.

    Regarding the declaration of the parameters, it is in most situations advisable to do this, but when you are testing for a parameter IS NULL, if you were to declare the parameter as TEXT, LONG, or SHORT etc, you'd find that the parameter would never evaluate as NULL .  Declaring a parameter as DATETIME, as in your query, is the exception.  Parameters of DATETIME data type should always be declared as, unlike other data types,  these can evaluate as NULL when declared.  Declaration of the parameter as DATETIME avoids any inadvertent misinterpretation of its value as an arithmetical expression, and caters for the regional date format in which the value is entered.

    Ken Sheridan, Stafford, England

    • Marked as answer by KCDW Tuesday, February 23, 2016 2:28 PM
    Monday, February 22, 2016 6:37 PM

All replies

  • Hi Chris,

    If you want all records only in case that none of the two dates is entered:

    Between [Begin Date] And [End Date] Or [Begin Date] Is Null And [End Date] Is Null

    If you want all records if one or all parameters are missing:

    Between [Begin Date] And [End Date] Or [Begin Date] Is Null Or [End Date] Is Null


    cu
    Karl
    Access FAQ (de/it): donkarl.com
    Access Lobby: AccessDevelopers.org

    • Marked as answer by KCDW Friday, February 19, 2016 5:54 PM
    • Unmarked as answer by KCDW Friday, February 19, 2016 7:13 PM
    Friday, February 19, 2016 4:00 PM
  • Hi Chris,

    If you want all records only in case that none of the two dates is entered:

    Between [Begin Date] And [End Date] Or [Begin Date] Is Null And [End Date] Is Null

    If you want all records if one or all parameters are missing:

    Between [Begin Date] And [End Date] Or [Begin Date] Is Null Or [End Date] Is Null

    I usually want something like this:

        ([DateField]>= [Begin Date] OR [Begin Date] Is Null) AND ([DateField]<= [End Date] OR [End Date] Is Null)

    So if the [Begin Date] is Null, there's no criterion on begin date, but [End Date] can still specify the latest acceptable date; similarly, if the [End Date] is Null, there's no criterion on end date, but [Begin Date] can still specify the earliest acceptable date.  And if both date criteria are Null, then there's no limit on acceptable dates.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by KCDW Tuesday, February 23, 2016 2:26 PM
    Friday, February 19, 2016 5:28 PM
  • I'd recommend a small refinement to Dirk's solution:

    ([DateField]>= [Begin Date] OR [Begin Date] IS NULL) AND ([DateField]< [End Date]+1 OR [End Date] IS NULL)

    It's not only more flexible than a BETWEEN…..AND operation, but more importantly, it's more reliable as it caters for rows with a date on the last day of the range where the time of day element is greater than zero.  Unless specific steps have been taken in the table definition, i.e. an appropriate ValidationRule property, it cannot be assumed with complete confidence that such rows do not exist, in which case a BETWEEN…..AND operation would not return the rows with those values in the column.

    Ken Sheridan, Stafford, England

    • Marked as answer by KCDW Tuesday, February 23, 2016 2:27 PM
    Friday, February 19, 2016 6:16 PM
  • If you want all records only in case that none of the two dates is entered:

    Between [Begin Date] And [End Date] Or [Begin Date] Is Null And [End Date] Is Null

    If you want all records if one or all parameters are missing:

    Between [Begin Date] And [End Date] Or [Begin Date] Is Null Or [End Date] Is Null

    Hi Chris,

    You marked the above quotation of Karl as an answer. I very agree with Ken that you should not use BETWEEN ... AND to define time ranges, but always something like:  >= [begin date]  AND   < [until date]. Personally I never use BETWEEN ... AND for that reason.

    Probably you want all occurances that could have occured in your specified time range. When that is true, you need a little modification of the above answer. You must still exclude the occurances that are already ended before [begin date], and also the occurances that are started on or after [until date].

    Imb.

    Friday, February 19, 2016 6:49 PM
  • I'd recommend a small refinement to Dirk's solution:

    ([DateField]>= [Begin Date] OR [Begin Date] IS NULL) AND ([DateField]< [End Date]+1 OR [End Date] IS NULL)

    I agree with this amendment.  If there's any chance the date field might include a time, you need to check for [DateField] < ([EndDate] + 1).


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by KCDW Tuesday, February 23, 2016 2:27 PM
    Friday, February 19, 2016 7:06 PM
  • Hi Karl,

    I thought that would be the answer.

    Was in too much of a hurry to get to a meeting and didn't fully test. On the second line of code you provided...If the beginning and end date fields are both empty then I get all records which I tested before the meeting and thought that was the answer but now testing shows if I have values then I get an error that it is too complex. Also noted that Access changed the criteria from 2 lines to 6 lines checking that out.

    @ Dirk,

    I missed your post earlier, will check it out in a minute

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, February 19, 2016 7:19 PM
  • Alright now that I posted again I can see Ken's and Imb's posts...

    I am reviewing now.

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, February 19, 2016 7:22 PM
  • Hi Dirk,

    your original posted code seems to work however Access has taken the original criteria of 2 lines and made it 8 lines. This is almost comical.

    So a comment about suggestions for concerns with time???

    There has never been data entry in this beast in the last 5 years outside of 6am to 9pm.

    Do I need to worry about that or just better to code as correctly possible always?

    Testing Ken's modification next...

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, February 19, 2016 7:31 PM
  • There has never been data entry in this beast in the last 5 years outside of 6am to 9pm.

    Do I need to worry about that or just better to code as correctly possible Always?

    Hi Chris,

    Though you question was adressed to Dirk, I will add my opinion.

    That something did not happen in the last five years, will not guarantee that it will never happen in future.

    Code must be robust, with all situations handled. Not handled situations can be (and according to Murphy: will be) the cause of future bugs.

    With respect to your remark, you are not handling date ranges, but time ranges. In the same way as a date range your time range is defined as:

          >= 6am   ...    < 9pm

    Imb.

    Friday, February 19, 2016 8:14 PM
  • ...............however Access has taken the original criteria of 2 lines and made it 8 lines.
    That's just a result of Access shoe-horning the logic into the design interface.  When writing a query like this, which makes parameters optional by testing for Null, it's advisable to write and, most importantly, save the query, or at least its WHERE clause, in SQL view without switching to design view.  While Access's moving stuff around preserves the underlying logic it is at best obscured, but if the query is more than moderately complex, can at worst result it being too complex to open.

    Ken Sheridan, Stafford, England

    Friday, February 19, 2016 8:14 PM
  • your original posted code seems to work however Access has taken the original criteria of 2 lines and made it 8 lines. This is almost comical.

    Yes, it will do that any time you put multiple conditions joined by OR in one criteria cell.  That's just its way of parsing multiple criteria and presenting them in the design grid.  The actual logic of the criteria is maintained.

    So a comment about suggestions for concerns with time???

    There has never been data entry in this beast in the last 5 years outside of 6am to 9pm.

    Do I need to worry about that or just better to code as correctly possible always?

    It's not a question of when the data entry was made.  It's a matter of whether the relevant date field's value was set to Date() or to Now().  The Date() function always returns the date without any time, while the Now() function always returns both the date and the time.  Or, if the user entered the date, did the user enter just the date, or did they enter date and time?

    You may be aware that an Access Date/Time field is stored as a floating point number, with the integer part of the number representing the date and the fractional part representing the time.  There's no "date-only" data type in Access -- there's always an implied time, whether the formatting of the field displays the time or not.  But when you enter only a date, or use the Date() function to set the field, the time part of the field is 0, which is equivalent to 12:00 AM (midnight) on that date.

    If you have a criterion on a date field that says, for example, "<= #2/19/2016#", then if the date field was set to #2/19/2016# (or to #2/19/2016 12:00:00 AM#). the field would pass the criterion.  But if the date field was set to #2/19/2016 12:00:01 AM# -- just one second later -- the date field would not pass the criterion, because #2/19/2016 12:00:01 AM# is later than -- and so greater than -- #2/19/2016#.  This is why it matters whether the field in question might possibly contain a time as well as the date, and why Ken's suggestion is the safest method if there can be any doubt.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, February 19, 2016 8:19 PM
  • Apparently it is too complex. If I just use the original Between it returns values and blank prompts return nothing. Trying to return all records with a null prompt gets the error. If I leave the date criteria out all together I get all the records.

    Is there another method to do this without the error?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, February 19, 2016 9:26 PM
  • Apparently it is too complex. If I just use the original Between it returns values and blank prompts return nothing. Trying to return all records with a null prompt gets the error. If I leave the date criteria out all together I get all the records.


    I suggest you post the SQL of your query.  There's no reason it should be
    "too complex", so far as the date criteria are concerned.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, February 19, 2016 9:37 PM
  • your original posted code seems to work ...

    It's not a question of when the data entry was made.  It's a matter of whether the relevant date field's value was set to Date() or to Now().  The Date() function always returns the date without any time...


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    These are Date(). The user only has the ability to enter the date value.

    In this case Dirk only your posted code works whether the dates are entered or left null. I think that means your post is the answer this time even though there may be better ways of handling this in other situations.

    What gets me is this Query isn't really complex at all, just 19 fields from 6 tables


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, February 19, 2016 9:40 PM
  • What gets me is this Query isn't really complex at all, just 19 fields from 6 tables


    I think there must have been an error in the SQL.  If you post it, maybe we can see what was wrong.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, February 19, 2016 9:45 PM
  • I'd recommend a small refinement to Dirk's solution:

    ([DateField]>= [Begin Date] OR [Begin Date] IS NULL) AND ([DateField]< [End Date]+1 OR [End Date] IS NULL)

    It's not only more flexible than a BETWEEN…..AND operation, but more importantly, it's more reliable as it caters for rows with a date on the last day of the range where the time of day element is greater than zero.  Unless specific steps have been taken in the table definition, i.e. an appropriate ValidationRule property, it cannot be assumed with complete confidence that such rows do not exist, in which case a BETWEEN…..AND operation would not return the rows with those values in the column.

    Ken Sheridan, Stafford, England

    Hi Ken,

    Sometimes I get an error. If I leave the prompts NULL then all records are returned but the error occurs when I enter dates in the prompts. This is the same way I get errors with Karl's suggestion above.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, February 19, 2016 9:51 PM
  • What gets me is this Query isn't really complex at all, just 19 fields from 6 tables


    I think there must have been an error in the SQL.  If you post it, maybe we can see what was wrong.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Here it is...

    SELECT Products.PEBNumber, Products.ProductName, Products.ProductDescription, Jurisdiction.JurisdictionID, Jurisdiction.Section, Products.SubmittalDate, Products.BoardDate, Products.MSDSARListPrimary, Products.MSDSARListSecondary, Products.DocumentsReceived, Products.[AssignedBureaus/Divisions], ([BalanceTestFees.SumOfTestFees]+[BalanceTrafficFees].[SumOfTrafficFees]+[BalanceManualFee].[SumOfManualFee]-[BalancePaymentInformation].[SumOfPayment]) AS SumofSums, BalancePaymentInformation.SumOfPayment, [BalanceTrafficFees.SumofTrafficFees]+[BalanceTestFees.SumOfTestFees]+[BalanceManualFee.SumOfManualFee] AS SumofFees
    FROM Jurisdiction RIGHT JOIN (BalanceTrafficFees RIGHT JOIN (BalanceTestFees RIGHT JOIN (BalancePaymentInformation RIGHT JOIN (Products LEFT JOIN BalanceManualFee ON Products.PEBNumber = BalanceManualFee.PEBNumber) ON BalancePaymentInformation.Products.PEBNumber = Products.PEBNumber) ON BalanceTestFees.Products.PEBNumber = Products.PEBNumber) ON BalanceTrafficFees.Products.PEBNumber = Products.PEBNumber) ON Jurisdiction.JurisdictionID = Products.Jurisdiction.Value
    WHERE (((Jurisdiction.JurisdictionID)=1) AND (([BoardDate]>=[Begin Date] Or [Begin Date] Is Null) And ([BoardDate]<[End Date]+1 Or [End Date] Is Null)) AND ((Products.MSDSARListPrimary) In (27,44,45,47,48)) AND ((BalancePaymentInformation.SumOfPayment)<>0)) OR (((Jurisdiction.JurisdictionID)=1) AND (([BoardDate]>=[Begin Date] Or [Begin Date] Is Null) And ([BoardDate]<[End Date]+1 Or [End Date] Is Null)) AND ((Products.MSDSARListSecondary) In (27,44,45,47,48)) AND ((BalancePaymentInformation.SumOfPayment)<>0))
    ORDER BY Products.BoardDate DESC;


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, February 19, 2016 10:01 PM
  • Hmm.  I have to say that, just eyeballing it and working out the parentheses, I don't see anything wrong with the WHERE clause.  I could be overlooking something, of course, and maybe someone else will spot a flaw.

    I do wonder about one thing, though:  the names of some fields, such as "SumOfPayment", "SumOfTrafficFees", etc., make me wonder if some of your 6 tables are actually queries themselves.  If they are, that might open the possibility for the query's actually being too complex, depending on the structure of those subordinate queries.  Are some of those tables actually queries?

    I notice also that your join on Products.Jurisdiction.Value implies that there's a multivalue field in the mix, which would also add complexity.  But I don't see any reason why that would be an issue in itself.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, February 19, 2016 10:34 PM
  • Yes Dirk,

    1 Table, 4 Queries and 1Multi-Value thingamajig.

    Interesting that your code works isn't it? Here is how yours looks...

    SELECT ([Products].[PEBNumber]) AS PEB, ([Products].[ProductName]) AS Product, ([Products].[ProductDescription]) AS Description, Jurisdiction.JurisdictionID, Jurisdiction.Section, Products.SubmittalDate, Products.BoardDate, Products.ConditionedApprovalDate, Products.MSDSARListPrimary, Products.MSDSARListSecondary, Products.DocumentsReceived, Products.[AssignedBureaus/Divisions], ([BalanceTestFees.SumOfTestFees]+[BalanceTrafficFees].[SumOfTrafficFees]+[BalanceManualFee].[SumOfManualFee]-[BalancePaymentInformation].[SumOfPayment]) AS SumofSums, BalancePaymentInformation.SumOfPayment, [BalanceTrafficFees.SumofTrafficFees]+[BalanceTestFees.SumOfTestFees]+[BalanceManualFee.SumOfManualFee] AS SumofFees
    FROM Jurisdiction RIGHT JOIN (BalanceTrafficFees RIGHT JOIN (BalanceTestFees RIGHT JOIN (BalancePaymentInformation RIGHT JOIN (Products LEFT JOIN BalanceManualFee ON Products.PEBNumber = BalanceManualFee.PEBNumber) ON BalancePaymentInformation.Products.PEBNumber = Products.PEBNumber) ON BalanceTestFees.Products.PEBNumber = Products.PEBNumber) ON BalanceTrafficFees.Products.PEBNumber = Products.PEBNumber) ON Jurisdiction.JurisdictionID = Products.Jurisdiction.Value
    WHERE (((Jurisdiction.JurisdictionID)=1) And ((Products.BoardDate>=[Begin Date] Or [Begin Date] Is Null) And (Products.BoardDate<=[End Date] Or [End Date] Is Null)) And ((Products.MSDSARListPrimary) In (27,44,45,47,48)) And ((BalancePaymentInformation.SumOfPayment)<>0)) Or (((Jurisdiction.JurisdictionID)=1) And ((Products.BoardDate>=[Begin Date] Or [Begin Date] Is Null) And (Products.BoardDate<=[End Date] Or [End Date] Is Null)) And ((Products.MSDSARListSecondary) In (27,44,45,47,48)) And ((BalancePaymentInformation.SumOfPayment)<>0))
    ORDER BY Products.BoardDate DESC;
    


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, February 19, 2016 10:46 PM
  • If we remove the unecessary garbage which the Access query designer throws in, and rationalize the logic we are left with the following as the query's WHERE clause:

    WHERE Jurisdiction.JurisdictionID = 1
    AND Products.MSDSARListPrimary IN (27,44,45,47,48)
    AND BalancePaymentInformation.SumOfPayment <> 0
    AND ([BoardDate] >= [Begin Date] OR [Begin Date] IS NULL)
    AND ([BoardDate] < [End Date]+1 OR [End Date] IS NULL)

    However, you appear to be restricting the query on columns on the left side of RIGHT OUTER JOINs, which will in effect turn them into INNER JOINs, so unless the outer joins are unnecessary and can be replaced by inner joins, I'd be doubtful about the query returning the correct result set.


    Ken Sheridan, Stafford, England

    • Marked as answer by KCDW Tuesday, February 23, 2016 2:27 PM
    Friday, February 19, 2016 10:49 PM
  • I'll bet that if you explicitly declare the data types of the parameters, Ken's query will work.  Either use the Query Parameters dialog (from the Show/Hide group in the query Design tab) to specify that both parameters, [Begin Date] and [End Date], are Date/Time, or else in SQL view prefix the original SQL with a PARAMETERS statement, like this:

    PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
    SELECT Products.PEBNumber, Products.ProductName, Products.ProductDescription, Jurisdiction.JurisdictionID, Jurisdiction.Section, Products.SubmittalDate, Products.BoardDate, Products.MSDSARListPrimary, Products.MSDSARListSecondary, Products.DocumentsReceived, Products.[AssignedBureaus/Divisions], ([BalanceTestFees.SumOfTestFees]+[BalanceTrafficFees].[SumOfTrafficFees]+[BalanceManualFee].[SumOfManualFee]-[BalancePaymentInformation].[SumOfPayment]) AS SumofSums, BalancePaymentInformation.SumOfPayment, [BalanceTrafficFees.SumofTrafficFees]+[BalanceTestFees.SumOfTestFees]+[BalanceManualFee.SumOfManualFee] AS SumofFees
    FROM Jurisdiction RIGHT JOIN (BalanceTrafficFees RIGHT JOIN (BalanceTestFees RIGHT JOIN (BalancePaymentInformation RIGHT JOIN (Products LEFT JOIN BalanceManualFee ON Products.PEBNumber = BalanceManualFee.PEBNumber) ON BalancePaymentInformation.Products.PEBNumber = Products.PEBNumber) ON BalanceTestFees.Products.PEBNumber = Products.PEBNumber) ON BalanceTrafficFees.Products.PEBNumber = Products.PEBNumber) ON Jurisdiction.JurisdictionID = Products.Jurisdiction.Value
    WHERE (((Jurisdiction.JurisdictionID)=1) AND (([BoardDate]>=[Begin Date] Or [Begin Date] Is Null) And ([BoardDate]<[End Date]+1 Or [End Date] Is Null)) AND ((Products.MSDSARListPrimary) In (27,44,45,47,48)) AND ((BalancePaymentInformation.SumOfPayment)<>0)) OR (((Jurisdiction.JurisdictionID)=1) AND (([BoardDate]>=[Begin Date] Or [Begin Date] Is Null) And ([BoardDate]<[End Date]+1 Or [End Date] Is Null)) AND ((Products.MSDSARListSecondary) In (27,44,45,47,48)) AND ((BalancePaymentInformation.SumOfPayment)<>0))
    ORDER BY Products.BoardDate DESC;


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    • Edited by Dirk Goldgar Sunday, February 21, 2016 10:33 PM
    • Marked as answer by KCDW Tuesday, February 23, 2016 2:26 PM
    Sunday, February 21, 2016 10:33 PM
  • I'll bet that if you explicitly declare the data types of the parameters, Ken's query will work.  Either use the Query Parameters dialog (from the Show/Hide group in the query Design tab) to specify that both parameters, [Begin Date] and [End Date], are Date/Time, or else in SQL view prefix the original SQL with a PARAMETERS statement, like this:

    PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
    SELECT Products.PEBNumber, Products.ProductName, Products.ProductDescription, Jurisdiction.JurisdictionID, Jurisdiction.Section, Products.SubmittalDate, Products.BoardDate, Products.MSDSARListPrimary, Products.MSDSARListSecondary, Products.DocumentsReceived, Products.[AssignedBureaus/Divisions], ([BalanceTestFees.SumOfTestFees]+[BalanceTrafficFees].[SumOfTrafficFees]+[BalanceManualFee].[SumOfManualFee]-[BalancePaymentInformation].[SumOfPayment]) AS SumofSums, BalancePaymentInformation.SumOfPayment, [BalanceTrafficFees.SumofTrafficFees]+[BalanceTestFees.SumOfTestFees]+[BalanceManualFee.SumOfManualFee] AS SumofFees
    FROM Jurisdiction RIGHT JOIN (BalanceTrafficFees RIGHT JOIN (BalanceTestFees RIGHT JOIN (BalancePaymentInformation RIGHT JOIN (Products LEFT JOIN BalanceManualFee ON Products.PEBNumber = BalanceManualFee.PEBNumber) ON BalancePaymentInformation.Products.PEBNumber = Products.PEBNumber) ON BalanceTestFees.Products.PEBNumber = Products.PEBNumber) ON BalanceTrafficFees.Products.PEBNumber = Products.PEBNumber) ON Jurisdiction.JurisdictionID = Products.Jurisdiction.Value
    WHERE (((Jurisdiction.JurisdictionID)=1) AND (([BoardDate]>=[Begin Date] Or [Begin Date] Is Null) And ([BoardDate]<[End Date]+1 Or [End Date] Is Null)) AND ((Products.MSDSARListPrimary) In (27,44,45,47,48)) AND ((BalancePaymentInformation.SumOfPayment)<>0)) OR (((Jurisdiction.JurisdictionID)=1) AND (([BoardDate]>=[Begin Date] Or [Begin Date] Is Null) And ([BoardDate]<[End Date]+1 Or [End Date] Is Null)) AND ((Products.MSDSARListSecondary) In (27,44,45,47,48)) AND ((BalancePaymentInformation.SumOfPayment)<>0))
    ORDER BY Products.BoardDate DESC;


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Well it took me long enough to understand this. I have never used that Parameter thing before. I just always put things in the SQL and moved on. That's another big lesson for me. Setting that in the Parameters did in fact change the results of Ken's version.

    Thank you very much!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, February 22, 2016 5:11 PM
  • If we remove the unecessary garbage which the Access query designer throws in, and rationalize the logic we are left with the following as the query's WHERE clause:

    WHERE Jurisdiction.JurisdictionID = 1
    AND Products.MSDSARListPrimary IN (27,44,45,47,48)
    AND BalancePaymentInformation.SumOfPayment <> 0
    AND ([BoardDate] >= [Begin Date] OR [Begin Date] IS NULL)
    AND ([BoardDate] < [End Date]+1 OR [End Date] IS NULL)

    However, you appear to be restricting the query on columns on the left side of RIGHT OUTER JOINs, which will in effect turn them into INNER JOINs, so unless the outer joins are unnecessary and can be replaced by inner joins, I'd be doubtful about the query returning the correct result set.


    Ken Sheridan, Stafford, England

    Good Morning Ken,

    As usual you are talking way over my understanding...I see that there are Right and Left Joins but all the Joins are on the Products Table in the grid view. The SQL looks like this

    FROM Jurisdiction RIGHT JOIN (BalanceTrafficFees RIGHT JOIN (BalanceTestFees RIGHT JOIN (BalancePaymentInformation RIGHT JOIN (Products LEFT JOIN BalanceManualFee ON Products.PEBNumber = BalanceManualFee.PEBNumber) ON BalancePaymentInformation.Products.PEBNumber = Products.PEBNumber) ON BalanceTestFees.Products.PEBNumber = Products.PEBNumber) ON BalanceTrafficFees.Products.PEBNumber = Products.PEBNumber) ON Jurisdiction.JurisdictionID = Products.Jurisdiction.Value

    3 rights and a left (no uppercut, he he)

    The records returned seems to be correct as I review the data on paper. Could this be, as Dirk pointed out, because there is 4 Queries, 2 Tables (one of which is a 1 Multi-value field, [Jurisdiction])?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Monday, February 22, 2016 5:28 PM
  • If you are sure that the query is returning the  correct rows, with no omissions, then don't attempt to fix what ain't broke.  Let me explain the reasoning behind what I said, though:

    An OUTER JOIN, be it RIGHT or LEFT returns rows from the table on one side of the JOIN regardless of there being a matching row in the table on the other side;  the columns from the other side would return Nulls if there is no matching row.  If you restrict the query on a column on the other side, however, then you are requiring the query to return rows from the other side with a value in the column in question, so the column cannot be Null, i.e. there must be a matching rows in the tables on both sides of the JOIN, i.e. it effectively  becomes an INNER JOIN.

    Regarding the declaration of the parameters, it is in most situations advisable to do this, but when you are testing for a parameter IS NULL, if you were to declare the parameter as TEXT, LONG, or SHORT etc, you'd find that the parameter would never evaluate as NULL .  Declaring a parameter as DATETIME, as in your query, is the exception.  Parameters of DATETIME data type should always be declared as, unlike other data types,  these can evaluate as NULL when declared.  Declaration of the parameter as DATETIME avoids any inadvertent misinterpretation of its value as an arithmetical expression, and caters for the regional date format in which the value is entered.

    Ken Sheridan, Stafford, England

    • Marked as answer by KCDW Tuesday, February 23, 2016 2:28 PM
    Monday, February 22, 2016 6:37 PM