Date Conversion Problem

[wasim_sono]

I am using a module in my access database. It is using for checking date in table.Actuallay what I need is that Module should check either Date punched on Form is already present in table or not. to do this I used following text.

Public Sub chk_dat()
Dim sql As Variant
Dim a As Integer
Dim b As Integer
a = Val(Forms!IncentiveB!IncB_Sub.Form.FMetersread)
b = Val(Forms!IncentiveB!IncB_Sub.Form.FMin_Meters)
sql = "Select * from inctransactionB where ((MRDate) =" & Forms!IncentiveB!IncB_Sub.Form.FDate & ") and (empno='" & Forms!IncentiveB!IncB_Sub.Form.SubEmp & "')"
Set Database = CurrentDb()
Set Recordset = Database.OpenRecordset(sql)
If Not Recordset.EOF Then
'MsgBox Recordset.MRDate
Forms!IncentiveB!IncB_Sub.Form.FInc = Forms!IncentiveB!IncB_Sub.Form.FMetersread
ElseIf Forms!IncentiveB!IncB_Sub.Form.FDay = "Holiday" Then
MsgBox Recordset
Forms!IncentiveB!IncB_Sub.Form.FInc = Forms!IncentiveB!IncB_Sub.Form.FMetersread
ElseIf b > a Then
MsgBox Recordset
Forms!IncentiveB!IncB_Sub.Form.FInc = 0

ElseIf Forms!IncentiveB!IncB_Sub.Form.FMetersread.Value > Forms!IncentiveB!IncB_Sub.Form.FMin_Meters.Value Then
MsgBox Recordset.MRDate
Forms!IncentiveB!IncB_Sub.Form.FInc = (Forms!IncentiveB!IncB_Sub.Form.FMetersread.Value - Forms!IncentiveB!IncB_Sub.Form.FMin_Meters.Value)
MsgBox (Forms!IncentiveB!IncB_Sub.Form.SubEmp)
MsgBox (Forms!IncentiveB!IncB_Sub.Form.FDate)
End If
End Sub

But the code could not find date in the table. However date is present in table. When I test by running a dummy query I found that Access convert date into "MM/DD/YY" format while On form and Table the date format is stored as "dd/mm/yy" format. Please help me out how to convert date which give me result i.e. Recordset should be not EOF.

Thanx.

 

[JHB]

Yes searching dates us the US format, so convert it to that.

sql = "Select * from inctransactionB where ((MRDate) =#" & Format( Forms!IncentiveB!IncB_Sub.Form.FDate,"mm/dd/yyyy") & "#) and (empno='" & Forms!IncentiveB!IncB_Sub.Form.SubEmp & "')"

 

[wasim_sono]

Dear JHB

Its working. Thanks a lot.

 

[JHB]

You're welcome.

 

[smig]

If you work with dates in SQL you must use the US date format mm/dd/yyyy

Also, dates are not a string and should be put in # #
They are saved in the system as numbers (Starting from 1900, I think).

If you format a date as a string (Format([MyDate], "dd/mm/yyyy"") You might get weired results when trying to compare.
"10/01/2016" is bigger then "01/10/2016"
You could go with "yyyymmdd".