Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Invalid use of Null (Access 2000)

    Error invalid use of Null


    I came accross a vert strange and inexplicable phenmenon.If i decide to open the report in case the control Office on my form is False,
    that is If Forms![FBenchmark]![Office] = False Then
    I get the error message Invalid use of Null.
    However, if i first use the Else condition, which means that i have chosen a number from the Option control Office,
    and after finishing the process i turn again to the first condition, which is that i select no option form the control Office, then the report opens
    in the way it should be opened that is Me.RecordSource = strBas

    In other words, if i decide to open the report with Me.RecordSource = StrBas, i cannot do it right away.I must at first select some option,open the report

    unnecessary, close the report and just then i can open the report with RecordSource = strBas.Is it possible to evade this nuisance,and how is it possible
    to happen? I have tried to reverse the order of the if condition but again withthe same effect.

    dim city as Long
    city = Forms![FBenchmark]![Office]
    Dim strBas As String
    Dim sqlafid As String
    sqlafid = "AND ((Customers.afid)= " & city & ")"
    strBas = " SELECT DISTINCTROW Orders.OrderID, Sum([Order Details].[UnitPrice]*[Quantity]) AS Subtotal, Orders.customerid, Orders.orderdate, Sum([Order

    Details].cartons) AS SumOfcartons, Sum([Order Details].Quantity) AS SumOfQuantity, Sum([Order Details].liters) AS SumOfliters, customers.CompanyName,

    customers.afid " & _
    " FROM customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON customers.Customerid = Orders.customerid

    " & _
    "GROUP BY Orders.OrderID, Orders.customerid, Orders.orderdate, customers.CompanyName, customers.afid, Orders.paymentid " & _
    " HAVING (((Orders.customerid) " & strNotIn & " AND ((Orders.paymentid)=False))"
    If Forms![FBenchmark]![Office] = False Then
    Me.RecordSource = strBas
    Else
    Me.RecordSource = strBas & sqlafid
    End If

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid use of Null (Access 2000)

    Where are you getting the null error on the line

    If Forms![FBenchmark]![Office] = False Then

    or

    Me.RecordSource = strBas


    Peter

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid use of Null (Access 2000)

    i receive the error on the following line:
    city = Forms![FBenchmark]![Office]
    obvisoly if i do not choose city ,it hampers the code.or at least i think so.I tried to move this line further down,
    but again with failure.
    regards

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Invalid use of Null (Access 2000)

    Your problem stems from the fact that Office is null.
    What do you want to populate City with if Office is null?
    Having worked that out, then change the instruction:
    city = Forms![FBenchmark]![Office]
    to:
    city = nz(Forms![FBenchmark]![Office],nnn)
    where nnn is the desired value for City if Office is null.
    The code should then work.
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid use of Null (Access 2000)

    It looks as if you are using a zero to indicate no city or a number to indicate which city. Is the control on the form blank when this is failing? if so you would be better to set it to a default value of zero

    HTH

    Peter

  6. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid use of Null (Access 2000)

    Thank you very much for your advices. I have used your advice for city = Nz(Forms![FBenchmark]![Office], nnn)
    and now i do not receive the error message any more.However the report is opened blank or with false results.I must click on the button second time and then the proper results are showed. WOuld you help me further solve my stupid case?
    Just to explain myself in more detail in order to get the help.I have several possibilites in opening the report,but in general either use the options,
    which is the Else condition in my code, or do not use any option and in this case i must receive the total figure for all the options.
    In either case, if i chose an option or if i do not choose the option the report is opened without showing an error, but the results are wrong, either
    nill or with false results.I must repeat my action, that is to click the button again and then i receive the proper results.So in fact my code works but is tedious,
    the user must always repeats his actions and the user must know that the first result must be disrgeraded.
    This is my problem in a nutshell.
    =======================
    On Open event of my report:

    Dim city As Long
    city = Nz(Forms![FBenchmark]![Office], nnn)
    Dim strBas As String
    Dim sqlafid As String
    sqlafid = "AND ((Customers.afid)= " & city & ")"
    strBas = " SELECT DISTINCTROW Orders.OrderID, Sum([Order Details].[UnitPrice]*[Quantity]) AS Subtotal, Orders.customerid, Orders.orderdate, Sum([Order

    Details].cartons) AS SumOfcartons, Sum([Order Details].Quantity) AS SumOfQuantity, Sum([Order Details].liters) AS SumOfliters, customers.CompanyName,

    customers.afid " & _
    " FROM customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON customers.Customerid = Orders.customerid

    " & _
    "GROUP BY Orders.OrderID, Orders.customerid, Orders.orderdate, customers.CompanyName, customers.afid, Orders.paymentid " & _
    " HAVING (((Orders.customerid) " & strNotIn & " AND ((Orders.paymentid)=False))"
    If Forms![FBenchmark]![Office] & "" = False Then
    Me.RecordSource = strBas
    If Forms![FBenchmark]![ChkSofia] = True Then
    Me.RecordSource = strBas
    FncParis (Me.Name)
    End If
    Else
    Me.RecordSource = strBas & sqlafid
    End If
    ==================================================

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid use of Null (Access 2000)

    A couple of points that might help you in tracking down the problem.

    It looks as if you don't have "Option Explicit" set in your module. This is worthwhile as it enforces the declaring of all Variables, which can help prevent mistakes being made.

    Where you use a variable to look up a value the use the variable thereafter for the value.

    When Pat said to use <font color=blue>city = Nz(Forms![FBenchmark]![Office], nnn ) </font color=blue> she said to replace the <font color=blue>nnn </font color=blue> with your own default value. I suspect that you want <font color=blue>city = Nz(Forms![FBenchmark]![Office], 0).</font color=blue>

    You should then replace any reference to <font color=blue>Forms![FBenchmark]![Office] </font color=blue> in the code with <font color=blue>city</font color=blue> .

    Your line <font color=blue>Forms![FBenchmark]![Office] & "" </font color=blue> Would appear to be just turning a Null value into an empty string and should be replaced by <font color=blue>city</font color=blue>


    HTH

    Peter

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Invalid use of Null (Access 2000)

    I'm a he NOT a she.
    Patrick <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid use of Null (Access 2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15> >> <img src=/S/hushmouth.gif border=0 alt=hushmouth width=16 height=16>

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Invalid use of Null (Access 2000)

    At least he didn't call you "Patti".... <img src=/S/noevil.gif border=0 alt=noevil width=25 height=17>

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Invalid use of Null (Access 2000)

    Now listen here Marki !!

Posting Permissions

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