access "invalid use of null" error

Or_A
Or_A used Ask the Experts™
on
hi!
i'm using access 2007,
i have in my form a "CarPN" field (PN for product number), a "CarPrice" field (of type 'currency'), and a button - "CarPriceRestore".
in the same principle, i have many other products types, prices and corresponded Restore buttons in my form.

the concept behind the restore button is that when it is pressed, the VBA code will get the product's price from the "Products" table and put it in the product's price field. if there's no match, it will put 0 rather than null, so to enable later aggregate functions.

for example, the user choose "CarPN": BMW2009, presses the "CarPriceRestore" button, and "CarPrice" gets 0 (zero), because there's no such PN in the "Products" table.

to do so i wrote the attached code. i get "invalid use of null" when running it while there's no match. if there's a match it works perfect.

what am i doing wrong?
thanks :)
Private Sub CarPriceRestore_Click()
  Me.CarPrice = GetPrice(Me.CarPN)
End Sub
 
Function GetPrice(Pn As String)
    If IsNull(DLookup("Price", "Products", "Pn = '" & Pn & "'")) Then
        GetPrice = 0
    Else: GetPrice = DLookup("Price", "Products", "Pn = '" & Pn & "'")
    End If
End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
what line does the error occur on ?

What is the Data Type of CarPrice ?

mx
Top Expert 2016
Commented:
you can eliminate the function with this

Private Sub CarPriceRestore_Click()
  Me.CarPrice = nz(DLookup("Price", "Products", "Pn = '" & Pn & "'"),0)

End Sub


if you want to use the function use this

Private Sub CarPriceRestore_Click()
  Me.CarPrice = GetPrice(Me.CarPN)
End Sub
 
Function GetPrice(Pn As String)
   
   GetPrice = nz(DLookup("Price", "Products", "Pn = '" & Pn & "'"),0)
 
End Function
Top Expert 2016
Commented:
oops, sorry

you can eliminate the function with this

Private Sub CarPriceRestore_Click()
  Me.CarPrice = nz(DLookup("Price", "Products", "Pn = '" & Me.CarPN & "'"),0)

End Sub
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
databaseMX: i can't (or at least dont know how to find out) say which line cause it, because the highlighted line is the line in the sub that calls the function...
both the "CarPrice" and "Price" are of 'Currency' datatype...

capricorn - i tried it but still no good. i get the same "run time error 94 - invalid use of null".

Database Architect / Application Developer
Top Expert 2007
Commented:
Something else must be going on, especially since the other solution posted did not work.  

Can you:

1) Compact & Repair (*** to shrink the size),

2) Zip up the MDB (*** to further shrink the size)

3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.

4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.

mx

Author

Commented:
ok i found out what was my problem,
i didnt explain that most of the times the "CarType" will be empty, so that the Sub sends to the function null value, and thats the null that caused the problem, rather than the null that the Dlookup creates...
so i changed the calling sub to:

Private Sub CarPriceRestore_Click()
    If IsNull(Me.CarType) Then
        Me.CarPrice = 0
    Else: Me.CarPrice = GetPrice(Me.CarType)
    End If
End Sub

im a beginner so though it works i'd be happy to hear if this is a good solution/good code.
thanks :)

Author

Commented:
improved it. at least i think so:

changed the function so it recieves variant instead of string:
Function GetPrice(Pn As String)

and used:
GetPrice = nz(DLookup("Price", "Products", "Pn = '" & Pn & "'"),0)

as Capricorn suggested.

thanks people
Why do you need code to set the value of a control on your form ...

It seems you can simply set an unbound text box controls' Control Source property to

=Nz(DLookup("Price", "Products", "Pn = '" & [NameOfPnControl] & "'"),0)

Author

Commented:
hi dat,
the reason is that i want the procedure to happen only in certain times (upon pressing button etc...).
I understand ... but in your description you are describing an model that indicates the button will be pushed for each time a change on a record occurrs.

For example, the following perfectly describes a calculated field:

>> when it is pressed, the VBA code will get the product's price from the "Products" table and put it in the product's price field. if there's no match, it will put 0 rather than null<<

and thus the result should NOT be stored in a table, but rather calc the result 'on the fly' when visulizing the data.  To calc the result on the fly you would create an expression column in a Query/SQL statement or a Control Source expression.  The data and result of your calc will then be concurrent, and if changes happen to an operand, the .Requery method will easily update the visulized result.

Also this description of a task of the task indicates that your use will alway have to click a button after a change is made:

>>  the user choose "CarPN": BMW2009, presses the "CarPriceRestore" button, and "CarPrice" gets 0 (zero), because there's no such PN in the "Products" table. <<

The comment is indicative of an event model that Access handles very well, without forcing your user to perform the manual step of clicking a button.  If a user selects an operand to your function via a combo box or text box, you can utilize the controls After Update event to .Requery your records if your expression is an expression column.  The expression will be refreshed with the .Requery method.  If your expression is in a Control Source expression property of a control, Access often recognizes the dependancy and will recalc the value with out any code at all.

You know your scenario much better than us, so I recognize that I may be off base, but with the description of your tasks and issues, it really seems like you can improve your data concurrency and make the application less of a burden to your users by taking advantage of the techniques I've outlined.

Author

Commented:
hi dat,
first of all i really appreciate your help, it's amazing that you invest so much time even in closed question!
as you undertand im preety much new to Access and having this discussions helps a lot!

ok, so,

>>in your description you are describing an model that indicates the button will be pushed for each time a change on a record occurrs<<

i'm not sure why did you get this impression (maybe you are confusing with the next question i posted?). here i specifically wrote that i need a code to run only after the button is being pressed.

>>and thus the result should NOT be stored in a table<<

again i'm not sure how you reached that conclusion - the "CarPrice" field is a real field in my table, and the data should be stored.
there are few triggers that i need to get the proper price in that field - one of them, is the button that i've described. the idea behind it is that the user (sales representative) can decide to apply manually a different price for the item by typing it to the field's control in the form, but then he can also change back his mind, press the button, and by that to restore the original price from "Products" table.

another trigger that i need (the open question that i have is dealing with this issue):
actually, each item in my "Products" table have 3 different prices: USD1, USD2 and USD3.
those are actually 3 different fields in the "Products" table.
the sales person can choose and change his mind again and again about which price method to use.
he do so by changing a field in the main form.
example: he decides to sell "BMW2009" in north america, so he needs to choose "USD1".
so each time he changes the price method, obviously all the prices of the products he picked in the different sub forms should be re-calculated => another trigger to call the sub. (but this is why i'm asking what i'm asking in the open question. i would try to clarify myself there as well).

>>Also this description of a task of the task indicates that your use will alway have to click a button after a change is made>>
>>The comment is indicative of an event model that Access handles very well, without forcing your user to perform the manual step of clicking a button.... [to the end]...<<

thats true, thats a third example of something that should trigger the system to re-calculate the item's price. but that's i allready know how to do, using the "after update" event as you say.

this is my special scenario... i would really be happy to hear more of your thoughts..

thanks :)
Hello Or_A ...

>> first of all i really appreciate your help, it's amazing that you invest so much time even in closed question! as you undertand im preety much new to Access and having this discussions helps a lot! <<

In my opinion, sometimes closed questions yeild great discussions about the how's and why's.  All too often a "code slinger" response (of which I have done as well, so .. no bad vibes towards anyone!) does indeed directly address the posters question, but often a deeper discussion should be occurring in order to create a better design.  Ultimately, when I help on forums like EE or UtterAccess or Access World Forums, my intention is to help folks understand how to make better databases... so "points", or other scores on a site are fun, but IMO (and many others feel the same) are secondary.  So ... if the poster is willing to continue, then I am good to do so also.... there is always a balance, and often there comes a point when the poster should open another question and generally that becomes evident if a "bunny trail" veers too far away from the original topic.

>> Access and having this discussions helps a lot <<

I am glad they do ... sometimes rebuttals and returning questions/suggestions are met with a bit of resistance/defensiveness and that is definately NOT an emotion I ever intend to raise.  

>> again i'm not sure how you reached that conclusion - the "CarPrice" field is a real field in my table, and the data should be stored. <<

Your original description perfectly described a calculated field.  Your follow up description in your latest post explains it MUCH better.... You simply want your procedure (function) to return a "Default Value" of sorts, for your field.  You may be able to even utilize the Default Value property of the control to get the intitial value by setting it to your function call {MyFunction([SomeControl])}, thus not needing an initial button push to get the "default", and your user still has the option to overwrite the result.  A sales situation often incurrs this type of scenario, however, many (including me at times) will argue that the result should be what ever the conditions force the expression to return, and NOT be stored, however, the salesperson would put have an "Adjustment" field to allow for the accommodation of a circumstance.  By using the adjustment methodology, a buisness can then see what kind of currency was lost (ie: coupons or negotiations or ??) and then tag that to "advertising" currency when doing accounting and such.  Many also present the idea that adjustments are transactions in and of themselves so an invoice or something like that would have a line item with a price for a product, then an other line item for an adjustment.

I will give word of caution though, the price should only be considered for storage if upon saving (or a certain commitment point) the record it is a "snapshot" in time (ie: and invoice or something similar).  If the records that the price is being stored in can be viewed by others, and subsequently CHANGEd by others (ie: two people choosing two differrng pricing methods) then there is a problem in the table structure.

Gotta run for now ... I hope this is helpful to you! ... and possibly spawns more thoughts ....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial