use an expression as a field

[Joseph]

I am trying to use an expression as a name of a field in a query. However it
is not working.
For instance, the expression Format("30/06/07","ddmmyy") comes as a string
"300607", which I want to use as a field name. When running the query a
parameter value needs to be entered. Can anyone help?

 

[Duane Hookom]

Don't expect Access to allow you to derive field or table names from
expressions. If we knew your actual requirements, someone might be able to
provide a workable solution for you.

 

[Joseph]

Hi Duane
I have a table with numerical field names. Each field contains price
information for specific stocks. Each field name represents a date for
instance "010108" representing 1 January 08 or "300607" representing 30 June
07. I have developed a query which, if I put the field name in by hand
works. However, I'm trying to automate the function whereby one only has to
put in the date (in date format) in a form. I use the
Format("30/06/07","ddmmyy") function to convert the date to a string in the
query, to represent the field name. However it doesn't work. Hope this
explains it better. Thanks for looking at my request. Regards Joseph

 

[Duane Hookom]

This does provide a better explaination but your table structure seems quite
un-normalized. I would expect the date values would be stored in a field, not
a field name. This would make querying much easier. You wouldn't need to
attempt referencing a field name.

I would normalize the tables. If you can't or won't do that, you might need
code to change the SQL of a saved query.