Dates swapping

  • Thread starter Thread starter Panos
  • Start date Start date
P

Panos

Hi everyone
I have a form with an option group that filters records for the past 1,3,6
and 12 months.
I'm using the format dd/mm/yyyy for my dates. Access at some point is
swapping the day and month parts to the American format mm/dd/yyyy. But only
if the day could be a month as well ie: if the day is less than or equal to
12. This is causing filters for the first twelve days of a month to be
invalid or for the wrong records to be returned
My Regional Settings in Control Panel are dd/mm/yyyy so no issue there.
Any answers for this cause i'm really stuck...
---Code---

Private Sub optFilterMonths_AfterUpdate()

Dim strwhere As Variant

Select Case Me.optFilterMonths
Case Is = 1
strwhere = DateAdd("m", -1, Date)
DoCmd.ApplyFilter , "[LessonDate] >= #" & DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"
Me.FilterOn = True
Case Is = 2
strwhere = DateAdd("m", -3, Date)
DoCmd.ApplyFilter , "[LessonDate] >= #" & DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"
Me.FilterOn = True
Case Is = 3
strwhere = DateAdd("m", -6, Date)
DoCmd.ApplyFilter , "[LessonDate] >= #" & DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"
Me.FilterOn = True
Case Is = 4
strwhere = DateAdd("yyyy", -1, Date)
DoCmd.ApplyFilter , "[LessonDate] >= #" & DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"
Me.FilterOn = True
Case Is = 5
Me.FilterOn = False
End Select
End Sub

---Code---

ThanKs in Advance
 
ACCESS likes to use US format for dates that may be ambiguous. Easiest
approach is to explicitly "convert" your dates into US format (mm/dd/yyyy).

For example, this line of code

DoCmd.ApplyFilter , "[LessonDate] >= #" & DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"


would be changed to this

DoCmd.ApplyFilter , "[LessonDate] >= #" & Format(DateValue(strwhere),
"mm\/dd\/yyyy") & "# And
[LessonDate] <= #" & Format(DateValue(Date), "mm\/dd\/yyyy") & "#"


or to this

DoCmd.ApplyFilter , "[LessonDate] >= " & Format(DateValue(strwhere),
"\#mm\/dd\/yyyy\#") & " And
[LessonDate] <= " & Format(DateValue(Date), "\#mm\/dd\/yyyy\#")
 
Guys Thank you for your replies
Ken
That really works!
I had tried solving this with the Format function but not converting at the
same time
Simple thinks make the difference i quess..........

Bob
I tried declaring dtwhere as date instead of strwhere as variant
Didn't work i'm afraid
Also between/and didn't cut it either

Thank you again
-------------------------------------------------------------

Ken Snell (MVP) said:
ACCESS likes to use US format for dates that may be ambiguous. Easiest
approach is to explicitly "convert" your dates into US format (mm/dd/yyyy).

For example, this line of code

DoCmd.ApplyFilter , "[LessonDate] >= #" & DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"


would be changed to this

DoCmd.ApplyFilter , "[LessonDate] >= #" & Format(DateValue(strwhere),
"mm\/dd\/yyyy") & "# And
[LessonDate] <= #" & Format(DateValue(Date), "mm\/dd\/yyyy") & "#"


or to this

DoCmd.ApplyFilter , "[LessonDate] >= " & Format(DateValue(strwhere),
"\#mm\/dd\/yyyy\#") & " And
[LessonDate] <= " & Format(DateValue(Date), "\#mm\/dd\/yyyy\#")

--

Ken Snell
<MS ACCESS MVP>




Panos said:
Hi everyone
I have a form with an option group that filters records for the past 1,3,6
and 12 months.
I'm using the format dd/mm/yyyy for my dates. Access at some point is
swapping the day and month parts to the American format mm/dd/yyyy. But
only
if the day could be a month as well ie: if the day is less than or equal
to
12. This is causing filters for the first twelve days of a month to be
invalid or for the wrong records to be returned
My Regional Settings in Control Panel are dd/mm/yyyy so no issue there.
Any answers for this cause i'm really stuck...
---Code---

Private Sub optFilterMonths_AfterUpdate()

Dim strwhere As Variant

Select Case Me.optFilterMonths
Case Is = 1
strwhere = DateAdd("m", -1, Date)
DoCmd.ApplyFilter , "[LessonDate] >= #" & DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"
Me.FilterOn = True
Case Is = 2
strwhere = DateAdd("m", -3, Date)
DoCmd.ApplyFilter , "[LessonDate] >= #" & DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"
Me.FilterOn = True
Case Is = 3
strwhere = DateAdd("m", -6, Date)
DoCmd.ApplyFilter , "[LessonDate] >= #" & DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"
Me.FilterOn = True
Case Is = 4
strwhere = DateAdd("yyyy", -1, Date)
DoCmd.ApplyFilter , "[LessonDate] >= #" & DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"
Me.FilterOn = True
Case Is = 5
Me.FilterOn = False
End Select
End Sub

---Code---

ThanKs in Advance
Click to expand...
Click to expand...
 
Yes i did modify the code to change the variable from string to date
Tried all kinds of things including your advice of course
....didn't work
Anyway it is solved now, with Ken's solution
Also found this
http://allenbrowne.com/ser-36.html

Thanks again for your time

Bob Quintal said:
Guys Thank you for your replies
Ken
That really works!
I had tried solving this with the Format function but not
converting at the same time
Simple thinks make the difference i quess..........

Bob
I tried declaring dtwhere as date instead of strwhere as variant
Didn't work i'm afraid
Also between/and didn't cut it either
Click to expand...

Did you modify the rest of the code to satisfy the change of string
to date?
Thank you again
-------------------------------------------------------------

Ken Snell (MVP) said:
ACCESS likes to use US format for dates that may be ambiguous.
Easiest approach is to explicitly "convert" your dates into US
format (mm/dd/yyyy).

For example, this line of code

DoCmd.ApplyFilter , "[LessonDate] >= #" & DateValue(strwhere)
& "# And
[LessonDate] <= #" & DateValue(Date) & "#"


would be changed to this

DoCmd.ApplyFilter , "[LessonDate] >= #" &
Format(DateValue(strwhere),
"mm\/dd\/yyyy") & "# And
[LessonDate] <= #" & Format(DateValue(Date), "mm\/dd\/yyyy") &
"#"


or to this

DoCmd.ApplyFilter , "[LessonDate] >= " &
Format(DateValue(strwhere),
"\#mm\/dd\/yyyy\#") & " And
[LessonDate] <= " & Format(DateValue(Date), "\#mm\/dd\/yyyy\#")

--

Ken Snell
<MS ACCESS MVP>




Hi everyone
I have a form with an option group that filters records for the
past 1,3,6 and 12 months.
I'm using the format dd/mm/yyyy for my dates. Access at some
point is swapping the day and month parts to the American
format mm/dd/yyyy. But only
if the day could be a month as well ie: if the day is less than
or equal to
12. This is causing filters for the first twelve days of a
month to be invalid or for the wrong records to be returned
My Regional Settings in Control Panel are dd/mm/yyyy so no
issue there. Any answers for this cause i'm really stuck...
---Code---

Private Sub optFilterMonths_AfterUpdate()

Dim strwhere As Variant

Select Case Me.optFilterMonths
Case Is = 1
strwhere = DateAdd("m", -1, Date)
DoCmd.ApplyFilter , "[LessonDate] >= #" &
DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"
Me.FilterOn = True
Case Is = 2
strwhere = DateAdd("m", -3, Date)
DoCmd.ApplyFilter , "[LessonDate] >= #" &
DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"
Me.FilterOn = True
Case Is = 3
strwhere = DateAdd("m", -6, Date)
DoCmd.ApplyFilter , "[LessonDate] >= #" &
DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"
Me.FilterOn = True
Case Is = 4
strwhere = DateAdd("yyyy", -1, Date)
DoCmd.ApplyFilter , "[LessonDate] >= #" &
DateValue(strwhere) & "# And
[LessonDate] <= #" & DateValue(Date) & "#"
Me.FilterOn = True
Case Is = 5
Me.FilterOn = False
End Select
End Sub

---Code---

ThanKs in Advance
Click to expand...
Click to expand...
Click to expand...
 
Back
Top