I must be blind!! (1 Viewer)

[johndoh!!]

Can anyone tell me why the following doesn't work?

Dim db As Database
Dim rst As Recordset
Dim strSql As String

OID = 415
strSql = "SELECT * FROM qryPrintPO WHERE OID = " & OID
Set rst = CurrentDb.OpenRecordset(strSql)

rst.MoveFirst
Do Until rst.EOF

strFilter = "Sku='" & rst!Sku & "'"
x = DLookup("Sku", "PrintPO", strFilter)
'PROBLEM IS HERE NO MATTER WHAT THE VALUE OF x IT SKIPS THE THEN
If x <> rst!Sku Then

Dim rst2 As Recordset
Dim strSql2 As String
strSql2 = "SELECT Quantity FROM qryPrintPO WHERE OID = " & OID & " AND Sku ='" & rst!Sku & "'"
Set rst2 = CurrentDb.OpenRecordset(strSql2)

rst2.MoveFirst
Quantity = 0
Do Until rst2.EOF
Quantity = Quantity + rst2!Quantity
rst2.MoveNext
Loop

Dim rst3 As Recordset
Set rst3 = CurrentDb.OpenRecordset("PrintPO")
rst3.AddNew
rst3!Qty = Quantity
rst3!Sku = rst!Sku
If rst!Quote > 0 Then
rst3!Cost = rst!Quote
Else
rst3!Cost = rst!DN
End If
rst3.update

End If

rst.MoveNext
Loop


If x = rst!Sku it skips the Then clause, EVEN when x <> rst!Sku it skips the Then Clause!!

D'oh!!

 

[Newman]

I'm not sure if that is the reason, but x is not declared.
Newman

 

[johndoh!!]

I changed it like this and it works:

strFilter = "Sku='" & rst!Sku & "'"
x = DLookup("Sku", "PrintPO", strFilter)
If x = rst!Sku Then

Else
Dim rst2 As Recordset
Dim strSql2 As String
strSql2 = "SELECT Quantity FROM qryPrintPO WHERE OID = " & OID & " AND Sku ='" & rst!Sku & "'"
Set rst2 = CurrentDb.OpenRecordset(strSql2)

rst2.MoveFirst
Quantity = 0
Do Until rst2.EOF
Quantity = Quantity + rst2!Quantity
rst2.MoveNext
Loop

Dim rst3 As Recordset
Set rst3 = CurrentDb.OpenRecordset("PrintPO")
rst3.AddNew
rst3!Qty = Quantity
rst3!Sku = rst!Sku
If rst!Quote > 0 Then
rst3!Cost = rst!Quote
Else
rst3!Cost = rst!DN
End If
rst3.update

End If

For some reason it can tell that "x = rst!Sku" is false but cannot tell that "x <> rst!Sku" is True!!

Strange.

 

[Pat Hartman]

If the DLookup() does not return anything, then x ends up as null. Null has an interesting property and that is that the result of any expression involving a null results in an answer that is null. Therefore - null <> "a" - is NOT true. The only way to get the correct answer is to test for a null specifically or test for a specific value. therefore - null Is Null - is true and - null = "a" is false. BUT null = null is also false. Confusing isn't it?

 

[johndoh!!]

Thank you Pat, I understand perfectly!!

D'oh!!