Link to home
Start Free TrialLog in
Avatar of Billy Ma
Billy MaFlag for Hong Kong

asked on

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

SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Billy Ma

ASKER

has an error....
invalid use of null

error.jpg
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I said "end date IS NULL" in my DlookUp, does it work?
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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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())
WHat is present in the database for the start date?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial