Pass a null string back from a function?

[jgoodman00]

I have the following code:
Function IDWork(strref) As String
Dim db As Database, rst As Recordset, rstAdopt As Recordset
Set db = currentdb
Set rst = db.OpenRecordset("SELECT Pnamstr.* FROM Pnamstr WHERE (((Pnamstr.RefNo) = '" & strref & "'));&quot
Set rstAdopt = db.OpenRecordset("SELECT ADOPT.* FROM ADOPT WHERE (((ADOPT.RefNo) = '" & strref & "'));&quot
If rstAdopt!priority <> &quot;A&quot; Then
IDWork = rstAdopt!RefNo & rstAdopt!brsno
Else
IDWork = Null
End If
End Function

The idea is that if the priority is A, the string IDWork will be null. This is passed back to the calling function. For some reason it is producing an error on the line 'IDWork = Null'. At this point, the error message is 'Invalid use of null'


This seems a little strange. It works if I change it into a zero length string, but I would rather pass out null.

Any suggestions??? James Goodman
j.goodman00@btinternet.com

 

[THoey]

Instead of passing back a NULL, what about just assigning an empty string?

If rstAdopt!priority <> &quot;A&quot; Then
IDWork = rstAdopt!RefNo & rstAdopt!brsno
Else
IDWork = &quot;&quot;
End If
Terry M. Hoey

 

[jgoodman00]

I didn't really want to pass back a zero length string. Kinda messy when it comes to update queries on that field, because some will have values, others none, & some zero length strings...

Is it actually possible to pass a null string out of a function, or am I trying to do something which is impossible?? James Goodman
j.goodman00@btinternet.com

 

[Craig0201]

James,

Strings cannot be null as far as I am aware. Seems to me you're attempting the impossible.

Craig