CODEWITHSUNDEEP
ms-accesssyntaxvba
Steve Cooke
Steve Cooke

Reputation: 457

Access 2002/VBA - IIf

Consider:

sqlAddress = "SELECT " _
& "address_line_1, address_line_2, city, postcode, state " _
& "FROM contacts " _
& "WHERE (contact_id =" & contact_id & ")"

Set rsAddress = dbs.OpenRecordset(sqlAddress)

If Not rsAddress.EOF Then
    addressStr = IIf(rsAddress("address_line_2")='', rsAddress("address_line_1") & Chr(13) + Chr(10) & rsAddress("city") & Chr(13) + Chr(10) & rsAddress("postcode") & Chr(13) + Chr(10) & rsAddress("state"), rsAddress("address_line_1") & Chr(13) + Chr(10) & rsAddress("address_line_2") & Chr(13) + Chr(10) & rsAddress("city") & Chr(13) + Chr(10) & rsAddress("postcode") & Chr(13) + Chr(10) & rsAddress("state"))
    Me.vcTBAddress.SetFocus
    Me.vcTBAddress = addressStr
End If

What I want this to do is to ONLY display the address_line_2 in case it is not null and it is not an empty string. The IIf is returning a syntax error. I cannot see why - what am I missing please?

Upvotes: 0

Views: 72

Answers (1)

Steve Cooke
Steve Cooke

Reputation: 457

The answer is, of course, that I have stuffed up the empty string test: rsAddress("address_line_2")=''.

The correct syntax is: rsAddress("address_line_2")="" with double quotes ", not single quotes '.

Upvotes: 1

Related Questions