IF statement or IIF statement?

[FeS2]

I am having a very hard time figuring out how to do the following.
I have a small table, users enter data into it. Most of the time the Phone field matches a completion table but sometimes the Phone is completely new. When the user clicks update, i need the code to update the Phone records with the new data and if the phone doesn't exist it has to append it to the completion table. But do I do this with if statements or use an IIF sql statement? The 2 tables i have are Faxination1 and Am Completion. Thanks for all the help this group has given me. Sometimes i feel like i'm asking too much with these.

 

[PHV]

And what have you so far ?
BTW, IIf is not a SQL statement but a VBA function.

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

 

[FeS2]

I decided to use If and this is how I am using it except it always skips the Else instead of doing the Then. Here is the code I am using, any advice would be greatly appreciated. What this is doing/should be doing, is using dLookup to compare whats in the recordset to another table named AM Completions. If it doesn't find the phone in the am completions table, varTest is null. Then the if comes in and if it's null i want it to append the record to the am completions table. If the phone exists in both the recordset and am completions table i want it to update the comp type field in am completions. From what i can find, varTest even though it is null according to the if is never equal to null.

Dim RS As DAO.Recordset
Dim strSQL As String
Dim varTest As Variant

Set RS = Me.RecordsetClone
RS.MoveFirst

Do While Not RS.EOF
varTest = DLookup("[phone]", "[am completions]", "[phone]=" & RS![Phone])
If varTest = Null Then
strSQL = "INSERT INTO [AM Completions] ( PHONE, [Cable Pair], [Prov Type], [ORDER ID], [Wire Center], [Fax/VM Time] ) SELECT Faxination1.Phone, Faxination1.[Cable Pair], Faxination1.[Prov Type], Faxination1.[Order Id], Faxination1.[Wire Center], Faxination1.[Fax Time] FROM Faxination1;"
DoCmd.RunSQL strSQL
Else
strSQL = "UPDATE [AM Completions] INNER JOIN Faxination1 ON [AM Completions].PHONE=Faxination1.Phone SET [AM Completions].[comp type] = faxination1.[acs type] WHERE ((([AM Completions].PHONE)= faxination1.[phone]));"
DoCmd.RunSQL strSQL
End If
RS.MoveNext
Loop

 

[FeS2]

I got it working. I added varTest = Nz(varTest, "0") right after using DLookup and changed the if to check to see varTest = 0 instead of null.

 

[earthandfire]

You could also have used If IsNull(varTest) Then etc. etc.

Hope this helps.