Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Nulls, Variants, Strings (2002)

    I had previously submitted the following code as working (see post 418077). There are some modifications denoted in bold and underlines to the original code . The code in the original post works and it should not work. One of the possible variable assignments for strDKend is "Null". This code worked until I started adding other unbound fields and received an "invalid use of null" error, since I was trying to assign a null to a string variable. Interestingly, this code will work ,assigning "null" to the first four string variables. I only get the "invalid use of null" error at the strdate assignment.

    Anyone run into this? I have fixed my code to change the strings to variants, but I am still wondering why I got and can get away with this for a period of code/

    Private Sub SelectIDs_Click()
    'insert new ID or range of IDs into ID table

    On Error GoTo Err_SelectIDs_Click

    Dim strDKstart, strDKend, strindexid, strsiteid, strdate, strcnty As String
    Dim strcount, strSelect As String

    Dim counter As Integer

    Dim qdf As DAO.QueryDef
    Dim dbs As DAO.Database

    Set dbs = CurrentDb

    strDKstart = [Text0]
    strDKend = [Text2]
    strindexid = [Text17]
    strsiteid = [Text19]
    strdate = [Text21]
    strcnty = [Text23]

    strcount = strDKstart

    If IsNull(strDKend) Then
    MsgBox "Second Value is NULL " & strDKend
    strSelect = "INSERT INTO TEST(sampleID) values (" & strDKstart & ");"
    Set qdf = dbs.CreateQueryDef("", strSelect)
    qdf.Execute
    ELSE
    For counter = 1 To (strDKend - strDKstart + 1)
    strSelect = "INSERT INTO TEST(sampleID) values (" & strcount & ");"
    MsgBox strSelect
    Set qdf = dbs.CreateQueryDef("", strSelect)
    qdf.Execute
    strcount = strcount + 1
    Next
    End If

    Set qdf = Nothing
    Set dbs = Nothing

    Exit_SelectIDs_Click:
    Exit Sub

    Err_SelectIDs_Click:
    MsgBox Err.Description
    Resume Exit_SelectIDs_Click

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nulls, Variants, Strings (2002)

    In Visual Basic, you must declare the type of each variable separately. The line

    Dim strDKstart, strDKend, strindexid, strsiteid, strdate, strcnty As String

    declares strDKstart, strDKend, strindexid, strsiteid and strdate without a specific type, i.e. as a Variant (which can be Null), and only strcnty as a String. This is different from (for example) Pascal. If you need string variables, you should use either

    Dim strDKstart As String, strDKend As String, strindexid As String, strsiteid As String, strdate As String, strcnty As String

    or

    Dim strDKstart As String
    Dim strDKend As String
    Dim strindexid As String
    Dim strsiteid As String
    Dim strdate As String
    Dim strcnty As String

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Nulls, Variants, Strings (2002)

    Ahhh yes, all is clear now and suitably failing as null assignments to strings should when editing to assignment with "as". I did not remember seeing that only the last variable in the dim statement was the only one assigned the type.

    Why doesn't "Option Explicit" handle the unassigned data types? Seems like we have implicit assignment here.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Nulls, Variants, Strings (2002)

    Option Explicit requires that declare variables vefore using them. It has nothing to do with the type you declare. You do have an implicit assignment if you don't specify the datatype. In that case, you get a variant, which can hold any other datatype, including a null.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Nulls, Variants, Strings (2002)

    It is my understanding that in all but vb.net, "option explicit" forced one to declare a variable or you would get an error at compile.

    From Visual Basic Language Reference

    Option Explicit Statement


    If used, the Option Explicit statement must appear in a file before any other source statements.

    When Option Explicit appears in a file, you must explicitly declare all variables using the Dim, Private, Public, or ReDim statements. If you attempt to use an undeclared variable name, an error occurs at compile time.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nulls, Variants, Strings (2002)

    If a module has Option Explicit at the top, you must declare all variables explicitly, but that doesn't force you to specify a variable type explicitly.

    Dim MyVar

    is a valid declaration - it explicitly tells VB(A) that you're going to use a variable named MyVar, but it doesn't specify a variable type. Therefore, MyVar is implicitly declared as a Variant. This declaration is equivalent to

    Dim MyVar As Variant

    Similarly,

    Dim a, b, c As Integer

    is a valid explicit declaration of all three variables a, b and c. Since no variable type is specified for a and b, it is equivalent to

    Dim a As Variant, b As Variant, c As Integer

  7. #7
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Nulls, Variants, Strings (2002)

    Thanks Hans and Charlotte

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Nulls, Variants, Strings (2002)

    You have to declare them in VB.Net too, but they're all objects of some sort and strongly typed, no variants.
    Charlotte

Posting Permissions

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