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]

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 & "#"

*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) & "#"

 

[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?

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

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) & "#"

 

[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)

 

[Rick Brandt]

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.

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©]

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\#")

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

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)

 

[JethroUK©]

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©]

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\#")

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

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

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)

 

[JethroUK©]

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

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

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]

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\#")

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.

 

[whitbacon]

One thing that comes up is that records may happen at seconds and you
are only looking for the same date! you may try and int() the date
field to give you only days that are relevant

 

[JethroUK©]

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\#")

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.

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