Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    12

    Unanswered: [query] Referencing column names via an expression...

    Hi all,

    I have a non-normalized table and I have a set of expressions that will return the same text as my set of column headers.

    For instance =Format(DateAdd("m",0,Date()),"mmm yy") is an expression that will give me "Jun 10" which is also the name of one of my columns.

    In my table, I have column names [Jun 10],[Jul 10], [Aug 10]... Etc and I want to know how to use expressions in SQL to reference these columns - currently I have expressions that return the correct column names, but for some reason Access cannot recognize them as such.


    Can anybody help?

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by valverde311 View Post
    Hi all,

    I have a non-normalized table and I have a set of expressions that will return the same text as my set of column headers.

    For instance =Format(DateAdd("m",0,Date()),"mmm yy") is an expression that will give me "Jun 10" which is also the name of one of my columns.

    In my table, I have column names [Jun 10],[Jul 10], [Aug 10]... Etc and I want to know how to use expressions in SQL to reference these columns - currently I have expressions that return the correct column names, but for some reason Access cannot recognize them as such.


    Can anybody help?
    We could help you eassier is we could see the SQL and any other code you are using.

    Would you please post the SQL and any VBA code you are using.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Jun 2010
    Posts
    12
    SELECT vend_forecast.Release, vend_forecast.Vendor, vend_forecast.Name, vend_forecast.Eplan, vend_forecast.Role, vend_forecast.Location, vend_forecast.[Jun 10]
    FROM vend_forecast;

    ^ This is my SQL code for the hardcoded number column of [Jun 10].

    V Below is my unsuccessful attempt to use an expression in place of the column name.

    SELECT vend_forecast.Release, vend_forecast.Vendor, vend_forecast.Name, vend_forecast.Eplan, vend_forecast.Role, vend_forecast.Location, vend_forecast.[=Format(DateAdd("m",0,Date()),"mmm yy")]
    FROM vend_forecast;

    I was hoping that Access would find the result of the expression, "Jun 10", and use that result to reference a column of the same name, in way that is no different from me typing in [Jun 10]. Unfortunately, it prompts me to "enter parameter value".

    Any suggestions?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •