Hi all.

I've got a table linked to Excel where every three consecutive columns are for a
specific calendar month, ie. F4, F5 & F6 are for July, F7 to F9 represent August
etc etc. I'm setting this up for the next f/y and thought it'd be cute to be
able to automatically pull stats out of it for both the current & last completed
month by using the system date/month to select the appropriate columns.
Something like;

SELECT F&MONTH(NOW())*3, F&MONTH(NOW())*3+1.....

Tragically, I'm as competent with Access SQL as is the average zuchini and can't
get it to work. Is it even possible? And if 'Yes', could some nice person point
me in the right direction.


cheers


Mal

Mal wrote:
>I've got a table linked to Excel where every three consecutive columns are for a
>specific calendar month, ie. F4, F5 & F6 are for July, F7 to F9 represent August
>etc etc. I'm setting this up for the next f/y and thought it'd be cute to be
>able to automatically pull stats out of it for both the current & last completed
>month by using the system date/month to select the appropriate columns.
>Something like;
>
>SELECT F&MONTH(NOW())*3, F&MONTH(NOW())*3+1.....


You will have to construct the SQL statement in VBA (using a
string expression much like what you posted above).

Dim strSQL As String
strSQL = "SELECT F" & ((MONTH(Date) - 6) * 3 + 1) & _
", F" &" & ((MONTH(Date) - 6) * 3 + 1) & _
& . . .


You never said where/how you were going to use the SQL
statement, so I'm just guessing now. To use that as a
form/report's RecordSource, put the code in the
form/report's Open event followed by the line:

Me.RecordSource = strSQL

--
Marsh
MVP [MS Access]

Marshall Barton wrote:
> Mal wrote:
>
>>I've got a table linked to Excel where every three consecutive columns are for a
>>specific calendar month, ie. F4, F5 & F6 are for July, F7 to F9 represent August
>>etc etc. I'm setting this up for the next f/y and thought it'd be cute to be
>>able to automatically pull stats out of it for both the current & last completed
>>month by using the system date/month to select the appropriate columns.
>>Something like;
>>
>>SELECT F&MONTH(NOW())*3, F&MONTH(NOW())*3+1.....
>
>
>
> You will have to construct the SQL statement in VBA (using a
> string expression much like what you posted above).
>
> Dim strSQL As String
> strSQL = "SELECT F" & ((MONTH(Date) - 6) * 3 + 1) & _
> ", F" &" & ((MONTH(Date) - 6) * 3 + 1) & _
> & . . .
>
>
> You never said where/how you were going to use the SQL
> statement, so I'm just guessing now.

I'd planned to send the output straight to Excel to fill in a Pivot
Table. I won't go into the details. It's ugly.

> To use that as a
> form/report's RecordSource, put the code in the
> form/report's Open event followed by the line:
>
> Me.RecordSource = strSQL
>

Great. Thanks Marshall, I'm on my way.


Mal