VBA query selecting records outside of date range (1 Viewer)

[rkm]

I’ve run into an extremely bizarre problem.

I have written a program using Access 2010 on a Win 8.1 machine. The default regional date setting is yyyy-mm-dd. When I run the VBA queries to find records that have a start date between a user input date range it works fine.

The problem occurs at the client’s location where they have Access 2010 running on Win 7 machines. Their default regional date setting is dd-mm-yyyy (The dates all display properly, btw). When the queries run it seems to also select records with a start date that is in the previous year. For example, if the user inputs the date range as April 1, 2015 and August 1, 2015, it returns all of the appropriate records for that date range, but, it also returns all of the records that are in the previous year (2014).

Has anyone else run into this kind of problem? Any suggestions as to a solution (without having them all upgrade to Win 8.1)?

 

[spikepl]

Somewhere the dates are treated as strings. yyyy-mm-dd will yield the same sorting whether date or string. dd-mm-yyyy or mm-dd-yyyy will yield improper sorting when string.

Note that the function Format yields a string and not a date.

 

[MarkK]

And, "the problem" could be that the user made an error in their observation, right? Like, can you cause this outcome? And if not, how can we conclude that the user's report is accurate?

 

[JHB]

I’ve run into an extremely bizarre problem.
..
Has anyone else run into this kind of problem? Any suggestions as to a solution (without having them all upgrade to Win 8.1)?

Yes many times, because dates in SQL queries must be handled in US format, "mm/dd/yyyy".
You can use the Format function in combination with the CDate function.
Here is a link to description from Allen Browne.
http://allenbrowne.com/ser-36.html

 

[rkm]

Thanks all for your help. The Allen Browne article was a big help.

It now seems to work in Win 7 changing the date format from dd-mm-yyyy to mm-dd-yyyy and using that in the SQL statement, however, it now does not work in Win 8.1. The Format function does not seem to change the yyyy-mm-dd to mm-dd-yyyy. When I take a look at it in the debugger the variable still shows yyyy-mm-dd after "conversion".

This is how I'm converting it:

dteStart = CDate(Format(Me.txtStartDate, "mm\/dd\/yyyy"))

dteStart has been defined as "Date".

Any ideas?

Thanks.

 

[spikepl]

Your statement dteStart .. makes little sense without the context in which it is used.

SHow the SQL for the query, including the criteria.

 

[rkm]

spikepl,

Sorry, I thought my syntax for "Format" and "CDate" might be wrong. Here are the relevant functions.

Private Sub cmdCityRep_Click()
Dim dteStart As Date
Dim dteEnd As Date

If IsNull(Me.txtStartDate) Or IsNull(Me.txtEndDate) Then
    MsgBox "Please enter date range"
Else
    dteStart = CDate(Format(Me.txtStartDate, "mm\/dd\/yyyy"))
    dteEnd = CDate(Format(Me.txtEndDate, "mm\/dd\/yyyy"))
    
    Call LineItem01(dteStart, dteEnd)

End If

End Sub
-----------------------------------------------------------------------
Private Function LineItem01(dteStart As Date, dteEnd As Date)
Dim rstLI01 As DAO.Recordset
Dim iLIAmount As Long
Dim stLineItem As String
Dim iClientID As Integer

Set rstLI01 = CurrentDb.OpenRecordset("SELECT DISTINCT tblHousingPhase.ClientID FROM tblHousingPhase WHERE (AgreedICM = False OR AgreedFollowUp = False) AND HouseEngageStartDate BETWEEN #" & dteStart & "# AND #" & dteEnd & "#")

iLIAmount = rstLI01.RecordCount
stLineItem = "1"

With rstLI01
    Do Until .EOF
        iClientID = !ClientID
        Call UpdateCityRepDBugTable(iClientID, stLineItem)
        .MoveNext
    Loop
End With

rstLI01.Close

Call UpdateCityRepTable(iLIAmount, stLineItem)

End Function

Thanks for your help.

 

[gemma-the-husky]

this

dteStart = CDate(Format(Me.txtStartDate, "mm\/dd\/yyyy"))

if txtstartdate is a string, then I can't see the assignment working.
if it's a date, then it's not necessary.

what does the date look like in txtstartdate

if it is 2015-09-03 (say) for today (ie a 10 character string - and ALWAYS a 10 character string) then one way is

dtestart = dateserial(left(txtdate,4) , mid(txtdate,6,2),right(txtdate,2))

 

[rkm]

this

if txtstartdate is a string, then I can't see the assignment working.
if it's a date, then it's not necessary.

what does the date look like in txtstartdate

if it is 2015-09-03 (say) for today (ie a 10 character string - and ALWAYS a 10 character string) then one way is

dtestart = dateserial(left(txtdate,4) , mid(txtdate,6,2),right(txtdate,2))

txtStartDate is a string. In Win 7 it shows as dd-mm-yyyy. In Win 8 it shows as yyyy-mm-dd. So I don't think dateserial will work for the Win 7 version.

 

[JHB]

Use the format in the SQL-string instead.

Set rstLI01 = CurrentDb.OpenRecordset("SELECT DISTINCT tblHousingPhase.ClientID FROM tblHousingPhase WHERE (AgreedICM = False OR AgreedFollowUp = False) AND HouseEngageStartDate BETWEEN #" & Format(dteStart, "mm/dd/yyyy") & "# AND #" & Format(dteEnd, "mm/dd/yyyy") & "#")

 

[gemma-the-husky]

txtStartDate is a string. In Win 7 it shows as dd-mm-yyyy. In Win 8 it shows as yyyy-mm-dd. So I don't think dateserial will work for the Win 7 version.

I don't understand how a string can be different irrespective of the OS.

 

[rkm]

JHB,

Thanks so much for your reply. The piece of code worked in both Win 8 and Win 7. I guess because Format() converts it to a string, as spikepl explained, it needed to happen within the SQL statement.

Thanks again.

 

[rkm]

Gemma-the-husky,

Thanks for your reply.

That's an interesting point. I guess I think of it as a string because there is a date mask on the input field and I think you can only mask a string. However, it does act like a date, so I'm not sure......

 

[JHB]

JHB,

Thanks so much for your reply.
...

Thanks again.

You're welcome, good luck.