Use of & String Expression as Field Definition and ARGUMENT in IIF Statement

[sjdickey]

I am developing a calender to display HOTEL room occupancy (past,present) and combine with future "outlook" dates and 1/0 values from active registrations that go beyond the present date.



I am working with MS ACCESS 2007. My problem is ONE SPECIFIC QUERY AND IIF STATEMENT. I want to concatenate some text (using &) along with numbers converted to text (using the CSTr function). I am using iif function and I want to use the full text string as a variable argument to be executed in the iif function. The result of the &concatenate is a text field like [p1] or [p2] or [p#] with numbers 1-31.
But, I do not want the final result as the argument. I want the query and iif to use the string expression written into the argument as the variable argument that can be calculated based on OTHER numbers that change everyday in the daily run of the calender. The field in the statement [calc number] is a date conversion factor that changes everyday.

I want the iif statement to execute using the string as the variable argument.
I am writing only within the QUERY. I am not writing into any SQL module or code.

My field definition and iif statement is below (calc number changes everyday)

CalDate18: iif(18 greater date();"[p" & [calc number] & "]";0
Sorry I do not have greater-than key on this international keyboard !
When I use this in an update query, I get format conversion error. When I use the same definition in MAKE-TABLE query, it gives the final value "[p1]" for the value of [CalDate18] ......... that is not what I want.

[June7]

You are trying to dynamically build a field name? This doesn't work in query object. Suggest you use VBA to construct SQL statement and execute with CurrentDb.Execute method.

[sjdickey]

No, I am not trying to dynamically build a field name. The field name is always CalDate18. I am trying to create a variable argument for the iif statement. The [calc number] is a date conversion number that changes every day. The fields of the calender will always be the same like CalDate1, CalDate2, CalDate6, CalDate18 .... and so on on (1-31). I want the iif statement to execute the argument based on the ever changing [calc number].

It seems there should be some special character that needs to proceed or wrap the string such as (maybe?) #"[p" & [calc number] & "]"# so that the string will be taken LITERALLY rather than resolved to its actual value. Any ideas of that ?

[June7]

The result of the &concatenate is a text field like [p1] or [p2] or [p#] with numbers 1-31.

Yes, CallDate18 never changes, I get that. However, that statement from your post indicates to me you are trying to dynamically build the [pN] field reference. AFAIK, cannot do that in query object. I've tried.

[sjdickey]

How else can I define my problem with this query object ? The argument to my iif statement needs to be taken literally, and excecute based on the new [calc number] with each new day. The days of the calender remain the same always such as [caldate18] for the 18th of the month. But the value of [calc number] changes everyday, and I want the argument of the iif statement to be executed rather than resolved to its real value. Isnt there a special character to use with the argument so that it will be taken literally ?

[June7]

As stated, none that I know of.

I would use VBA to construct sql statement and run the UPDATE action in VBA.