asked on

check fro exist date in a table?

Hi:
I try several ways to check specific date in tblHstPayroll
But I always get the same error message
Invalid use of null?
Please help
See the attach code and mdb please

Private Sub btn1_Click()
      MsgBox DLookup("[PayMonth]", "tblHstPayroll", "[PayMonth] = #01/25/1431#")
      MsgBox DLookup("[PayMonth]", "tblHstPayroll", "[PayMonth] = " & Me.dtPayMonth3)
      MsgBox DLookup("[PayMonth]", "tblHstPayroll", "[PayMonth] = " & CDate(Me.dtPayMonth3))
End Sub

Open in new window

InvalidUseOfNull.mdb

SOLUTION

jradkris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

You have to format the date and exclude Null to reach MsgBox:

MsgBox Nz(DLookup("[PayMonth]", "tblHstPayroll", "[PayMonth] = #" & Format(Me!dtPayMonth3, "yyyy\/mm\/dd") & "#"), "Not found.")

Also, PayMonth must hold datetime value data type for this to work.

/gustav

Mohammad Alsolaiman

ASKER

yes
#25/01/1431#
this is Hijiry date
but you can use any date
you can store one record in Gregorian
and try it please

Mohammad Alsolaiman

ASKER

"MsgBox Nz(DLookup("[PayMonth]", "tblHstPayroll", "[PayMonth] = #" & Format(Me!dtPayMonth3, "yyyy\/mm\/dd") & "#"), "Not found.")"

i try it
but always i get Not Found

jradkris

Try
MsgBox DLookup("[PayMonth]", "tblHstPayroll", "[PayMonth] = #" & DateValue("1/10/2010") & "#")

And also you need to bound the message box with in a try catch state ment to avoid null error messages

Mohammad Alsolaiman

ASKER

OK
I try it in Gregorian (I change the calendar in Options)
It works good
But when I change it in Hijiry
I get this "invalid" error
Is there any other way to check if this date is exist?
please

jradkris

The exact format will e something like:
Private Sub btn1_Click()
On Error GoTo Err_Handling
MsgBox DLookup("[PayMonth]", "tblHstPayroll", "[PayMonth] = #" & DateValue(Me.dtPayMonth3) & "#")

Err_Handling :
MsgBox Err.Description
End Sub

jradkris

OR

Private Sub btn1_Click()
On Error GoTo Err_Handling
Nz(DLookup("[PayMonth]", "tblHstPayroll", "[PayMonth] = #" & DateValue(Me.dtPayMonth3) & "#"), "Not found.")

Err_Handling :
MsgBox Err.Description
End Sub

Private Sub btn1_Click() 
On Error GoTo Err_Handling 

Nz(DLookup("[PayMonth]", "tblHstPayroll", "[PayMonth] =  #" & DateValue(Me.dtPayMonth3) & "#"), "Not found.")
 
Err_Handling : 
   MsgBox Err.Description 
End Sub

Open in new window

jradkris

Sorry, forgot to add the message box command

Private Sub btn1_Click()  
On Error GoTo Err_Handling  
 
MsgBox Nz(DLookup("[PayMonth]", "tblHstPayroll", "[PayMonth] =  #" & DateValue(Me.dtPayMonth3) & "#"), "Not found.") 
  
Err_Handling :  
   MsgBox Err.Description  
End Sub

Open in new window

jradkris

Go through this link for calendar conversion.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mohammad Alsolaiman

ASKER

"Also, no Access VBA functions work with Hijiry date so you must convert such dates before applying them in code."
yes Gustav
i believe at least now that this is the point
thanks a lot

Mohammad Alsolaiman

ASKER

thank to all of u

Gustav Brock

You are welcome!

/gustav