Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95

    Unanswered: Invalid use of Null

    Why doesnt this work:


    If rsRecords!pctyp.Value = "" Then
    usrresponse = MsgBox("You cannot choose this.", vbInformation, "Warning")
    Exit Function
    End If

    If Not rsRecords.NoMatch Then
    FindPCtypeIDFromUSRID = rsRecords!pctyp.Value
    Else: FindPCtypeIDFromUSRID = 2
    End If


    When rsRecords!pctyp.value is null, i.e. the cell in the db is empty, it still ignores the 'exit function' and does not give the msgbox, it gives error94: invalid use of 'null' ... Debugging points at

    FindPCtypeIDFromUSRID = rsRecords!pctyp.Value

    as being the problem, which I understand, but it should not go there at all, it should've already exited the function. Am I making a syntax error?

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Use NZ(rsRecords!pctyp.Value, "")

    That converts the Nulls to a zero length string
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Or ... If rsRecords!pctyp.Value & "" = "" Then
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by RedNeckGeek
    Use NZ(rsRecords!pctyp.Value, "")

    That converts the Nulls to a zero length string
    What where when how?

    I understand correctly that saying = "" is not good enough, it would recognize that?

  5. #5
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by M Owen
    Or ... If rsRecords!pctyp.Value & "" = "" Then
    Heh nice solution I think it actually works, however I'm getting the msgbox twice for some reason :E

  6. #6
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    How do I set a long to zero (empty)?

    dim x as long
    x = ""

    doesnt work

    dim x as long
    x = null

    doesnt work

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I hate to bring it up again, but...

    x=NZ([somefield],0)

    NZ means NullToZero, but you can also use it to substitute values other than zero.
    Inspiration Through Fermentation

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by avlan
    How do I set a long to zero (empty)?

    dim x as long
    x = ""

    doesnt work

    dim x as long
    x = null

    doesnt work
    x=0

    [This is filler.]
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by M Owen
    x=0

    [This is filler.]
    You just couldn't resist, could you?
    Inspiration Through Fermentation

  10. #10
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by RedNeckGeek
    I hate to bring it up again, but...

    x=NZ([somefield],0)

    NZ means NullToZero, but you can also use it to substitute values other than zero.
    k didnt know that

    So x=0 is the same as an empty cell?

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by RedNeckGeek
    You just couldn't resist, could you?
    Nope ... My 1st reaction was: "You've got to be kidding me!!!"

    You know me ... I'll give you your exact answer even if it's not the one you really want ... (Check with Izy on that ...) Some people have got to learn to never ask me if some thing is possible ... My answer is almost always: YES even tho they were asking for something else (i.e. Don't ask the wrong question ...)
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by avlan
    So x=0 is the same as an empty cell?
    ummmm.... You lost me there.
    Inspiration Through Fermentation

  13. #13
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by RedNeckGeek
    ummmm.... You lost me there.
    Well.... we're working with tables here, and I was under the impression that an empty cell in a table is not the same as the value 0 in a cell..

    Not that I need it, but im only working with access for 2 months or so, I just wanna get all the syntaxes and things right :P Some things are just not logical

    A = B

    is not the same as

    B = A

    in Access. That kinda thing. I admit, I havent programmed in years or in depth so I just need to learn the logics of programming

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •