Hi All
Ok, it's a date issue. I have yet to find a language that doesn't quickly demonstrate date issues.
I am building an Unbound form in Access 2007. It's a General Ledger posting program.
What I am trying to achieve is accepting the input of a document date from a user, then going off to a GLPeriods table to see if it's posting period is open or closed. I honestly thought it would be simple !
Each Company Code is assigned to a posting period variant. Each variant has up to twelve periods with a From and a To date in them, together with a status flag which holds a "Yes" or a "No" value to define whether the period is open ("Yes") or closed.
On my form The DocDate Text box is formatted as a Short Date.
In my GLPeriods table the FromDate and the ToDate are also both defined as short dates, so they ought to match up in an SQL SELECT. But they do not. When I look at the SQL statement in the WATCH window it looks absolutely fine. Exactly as I want it to look.
Here is my code
=============================================
Private Sub DocDate_Change()
'Determine the period and if it is open for posting
'--------------------------------------------------
' 1. Get the Variant used by the Company Code
Dim PeriodVariant As String
PeriodVariant = Me.PeriodVariant.Value
Dim DocDate As Date
DocDate = Me.DocDate.Value
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM GLPeriods " & _
" WHERE PeriodVariant = '" & PeriodVariant & "' AND FromDate <= '" & DocDate & "' AND ToDate >= '" & DocDate & "'"
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then
MsgBox "No valid records found ", , "Error"
Exit Sub
End If
If Me.DocDate.Value >= rs.Fields("FromDate") And Me.DocDate.Value <= rs.Fields("ToDate") Then
Me.Period.Value = rs.Fields("Period")
If rs.Fields("Open") = "Yes" Then
Me.OkToPost.Value = "Yes"
Exit Sub
Else
MsgBox "This Period is block for posting.", , "Error: unable to continue"
Me.OkToPost.Value = "No"
Exit Sub
End If
End If
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Sub
==========================================
I have proved that the issue is the dates by truncating the SQL statement to read ...
strSQL = "SELECT * FROM GLPeriods " & _
" WHERE PeriodVariant = '" & PeriodVariant & "' "
It runs fine (except that I get the wrong result !!). However when I run the full statement (including the date filters) the error I get is ...
Run Time Error '3464' : data Type Mismatch in criteria expression.
... at the statement Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) ....
... yet they are all short dates. The compiler accepts the SQL SELECT line etc, but execution time is a different story.
The Watch Window shows this
Watch : : strSQL : "SELECT * FROM GLPeriods WHERE PeriodVariant = 'STD' AND FromDate <= '01/10/2014' AND ToDate >= '01/10/2014'" : String : Form_GLFI50.DocDate_Change
I am totally bewildered
Any ideas please? I am new to this Access VBA game and my sanity may be at risk here.
John
Ok, it's a date issue. I have yet to find a language that doesn't quickly demonstrate date issues.
I am building an Unbound form in Access 2007. It's a General Ledger posting program.
What I am trying to achieve is accepting the input of a document date from a user, then going off to a GLPeriods table to see if it's posting period is open or closed. I honestly thought it would be simple !
Each Company Code is assigned to a posting period variant. Each variant has up to twelve periods with a From and a To date in them, together with a status flag which holds a "Yes" or a "No" value to define whether the period is open ("Yes") or closed.
On my form The DocDate Text box is formatted as a Short Date.
In my GLPeriods table the FromDate and the ToDate are also both defined as short dates, so they ought to match up in an SQL SELECT. But they do not. When I look at the SQL statement in the WATCH window it looks absolutely fine. Exactly as I want it to look.
Here is my code
=============================================
Private Sub DocDate_Change()
'Determine the period and if it is open for posting
'--------------------------------------------------
' 1. Get the Variant used by the Company Code
Dim PeriodVariant As String
PeriodVariant = Me.PeriodVariant.Value
Dim DocDate As Date
DocDate = Me.DocDate.Value
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM GLPeriods " & _
" WHERE PeriodVariant = '" & PeriodVariant & "' AND FromDate <= '" & DocDate & "' AND ToDate >= '" & DocDate & "'"
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then
MsgBox "No valid records found ", , "Error"
Exit Sub
End If
If Me.DocDate.Value >= rs.Fields("FromDate") And Me.DocDate.Value <= rs.Fields("ToDate") Then
Me.Period.Value = rs.Fields("Period")
If rs.Fields("Open") = "Yes" Then
Me.OkToPost.Value = "Yes"
Exit Sub
Else
MsgBox "This Period is block for posting.", , "Error: unable to continue"
Me.OkToPost.Value = "No"
Exit Sub
End If
End If
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Sub
==========================================
I have proved that the issue is the dates by truncating the SQL statement to read ...
strSQL = "SELECT * FROM GLPeriods " & _
" WHERE PeriodVariant = '" & PeriodVariant & "' "
It runs fine (except that I get the wrong result !!). However when I run the full statement (including the date filters) the error I get is ...
Run Time Error '3464' : data Type Mismatch in criteria expression.
... at the statement Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) ....
... yet they are all short dates. The compiler accepts the SQL SELECT line etc, but execution time is a different story.
The Watch Window shows this
Watch : : strSQL : "SELECT * FROM GLPeriods WHERE PeriodVariant = 'STD' AND FromDate <= '01/10/2014' AND ToDate >= '01/10/2014'" : String : Form_GLFI50.DocDate_Change
I am totally bewildered
Any ideas please? I am new to this Access VBA game and my sanity may be at risk here.
John