Filter date values

  • Thread starter Thread starter Joe Bohen
  • Start date Start date
J

Joe Bohen

The following code is used to filter records on a date
field. The result is another form being opened, which
shows only records with the same line date. The results
are inconsistent, when the cell is double clicked the
second form opens about 80% of the time with the
appropriate records displayed, on some date values (always
the same ones. i.e. 12/11/03 will always show all records
on that date, 10/11/03 will always show no records on that
date.) Why does the code work on some days and not
others? I have tried declaring the stLinkCriteria as a
variant but this has exactly the same results.

Private Sub LINEDATE_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFilter_paid"
Me.Visible = False
stLinkCriteria = "[LINEDATE]=" & "#" & Me![LINEDATE]
& "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

I would very much appreciate any support on this issue.

Joe
 
Try explicitly formattting the literal date as expected by JET SQL:

stLinkCriteria = "[LINEDATE] = " & Format(Me![LINEDATE], "\#mm\/dd\/yyyy\#")

For an explanation of how to avoid this and similar issues, see:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html
 
Posted at 9am reply by 11am. Many thanks once again.
Joe
-----Original Message-----
Try explicitly formattting the literal date as expected by JET SQL:

stLinkCriteria = "[LINEDATE] = " & Format(Me!
Click to expand...
[LINEDATE], "\#mm\/dd\/yyyy\#")
For an explanation of how to avoid this and similar issues, see:
International Dates in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The following code is used to filter records on a date
field. The result is another form being opened, which
shows only records with the same line date. The results
are inconsistent, when the cell is double clicked the
second form opens about 80% of the time with the
appropriate records displayed, on some date values (always
the same ones. i.e. 12/11/03 will always show all records
on that date, 10/11/03 will always show no records on that
date.) Why does the code work on some days and not
others? I have tried declaring the stLinkCriteria as a
variant but this has exactly the same results.

Private Sub LINEDATE_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFilter_paid"
Me.Visible = False
stLinkCriteria = "[LINEDATE]=" & "#" & Me![LINEDATE]
& "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

I would very much appreciate any support on this issue.

Joe
Click to expand...


.
Click to expand...
 
Back
Top