Is it possible to find date/time?
JethroUK©
I been trying to set up a form filter to find where [start date] is the same
as Start_Date (currently being displayed)
Filter = "[start date] = #" & Start_Date & "#"
but it just cant find the match - the date is date/time fields (1/7/05
11:00) - i figure it's the time part causing the problem - even tried:
Filter = "[start date] = #" & DateValue(Start_Date) & "#"
but it still cant match them
Is it possible to find date/time?
Rick Brandt
news:lmBsf.57531$a15....@newsfe5-win.ntli.net...
You have the above backwards. You would need to use DateValue on the left side
of the expression not the right.
Filter = "DateValue([start date]) = #" & Start_Date & "#"
*HOWEVER*; one should always avoid using an expression on the left side as this
negates the possibility of using an index on the field (you do have this field
indexed right?).
While a bit more complicated to write, a more efficient filter would be...
Filter = "[start date] BETWEEN #" & Start_Date & "# AND #" & DateAdd("s", 86399,
Start_Date) & "#"
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
JethroUK©
"Rick Brandt" <rickb...@hotmail.com> wrote in message
news:bxBsf.39124$BZ5....@newssvr13.news.prodigy.com...
> "JethroUK©" <re...@the.board> wrote in message
> news:lmBsf.57531$a15....@newsfe5-win.ntli.net...
> >
> > I been trying to set up a form filter to find where [start date] is the
same
> > as Start_Date (currently being displayed)
> >
> > Filter = "[start date] = #" & Start_Date & "#"
> >
> > but it just cant find the match - the date is date/time fields (1/7/05
> > 11:00) - i figure it's the time part causing the problem - even tried:
> >
> > Filter = "[start date] = #" & DateValue(Start_Date) & "#"
> >
> > but it still cant match them
> >
> > Is it possible to find date/time?
>
> You have the above backwards. You would need to use DateValue on the left
side
> of the expression not the right.
>
> Filter = "DateValue([start date]) = #" & Start_Date & "#"
>
I assumed it would be quicker to format the single form contents to match
all existing records than to format all existing records to match the form
contents
However, I have tried this but without success - so i tried to eliminate the
time issue altogether by using a different field with a simple date
Filter = "[Enquired] = #" & Enquired & "#"
After many trials i have noticed that it only works/finds where the 'day'
part of the date is double digit - e.g 12/12/05 it filters out correctly -
it doesn't work/find any single day digits 09/12/05 - it cant find a single
match - this is consistant on all dates
the filter reports the correct clause e.g. "[Enquired] = #09/12/05#"
i even formatted the date in the table the exact same way it is formatted on
the form - 'dd/mm/yy'
but this double digit thing leads me to suspect some underlying format
issues - i.e it's actually trying to match 9/12/05 with 09/12/05 & cant
Is this a known issue? I understand access tables actually store dates
according to windows regional setting (irrespective of format)
> *HOWEVER*; one should always avoid using an expression on the left side as
this
> negates the possibility of using an index on the field (you do have this
field
> indexed right?).
i haven't indexed this field - each record has about 15 dates - each 'may'
be used to filter, but no more than any other field so there's no speed
priority as such
Douglas J. Steele
>
> I assumed it would be quicker to format the single form contents to match
> all existing records than to format all existing records to match the form
> contents
>
> However, I have tried this but without success - so i tried to eliminate
> the
> time issue altogether by using a different field with a simple date
>
> Filter = "[Enquired] = #" & Enquired & "#"
>
> After many trials i have noticed that it only works/finds where the 'day'
> part of the date is double digit - e.g 12/12/05 it filters out correctly -
> it doesn't work/find any single day digits 09/12/05 - it cant find a
> single
> match - this is consistant on all dates
>
> the filter reports the correct clause e.g. "[Enquired] = #09/12/05#"
> i even formatted the date in the table the exact same way it is formatted
> on
> the form - 'dd/mm/yy'
>
> but this double digit thing leads me to suspect some underlying format
> issues - i.e it's actually trying to match 9/12/05 with 09/12/05 & cant
Access will not work with dd/mm/yyyy in SQL Statements, regardless of what
the short date format has been set to through Regional Settings. Once you
get days of greater than 13, Access will recognize that it's not a valid
date, and correct it, which explains why 12/12/05 works, but 09/12/05
doesn't: Access will always treat that as 12 Sept, 2005.
Change your code to:
Filter = "[Enquired] = " & Format(Enquired, "\#mm\/dd\/yyyy\#")
or
Filter = "[Enquired] = " & Format(Enquired, "\#yyyy\-mm\-dd\#")
You may want to read Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html
or what I have in my September 2003 Access Answers column for Pinnacle
Publication's "Smart Access" newsletter. (The column and accompanying
database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
Rick Brandt
>
> "Rick Brandt" <rickb...@hotmail.com> wrote in message
> news:bxBsf.39124$BZ5....@newssvr13.news.prodigy.com...
>> side of the expression not the right.
>>
>> Filter = "DateValue([start date]) = #" & Start_Date & "#"
>>
>
> I assumed it would be quicker to format the single form contents to match
> all existing records than to format all existing records to match the form
> contents
Yes, but the field in the table is the one that has a time component that
DateValue() would strip off. The value on the form is already a date with no
time so wrapping it in DateValue() accomplishes nothing.
> However, I have tried this but without success - so i tried to eliminate the
> time issue altogether by using a different field with a simple date
>
> Filter = "[Enquired] = #" & Enquired & "#"
>
> After many trials i have noticed that it only works/finds where the 'day'
> part of the date is double digit - e.g 12/12/05 it filters out correctly -
> it doesn't work/find any single day digits 09/12/05 - it cant find a single
> match - this is consistant on all dates
As Douglas pointed out, a Date literal used in an Access query must either use
US format, a format that is non-ambiguous regarding day/month (alpha characters
for month) or ISO format (yyyy-mm-dd). Formatting applied to the field in the
table is irrelevent because that does not affect what is stored at all, only
what you see.
JethroUK©
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:uVJP7HHD...@TK2MSFTNGP09.phx.gbl...
That worked fine - how would i need to format to find date/time e.g 21/09/05
11:00
I would've thought it would match dates or times by using the underlying
date/time number seed (no/any/all formats) - albeit i can't find a function
to return this
JethroUK©
> "JethroUK©" <re...@the.board> wrote in message
> news:CeGsf.13556$5v1....@newsfe2-win.ntli.net...
> >
> > "Rick Brandt" <rickb...@hotmail.com> wrote in message
> > news:bxBsf.39124$BZ5....@newssvr13.news.prodigy.com...
> >> You have the above backwards. You would need to use DateValue on the
left
> >> side of the expression not the right.
> >>
> >> Filter = "DateValue([start date]) = #" & Start_Date & "#"
> >>
> >
> > I assumed it would be quicker to format the single form contents to
match
> > all existing records than to format all existing records to match the
form
> > contents
>
> Yes, but the field in the table is the one that has a time component that
> DateValue() would strip off. The value on the form is already a date with
no
> time so wrapping it in DateValue() accomplishes nothing.
i maybe didn't explain fully but the field is identical format "dd/mm/yy
hh:nn) in both the table and the form - hence i couldn't figure why it cant
find it
>
> > However, I have tried this but without success - so i tried to eliminate
the
> > time issue altogether by using a different field with a simple date
> >
> > Filter = "[Enquired] = #" & Enquired & "#"
> >
> > After many trials i have noticed that it only works/finds where the
'day'
> > part of the date is double digit - e.g 12/12/05 it filters out
correctly -
> > it doesn't work/find any single day digits 09/12/05 - it cant find a
single
> > match - this is consistant on all dates
>
> As Douglas pointed out, a Date literal used in an Access query must either
use
> US format, a format that is non-ambiguous regarding day/month (alpha
characters
> for month) or ISO format (yyyy-mm-dd). Formatting applied to the field in
the
> table is irrelevent because that does not affect what is stored at all,
only
> what you see.
>
i'll have to remember this - i haven't come across this issue before in
normal query designs - just used english date format as criteria but maybe
the query converts it to U.S. date format in the sql - i haven't took much
notice
it would be nice if it compaired the underlying date/time number seed
irrespective of format
JethroUK©
"JethroUK©" <re...@the.board> wrote in message
Success:
It seems you can match any/all date/time formats by using the underlying
number seed, which as returned from CDec() function - e.g:
Filter = "[Enquired] = " & CDec(Enquired)
Filter = "[start date] = " & CDec(Start_Date)
works perfectly for Date or date/time fields
JethroUK©
"JethroUK©" <re...@the.board> wrote in message
i managed to do exactly that - i returned this number by using CDec function
and it seems to work perfectly for both date fields and date/time e.g.:
Filter = "[Enquired] = " & CDec(Enquired)
Filter = "[start date] = " & CDec(Start_Date)
this has been nagging me for over a month now - so i'm dead chuffed - thanx
for the input
Douglas J. Steele
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:uVJP7HHD...@TK2MSFTNGP09.phx.gbl...
>>
>> what
>> the short date format has been set to through Regional Settings. Once you
>> get days of greater than 13, Access will recognize that it's not a valid
>> date, and correct it, which explains why 12/12/05 works, but 09/12/05
>> doesn't: Access will always treat that as 12 Sept, 2005.
>>
>> Change your code to:
>>
>> Filter = "[Enquired] = " & Format(Enquired, "\#mm\/dd\/yyyy\#")
>>
>
> That worked fine - how would i need to format to find date/time e.g
> 21/09/05
> 11:00
I see you've already found a solution, but another approach would have been
to use
Filter = "[Enquired] = " & Format(Enquired, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
or
Filter = "[Enquired] = " & Format(Enquired, "\#yyyy\-mm\-dd hh\:nn\:ss\#")
>
> I would've thought it would match dates or times by using the underlying
> date/time number seed (no/any/all formats) - albeit i can't find a
> function
> to return this
Not quite sure what you mean by this.
whit...@gmail.com
are only looking for the same date! you may try and int() the date
field to give you only days that are relevant
JethroUK©
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
i read the 2 articles you posted by the way
since dates/times are stored (internationally) as a decimal number +
fractions, that access would search/match dates/times by comparing this
underlying number instead of by the more specific U.S. date format - then it
would be able to match it irrespective of any/all date formats
that's what i persued and indeed it does
initially i couldn't find a date function to return this date/time number -
but CDec() does it perfectly e.g:
CDec(Your date and/or time field, whatever international format) =
Date/Time as a decimal number & fraction
- access can compare the resulting date/time number seed with the existing
values in the table with no need for re-formatting at all - it doesn't even
need date delimiters