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 a variable argument.
I am writing only within the QUERY to define the query object. 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 resolved value "[p1]" for the value of [CalDate18] ......... that is not what I want.

I want the string to be taken literally and executed. Seems there may be a special character to precede the argument or WRAP the argument such as done in Excel. Example # "[p" & [calc number] & "]" #

 

[CJ_London]

not sure what else you are missing on your keyboard but

CalDate18: iif(18 greater date();"[p" & [calc number] & "]";0

should be written as

CalDate18: (18>date(),"[p" & [calc number] & "]","")

however 18 and Date() are not compatible the date equivalent of 18 is 17/01/1900 (uk format) so 18 will never be greater than Date()

If the above is not the answer to your question provide some sample data plus the result you are looking for.

 

[sjdickey]

Hello and thanks, I want to clarify a few things.

You wrote and indicated that you understood I do not have a GREATER than key on this keyboard. I am in the Dominican Republic and quite often the computer keyboards are like scrambled aggs

CalDate18: (18>date(),"[p" & [calc number] & "]","")

Yes I know that the correct symbol for GREATER is required. I simply do not have that character on this keyboard, but in my office I do.

I am on ACCESS 2007 and the comma is not used in the if statement to separate arguments. In my environment the semi-colon is required. Anyway, my iif statements in all other areas work just fine. It is only this one query that I have a special need and a problem.

Yes I made a mistake with my example object definition. I should have used my "day of the month" isolated and stored for easy comparisons. I use [cal-date] (10) to compare against 18 or whatever future date is being evaluated.

If I am running the calender on the 10th, then the 18th (CalDate18) is greater than the date() which I will call [run-date]

So then, in my environment, the iif statement looks like below :

CalDate18: iif(18>[cal-date];"[p" & [calc number] & "]";0)

The date format here in the DR is like 18-04-2014

So then if I run the query on the 10th, the CalDate18 is greater than the 10th.

[run-date]= 10-04-2014
[cal-date]= 10
Date that is being evaluated=18-04-2014
[calc number] = (18-10) = 8

true, 18 > 10 (I stole your greater-than character)

In the iif , I use the [calc number] which would (18 - [cal-date]) = 8
So then my object definition below would resolve to [p10]

CalDate18: iif(18> [cal-date];"[p" & [calc number] & "]";0)

But I do not want the string resolved in the argument. I want the string to be taken literally and executed using the variable [calc number] which changes everyday.

I am attempting this as a query object definition. There is no code other than the object definition. The iif statement requires the database fields as stated

It seems there would be a special character that precedes the argument or to WRAP the argument so that it is taken literally and not resolved like maybe below:

CalDate18: iif(18> [cal-date]; #"[p" & [calc number] & "]"#;0) with the # as the special character ....... anything like this available ?

 

[CJ_London]

So is [P10] supposed to be a field name? If so, you cannot build it in a query like this you need to build the query in VBA , something like this:

sqlStr="SELECT " & iif(18> [cal-date];"[p" & [calc number] & "]";0) & " FROM myTable"

 

[sjdickey]

Yes, [p10] is a field name. But I am not building the field name. The p-numbers 1-31 are fields in a table that each contain 1/0 based on occupied or vacant in future dates in active registrations. I meant earlier to type [p8].

The [calc num] changes everyday to relate [CalDate18] to the correct
p-number. I want the argument of the iif satement to be taken literally and used with each new day.

If [Run-date] = 10-04-2014, Cal-date = 10,
The Future Date to assign value is 18-04-2014
[calc number] = 8, so the p-number is [p8] (8 days in the future) ....0/1

If [Run-date] = 11-04-2014, Cal-date = 11,
The Future Date to assign value is 18-04-2014
[calc number] = 7, so the p-number is [p7] (7 days in the future) ...... 0/1

If [Run-date] = 12-04-2014, Cal-date = 12,
The Future Date to assign value is 18-04-2014
[calc number] = 6, so the p-number is [p6] (6 days in the future) ...... 0/1

......... and so on until the 18th is the current date

The argument should be used every day to assign correct [p#] to CalDate based on [calc number]

There is a special character ($) in EXCEL you can insert into a formula to precede or wrap an address. As you use that same formula elsewhare in the spreadsheet, that address remains constant. I know the purpose is different, but isnt there a special character I can use to force my string to be taken literally in the argument thus using the [calc number] as a variable changing everyday ?

 

[CJ_London]

can you post the sql to your entire query, I do not understand what you are trying to do - it may be the square brackets are confusing Access, try removing them since they are used to identify a field or parameter

 

[sjdickey]

I do not have daily access to the computer and database that I am working on. I do not have my own computer. So I will post the sql when I am able to access,

I still would like to know if there is a special character I can use in a query object definition that would force my iif argument to be taken literally. The concatenated string resolves perfectly to provide the [p-number] in this case [p8] but it will be different everyday based on value of [calc num] and the moving calender.

I want my assembled argument to be taken literally and executed. I do not want the actual result of the argument [p8]. Instead, I want the value that is stored in that field. the p-numbers [p1], [p2], [p8]. [p10]. etc all contain 1 or 0 to indicate if a hotel room will be occupied or vacant on those future dates.

By the way, the square brackets work fine because all where I have described are existing fields in the tables.

My question if you can advise me this : Is there a special character I can insert into my argument so that the assemble string (with changing value) will be taken literaaly. I would appreciate your comment on this specifically in comparison to the EXCEL special character $ that is used in formulas so that an address in a worksheet will used as absolute address.

 

[CJ_London]

My question if you can advise me this : Is there a special character I can insert into my argument so that the assemble string (with changing value) will be taken literaaly

No. Not in SQL, but you can build the query in VBA as per my 2nd post