Problem with IIf statement 1

[MA04]

Hi all,

I can't seem to get my IIF(IsNull(...)) statement correct here it is

IIF(IsNull([Forms]![Ma_search2]![Expr1]), ([Forms]![Ma_search2]![Expr1])) AS Expr1, IIF(IsNull([Forms]![Ma_search2]![Expr2]), ([Forms]![Ma_search2]![Expr2])) AS Expr2, IIF(IsNull([Forms]![Ma_search2]![Expr3]),([Forms]![Ma_search2]![Expr3])) AS Expr3, IIF(IsNull([Forms]![Ma_search2]![postcode]), ([Forms]![Ma_search2]![postcode])) AS Expr4, IIF(IsNull([Forms]![Ma_search2]![Name_input]), ([Forms]![Ma_search2]![Name_input])) AS Expr5;

The above is part of an append query reading unbound textboxes. Any Help appreciated, thanks in advance,
M-.

 

[PHV]

The syntax of the IIf function:
IIf(condition, true part, false part)
i.e. three arguments.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MA04]

Thanks for the reply PHV,

I still can't seem to get syntax correct, what can i put for a false part?

M-.

 

[PHV]

What do you want to do ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MA04]

I have an append query that is run from a command button. The query appends unbound textboxes which have been populated by query values, if one value is null the query will not run, it cuses an error.

What i want to do is append the unbound textboxes even if there are null values in any of the fields.

M-.

 

[PHV]

You may try this in the relevant part of the SQL code.
IIf(IsNull([Forms]![Ma_search2]![Expr1]), Null, [Forms]![Ma_search2]![Expr1]) AS Expr1,
IIf(IsNull([Forms]![Ma_search2]![Expr2]), Null, [Forms]![Ma_search2]![Expr2]) AS Expr2,
IIf(IsNull([Forms]![Ma_search2]![Expr3]), Null, [Forms]![Ma_search2]![Expr3]) AS Expr3,
IIf(IsNull([Forms]![Ma_search2]![postcode]), Null, [Forms]![Ma_search2]![postcode]) AS Expr4,
IIf(IsNull([Forms]![Ma_search2]![Name_input]), Null, [Forms]![Ma_search2]![Name_input]) AS Expr5

You haven't say how the append query is executed, so it's just a guess.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MA04]

Hi PHV,

The query is run from a command button in VBA as a event procedure like so:

Private Sub Append_Click()
Dim strSQL As String

strSQL = "INSERT INTO ma_enq ( A1, A2, A3, A4, NAME )" & _
"SELECT IIf(IsNull([Forms]![Ma_search2]![Expr1]), Null, [Forms]![Ma_search2]![Expr1]) AS Expr1, IIf(IsNull([Forms]![Ma_search2]![Expr2]), Null, [Forms]![Ma_search2]![Expr2]) AS Expr2, IIf(IsNull([Forms]![Ma_search2]![Expr3]), Null, [Forms]![Ma_search2]![Expr3]) AS Expr3, IIf(IsNull([Forms]![Ma_search2]![postcode]), Null, [Forms]![Ma_search2]![postcode]) AS Expr4, IIf(IsNull([Forms]![Ma_search2]![Name_input]), Null, [Forms]![Ma_search2]![Name_input]) AS Expr5 "

DoCmd.RunSQL strSQL
End Sub

I have pasted your code on the query but still seem to get an error if i try to append a null value, the error i get is:
'Access can't append all records in the append query''..., 1 record due to validation rule violation',

any ideas,
M-.

 

[PHV]

And this ?
strSQL = "INSERT INTO ma_enq ( A1, A2, A3, A4, NAME )" & _
"SELECT Nz([Forms]![Ma_search2]![Expr1]) AS Expr1, Nz([Forms]![Ma_search2]![Expr2]) AS Expr2, Nz([Forms]![Ma_search2]![Expr3]) AS Expr3, Nz([Forms]![Ma_search2]![postcode]) AS Expr4, Nz([Forms]![Ma_search2]![Name_input]) AS Expr5"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MA04]

I seem to get the same error message with above code the append querys i have tried all work only if i have filled in all the text boxes but not if one is null.

This is the error message again:
'Access can't append all records in the append query''..., 1 record due to validation rule violation',

M-.

 

[PHV]

Check the validation rules of the 5 fields in table design view for ma_enq.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MA04]

Thank you very much PHV for all your effort, it was a silly mistake by me, must have set field property for allow zero length field to no, have now changed to yes and it works.

Thank you very much for the help,
M-.