Query Everything Before Last Month

[Link489]

Hi, I am new to SQL and generally end up using the functions built into the query, I have to bucket records by a date by month. So far I have my first query re-format it to:
Format$([Aging 16+].[Effective Date],'yyyymm')
I used yyyymm because w/ the standard 'mmmm yyyy' it seems to calculate based on the first letter of the month.
I have gotten it to find the future months current month but just not the last full month and everything prior to. I have tried:
Between (Format$(Date()-360,'yyyymm')) And (Format$(Date()-30,'yyyymm'))

Between (Format$(1/1/1950,'yyyymm')) And (Format$(Date()-30,'yyyymm'))

Between "200001" And (Format$(Date()-30,'yyyymm'))

<(Format$(Date(),'yyyymm'))

Also I have tried some variations of these, searched the strings, and to no avail I cannot get it to work.
Any help would be appreciated.
Thanks
Peter

 

[PHV]

You may try this:
<DateSerial(Year(Date()),Month(Date()),1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Link489]

I gave that a shot, unfortunately w/ that it returns no results I think because it will format the date into a mm/dd/yyyy format that doesn't match, and unfortunately I can't use that kind of format because i need to group by whole months. Thanks for the reply though!

 

[jebry]

Hi!

You can wrap PHV's function in your Format:

< Format(DateSerial(Year(Date()),Month(Date()),1), "yyyymm")


hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[PHV]

Any chance you could post your actual SQL code and the data type of the relevant fields ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Link489]

I tried changing it to:
< Format(DateSerial(Year(Date()),Month(Date()),1), "yyyymm")

But I get the same results as some of my other expressions, it seems not to calculate these as dates or as numbers I don't understand it, it will return results correctly if it was last month, but anything before that will not show up in my query.

I have actually never coded in SQL but here is what it shows up as:

SELECT DISTINCTROW [Aging 0-3 Query].Region, Sum([Aging 0-3 Query].[CountOfGroup # (source system)]) AS [Sum Of CountOfGroup # (source system)], Sum([Aging 0-3 Query].[Sum Of Number of Sections]) AS [Sum Of Sum Of Number of Sections]
FROM [Aging 0-3 Query]
WHERE ((([Aging 0-3 Query].[Effective Date By Month])<Format(DateSerial(Year(Date()),Month(Date()),1),"yyyymm") And ([Aging 0-3 Query].[Effective Date By Month]) Is Not Null And ([Aging 0-3 Query].[Effective Date By Month])<>""))
GROUP BY [Aging 0-3 Query].Region;

I appreciate all the help though.
Thanks
Peter

 

[PHV]

What is the data type and format of [Aging 0-3 Query].[Effective Date By Month] ?
Even better, what is the SQL code of [Aging 0-3 Query] ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Link489]

Sorry for the delay, I had the opportunity for my 4th of July vacation.
But...

Here is the SQL for the Aging 0-3 Query

SELECT DISTINCTROW [Aging 0-3].Region, Format$([Aging 0-3].[Effective Date],'yyyymm') AS [Effective Date By Month], Count([Aging 0-3].[Group # (source system)]) AS [CountOfGroup # (source system)], Sum([Aging 0-3].[Number of Sections]) AS [Sum Of Number of Sections]
FROM [Aging 0-3]
GROUP BY [Aging 0-3].Region, Format$([Aging 0-3].[Effective Date],'yyyymm'), Year([Aging 0-3].[Effective Date])*12+DatePart('m',[Aging 0-3].[Effective Date])-1;

and here is for the Aging 0-3 source for Aging 0-3 Query

SELECT Aging.Age, Aging.Region, Aging.ST, Aging.[GI Assigned To], Aging.[Group # (source system)], Aging.[Group Name], Aging.[Effective Date], Aging.[Transaction Code], Aging.[Date GI Received], Aging.[Number of Sections], Aging.[Method received], Aging.[GI Missing Information Code], Aging.Comments
FROM Aging
WHERE (((Aging.Age)<4));

and here is for the Aging, source for Aging 0-3

SELECT [Report Date]-[Date GI Received] AS Age, [Records Revised].[Form ID number], [Records Revised].Region, [Records Revised].ST, [Records Revised].[Group # (source system)], [Records Revised].[Transaction Code], [Records Revised].[Group Name], [Records Revised].[Effective Date], [Records Revised].[Number of Sections], [Records Revised].[GI Assigned To], [Records Revised].[Date GI Received], [Records Revised].[Date GI Completed], [Records Revised].[Method received], [Records Revised].[GI Missing Information Code], [Records Revised].Comments
FROM [Records Revised], [Report Date]
WHERE ((([Records Revised].[Date GI Completed]) Is Null Or ([Records Revised].[Date GI Completed])>[Report Date]))
ORDER BY [Report Date]-[Date GI Received] DESC;

Sorry, I don't know about SQL so I don't know exactly what you're looking for. Thanks for any help!

Peter

 

[Link489]

Actually I got it to work!
Thanks for all your help.
Peter