Date Selections in SQL (1 Viewer)

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

well personally - I would have the current period open until it is closed, and once it is closed, it is permanently closed.

a real high end system might let you post to a closed period, but doing so immediately invalidates any data you have already produced.

I wouldn't do it. (thinking again, if I did want to do it, I would give it a different transaction type that makes it easily identifiable - but I wouldn't want to do it)


----
ANYWAY

if periodvariant is numeric then

strSQL = "SELECT * FROM GLPeriods " & _
" WHERE PeriodVariant = " & PeriodVariant

if text then I would format it this way. which is proably the same, but I find it easier to follow

strSQL = "SELECT * FROM GLPeriods " & _
" WHERE PeriodVariant = " & chr(34) & PeriodVariant & chr(34)

if it's a date, it's different again.


-----
note that your tests for fromdate and todate are incorrect

you have to use # delimiters with dates, so the string looks like for example this

postingdate between #01/03/2014# and #31/03/2014#

except that sql will treat these as American dates, so 1/3/2014 becomes Jan 3rd, not March 1st. If you are not in the US you need to coerce the SQL to a UK (appropriate) date format.

assuming you are in the UK, your example of "1/10/2014" should be #1/10/2014#, but will be Jan 10th!

Hi Gemma

Thanks very much for your reply. It is much appreciated.

Unfortunately your option is not available to me. That is because there are some points when the system needs to have two periods open, at period end and also at year end as the accountants go about their closing routines.

I am happy with the format of your SQL statement of course (it's a text field by the way). In fact, as I say that was the SQL that I ran to narrow the issue down to dates. It worked technically but of course came up with 12 records.

I don't want users to post to closed periods, that is my objective. so the Senior users (Accountants mostly) will have access to the functionality that opens and closes periods in order to enforce that.

The reason that periods are defined by date is because some people like 13 equal periods and others like 12 Calendar ones. Others again, in some small businesses only produce accounts quarterly - so only four periods for them.

These are the reasons for the date sensitivity and this date definition difference has me baffled! I imagined that defining them all as short dates would do the trick, but it hasn't

However I am trying out the ## bit right now. If it works (and I bet it does) you will go down in folklore as the person who saved my sanity.

John

try this.

(works for me)
"somedate = #" & format(datefrom, "long date") & "#"

others have a way of setting out this with extra formatting characters to resolve the UK/US date issue

(not quite right)
"somedate = #" & format(datefrom, "dd/mm/yyyy") & "#"

----

actually defining as short date is not a solution precisely because 1/10/14 and 10/1/14 are both "legal" dates

defining as a long date is safe because
1st Oct 2014 is clearly defined

Great stuff Gemma. Really, really helpful. The SUB now runs.

The technical issue is fixed thanks to you. the SUB now runsNow I have to find a way to to the date conversion to (I suppose) yyyymmdd. That sounds like fun !!.

Dates really are a nightmare in most languages aren't they? I though PHP was bad, but this is coming a close second. Looks like more research with Mr Google for me.

John

the last is easy

numericdate = format(somedate,"yyyymmdd")

it's only an issue when you need to use it in SQL, because SQL tries to use American dates if it can. Dates OUGHT to be yyyymmdd, because then they are naturally sorted, aren't they.

Hi again Gemma

Ok, I'm going to fool around with these options. Once I get it to work I will post the solution on this thread because I imagine that there will be others looking for this solution.

You've been great. Thanks ever so much.

ps. I hope that I don't have to get into database date conversions to YYYMMDD like in the old days - but I don't expect so,.

I love learning new languages, but some frustrations usually arise.

John