Using IIF function in query? (1 Viewer)

D

DarkAngel

Registered User.

Local time

Today, 01:26

Joined

Jul 3, 2007

Messages

21

• Jul 5, 2007

• #1

I have a field in a query that I want to select the value from a field, but if the value is null, then to select the value from the same named field in a different table.

Basically the whole point is to use the given description unless one doesnt exist, where the default one will be inserted instead.

Cheers,

 

D

DarkAngel

Registered User.

Local time

Today, 01:26

Joined

Jul 3, 2007

Messages

21

• Jul 5, 2007

• #2

IIF((isnull [curr value table].[parm description]), [refererence Parms].[parm description], [curr value table].[parm description]) AS [parm description]

That's what I've tried to use but says it has a syntax error, any ideas?

 

S

Squidinker

Registered User.

Local time

Today, 01:26

Joined

Jun 28, 2007

Messages

11

• Jul 5, 2007

• #3

Just a quick reply DarkAngel - it's not exactly what you need but I don't quite have the time at the moment to tailor it for you. It should be the right avenue though.


The following is from Wazz in reply a thread of mine earlier, so credit to him of course:

Private Sub chkFilterForNulls_AfterUpdate()

With Me
If .RecordSource = "qryMyFirstQueryWithoutCriteria" Then
.RecordSource = "qryMySecondQueryWithCriteria"
Else
.RecordSource = "qryMyFirstQueryWithoutCriteria"
End If
End With

End Sub

Click to expand...

This would be a VB routine but Wazz would be your man to explain it better.

 

gemma-the-husky

Super Moderator

Staff member

Local time

Today, 01:26

Joined

Sep 12, 2006

Messages

15,641

• Jul 5, 2007

• #4

to do it in a query, you need to put both tables in the query, linked by whatever pk/fk is appropriate

then you can add a field to the query saying in effect

iif(isnull(table1.fieldname),table2.whateverfield,table1.fieldname)

ie if the field in table1 is null, then use a field from table2, else use the field from table 1.

but you need both tables to be available to the query.

 

pbaldy

Wino Moderator

Staff member

Local time

Yesterday, 17:26

Joined

Aug 30, 2003

Messages

36,124

• Jul 5, 2007

• #5

In your original code:

IIF((isnull [curr value table].[parm description])...

Note the opening parenthesis for the IsNull function is out of place. In any case, the Nz() function would be simpler.

 

D

DarkAngel

Registered User.

Local time

Today, 01:26

Joined

Jul 3, 2007

Messages

21

• Jul 6, 2007

• #6

Thankyou for help guys, turned out the the blank values were zero length strings as well as null values, so had to use IIF with an OR clause in, but works 100% now.

parm description: IIf(IsNull([curr value table].[parm description]) Or [curr value table].[parm description]="",[reference Parms].[parm description],[curr value table].[parm description])

Cheers,