What does 01/10/2006 mean to you: 1 Oct, 2006 or 10 Jan, 2006? To Access, it
ALWAYS means 10 Jan, 2006, regardless of what your Short Date format has
been set to through Regional Settings. 11/10/2006 would also be interpretted
incorrectly, though, although 13/10/2006 and on would be fine.
If that's the issue, check what Allen Browne has at
http://www.allenbrowne.com/ser-36.html and/or what I had in my September,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access". (You
can download the column and sample database from
http://www.accessmvp.com/djsteele/smartaccess.html)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Zedbiker said:
OK thanks I didn't know that but unfortunately it still hasn't solved the
problem.
Thanks you all for your patience.
Ian
Douglas J. Steele said:
You can only use Like with text fields.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
SELECT DISTINCT Logtable.DateReported
FROM Logtable
WHERE (((Logtable.DateReported) Is Not Null And (Logtable.DateReported)
Like
"*" & [Enter Search Details or "*":] & "*"))
ORDER BY Logtable.DateReported DESC;
Column Count = 1
Column Width = 2.554cm
I am sorry if I misunderstood. Should I have used "#" here instead of
"*"
Regards
Ian
:
I don't know what you are using to "fill" the CmbDate combobox.
Please
post
the SQL of the RowSource, indicate column widths and "bound column".
Regards
Jeff Boyce
Microsoft Office/Access MVP
See the two lines of code showing how I delimit the date.
This has been taken from suggested code from another query on a
forum.
rs.FindFirst "[DateReported] = #" & Me.CmbDate & "#"
stLinkCriteria = "[DateReported]= #" & Me.CmbDate & "#"
Thanks again.
Ian
:
Delimiters surround a value. For example, you use quotes to
surround
text
.... "This is text".
The delimiter Access recognizes as surrounding a date value is the
"#"
...
#1/1/2007# will be recognized as a date.
Regards
Jeff Boyce
Microsoft Office/Access MVP
No I am using "0". Should I be using ##/##/####?
Thanks for your patience.
Ian
:
Are you using "#" as a delimiter?
Regards
Jeff Boyce
Microsoft Office/Access MVP
Thanks for your reply.
The data type is Date/Time, formatted as short date, with the
input
mask "
00/00/0000;0;0_ ". Not quite sure I understand the last part
of
your
reply.
Thanks again.
Ian
:
It all starts with the data...
You are calling "01/10/2006" a "date". What is the
underlying
data
type
of
the field in which this "value" is stored?
How you select/search will depend on what is stored. It may
be
that
you
actually have a Date/Time value, but you are trying to find
the
"formatted"
version. Or you may be using a selection criterion but not
treating
it
as a
date (i.e., using the "#" delimiters).
Regards
Jeff Boyce
Microsoft Office/Access MVP
message
I am trying to filter records by searching for a date.
However
when
the
date
starts with a zero eg. 01/10/2006 for some reason it can't
find
the
date.
It
will find any other date with no problem. eg. 11/10/2006
Below is the code I am using.
Many thanks in advance for any help .
Private Sub CmbDate_AfterUpdate()
Dim rs As Object
Dim stDocName As String
Dim stLinkCriteria
stDocName = "Fault Log"
DoCmd.ShowAllRecords
Set rs = Me.Recordset.Clone
rs.FindFirst "[DateReported] = #" & Me.CmbDate & "#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
stLinkCriteria = "[DateReported]= #" & Me.CmbDate & "#"
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
***************************
