=DCount("[ID]","QUALS","[Submitted] >#" & [From] & "# and [Unit1] = yes and [interest] = 'CLAIT1'")

[JethroUK©]

i have a report that produces aroung 80 statistics of which this is just
one:

=DCount("[ID]","QUALS","[Submitted] >#" & [From] & "# and [Unit1] = yes and
[interest] = 'CLAIT1'")



this (and the others) were working perfectly well until this week - it took
me a while to trace the problem, but i'm pretty sure now that the problem is
with UK vs US date format date conflict (as per SQL)



i have tried:

=DCount("[ID]","QUALS","[Submitted] >" & CDec([From]) & " and [Unit1] = yes
and [interest] = 'CLAIT1'")

i figured a while back that converting dates to decimal via CDec resolves
this dates format issue perfectly in VB - however, Access reports dont seem
to support the CDEC function ?



Any suggestions?

 

[JethroUK©]

i have cured it with a function wrapper - but it has raised another
rellevant question:

is Month([from]) affected by the UK/US date conflict - or will the month
function always find the correct month?

 

[Douglas J. Steele]

Under the covers, Access stores dates as 8 byte floating point numbers (the
integer part is the date as the number of days relative to 30 Dec, 1899,
while the decimal part is the time as a fraction of a day). As long as
Access has recognized your date correctly, Month will always be correct.

It's important to realize that regardless of what you've set your Short Date
format to in Regional Settings, Access is always going to assume that dates
in # delimiters are in mm/dd/yyyy format. (Okay, this isn't 100% true: it
will recognize unambiguous date formats such as dd mmm yyyy or yyyy-mm-dd.
The point is, if a date is in dd/mm/yyyy format, it's always going to read
that as mm/dd/yyyy format unless dd is greater than 12.)

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 for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

i have cured it with a function wrapper - but it has raised another
rellevant question:

is Month([from]) affected by the UK/US date conflict - or will the month
function always find the correct month?

i have a report that produces aroung 80 statistics of which this is just
one:

=DCount("[ID]","QUALS","[Submitted] >#" & [From] & "# and [Unit1] = yes and
[interest] = 'CLAIT1'")



this (and the others) were working perfectly well until this week - it took
me a while to trace the problem, but i'm pretty sure now that the problem is
with UK vs US date format date conflict (as per SQL)



i have tried:

=DCount("[ID]","QUALS","[Submitted] >" & CDec([From]) & " and [Unit1] = yes
and [interest] = 'CLAIT1'")

i figured a while back that converting dates to decimal via CDec resolves
this dates format issue perfectly in VB - however, Access reports dont seem
to support the CDEC function ?



Any suggestions?

 

[JethroUK©]

Under the covers, Access stores dates as 8 byte floating point numbers (the
integer part is the date as the number of days relative to 30 Dec, 1899,
while the decimal part is the time as a fraction of a day). As long as
Access has recognized your date correctly, Month will always be correct.

I understand the dates stored as decimal - hence i use CDec wrapper, which
always interprets dates correctly irespective of the format - what's
bothering me is the 'As long as Access has recognized your date correctly'
bit - i assumed (in lots of my other statistics) that such as Month()
function would always read date field content correctly by reading the date
seed rather than any surface table formatting - but you seem to be
suggesting that i cant rely on it

i'm confident that the values in the table are absolutely correct - the
inputting works fine e.g 5/6/06 goes in as 5th June 2006

it's just reading those dates from the table correctly afterwards that's at
issue - e.g. Month (mydate) i 'assumed' would read 6 (June) - i've never
noticed it mis-read unless the date field is passed to sql - in this case
via COUNT function

i think my only guarantee is to read all dates as decimal before passing
them to any other function e.g.

Month(CDec([mydate]))

It's important to realize that regardless of what you've set your Short Date
format to in Regional Settings, Access is always going to assume that dates
in # delimiters are in mm/dd/yyyy format. (Okay, this isn't 100% true: it
will recognize unambiguous date formats such as dd mmm yyyy or yyyy-mm-dd.
The point is, if a date is in dd/mm/yyyy format, it's always going to read
that as mm/dd/yyyy format unless dd is greater than 12.)

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 for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

i have cured it with a function wrapper - but it has raised another
rellevant question:

is Month([from]) affected by the UK/US date conflict - or will the month
function always find the correct month?

i have a report that produces aroung 80 statistics of which this is just
one:

=DCount("[ID]","QUALS","[Submitted] >#" & [From] & "# and [Unit1] = yes and
[interest] = 'CLAIT1'")



this (and the others) were working perfectly well until this week - it took
me a while to trace the problem, but i'm pretty sure now that the

problem
is

with UK vs US date format date conflict (as per SQL)



i have tried:

=DCount("[ID]","QUALS","[Submitted] >" & CDec([From]) & " and [Unit1] = yes
and [interest] = 'CLAIT1'")

i figured a while back that converting dates to decimal via CDec resolves
this dates format issue perfectly in VB - however, Access reports dont seem
to support the CDEC function ?



Any suggestions?

 

[Duane Hookom]

If you have a stored date value whether in a memory variable or a date/time
field, the date functions such as Month(), Year(), etc will always work and
return the appropriate value.

--
Duane Hookom
MS Access MVP

Under the covers, Access stores dates as 8 byte floating point numbers (the
integer part is the date as the number of days relative to 30 Dec, 1899,
while the decimal part is the time as a fraction of a day). As long as
Access has recognized your date correctly, Month will always be correct.

I understand the dates stored as decimal - hence i use CDec wrapper, which
always interprets dates correctly irespective of the format - what's
bothering me is the 'As long as Access has recognized your date correctly'
bit - i assumed (in lots of my other statistics) that such as Month()
function would always read date field content correctly by reading the
date
seed rather than any surface table formatting - but you seem to be
suggesting that i cant rely on it

i'm confident that the values in the table are absolutely correct - the
inputting works fine e.g 5/6/06 goes in as 5th June 2006

it's just reading those dates from the table correctly afterwards that's
at
issue - e.g. Month (mydate) i 'assumed' would read 6 (June) - i've never
noticed it mis-read unless the date field is passed to sql - in this case
via COUNT function

i think my only guarantee is to read all dates as decimal before passing
them to any other function e.g.

Month(CDec([mydate]))

It's important to realize that regardless of what you've set your Short Date
format to in Regional Settings, Access is always going to assume that dates
in # delimiters are in mm/dd/yyyy format. (Okay, this isn't 100% true: it
will recognize unambiguous date formats such as dd mmm yyyy or
yyyy-mm-dd.
The point is, if a date is in dd/mm/yyyy format, it's always going to
read
that as mm/dd/yyyy format unless dd is greater than 12.)

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 for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

i have cured it with a function wrapper - but it has raised another
rellevant question:

is Month([from]) affected by the UK/US date conflict - or will the
month
function always find the correct month?


i have a report that produces aroung 80 statistics of which this is just
one:

=DCount("[ID]","QUALS","[Submitted] >#" & [From] & "# and [Unit1] =
yes
and
[interest] = 'CLAIT1'")



this (and the others) were working perfectly well until this week - it
took
me a while to trace the problem, but i'm pretty sure now that the problem
is
with UK vs US date format date conflict (as per SQL)



i have tried:

=DCount("[ID]","QUALS","[Submitted] >" & CDec([From]) & " and [Unit1]
=
yes
and [interest] = 'CLAIT1'")

i figured a while back that converting dates to decimal via CDec resolves
this dates format issue perfectly in VB - however, Access reports dont
seem
to support the CDEC function ?



Any suggestions?

 

[JethroUK©]

Thanks for that - it took me forever to compile this statistics report & i'd
hate to think it's been generating rubbish for the past 3 months - it's too
comprehensive to check every result and i have relied on the maths to get it
reporting correctly

it was producing 'sensible' results this week so i might be home free

If you have a stored date value whether in a memory variable or a date/time
field, the date functions such as Month(), Year(), etc will always work and
return the appropriate value.

--
Duane Hookom
MS Access MVP

Under the covers, Access stores dates as 8 byte floating point numbers (the
integer part is the date as the number of days relative to 30 Dec, 1899,
while the decimal part is the time as a fraction of a day). As long as
Access has recognized your date correctly, Month will always be

correct.

I understand the dates stored as decimal - hence i use CDec wrapper, which
always interprets dates correctly irespective of the format - what's
bothering me is the 'As long as Access has recognized your date correctly'
bit - i assumed (in lots of my other statistics) that such as Month()
function would always read date field content correctly by reading the
date
seed rather than any surface table formatting - but you seem to be
suggesting that i cant rely on it

i'm confident that the values in the table are absolutely correct - the
inputting works fine e.g 5/6/06 goes in as 5th June 2006

it's just reading those dates from the table correctly afterwards that's
at
issue - e.g. Month (mydate) i 'assumed' would read 6 (June) - i've never
noticed it mis-read unless the date field is passed to sql - in this case
via COUNT function

i think my only guarantee is to read all dates as decimal before passing
them to any other function e.g.

Month(CDec([mydate]))

It's important to realize that regardless of what you've set your Short Date
format to in Regional Settings, Access is always going to assume that dates
in # delimiters are in mm/dd/yyyy format. (Okay, this isn't 100% true: it
will recognize unambiguous date formats such as dd mmm yyyy or
yyyy-mm-dd.
The point is, if a date is in dd/mm/yyyy format, it's always going to
read
that as mm/dd/yyyy format unless dd is greater than 12.)

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 for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


i have cured it with a function wrapper - but it has raised another
rellevant question:

is Month([from]) affected by the UK/US date conflict - or will the
month
function always find the correct month?


i have a report that produces aroung 80 statistics of which this is just
one:

=DCount("[ID]","QUALS","[Submitted] >#" & [From] & "# and [Unit1] =
yes
and
[interest] = 'CLAIT1'")



this (and the others) were working perfectly well until this week - it
took
me a while to trace the problem, but i'm pretty sure now that the problem
is
with UK vs US date format date conflict (as per SQL)



i have tried:

=DCount("[ID]","QUALS","[Submitted] >" & CDec([From]) & " and [Unit1]
=
yes
and [interest] = 'CLAIT1'")

i figured a while back that converting dates to decimal via CDec resolves
this dates format issue perfectly in VB - however, Access reports dont
seem
to support the CDEC function ?



Any suggestions?

 

[Douglas J. Steele]

I understand the dates stored as decimal - hence i use CDec wrapper, which
always interprets dates correctly irespective of the format - what's
bothering me is the 'As long as Access has recognized your date correctly'
bit - i assumed (in lots of my other statistics) that such as Month()
function would always read date field content correctly by reading the
date
seed rather than any surface table formatting - but you seem to be
suggesting that i cant rely on it

Did you read the two references I gave you? I think Allen & I talk fairly
comprehensively about what you need to do.

i'm confident that the values in the table are absolutely correct - the
inputting works fine e.g 5/6/06 goes in as 5th June 2006

it's just reading those dates from the table correctly afterwards that's
at
issue - e.g. Month (mydate) i 'assumed' would read 6 (June) - i've never
noticed it mis-read unless the date field is passed to sql - in this case
via COUNT function

i think my only guarantee is to read all dates as decimal before passing
them to any other function e.g.

Month(CDec([mydate]))

I don't believe CDec will do anything useful in this context. The date is
already a number: using CDec on it won't change it. The only time using CDec
might help is in your WHERE clause: using the CDec function might ensure
that your machine's Short Date format is respected, as you saw when you used

=DCount("[ID]","QUALS","[Submitted] >" & CDec([From]) & " and [Unit1] = yes
and [interest] = 'CLAIT1'")

 

[JethroUK©]

Did you read the two references I gave you? I think Allen & I talk fairly
comprehensively about what you need to do.

ill take a look at those - i have been learning a bit more each time i hit a
date issue

i'm confident that the values in the table are absolutely correct - the
inputting works fine e.g 5/6/06 goes in as 5th June 2006

it's just reading those dates from the table correctly afterwards that's
at
issue - e.g. Month (mydate) i 'assumed' would read 6 (June) - i've never
noticed it mis-read unless the date field is passed to sql - in this case
via COUNT function

i think my only guarantee is to read all dates as decimal before passing
them to any other function e.g.

Month(CDec([mydate]))

I don't believe CDec will do anything useful in this context. The date is
already a number: using CDec on it won't change it. The only time using CDec
might help is in your WHERE clause:

i am showing it out of context here, because it is in a 'where' statement -
here's a specific example/statistic from same report:

=DCount("[ID]","Main Query","Month([Enrolled]) = " & Month([from]) & " and
[provider] = 'L COL'")

i didn't give it much thought until i had a problem - but now it's clear to
see that DCount parameters are passed as sql 'where' statement, which in
turn is where UK dates get interpreted as US

by chance this is a particularly good example because the Month function
appears twice - one being interpreted by Access main & the other being
interpreted by sql - i suspect (and i will start testing), that Access Month
function will interpret as UK, but sql will interpret the Month function
'Month([Enrolled])', as US - unless Duane is correct & Date functions always
return the correct date from a field value

living in UK, i have read a lot of articles/posts about US/UK date issues &
most seem to conclude that any ambiguous dates must be formatted as UK for
the UK, which was misleading - Access main, installed in UK, never
interprets UK dates wrongly (of course this wouldn't apply to a US installed
machine using UK dates, which could be the reason for mis understanding) -
it's only sql that interprets UK dates wrongly - in this case, i have found
that converting the date to decimal (returning the date/time number seed)
works fine without resorting to further date formatting - suprised it's not
a documented method since it works internationally (doesn't rely on any
specific date/time format), & i suspect much faster search as a result

using the CDec function might ensure
that your machine's Short Date format is respected, as you saw when you used

=DCount("[ID]","QUALS","[Submitted] >" & CDec([From]) & " and [Unit1] = yes
and [interest] = 'CLAIT1'")

 

[Douglas J. Steele]

i think my only guarantee is to read all dates as decimal before
passing
them to any other function e.g.

Month(CDec([mydate]))

I don't believe CDec will do anything useful in this context. The date is
already a number: using CDec on it won't change it. The only time using CDec
might help is in your WHERE clause:

i am showing it out of context here, because it is in a 'where'
statement -
here's a specific example/statistic from same report:

=DCount("[ID]","Main Query","Month([Enrolled]) = " & Month([from]) & " and
[provider] = 'L COL'")

i didn't give it much thought until i had a problem - but now it's clear
to
see that DCount parameters are passed as sql 'where' statement, which in
turn is where UK dates get interpreted as US

by chance this is a particularly good example because the Month function
appears twice - one being interpreted by Access main & the other being
interpreted by sql - i suspect (and i will start testing), that Access
Month
function will interpret as UK, but sql will interpret the Month function
'Month([Enrolled])', as US - unless Duane is correct & Date functions
always
return the correct date from a field value

There's no problem using a date in a function. It's only when you use the #
delimiter that you're going to run into problems.

living in UK, i have read a lot of articles/posts about US/UK date issues
&

Obviously not the right ones. Allen's article is pretty much the definitive
article on this topic.

most seem to conclude that any ambiguous dates must be formatted as UK for
the UK, which was misleading - Access main, installed in UK, never
interprets UK dates wrongly (of course this wouldn't apply to a US
installed
machine using UK dates, which could be the reason for mis understanding) -
it's only sql that interprets UK dates wrongly - in this case, i have
found
that converting the date to decimal (returning the date/time number seed)
works fine without resorting to further date formatting - suprised it's
not
a documented method since it works internationally (doesn't rely on any
specific date/time format), & i suspect much faster search as a result

using the CDec function might ensure
that your machine's Short Date format is respected, as you saw when you used

=DCount("[ID]","QUALS","[Submitted] >" & CDec([From]) & " and [Unit1] = yes
and [interest] = 'CLAIT1'")

Sorry if I appear short, but read the two articles I cited then post back if
you still have any questions, as opposed to continuing to repeating the same
confusion.