Billy Ma
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....
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I said "end date IS NULL" in my DlookUp, does it work?
ASKER
even I change it like this,
still doesn't work...
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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())
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
invalid use of null
error.jpg