Access "IIf" condition messes up schema of table

Question

I have a ms-access file which has some tables that are generated by using joins on other tables in the db file. One of the tables (called "Pin_Codes") has got an access "IIf" condition in the script to populate the column. The SQL script for the same is as below.

SELECT TableA.POSTCODE AS PINCODE,
       IIf([tableC_column] IS NOT NULL, [tableC_column], Iif([tableB_column] IS NOT NULL, [tableB_column], "NA")) AS DISTRICT
FROM   (TableA
        LEFT JOIN [tableB]
          ON TableA.POSTCODE = [tableB].Postcode)
       LEFT JOIN [tableC]
         ON TableA.POSTCODE = [tableC].Postcode_Final;

So there are 2 columns named PINCODE (type string) and DISTRICT (type string) in the table Pin_Codes

I import this access file in my application, which first checks for the schema of all the tables in the accdb file. But when it gets the schema of Pin_Codes, the max_length of DISTRICT column comes out to be about 577346 (which should have been 255 as it is of string type). The max_length of PINCODE comes out fine as 255 though.

Even a dummy IIf condition (having true condition always and same true and false part) gives this error. Furthermore, on removing the IIf condition for DISTRICT column (and populating it with say tableB_column or "NA") solves the issue, and the length value comes to be 255.

I verified the schema for the source tables TableA, tableB & tableC, which is being interpreted properly by the application.

What can be the cause for this issue and is there any way (besides doing away with "IIf" condition) to overcome this?

N.B. The shema was obtained and tested using both System.Data.DataTable.GetSchemaTable(), OleDB functions to get schema table and JetOLEDB4.0 dlls

Your question is hard to understand, you might get better responses if you reword it and possibly include context code or data from the tables. — Michael, Aug 05 '13 at 18:52
Instead of `IIf([tableC_column] IS NOT NULL, [tableC_column], Iif([tableB_column] IS NOT NULL, [tableB_column], "NA"))` you could try `Nz([tableC_column], Nz([tableB_column], "NA"))` — Monty Wild, Aug 07 '13 at 23:25
Don't you mean: "I have a ms-access file which has some *queries* that are generated by using joins on other tables in the db file."? — Monty Wild, Aug 07 '13 at 23:30

score 4 · Answer 1 · answered Aug 07 '13 at 23:38

It would appear that the IIf statement is making its query field appear to be a memo type. Perhaps you could try wrapping the IIf([tableC_column] IS NOT NULL, [tableC_column], Iif([tableB_column] IS NOT NULL, [tableB_column], "NA")) or Nz([tableC_column], Nz([tableB_column], "NA")) in a CStr() function:

SELECT TableA.POSTCODE AS PINCODE,
       CStr(IIf([tableC_column] IS NOT NULL, [tableC_column], Iif([tableB_column] IS NOT NULL, [tableB_column], "NA"))) AS DISTRICT
FROM   (TableA
        LEFT JOIN [tableB]
          ON TableA.POSTCODE = [tableB].Postcode)
       LEFT JOIN [tableC]
         ON TableA.POSTCODE = [tableC].Postcode_Final;

or

SELECT TableA.POSTCODE AS PINCODE,
       CStr(Nz([tableC_column], Nz([tableB_column], "NA"))) AS DISTRICT
FROM   (TableA
        LEFT JOIN [tableB]
          ON TableA.POSTCODE = [tableB].Postcode)
       LEFT JOIN [tableC]
         ON TableA.POSTCODE = [tableC].Postcode_Final;

The CStr should force the field to a string type.

Access "IIf" condition messes up schema of table

1 Answers1