SQL as EOMONTH

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

SQL as EOMONTH

Post by jstevens »

Is it possible for a query to populate a field heading with the EOMONTH?

Code: Select all

SELECT '' as '1/1/2020'     --Returns 1/1/2020 as a column header
I'm looking for the column header to be 1/31/2020 ie calculated if the month and year are known.
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: SQL as EOMONTH

Post by HansV »

Do you create the SQL dynamically in VBA?
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: SQL as EOMONTH

Post by jstevens »

Hans,

I do but in this case the column records should be returned blank however the column heading is the EOMONTH.

VBA example:

Code: Select all

SELECT
'' as '1/1/2020';    --Returns 1/1/2020 as a column header
'' as '2/1/2020';    --Returns 2/1/2020 as a column header
'' as '3/1/2020'     --Returns 3/1/2020 as a column header
Based on the column and heading date my code performs some type of action.
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: SQL as EOMONTH

Post by HansV »

Let's say you have a variable d of type Date that you use in the code. Something like

Code: Select all

strSQL = "SELECT '' AS '" & d & ';"
Change this to

Code: Select all

strSQL = "SELECT '' AS '" & DateSerial(Year(d), Month(d) + 1, 0) & ';"
or to be sure that the date is formatted correctly for SQL:

Code: Select all

strSQL = "SELECT '' AS '" & Format(DateSerial(Year(d), Month(d) + 1, 0), "yyyy-mm-dd") & ';"
If that doesn't help, I'd have to see your code, or at least the relevant part.
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: SQL as EOMONTH

Post by jstevens »

Hans,

Your suggestion worked.

Thank you!
Regards,
John