Invalid use of Null

[W.Dnx.W]

Hi all,

I've got this line of code under my form_current() envent (frmEmails)

str2 = Forms!frmMain!txtSQL.Value

but when I start the DB I get the Invalid use of Null error,

now, it seems that I need to define my txtSQL.value = "" but am not sure where. I've tried under frmMain_load event but it seems that's not the right spot...

is it problem that I had put that code on form_current() or is that OK and I just need to define my txtSQL.valuse somewhere else.

thanks a lot

D

 

[MStef]

1) What is "str2"??? Is it Numeric field.
2) What is txtSQL ??????

 

[W.Dnx.W]

str2 is DIm'd as string
txtSQL is a text box
.....

 

[MStef]

Are you sure, the error appear on this line of code??

 

[W.Dnx.W]

No, the error apears when I open the DB and then highlights this line, and I knwo why...because it is assigning str2 a value which is still Null at the moment the db is opened...so I need to define either str2="" or txtSQL.value ="" at some point, but not sure where.

D

 

[Fear Naught]

It sounds to me as if the txtSQL control does not have any data i.e contains null so I would try using the NZ() function which will return a valid result if txtSQL is in fact a NULL.

Try this

str2 = NZ(Forms!frmMain!txtSQL.Value,"")

You might actually get away with

str2 = NZ(Forms!frmMain.txtSQL,"")

 

[W.Dnx.W]

You might actually get away with

str2 = NZ(Forms!frmMain.txtSQL,"")

Yep...
thanks a lot

though I'm still not sure where could I have stored that string so that I don't get that error.

 

[W.Dnx.W]

BUT...there's always but at some point, isn't it...now I get another error, and that is ONLY when I switch to design view and back again to normal if I am at my form where the code is (the one we just discussed above).
it says

invalid procedure call or argument

...and it's refering to the str2

str2 = Mid(str2, 1, b - 1) & " AND email <>'' ORDER BY tblUsers.Ime"

because I had used the NZ() with a Null (Is suppose)...

what now...can i put something on my txtSQL control to check if the value is Null and then to make it ""

D

 

[Fear Naught]

What you have just posted looks like you are trying to construct an SQL statement. Is this what you are doing??

The line of code you have posted looks very odd to me but without seeing the full procedure I can't tell.

 

[W.Dnx.W]

Yes, I am constructing the SQL statement..and it works great...the thing is that I need that same SQL st. in another form so I'm using a hidden text box to store my SQL string and use it elsewhere...a long way around

 

[W.Dnx.W]

str1 = "SELECT DISTINCT tblUsers.Email,tblUsers.Ime, tblUsers.Prezime..." - this a long SQL string

str2 = Nz(Forms!frmMain.txtSQL, "") 



b = InStr(str1, "order")

    If Len(str2) = 0 Then
        b = InStr(str1, "order")
        str1 = Mid(str1, 1, b - 1)
        str2 = " where email <> '' ORDER BY tblUsers.Ime "
        str1 = str1 & str2
        Me.lblPrimatelji.Caption = "svi" 'mijenja naziv primatelja ovisno o kategoriji izbora
    Else
        str1 = Mid(str1, 1, b - 1)
        b = InStr(str2, "order")
        c = InStr(str2, "AND")
        str2 = Mid(str2, 1, b - 1) & " AND email <>'' ORDER BY tblUsers.Ime"
        str1 = str1 & str2
        b = InStr(str2, "WHERE")
        str = Mid(str2, b + 23, InStr(str2, ")") - b - 23)
        'lista primatelja dobiva naziv kategorije koja je odabrana
        Me.lblPrimatelji.Caption = str
        'MsgBox ("AND se nalazi na " & c & " mjestu")
    End If

so, here is the code and as I said, now again I'm getting the error invalid procedure call or argument...I suppose because NZ() only translates Null as "zero length" but it is still a Null. Am I right?

D
p.s. don't bother the part with Me.lbl that's just something I'm using to make my form more user friendly...