PC Review


Reply
Thread Tools Rate Thread

Using column name in SQL Select statement

 
 
Mal
Guest
Posts: n/a
 
      12th Jun 2005
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
 
Reply With Quote
 
 
 
 
Marshall Barton
Guest
Posts: n/a
 
      12th Jun 2005
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]
 
Reply With Quote
 
 
 
 
Mal
Guest
Posts: n/a
 
      13th Jun 2005
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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL: Select a column name "Short Name" =?Utf-8?B?YWxp?= Microsoft Access Queries 6 8th Nov 2007 05:52 PM
SQL Statement trying to call column not specified in statement Trepalium@gmail.com Microsoft Access Form Coding 2 15th May 2007 10:38 PM
Correct syntax for SELECT statement within a SELECT statement. =?Utf-8?B?R3JlZw==?= Microsoft Access ADP SQL Server 2 16th Oct 2006 09:20 PM
run SQL statement from same SQL statement for searching =?Utf-8?B?TWlrZQ==?= Microsoft Access VBA Modules 4 15th Apr 2005 06:47 PM
SQL statement to insert a value generated by a SELECT statement? Rob Richardson Microsoft ADO .NET 2 21st Feb 2004 05:26 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:53 PM.