Between Dates Query....FAST HELP

[stlrain95]

I have 2 joined tables but I am trying to get a query where items sold greater than 6 months ago but less than 1 year within this query. I seem to be having trouble obtaining this.

SELECT SOLAR_CustItem.[Part Number], SOLAR_CustItem.[Description 1], SOLAR_INVENTORY.[Item Code], SOLAR_INVENTORY.[Free Stock (in Eaches)], SOLAR_CustItem.[Sales Unit Price], SOLAR_CustItem.[Sales Unit], [Free Stock (in Eaches)]*[Sales Unit Price] AS [Inventory $], SOLAR_INVENTORY.[Date of Last Sale], SOLAR_INVENTORY.[Date of Last Purchase]
FROM SOLAR_CustItem INNER JOIN SOLAR_INVENTORY ON SOLAR_CustItem.[Item Code] = SOLAR_INVENTORY.[Item Code]
WHERE (((SOLAR_INVENTORY.[Date of Last Sale])>-180 And (SOLAR_INVENTORY.[Date of Last Sale])>-365))
GROUP BY SOLAR_CustItem.[Part Number], SOLAR_CustItem.[Description 1], SOLAR_INVENTORY.[Item Code], SOLAR_INVENTORY.[Free Stock (in Eaches)], SOLAR_CustItem.[Sales Unit Price], SOLAR_CustItem.[Sales Unit], SOLAR_INVENTORY.[Date of Last Sale], SOLAR_INVENTORY.[Date of Last Purchase];

Obviously, I need to have the >-180 to be a "<". But I get no data in return from this.

Thank you,

 

[SkipVought]

stlrain95

180 as a DATE is a LOOOOOOOOOOOooooooooooooonng time ago.

WHERE ((SOLAR_INVENTORY.[Date of Last Sale]) BETWEEN Date - 180 AND Date - 365)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stlrain95]

Ahhhhhh. I see grasshopper.

But this is giving me a data type mismatch?? What would be causing this issue?

 

[SkipVought]

May need to FORMAT you date to the DEFAULT data format, for instance my DEFAULT format is "mm/dd/yyyy"...

WHERE ((Format(SOLAR_INVENTORY.[Date of Last Sale],"mm/dd/yyyy")) BETWEEN Date - 180 AND Date - 365)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stlrain95]

Skip....still with the compiler error?

I did the format and I have the data in the table as a Date/Time. But not sure what is going on.

SELECT SOLAR_CustItem.[Part Number], SOLAR_CustItem.[Description 1], SOLAR_INVENTORY.[Item Code], SOLAR_INVENTORY.[Free Stock (in Eaches)], SOLAR_CustItem.[Sales Unit Price], SOLAR_CustItem.[Sales Unit], [Free Stock (in Eaches)]*[Sales Unit Price] AS [Inventory $], SOLAR_INVENTORY.[Date of Last Sale], SOLAR_INVENTORY.[Date of Last Purchase]
FROM SOLAR_CustItem INNER JOIN SOLAR_INVENTORY ON SOLAR_CustItem.[Item Code] = SOLAR_INVENTORY.[Item Code]
WHERE ((Format(SOLAR_INVENTORY.[Date of Last Sale],"mm/dd/yyyy")) BETWEEN Date - 180 AND Date - 365)
GROUP BY SOLAR_CustItem.[Part Number], SOLAR_CustItem.[Description 1], SOLAR_INVENTORY.[Item Code], SOLAR_INVENTORY.[Free Stock (in Eaches)], SOLAR_CustItem.[Sales Unit Price], SOLAR_CustItem.[Sales Unit], SOLAR_INVENTORY.[Date of Last Sale], SOLAR_INVENTORY.[Date of Last Purchase];

 

[BobJacksonNCI]

Sorry to protrude...

...

BETWEEN Date() - 180 AND Date() - 365)

Gotta include parenthesis.




HTH,
Bob

 

[SkipVought]

The DEVIL is in the DETAILS -- Thanx Bob!

stlrain95, your comment, "I did the format and I have the data in the table as a Date/Time" leads me to suspect that Date() may NOT be in the format of your [Date of Last Sale], in which case...

BETWEEN Format(Date() - 180, "mm/dd/yyyy") AND Format(Date() - 365, "mm/dd/yyyy")

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stlrain95]

Thank you gents...
I am still getting a compiler error...not sure what is going on with this thing. I know the code is correct and I am not getting a syntax issue. But for some reason, it just doesn't like the where statement.

 

[SkipVought]

Do you have the sql assigned to a variable? Sometimes, one forgets SPACES in strategic places, like the end to string segments...

sql = "SELECT SOLAR_CustItem.[Part Number], SOLAR_CustItem.[Description 1], SOLAR_INVENTORY.[Item Code], SOLAR_INVENTORY.[Free Stock (in Eaches)], SOLAR_CustItem.[Sales Unit Price], SOLAR_CustItem.[Sales Unit], [Free Stock (in Eaches)]*[Sales Unit Price] AS [Inventory $], SOLAR_INVENTORY.[Date of Last Sale], SOLAR_INVENTORY.[Date of Last Purchase] " & _
    "FROM SOLAR_CustItem INNER JOIN SOLAR_INVENTORY ON SOLAR_CustItem.[Item Code] = SOLAR_INVENTORY.[Item Code] " & _
    "WHERE ((Format(SOLAR_INVENTORY.[Date of Last Sale],""mm/dd/yyyy"")) BETWEEN Format(Date() - 180, ""mm/dd/yyyy"") AND Format(Date() - 365, ""mm/dd/yyyy"")) " & _
    "GROUP BY SOLAR_CustItem.[Part Number], SOLAR_CustItem.[Description 1], SOLAR_INVENTORY.[Item Code], SOLAR_INVENTORY.[Free Stock (in Eaches)], SOLAR_CustItem.[Sales Unit Price], SOLAR_CustItem.[Sales Unit], SOLAR_INVENTORY.[Date of Last Sale], SOLAR_INVENTORY.[Date of Last Purchase];"

???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stlrain95]

I figured it out!!

I created a Public Variable...by doing this, it was creating a conflict in the code for some reason? Not sure why, but when I deleted this, it ran the query.

But, I am getting dates from 98? This should only be returning lines between a year ago and 6 months.?

 

[SkipVought]

[Date of Last Sale] or [Date of Last Purchase] in 1998?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[stlrain95]

[Date of last sale], where I did the Between.

 

[SkipVought]

Try changing ALL the Formats to "yyyy/mm/dd"



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884