recordset value failing on null variable

[gangel]

Hey pplz!



WOW ive been writing some crazy code today and been very successful up until now!


In basic terms:
I have a table [customers]
and i have [firstname] [lastname] and [address]

im using this fields as variables in a dao.recordset as 'lookup' variables in a query.

My code runs 100% EXCEPT when one of the fields is null...

So have:

Dim Fname As String
Dim Lname As String
Dim add As string

dupcust.MoveFirst

Do Until dupcust.EOF


Fname = dupcust(1)
Lname = dupcust(2)


If IsNull(dupcust(3)) Then
Set dupsingle = CurrentDb.OpenRecordset("SELECT * FROM Customers_Date_Sub WHERE Surname ='" & Lname & "' and Firstname = '" & Fname & "'")
Else
add = dupcust(3)


Set dupsingle = CurrentDb.OpenRecordset("SELECT * FROM Customers_Date_Sub WHERE Surname ='" & Lname & "' and Firstname = '" & Fname & "' and Address1 = '" & add & "'")
End If


etc etc

...10 mins later

I have changed dim add as VARIANT and this has solved the issue it seems.

I am now having issues with lastnames like O'Brien

I read somewhere that you can change the ' to " and it solves the issue... but in my case it seems to not work. it doesnt Error, it just runs through the loop and ends as the dupsingle seems to have no records....



So if anyone coud help me on the ' issue and maybe confirm that if im using a dao.recordset and assigning MYVAR = RST(0) i MUST have myvar as a variant because if i do a string and then a record has a null value it will always fail?

Thanks for helping!!

[CJ_London]

to solve the O'Brien issue you need to replace the ' with two so

....Surname ='" & replace(Lname,"'","''") & "'....

with regards nulls, look at the nz function

add=nz(dupcast(3),"")

Note that add is a reserved word, so using it can create unexpected errors

[gangel]

Amazing thanks Ajax, it's midnight but I'll try it out tm!!
And I bet the add thing was causing an issue!


I'll do nz and also change to fadd and get back tm

[gangel]

hmmm it works for the ' issue but im having issues with the nz still.

Works for everything except this one field which:

Dim subid as integer

subid = nz(dupsingle(5),"")

Both tables have suburbID as a number

this ends up with a "type mismatch" error....

ideas?

[CJ_London]

you are trying to assign a text value to a numeric variable

try

subid = nz(dupsingle(5),0)