Append Query - IIF statement (1 Viewer)

[Summer123]

Hello, i am writing an append query where certain fields are written to a table, this is working fine but how do i write an if then statement in the query where i am asking to look for 2 different situations?

table with 4 different fields, NameID, RecID, FieldNumber, Amount - tryingt o query and append table if the fieldnumber or amount are null.

INSERT INTO tblEdits ( NameID, RecID, EditedFieldValue, EditedFieldMsg)
SELECT NameID, RecID, FieldNumber, "Missing Field Number" AS EditedFieldMsg
FROM Temprecord
WHERE (FieldNumber Is Null);

How do i make it so that when the field number is missing it will report out "Missing Field Number" in the EditedFieldMsg or if Amount is missing then it will report out "Missing Amount"?

I know that i can write 2 append queries but trying to go away from the repetative work.
thanks Summer

 

[apr pillai]

INSERT INTO tblEdits ( NameID, RecID, EditedFieldValue, EditedFieldMsg)
SELECT NameID, RecID, FieldNumber, IIF(Nz([Amount],0)+Nz([FieldNumber],0)=0,"Missing Amount and FieldNumber",IIF(Nz([Amount],0)=0 AND Nz([FieldNumber],0)>0,"Missing Amount",IIF(Nz([Amount],0)>0 AND Nz([FieldNumber],0)=0,"Missing Field Number",""))) AS EditedFieldMsg
FROM Temprecord
WHERE (FieldNumber Is Null);

 

[Summer123]

great thank you that works perfectly. Since i am new to this, what does Nz stand for and what is the use of it?

 

[Summer123]

also how can i display the actual field itself?

 

[apr pillai]

NZ() is a built-in Function to check for Null Value in a Field or Variable. If the test found Null value in the Field then takes the Value given as second parameter.

Example:

x = Null
x = Nz(x,0)

In the above example we have initialized the value of x = Null explicitly. We pretent that we don't know what value we have in x, so we have to test it to find out. That is what we did in the second statement. If found x=Null then asks Nz() function to take 0 into x replacing Null.

If x have a value other than Null then returns that value without change.

This function is very useful and important in calculations.

Example

x = 10
y = Null
z = x + y
z = Null 

The result in z is Null because 10 + Null = Null.

z = x + Nz(y,0)
z = 10 
 (10 + 0 = 10)
[/CODE