IIF In Query that refers to other field names

[JohnBoy]

Hi



I have a query that I want to amend so that at run time it looks for a value in one field and dependant on the value returned uses one of 2 other fields values to populate itself,

So:
Field to populate is called LatestCode,
Fields to check are AuditDate (see if it has a date in it)
Result Fields are OrigCode and NewCode

I have the following LATESTCODE: IIf([AuditDate]<=0,[OrigCode],[NewCode])

I am not getting any reult from this just, I have never used other field names to populate the new field like this so is it actually possible to do this or have I missed something in the formula..

Any help welcome

Regards

JB

[pbaldy]

That should work, though I think the test is off. If you're looking for a date, try this:

IIf(IsDate([AuditDate]),[NewCode],[OrigCode])

[JohnBoy]

Hi thanks for that it works fine now, it was obviouslyt the date bit that I had wrong.

Many thanks again

JB