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

mawinghoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.