none
Query showing the wrong month! RRS feed

  • Question

  • My InvoiceInv is 31-8-2017

    My tbDateFrom is 1-8-2017

    my tbDateTo is 31-8-2017

    but when it finds my Invoices for that OwnerID is showing Julys Invoices and not Augusts

    Thanks for any help Bob

    SELECT tblInvoice.*
    FROM tblInvoice
    WHERE (((tblInvoice.InvoiceDateInv) Between [Forms]![frmBillStatement]![tbDateFrom] And [Forms]![frmBillStatement]![tbDateTo]) AND ((tblInvoice.OwnerID)=[Forms]![frmBillStatement]![cbOwnerName]));


    xxx

    Thursday, September 7, 2017 1:32 AM

Answers

  • Firstly, date/time parameters should always be de declared to avoid the parameter value being misinterpreted as an arithmetical expression.  It also caters for regional differences in date formats.

    Secondly, a more reliable way to define a data range is as > start date and < end date+1.  This allows for values in the column with a non-zero time of day.  You cannot completely guarantee the absence of such values unless specific provision has been made in the table definition to disallow such values.

    Finally, if you include the parameters in the query's result table this will indicate whether the query is using the correct parameter values or not.

    So, putting all this together:

    PARAMETERS [Forms]![frmBillStatement]![cbOwnerName] LONG,
    [Forms]![frmBillStatement]![tbDateFrom] DATETIME,
    [Forms]![frmBillStatement]![tbDateTo] DATETIME;
    SELECT *,
    [Forms]![frmBillStatement]![tbDateFrom] AS DateFrom,
    [Forms]![frmBillStatement]![tbDateTo] AS DateTo
    FROM tblInvoice
    WHERE InvoiceDateInv >=  [Forms]![frmBillStatement]![tbDateFrom]
    AND InvoiceDateInv < [Forms]![frmBillStatement]![tbDateTo]+1
    AND OwnerID = [Forms]![frmBillStatement]![cbOwnerName];

    I've assumed that the OwnerID column is a long integer number data type.

    Ken Sheridan, Stafford, England

    • Marked as answer by TurnipOrange Saturday, September 9, 2017 12:32 AM
    Thursday, September 7, 2017 5:31 PM

All replies

  • Try adding a day to your end date. For example:

    SELECT tblInvoice.*
    FROM tblInvoice
    WHERE (((tblInvoice.InvoiceDateInv) Between [Forms]![frmBillStatement]![tbDateFrom] And [Forms]![frmBillStatement]![tbDateTo]+1) AND ((tblInvoice.OwnerID)=[Forms]![frmBillStatement]![cbOwnerName]));

    Hope it helps...


    • Edited by .theDBguy Thursday, September 7, 2017 1:48 AM
    Thursday, September 7, 2017 1:47 AM
  • Thanks DB,Still gave me the July Invoice and not the august Invoice

    xxx

    Thursday, September 7, 2017 1:54 AM
  • qrySelInvoices is my query to select Invoices

    Dim mytot As Long
    mytot = DCount("[InvoiceID]", "qrySelInvoices", "")

    Thanks for the help....Bob


    xxx

    Thursday, September 7, 2017 2:00 AM
  • DB this what controls my Date Boxes

     tbDateFrom.value = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "dd-mmm-yyyy")
               Me.tbDateTo = DateSerial(Year(Date), Month(Date), 0)

    may be I need the formating in tbDateto

    Thanks Bob


    xxx

    Thursday, September 7, 2017 2:07 AM
  • You say “My InvoiceInv is 31-8-2017” and in query have “InvoiceDateInv.”   Is that a typo or a different field?

     

    Is your [tblInvoice].[InvoiceDateInv] a DateTime datatype or is it text?

     

    Try this --

    SELECT tblInvoice.*

    FROM tblInvoice

    WHERE ([tblInvoice].[InvoiceDateInv] Between [Forms]![frmBillStatement]![tbDateFrom] And [Forms]![frmBillStatement]![tbDateTo]) AND ([tblInvoice].[OwnerID] = [Forms]![frmBillStatement]![cbOwnerName]);


    Build a little, test a little

    Thursday, September 7, 2017 2:14 AM
  • DB this what controls my Date Boxes

     tbDateFrom.value = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "dd-mmm-yyyy")
               Me.tbDateTo = DateSerial(Year(Date), Month(Date), 0)

    may be I need the formating in tbDateto

    Thanks Bob


    xxx

    Hi Bob,

    I think the above code produces the start and end dates for the previous month. Is that what you want? Or, did you want the current month? 

    If the current month, try either this:

    Me.tbDateFrom = DateSerial(Year(Date),Month(Date),1)

    Me.tbDateTo = DateSerial(Year(Date),Month(Date)+1,0)

    or this:

    Format(Me.tbDateFrom = DateSerial(Year(Date),Month(Date),1),"yyyy-mm-dd")

    Format(Me.tbDateTo = DateSerial(Year(Date),Month(Date)+1,0),"yyyy-mm-dd")

    Hope it helps...

    • Edited by .theDBguy Thursday, September 7, 2017 2:34 AM
    Thursday, September 7, 2017 2:30 AM
  • Firstly, date/time parameters should always be de declared to avoid the parameter value being misinterpreted as an arithmetical expression.  It also caters for regional differences in date formats.

    Secondly, a more reliable way to define a data range is as > start date and < end date+1.  This allows for values in the column with a non-zero time of day.  You cannot completely guarantee the absence of such values unless specific provision has been made in the table definition to disallow such values.

    Finally, if you include the parameters in the query's result table this will indicate whether the query is using the correct parameter values or not.

    So, putting all this together:

    PARAMETERS [Forms]![frmBillStatement]![cbOwnerName] LONG,
    [Forms]![frmBillStatement]![tbDateFrom] DATETIME,
    [Forms]![frmBillStatement]![tbDateTo] DATETIME;
    SELECT *,
    [Forms]![frmBillStatement]![tbDateFrom] AS DateFrom,
    [Forms]![frmBillStatement]![tbDateTo] AS DateTo
    FROM tblInvoice
    WHERE InvoiceDateInv >=  [Forms]![frmBillStatement]![tbDateFrom]
    AND InvoiceDateInv < [Forms]![frmBillStatement]![tbDateTo]+1
    AND OwnerID = [Forms]![frmBillStatement]![cbOwnerName];

    I've assumed that the OwnerID column is a long integer number data type.

    Ken Sheridan, Stafford, England

    • Marked as answer by TurnipOrange Saturday, September 9, 2017 12:32 AM
    Thursday, September 7, 2017 5:31 PM
  • Secondly, a more reliable way to define a data range is as > start date and < end date+1.  This allows for values in the column with a non-zero time of day.  You cannot completely guarantee the absence of such values unless specific provision has been made in the table definition to disallow such values.

    Hi Ken,

    In addition to the above, the time range definition is still sharper if you use:

             a more reliable way to define a data range is as >= start date and < end date+1.

    Imb.

    Thursday, September 7, 2017 8:40 PM
  • In addition to the above, the time range definition is still sharper if you use:

             a more reliable way to define a data range is as >= start date and < end date+1.


    You are quite right.  Mea culpa, I missed the equals sign in the first operator.  I did include it in the SQL statement, however, so some of the ageing grey matter must still be functioning!

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Thursday, September 7, 2017 9:57 PM Typo corrected.
    Thursday, September 7, 2017 9:57 PM
  • Thanks Guys Brilliant..  :)

    xxx

    Saturday, September 9, 2017 12:32 AM
  • Sorry Guys, thought it was fixed but it is not finding the field InvoiceDateInv but the field InvoiceDate, even using your codes. I tried with this code and still picking up the wrong field. for some reason it only uses the Invoicedate field. I change some fields in my database to check it. thanks if you can help me out......Bob

    DateInvoice will have the date 1 Sep 2017

    DateInvoiceInv will have 31 Aug 2017

    relative to each month

    SELECT tblInvoice.*
    FROM tblInvoice
    WHERE (((tblInvoice.InvoiceDateInv) Between [Forms]![frmBillStatement]![tbDateFrom] And [Forms]![frmBillStatement]![tbDateTo]) AND ((tblInvoice.OwnerID)=[Forms]![frmBillStatement]![cbOwnerName]) );


    xxx

    Saturday, September 16, 2017 1:14 AM
  • DateInvoiceInv will have 31 Aug 2017

    ...

    SELECT tblInvoice.*
    FROM tblInvoice
    WHERE (((tblInvoice.InvoiceDateInv) Between [Forms]![frmBillStatement]![tbDateFrom] And [Forms]![frmBillStatement]![tbDateTo]) AND ((tblInvoice.OwnerID)=[Forms]![frmBillStatement]![cbOwnerName]) );

    Hi TurnipOrange,

    You want to select for:    DateInvoiceInv,

    but in the query you use:   InvoiceDateInv

    Imb.

    Saturday, September 16, 2017 5:34 AM
  • I assume the discrepancy in the column name to which Imb has drawn attention is just a typo.  Otherwise the only possible problem with the query which I can see is that the parameters are not declared.

    Ken Sheridan, Stafford, England

    Saturday, September 16, 2017 9:40 AM