Questions

The last 6 months of data between the 1st and the last day of the month

+
0 Votes
Locked

The last 6 months of data between the 1st and the last day of the month

jonesn
I have been stuck on this for about a month. I am trying to get a query to count and show the last month of specific barcode prefix data that is between the first and the last day of the last month.

This is what I got so far, but I get a data mismatch error. I'm also using Access 2003 and I am pretty much a novice to Access and SQL.

SELECT Count(tblComponentInventory.Date) AS Month5ValveCount
FROM tblComponentInventory
WHERE (((tblComponentInventory.Date)=DateAdd("dd",-(Day(DateAdd("mm",1,"now()"))-1),DateAdd("mm",-1,"now()")) And (tblComponentInventory.Date)=DateAdd("dd",-Day(DateAdd("m",1,"now()")),DateAdd("m",-1,"@Today"))) AND ((tblComponentInventory.EquipmentID) Like "??VC*" Or (tblComponentInventory.EquipmentID) Like "??VO*"));

Any help would be highly appreciated.

TIA
  • +
    0 Votes
    LocoLobo

    If I read it right you need "Between" to set up the date range. You could also set it up as date >= startdate AND date <= enddate. But your SQL should look something like

    WHERE
    (((tblComponentInventory.Date) Between DateAdd("dd",-(Day(DateAdd("mm",1,"now()"))-1),DateAdd("mm",-1,"now()"))

    And DateAdd("dd",-Day(DateAdd("m",1,"now()")),DateAdd("m",-1,"@Today")))

    The way it looks is like you are saying

    Where date = 1st day of month AND date = last day of month. So try the between clause.

    Warning: I didn't check parens nor do I understand exactly how you are using the Day and DateAdd functions. May I ask why the first line uses "mm" and the second "m"? Is there a way to query out the individual parts? i.e. to make sure that each of the dates you arrive for start and end are working?

    +
    0 Votes
    jonesn

    Ok I put the between in but it still isn't working. I am get all confused with all the paranthesis.

    Here's what I have

    SELECT Count(tblComponentInventory.Date) AS Month5ValveCount
    FROM tblComponentInventory
    WHERE (((tblComponentInventory.Date) Between DateAdd("dd",-(Day(DateAdd("mm",1,"now()"))-1),DateAdd("mm",-1,"now()")) And (tblComponentInventory.Date)=DateAdd("dd",-Day(DateAdd("m",1,"now()")),DateAdd("m",-1,"@Today"))) AND ((tblComponentInventory.EquipmentID) Like "??VC*" Or (tblComponentInventory.EquipmentID) Like "??VO*"));

    Also, I picked this code up at http://sqltutorials.blogspot.com/2007/06/sql-first-and-last-day-of-month.html

    So it is pretty hard to understand the details and where everything should go.

    Any other help will would be highly appreciated. TIA

    +
    0 Votes
    LocoLobo

    In the examples referenced @Today is a variable created for a stored procedure. Is your Select statement in a procedure? If not then we need to change it but I'm not sure to what. If I get a chance I'll play with it this afternoon.

    Maybe somebody else here knows and can help out. Thanks for the ref by the way!

    +
    0 Votes
    LocoLobo

    but I'm not sure. This comes from the following web page:

    http://support.microsoft.com/kb/202319

    Here they use the DateSerial function to get last months records. Anyway the completed Where clause looks more like this:

    WHERE (((tblComponentInventory.Date) Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)))
    AND
    ((tblComponentInventory.EquipmentID) Like "??VC*" Or (tblComponentInventory.EquipmentID) Like "??VO*"));

    OOPS! More reading reveals that SQL Server doesn't have a DateSerial function. Are you doing this query in Access or in SQl Server?

    +
    0 Votes
    jonesn

    I'm using Access 2003.

    +
    0 Votes
    LocoLobo

    I tested it using a quick table in Access 2003. Try the between clause in your Where statement. Or if you are using the Query Design view but it in the criteria for the Date field. You may have to play with it.

  • +
    0 Votes
    LocoLobo

    If I read it right you need "Between" to set up the date range. You could also set it up as date >= startdate AND date <= enddate. But your SQL should look something like

    WHERE
    (((tblComponentInventory.Date) Between DateAdd("dd",-(Day(DateAdd("mm",1,"now()"))-1),DateAdd("mm",-1,"now()"))

    And DateAdd("dd",-Day(DateAdd("m",1,"now()")),DateAdd("m",-1,"@Today")))

    The way it looks is like you are saying

    Where date = 1st day of month AND date = last day of month. So try the between clause.

    Warning: I didn't check parens nor do I understand exactly how you are using the Day and DateAdd functions. May I ask why the first line uses "mm" and the second "m"? Is there a way to query out the individual parts? i.e. to make sure that each of the dates you arrive for start and end are working?

    +
    0 Votes
    jonesn

    Ok I put the between in but it still isn't working. I am get all confused with all the paranthesis.

    Here's what I have

    SELECT Count(tblComponentInventory.Date) AS Month5ValveCount
    FROM tblComponentInventory
    WHERE (((tblComponentInventory.Date) Between DateAdd("dd",-(Day(DateAdd("mm",1,"now()"))-1),DateAdd("mm",-1,"now()")) And (tblComponentInventory.Date)=DateAdd("dd",-Day(DateAdd("m",1,"now()")),DateAdd("m",-1,"@Today"))) AND ((tblComponentInventory.EquipmentID) Like "??VC*" Or (tblComponentInventory.EquipmentID) Like "??VO*"));

    Also, I picked this code up at http://sqltutorials.blogspot.com/2007/06/sql-first-and-last-day-of-month.html

    So it is pretty hard to understand the details and where everything should go.

    Any other help will would be highly appreciated. TIA

    +
    0 Votes
    LocoLobo

    In the examples referenced @Today is a variable created for a stored procedure. Is your Select statement in a procedure? If not then we need to change it but I'm not sure to what. If I get a chance I'll play with it this afternoon.

    Maybe somebody else here knows and can help out. Thanks for the ref by the way!

    +
    0 Votes
    LocoLobo

    but I'm not sure. This comes from the following web page:

    http://support.microsoft.com/kb/202319

    Here they use the DateSerial function to get last months records. Anyway the completed Where clause looks more like this:

    WHERE (((tblComponentInventory.Date) Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)))
    AND
    ((tblComponentInventory.EquipmentID) Like "??VC*" Or (tblComponentInventory.EquipmentID) Like "??VO*"));

    OOPS! More reading reveals that SQL Server doesn't have a DateSerial function. Are you doing this query in Access or in SQl Server?

    +
    0 Votes
    jonesn

    I'm using Access 2003.

    +
    0 Votes
    LocoLobo

    I tested it using a quick table in Access 2003. Try the between clause in your Where statement. Or if you are using the Query Design view but it in the criteria for the Date field. You may have to play with it.