• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

How to handle null value in MS Access

I keep receiving an error message....I really want to cry...
I do not know how to handle this.

I want to do a very simple thing, just get the value, from a table,
DLOOKUP returns null, then do not set anything to Me.price.Value

but i do not know how to do it....
Private Sub Type_LostFocus()

    Dim fuel_price_value As String
    
    fuel_price_value = Nz(DLookup("[price]", "Fuel_Price", "[type] = '" & Me.type.Value & "' AND [start_date] <= '" & DateValue(Me.date.Value) & "' AND ('" & DateValue(Me.date.Value) & "' <= [end_date] OR [end_date] Is Null)"), "")
        
    If Len(fuel_price_value) > 0 Then
        Me.price.Value = DLookup("[price]", "Fuel_Price", "[type] = '" & Me.type.Value & "' AND [start_date] <= '" & DateValue(Me.date.Value) & "' AND ('" & DateValue(Me.date.Value) & "' <= [end_date] OR [end_date] Is Null)")
    End If

  End Sub

Open in new window

0
mawingho
Asked:
mawingho
  • 5
  • 2
  • 2
  • +2
5 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
Private Sub Type_LostFocus()

    Dim fuel_price_value As Variant
    
    fuel_price_value = DLookup("[price]", "Fuel_Price", "[type] = '" & Me.type.Value & "' AND [start_date] <= '" & DateValue(Me.date.Value) & "' AND ('" & DateValue(Me.date.Value) & "' <= [end_date] OR [end_date] Is Null)")
        
    If not isnull(fuel_price_value)  Then
        Me.price.Value = DLookup("[price]", "Fuel_Price", "[type] = '" & Me.type.Value & "' AND [start_date] <= '" & DateValue(Me.date.Value) & "' AND ('" & DateValue(Me.date.Value) & "' <= [end_date] OR [end_date] Is Null)")
    End If

  End Sub

Open in new window

0
 
mawinghoAuthor Commented:
has an error....
invalid use of null

error.jpg
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
angelIII:

Just a thought since you set fuel_price_value = DLookup("[price]", "Fuel_Price", "[type] = '" & Me.type.Value & "' AND [start_date] <= '" & DateValue(Me.date.Value) & "' AND ('" & DateValue(Me.date.Value) & "' <= [end_date] OR [end_date] Is Null)")

Isn't it easier to use:

Private Sub Type_LostFocus()

    Dim fuel_price_value As Variant
   
    fuel_price_value = DLookup("[price]", "Fuel_Price", "[type] = '" & Me.type.Value & "' AND [start_date] <= '" & DateValue(Me.date.Value) & "' AND ('" & DateValue(Me.date.Value) & "' <= [end_date] OR [end_date] Is Null)")
       
    If not isnull(fuel_price_value)  Then
        Me.price.Value = fuel_price_value
    End If

  End Sub
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
BardobraveCommented:
VB has a deficient null management.

When you need to add a value that could be null to an object you need to control it before doing anything

If Not IsNull(possibleNullValue) Then
  do anything
else
  do nothing or assign empty value
end if

I've found that using strings sometimes you can concatenate your possibly null value to the constant vbNullString, but this not always works.
0
 
mawinghoAuthor Commented:
I said "end date IS NULL" in my DlookUp, does it work?
0
 
mawinghoAuthor Commented:
even I change it like this,
still doesn't work...

If Not IsNull(DLookup("[price]", "Fuel_Price", "[type] = '" & Me.type.Value & "' AND [start_date] <= '" & DateValue(Me.date.Value) & "' AND ('" & DateValue(Me.date.Value) & "' <= [end_date] OR [end_date] is null)")) Then
        Me.price.Value = DLookup("[price]", "Fuel_Price", "[type] = '" & Me.type.Value & "' AND [start_date] <= '" & DateValue(Me.date.Value) & "' AND ('" & DateValue(Me.date.Value) & "' <= [end_date] OR [end_date] Is Null)")
    End If

Open in new window

0
 
BardobraveCommented:
Something inside your expression is still null and it's getting you the error.

Me.type.Value or Me.date.Value, try:

if not isnull(Me.type.Value) then
 sTypeValue = Me.type.Value
else
 sTypeValue = ""
End if

if not isNull(Me.date.Value) Then
  dDateValue = Me.date.Value
else
 dDateValue = ""
End if

And use sTypeValue and dDateValue into your DLookup
0
 
JVWCCommented:
As an aside only:
I see field names like TYPE and DATE in there!
It would be a good idea to research 'Reserved words' and think about changing those names. It will cause you no end of trouble in the future...

Cheers
JC
0
 
mawinghoAuthor Commented:
this line is ok
Me.price.Value = DLookup("[price]", "Fuel_Price", "[type] = '" & Me.type.Value & "' AND [end_date] is null")
but, why this line doesn't work....
Me.price.Value = DLookup("[price]", "Fuel_Price", "[type] = '" & Me.type.Value & "' AND [start_date] <= " & Now())
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
WHat is present in the database for the start date?
0
 
mawinghoAuthor Commented:
The correct solution should be
Private Sub Type_LostFocus()
     
    Me.price.Value = DLookup("[price]", "Fuel_Price", "[type] = '" & Me.type.Value & "' AND [start_date] <= #" & Me.date.Value & "# AND (#" & Me.date.Value & "# <= [end_date] OR [end_date] IS NULL)")
    
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now