Access2000 Nested IIf() functions in date/time format

[BrianM2]

I am working on a report in Access2000 which is getting information from several tables via a number of queries.
I am using nested IIf() functions to combine text formatted data in a calculated query field before using it in a report.

One such IIf() function is

Desc_col: IIf([HOVR - UDP].[SERVICE] Is Not Null,[DA] & " " & [SEQ] & " " & [TYPE] & [MO] & " " & [SUB] & " - " & [HOVR - UDP].[SERVICE],
IIf([MinOfCCT] Is Not Null,"I/O DIST CAB " & [UDP - JC CIRCUITS-05-F].[PWR] & " - CCTS " & [MinOfCCT] & " thru " & [MaxOfCCT],IIf([DESCRIPTIO] Is Not Null,[DESCRIPTIO],"Spare")))

When I have tried to do the same thing with date fields it does not work. The function is fine as long as it is not nested.

That is

Rev_col: IIf([Rev_HOVR] Is Not Null,[Rev_HOVR],"")

and

Rev_col: IIf([Rev_Elec_equip] Is Not Null,[Rev_Elec_equip],"")

both work.

But when I combine them into a nested function

Rev_col: IIf([Rev_HOVR] Is Not Null,[Rev_HOVR],IIf([Rev_Elec_equip] Is Not Null,[Rev_Elec_equip],""))

I get an error message "! Data type mismatch in criterial expression"

There is no data mismatch in the original tables from which the information was extracted. The dates are all in DATE/TIME and set to "General"format in the tables.

I am wondering if I will have to use Visual Basic function to combine these fields. However that poses a problem. When I've looked at this I find that runtime VB is no longer available in Access and I do not want to go out and buy it on the offchance that it will work.

Can anyone tell me if there is another solution to my problem?

 

[WayneRyan]

Brian,

Indented for readability:

Desc_col: IIf(Not IsNull([HOVR - UDP].[SERVICE]),
              [DA] & " " & [SEQ] & " " & [TYPE] & [MO] & " " & [SUB] & " - " & [HOVR - UDP].[SERVICE],
              IIf(Not IsNull([MinOfCCT]),
                  "I/O DIST CAB " & [UDP - JC CIRCUITS-05-F].[PWR] & " - CCTS " & [MinOfCCT] & " thru " & [MaxOfCCT],
                   IIf(Not IsNull([DESCRIPTIO]),
                       [DESCRIPTIO],
                       "Spare")))

Wayne

 

[BrianM2]

Wayne

Thanks for the prompt reply.

I note the change from "Is Not Null" which is what I usually use (is it an archaic form?) to "Not IsNull" and have applied it to the Date/Time formatted IIf() function. Unfortunately running the query still results in the same error code.

In the meantime I have located the runtime visual basic editor in the program and will try to develop a module to combine the date fields.

I certainly appreciate your interest and if you have any other ideas would be delighted to hear from you.

Brian

 

[WayneRyan]

Brian,

"Is Not Null" is good in the SQL environment.

"Not IsNull(SomeField)" is good in the VBA environment.

As for the "Type Mismatch" error ...

Need more info, but you keep referring to date fields. The "&" is
for concatenating strings. If some of those fields are not strings
you should use functions like "CStr" (for numbers) and "Format"
(for dates) to convert them to strings so that you can concatenate
them.

Wayne

 

[BrianM2]

Wayne

We may be at cross purposes here - and having reread my initial statement I guess it could have been put more clearly. I agree that I am using "&" for concatenation of the initial function which I quoted and which you modified for me. That function which is concatenating strings is working well.

The IIf() which is giving me a problem is:

Rev_col: IIf([Rev_HOVR] Is Not Null,[Rev_HOVR],IIf([Rev_Elec_equip] Is Not Null,[Rev_Elec_equip],""))

where both [Rev_HOVR] and [Rev_Elec-equip] are date/time formatted field variables and Rev_col is intended to populate a column with one or the other or a null. When I split it into two functions and run them independently they are fine and return correct results. However when I nest them I get the quoted error message.

Any more thoughts?

Brian