Case When Then , or If Then for Form Field

[Pete]

I am moving from Access 2003 to Access 2007 front -end with a SQL Server
Express Back-end. I have such a lasrge database with many complicated
functions I could not use any migrate process, it bombed out.

I have found , through trial and error, many of the functions I need to
extract data from the SQL to Access; however I am now trying to design a
field which is supperimposed over another field, if the main field below is
equal to No, then the upper field (with text color) , repeats No in red. Else
it is NULL then the lower field is equal to Yes, and shows through as a Black
Yes. This worked fine in 2003 useing IIF ([LINA]="No", "No",NULL) .

But using 2007 I have not found any solution - I have tried IF LINA='No'
Then 'No' Else NULL; and CASE WHEN LINA = 'No' THEN 'No' ELSE NULL END. plus
of course IIF([LINA]="No","No",NULL .

Any help here, Thanks

 

[Rob Parker]

Hi Pete,

The original IIf expression should work in A2007 - I have no idea why it
wouldn't/doesn't (and I don't have A2007 to check).

The If ... Then ... Else ... End If construction is not a single
expression; it is the set of statements used in VBA coding. For a
calculated expression the equivalent is the IIf function. And the Case
statement (whose syntax is considerably different from yours) is also a
series of statements used in VBA code.

That said, why are you trying to use this type of work-around (common in A97
and earlier), when you can use conditional formatting on the textbox to
change the colour of the text based on the value of the field (or on an
expression, if necessary). Look up conditional formatting in the Help
files - it's not difficult, and is the obvious choice for doing what you are
wanting.

HTH,

Rob