date criteria - data mismatch (aka: When is a date really a date????)

[Bob]

I can not believe I'm even having to post a question like this. This
is stupid beyond imagination.
running access 2000, and I'm TRYING to create a query using date
criteria.

I have a table T_call_log that has a date/time field (long format). I
want to do a short-date comparison so I have a query:

SELECT Format([call_started],"mm/dd/yyyy") AS call_date
FROM T_call_log
ORDER BY T_call_log.call_started;

This query is saved as "Q_daily_calls"

Now I want to have another query that allows users to enter date
criteria, and show days on which calls were made. So I create that
query & I get data mismatch errors, so I simplify the query to figure
out the problem. Here's a PERFECTLY good query that SHOULD work: ;O)

SELECT DISTINCT Q_daily_calls.call_date, CDate([call_date]) AS
date_called
FROM Q_daily_calls
WHERE (((CDate([call_date]))>#6/18/2007#));

So - the first query creates a date string shortening the date, but
the 2nd query convert's it back into a date. I KNOW THAT #6/18/07# IS
a fricking date. And if I check the VB immediate window, I find that
indeed:

?typename(cdate("6/18/07"))
Date

Yes, cdate() DOES also return date data.

So - could someone kindly explain to me how there's a fricking data
mismatch in comparing a date to a date!?!?!?!?!

TIA - Bob

 

[Guest]

Bob,

How about:

SELECT t_Call_log.*
FROM t_call_log
WHERE DateValue([Call_started]) = #6/18/2007#

HTH
Dale

 

[Bob]

hi Dale;

TX;

yep; I tried that too.... data mismatch.

apparently, in spite of the fact that VB thinks this is a date:
?typename(datevalue("6/18/07"))
Date

a datevalue() result is clearly not a date when compared to
#6/18/07#. :O)

I would love to get my hands around the neck of the programmer
responsible for interpretting queries......

Bob,

How about:

SELECT t_Call_log.*
FROM t_call_log
WHERE DateValue([Call_started]) = #6/18/2007#

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

I can not believe I'm even having to post a question like this. This
is stupid beyond imagination.
running access 2000, and I'm TRYING to create a query using date
criteria.

I have a table T_call_log that has a date/time field (long format). I
want to do a short-date comparison so I have a query:

SELECT Format([call_started],"mm/dd/yyyy") AS call_date
FROM T_call_log
ORDER BY T_call_log.call_started;

This query is saved as "Q_daily_calls"

Now I want to have another query that allows users to enter date
criteria, and show days on which calls were made. So I create that
query & I get data mismatch errors, so I simplify the query to figure
out the problem. Here's a PERFECTLY good query that SHOULD work: ;O)

SELECT DISTINCT Q_daily_calls.call_date, CDate([call_date]) AS
date_called
FROM Q_daily_calls
WHERE (((CDate([call_date]))>#6/18/2007#));

So - the first query creates a date string shortening the date, but
the 2nd query convert's it back into a date. I KNOW THAT #6/18/07# IS
a fricking date. And if I check the VB immediate window, I find that
indeed:

Yes, cdate() DOES also return date data.

So - could someone kindly explain to me how there's a fricking data
mismatch in comparing a date to a date!?!?!?!?!

TIA - Bob

 

[John Spencer]

Format turns NULLS in zero length strings. So if you have any Nulls in your
date field...

For instance
CDate(Format(Null,"MM/DD/YYYY")) Returns a type mismatch error (13)

Try

SELECT IIF(IsDate(Call_started),DateValue(Call_started),Null)
FROM T_Call_Log

Or

SELECT Format([call_started],"mm/dd/yyyy") AS call_date
FROM T_call_log
WHERE Call_started is not null


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Bob]

hi John;

TX for your reply...

In fact, I only posted the simplified query. In truth the query that
feeds the one checking date criteria already excludes records with
null date values.

So, I'm getting this error off of a query then generates only 4
records, each 1 with a valid date in the date field.
I have no idea what so ever, why this doesn't work.

Format turns NULLS in zero length strings. So if you have any Nulls in your
date field...

For instance
CDate(Format(Null,"MM/DD/YYYY")) Returns a type mismatch error (13)

Try

SELECT IIF(IsDate(Call_started),DateValue(Call_started),Null)
FROM T_Call_Log

Or

SELECT Format([call_started],"mm/dd/yyyy") AS call_date
FROM T_call_log
WHERE Call_started is not null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I can not believe I'm even having to post a question like this. This
is stupid beyond imagination.
running access 2000, and I'm TRYING to create a query using date
criteria.

I have a table T_call_log that has a date/time field (long format). I
want to do a short-date comparison so I have a query:

SELECT Format([call_started],"mm/dd/yyyy") AS call_date
FROM T_call_log
ORDER BY T_call_log.call_started;

This query is saved as "Q_daily_calls"

Now I want to have another query that allows users to enter date
criteria, and show days on which calls were made. So I create that
query & I get data mismatch errors, so I simplify the query to figure
out the problem. Here's a PERFECTLY good query that SHOULD work: ;O)

SELECT DISTINCT Q_daily_calls.call_date, CDate([call_date]) AS
date_called
FROM Q_daily_calls
WHERE (((CDate([call_date]))>#6/18/2007#));

So - the first query creates a date string shortening the date, but
the 2nd query convert's it back into a date. I KNOW THAT #6/18/07# IS
a fricking date. And if I check the VB immediate window, I find that
indeed:

Yes, cdate() DOES also return date data.

So - could someone kindly explain to me how there's a fricking data
mismatch in comparing a date to a date!?!?!?!?!

TIA - Bob

 

[Bob]

hi John;

TX for your reply...

In fact, I only posted the simplified query. In truth the query that
feeds the one checking date criteria already excludes records with
null date values.

So, I'm getting this error off of a query then generates only 4
records, each 1 with a valid date in the date field.
I have no idea what so ever, why this doesn't work.

Format turns NULLS in zero length strings. So if you have any Nulls in your
date field...

For instance
CDate(Format(Null,"MM/DD/YYYY")) Returns a type mismatch error (13)

Try

SELECT IIF(IsDate(Call_started),DateValue(Call_started),Null)
FROM T_Call_Log

Or

SELECT Format([call_started],"mm/dd/yyyy") AS call_date
FROM T_call_log
WHERE Call_started is not null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I can not believe I'm even having to post a question like this. This
is stupid beyond imagination.
running access 2000, and I'm TRYING to create a query using date
criteria.

I have a table T_call_log that has a date/time field (long format). I
want to do a short-date comparison so I have a query:

SELECT Format([call_started],"mm/dd/yyyy") AS call_date
FROM T_call_log
ORDER BY T_call_log.call_started;

This query is saved as "Q_daily_calls"

Now I want to have another query that allows users to enter date
criteria, and show days on which calls were made. So I create that
query & I get data mismatch errors, so I simplify the query to figure
out the problem. Here's a PERFECTLY good query that SHOULD work: ;O)

SELECT DISTINCT Q_daily_calls.call_date, CDate([call_date]) AS
date_called
FROM Q_daily_calls
WHERE (((CDate([call_date]))>#6/18/2007#));

So - the first query creates a date string shortening the date, but
the 2nd query convert's it back into a date. I KNOW THAT #6/18/07# IS
a fricking date. And if I check the VB immediate window, I find that
indeed:

Yes, cdate() DOES also return date data.

So - could someone kindly explain to me how there's a fricking data
mismatch in comparing a date to a date!?!?!?!?!

TIA - Bob

 

[John W. Vinson]

I have a table T_call_log that has a date/time field (long format). I
want to do a short-date comparison so I have a query:

Rather than two queries, going to string and back, might it not be simpler to
use a single query, with a calculated field

DateValue([call_date])

You can apply your criteria to this calculated field and it will trim off the
time portion.

Or, better if the table is large and call_date is indexed, you can use a
criterion

= DateValue([Enter date:]) & < DateAdd("d", 1, DateValue([Enter date:])

John W. Vinson [MVP]

 

[Bob]

I have a table T_call_log that has a date/time field (long format). I
want to do a short-date comparison so I have a query:

Rather than two queries, going to string and back, might it not be simpler to
use a single query, with a calculated field

DateValue([call_date])

You can apply your criteria to this calculated field and it will trim off the
time portion.

Or, better if the table is large and call_date is indexed, you can use a
criterion

= DateValue([Enter date:]) & < DateAdd("d", 1, DateValue([Enter date:])

John W. Vinson [MVP]

John;

TX for your reply!

I've tried various permutations including datevalue() to no avail.
I have some additional information:

note:
T_call_log.call_started is defined as a date/time field, and
contains LONG date (date+time) data.

query ZQ_S2:
SELECT DateValue([call_started]) AS call_date
FROM T_call_log
WHERE (((T_call_log.call_started) Is Not Null))
ORDER BY T_call_log.call_started;

NOW:
DCount("[call_date]","ZQ_S2","[call_date] = #6/13/07#" ) = a valid
number - this works no problem.

BUT:
query ZQ_S:
SELECT DateValue([call_started]) AS call_date
FROM T_contact_list LEFT JOIN T_call_log ON T_contact_list.contact_id
= T_call_log.contact_id
WHERE (((T_call_log.call_started) Is Not Null))
ORDER BY T_call_log.call_started;

NOW:
DCount("[call_date]","ZQ_S","[call_date] = #6/13/07#" ) = DATA
MISMATCH ERROR!?!?!?!?!?!?

IT'S THE SAME FRIGGIN QUERY!! IT'S THE SAME FRIGGIN <B>KNOWN</B> DATE
FIELD!!!!
(except for a join)
HOW THE HECK can a <b>KNOWN</b> date field NOT be a date data type
just because there's a friggin join!?!?!?!?!?!!?
(a join, by the way, THAT HAS NOTHING TO DO WITH THE FRIGGIN DATE
FIELD!)

Bob
PS:
apologies if this is a duplicate, my first post didn't appear.
Probably got censored, because I flamed microsoft.
( I didn't use any nasty language, but maybe they don't like the
stupidity of their programmers shown in the light of day

 

[Dale Fye]

Have you checked to confirm that the contact_ID field have the same data
type in the two tables? Are they both Long? or Double, or Text? If text,
are they the same length?

Dale

I have a table T_call_log that has a date/time field (long format). I
want to do a short-date comparison so I have a query:

Rather than two queries, going to string and back, might it not be
simpler to
use a single query, with a calculated field

DateValue([call_date])

You can apply your criteria to this calculated field and it will trim off
the
time portion.

Or, better if the table is large and call_date is indexed, you can use a
criterion

= DateValue([Enter date:]) & < DateAdd("d", 1, DateValue([Enter date:])

John W. Vinson [MVP]

John;

TX for your reply!

I've tried various permutations including datevalue() to no avail.
I have some additional information:

note:
T_call_log.call_started is defined as a date/time field, and
contains LONG date (date+time) data.

query ZQ_S2:
SELECT DateValue([call_started]) AS call_date
FROM T_call_log
WHERE (((T_call_log.call_started) Is Not Null))
ORDER BY T_call_log.call_started;

NOW:
DCount("[call_date]","ZQ_S2","[call_date] = #6/13/07#" ) = a valid
number - this works no problem.

BUT:
query ZQ_S:
SELECT DateValue([call_started]) AS call_date
FROM T_contact_list LEFT JOIN T_call_log ON T_contact_list.contact_id
= T_call_log.contact_id
WHERE (((T_call_log.call_started) Is Not Null))
ORDER BY T_call_log.call_started;

NOW:
DCount("[call_date]","ZQ_S","[call_date] = #6/13/07#" ) = DATA
MISMATCH ERROR!?!?!?!?!?!?

IT'S THE SAME FRIGGIN QUERY!! IT'S THE SAME FRIGGIN <B>KNOWN</B> DATE
FIELD!!!!
(except for a join)
HOW THE HECK can a <b>KNOWN</b> date field NOT be a date data type
just because there's a friggin join!?!?!?!?!?!!?
(a join, by the way, THAT HAS NOTHING TO DO WITH THE FRIGGIN DATE
FIELD!)

Bob
PS:
apologies if this is a duplicate, my first post didn't appear.
Probably got censored, because I flamed microsoft.
( I didn't use any nasty language, but maybe they don't like the
stupidity of their programmers shown in the light of day

 

[Bob]

Hi Dale;

yep; got that covered. contact_id is LONG int in all tables.

Bob

Have you checked to confirm that the contact_ID field have the same data
type in the two tables? Are they both Long? or Double, or Text? If text,
are they the same length?

Dale

I have a table T_call_log that has a date/time field (long format). I
want to do a short-date comparison so I have a query:
Rather than two queries, going to string and back, might it not be
simpler to
use a single query, with a calculated field
DateValue([call_date])
You can apply your criteria to this calculated field and it will trim off
the
time portion.
Or, better if the table is large and call_date is indexed, you can use a
criterion
= DateValue([Enter date:]) & < DateAdd("d", 1, DateValue([Enter date:])
John W. Vinson [MVP]

John;

TX for your reply!

I've tried various permutations including datevalue() to no avail.
I have some additional information:

note:
T_call_log.call_started is defined as a date/time field, and
contains LONG date (date+time) data.

query ZQ_S2:
SELECT DateValue([call_started]) AS call_date
FROM T_call_log
WHERE (((T_call_log.call_started) Is Not Null))
ORDER BY T_call_log.call_started;

NOW:
DCount("[call_date]","ZQ_S2","[call_date] = #6/13/07#" ) = a valid
number - this works no problem.

BUT:
query ZQ_S:
SELECT DateValue([call_started]) AS call_date
FROM T_contact_list LEFT JOIN T_call_log ON T_contact_list.contact_id
= T_call_log.contact_id
WHERE (((T_call_log.call_started) Is Not Null))
ORDER BY T_call_log.call_started;

NOW:
DCount("[call_date]","ZQ_S","[call_date] = #6/13/07#" ) = DATA
MISMATCH ERROR!?!?!?!?!?!?

IT'S THE SAME FRIGGIN QUERY!! IT'S THE SAME FRIGGIN <B>KNOWN</B> DATE
FIELD!!!!
(except for a join)
HOW THE HECK can a <b>KNOWN</b> date field NOT be a date data type
just because there's a friggin join!?!?!?!?!?!!?
(a join, by the way, THAT HAS NOTHING TO DO WITH THE FRIGGIN DATE
FIELD!)

Bob
PS:
apologies if this is a duplicate, my first post didn't appear.
Probably got censored, because I flamed microsoft.
( I didn't use any nasty language, but maybe they don't like the
stupidity of their programmers shown in the light of day

 

[Dale Fye]

OK Bob,

1. What happens when you run query ZQ_S? Does it work properly?
2. Why the left join between the contact list and the call log? The where
clause will negate this anyway, so why not just make it an inner join? What
happens when you do this?
3. Where are you using this query, and why are you so set on using the
DCOUNT to get the number of records that match your date? Why not change the
Query itself so that it only returns a single value:

PARAMETERS [DateSelected] DateTime;
SELECT COUNT(*) as Freq FROM T_Call_Log
WHERE [Call_Started] IS NOT NULL
AND DateValue([Call_Started]) = [DateSelected]

Then you could just pass the date of interest to the query define a
recordset based on the query, and get the value from the FreqCount field.
You could do all of this in a function so that you could call it from
whereever in your code that you need to.

4. Try this. Change query ZQ_S into a maketable query and see what happens
when you run it. What is the data type of the Call_Date field in the new
table? Does your DCOUNT work against the new table?

Just throwing out some ideas.

Dale

I have a table T_call_log that has a date/time field (long format). I
want to do a short-date comparison so I have a query:

Rather than two queries, going to string and back, might it not be
simpler to
use a single query, with a calculated field

DateValue([call_date])

You can apply your criteria to this calculated field and it will trim off
the
time portion.

Or, better if the table is large and call_date is indexed, you can use a
criterion

= DateValue([Enter date:]) & < DateAdd("d", 1, DateValue([Enter date:])

John W. Vinson [MVP]

John;

TX for your reply!

I've tried various permutations including datevalue() to no avail.
I have some additional information:

note:
T_call_log.call_started is defined as a date/time field, and
contains LONG date (date+time) data.

query ZQ_S2:
SELECT DateValue([call_started]) AS call_date
FROM T_call_log
WHERE (((T_call_log.call_started) Is Not Null))
ORDER BY T_call_log.call_started;

NOW:
DCount("[call_date]","ZQ_S2","[call_date] = #6/13/07#" ) = a valid
number - this works no problem.

BUT:
query ZQ_S:
SELECT DateValue([call_started]) AS call_date
FROM T_contact_list LEFT JOIN T_call_log ON T_contact_list.contact_id
= T_call_log.contact_id
WHERE (((T_call_log.call_started) Is Not Null))
ORDER BY T_call_log.call_started;

NOW:
DCount("[call_date]","ZQ_S","[call_date] = #6/13/07#" ) = DATA
MISMATCH ERROR!?!?!?!?!?!?

IT'S THE SAME FRIGGIN QUERY!! IT'S THE SAME FRIGGIN <B>KNOWN</B> DATE
FIELD!!!!
(except for a join)
HOW THE HECK can a <b>KNOWN</b> date field NOT be a date data type
just because there's a friggin join!?!?!?!?!?!!?
(a join, by the way, THAT HAS NOTHING TO DO WITH THE FRIGGIN DATE
FIELD!)

Bob
PS:
apologies if this is a duplicate, my first post didn't appear.
Probably got censored, because I flamed microsoft.
( I didn't use any nasty language, but maybe they don't like the
stupidity of their programmers shown in the light of day

 

[RoyVidar]

I can not believe I'm even having to post a question like this. This
is stupid beyond imagination.
running access 2000, and I'm TRYING to create a query using date
criteria.

I have a table T_call_log that has a date/time field (long format). I
want to do a short-date comparison so I have a query:

SELECT Format([call_started],"mm/dd/yyyy") AS call_date
FROM T_call_log
ORDER BY T_call_log.call_started;

This query is saved as "Q_daily_calls"

Now I want to have another query that allows users to enter date
criteria, and show days on which calls were made. So I create that
query & I get data mismatch errors, so I simplify the query to figure
out the problem. Here's a PERFECTLY good query that SHOULD work: ;O)

SELECT DISTINCT Q_daily_calls.call_date, CDate([call_date]) AS
date_called
FROM Q_daily_calls
WHERE (((CDate([call_date]))>#6/18/2007#));

So - the first query creates a date string shortening the date, but
the 2nd query convert's it back into a date. I KNOW THAT #6/18/07# IS
a fricking date. And if I check the VB immediate window, I find that
indeed:

?typename(cdate("6/18/07"))
Date

Yes, cdate() DOES also return date data.

So - could someone kindly explain to me how there's a fricking data
mismatch in comparing a date to a date!?!?!?!?!

TIA - Bob

Based on similar samples, I can verify the behaviour.

Why and where it occurs, I don't know, but I'll try to guess. I think
it might have something to do with using other Regional Settings than
US AND the casting between valid dates and text.

I try to not format anything in queries, as I think that is more the
work that the "application layer" is supposed to do, which I think
is the forms and reports in Access. Sometimes, there might be a need
to do formatting in queries, for instance to do text file export,
or needing a particular number/date format when displaying it in a
combo or list, but my general rule is to avoid casting a date to
string if there's a possibility of needing it back as a date.

In this case, it seems that even if we can get a correct result in
the datasheet view of a query without any criterion, something
happens when Jet evaluates a where condition between a textual
representation of a date (even when casted through CDate/CVDate)
against a valid date. I don't know why, could it be for instance
the order in which it evaluates and executes the query clauses?

One of the methods of doing a "short-date comparision", could be to
use the Int function to remove the fractions (if Null is possible,
perhaps also involve the NZ function).

SELECT Int([call_started]) AS call_date
FROM T_call_log
ORDER BY T_call_log.call_started;

SELECT DISTINCT Q_daily_calls.call_date, [call_date] AS
date_called
FROM Q_daily_calls
WHERE [call_date]>#6/18/2007#

You can then either apply the formatting to this column in the report
or form you'll be using it, or continue to have the real date field
in the query, and use the calculated column only for your
calculations.

 

[Bob]

Dale -

just throwing out some ideas? Throw nothing, you just won the cupi
doll!

UN-freaking believable!

to your points:
1) yes, ZQ_S works perfectly.
2) Out of personal habit, because of many strange results/situations
over the years, I have used left joins instead of inner joins with
access. Can't tell you how many times an inner join screwed up a
query, when a left join made things work. So NATURALLY, here is a
situation where just the opposite happens!
If I change the join to an inner, everything magically starts working
correctly!!!

I still see no freaking reason what-so-ever that microsoft should
think the nature of a field's data type changes due to a join which
has NOTHING to do with that field at all!@#!

3. I had tried using a single count query, but ran into problems, and
broke it into 2 queries to explore the nature of the problem. This
eventually evolved into using dcount - I don't remember exactly why,
but count wasn't giving me what I wanted. It had something to do with
my "ZQ" (or "top" query), needing to be a DISTINCT query.

The example query you show here IS actually [approximately] where I
wanted to go (except that I would have used dcount, having not taken
the time to figure out why count was problematic).

Basically, when microsoft starts telling me that a known date field is
not a date data type, I start questioning everything. Even the
definition of what the word "is" is ;o).

Dale - TB TX!
Bob

OK Bob,

1. What happens when you run query ZQ_S? Does it work properly?
2. Why the left join between the contact list and the call log? The where
clause will negate this anyway, so why not just make it an inner join? What
happens when you do this?
3. Where are you using this query, and why are you so set on using the
DCOUNT to get the number of records that match your date? Why not change the
Query itself so that it only returns a single value:

PARAMETERS [DateSelected] DateTime;
SELECT COUNT(*) as Freq FROM T_Call_Log
WHERE [Call_Started] IS NOT NULL
AND DateValue([Call_Started]) = [DateSelected]

Then you could just pass the date of interest to the query define a
recordset based on the query, and get the value from the FreqCount field.
You could do all of this in a function so that you could call it from
whereever in your code that you need to.

4. Try this. Change query ZQ_S into a maketable query and see what happens
when you run it. What is the data type of the Call_Date field in the new
table? Does your DCOUNT work against the new table?

Just throwing out some ideas.

Dale

I have a table T_call_log that has a date/time field (long format). I
want to do a short-date comparison so I have a query:
Rather than two queries, going to string and back, might it not be
simpler to
use a single query, with a calculated field
DateValue([call_date])
You can apply your criteria to this calculated field and it will trim off
the
time portion.
Or, better if the table is large and call_date is indexed, you can use a
criterion
= DateValue([Enter date:]) & < DateAdd("d", 1, DateValue([Enter date:])
John W. Vinson [MVP]

John;

TX for your reply!

I've tried various permutations including datevalue() to no avail.
I have some additional information:

note:
T_call_log.call_started is defined as a date/time field, and
contains LONG date (date+time) data.

query ZQ_S2:
SELECT DateValue([call_started]) AS call_date
FROM T_call_log
WHERE (((T_call_log.call_started) Is Not Null))
ORDER BY T_call_log.call_started;

NOW:
DCount("[call_date]","ZQ_S2","[call_date] = #6/13/07#" ) = a valid
number - this works no problem.

BUT:
query ZQ_S:
SELECT DateValue([call_started]) AS call_date
FROM T_contact_list LEFT JOIN T_call_log ON T_contact_list.contact_id
= T_call_log.contact_id
WHERE (((T_call_log.call_started) Is Not Null))
ORDER BY T_call_log.call_started;

NOW:
DCount("[call_date]","ZQ_S","[call_date] = #6/13/07#" ) = DATA
MISMATCH ERROR!?!?!?!?!?!?

IT'S THE SAME FRIGGIN QUERY!! IT'S THE SAME FRIGGIN <B>KNOWN</B> DATE
FIELD!!!!
(except for a join)
HOW THE HECK can a <b>KNOWN</b> date field NOT be a date data type
just because there's a friggin join!?!?!?!?!?!!?
(a join, by the way, THAT HAS NOTHING TO DO WITH THE FRIGGIN DATE
FIELD!)

Bob
PS:
apologies if this is a duplicate, my first post didn't appear.
Probably got censored, because I flamed microsoft.
( I didn't use any nasty language, but maybe they don't like the
stupidity of their programmers shown in the light of day

 

[Dale Fye]

Glad to help

Dale -

just throwing out some ideas? Throw nothing, you just won the cupi
doll!

UN-freaking believable!

to your points:
1) yes, ZQ_S works perfectly.
2) Out of personal habit, because of many strange results/situations
over the years, I have used left joins instead of inner joins with
access. Can't tell you how many times an inner join screwed up a
query, when a left join made things work. So NATURALLY, here is a
situation where just the opposite happens!
If I change the join to an inner, everything magically starts working
correctly!!!

I still see no freaking reason what-so-ever that microsoft should
think the nature of a field's data type changes due to a join which
has NOTHING to do with that field at all!@#!

3. I had tried using a single count query, but ran into problems, and
broke it into 2 queries to explore the nature of the problem. This
eventually evolved into using dcount - I don't remember exactly why,
but count wasn't giving me what I wanted. It had something to do with
my "ZQ" (or "top" query), needing to be a DISTINCT query.

The example query you show here IS actually [approximately] where I
wanted to go (except that I would have used dcount, having not taken
the time to figure out why count was problematic).

Basically, when microsoft starts telling me that a known date field is
not a date data type, I start questioning everything. Even the
definition of what the word "is" is ;o).

Dale - TB TX!
Bob

OK Bob,

1. What happens when you run query ZQ_S? Does it work properly?
2. Why the left join between the contact list and the call log? The
where
clause will negate this anyway, so why not just make it an inner join?
What
happens when you do this?
3. Where are you using this query, and why are you so set on using the
DCOUNT to get the number of records that match your date? Why not change
the
Query itself so that it only returns a single value:

PARAMETERS [DateSelected] DateTime;
SELECT COUNT(*) as Freq FROM T_Call_Log
WHERE [Call_Started] IS NOT NULL
AND DateValue([Call_Started]) = [DateSelected]

Then you could just pass the date of interest to the query define a
recordset based on the query, and get the value from the FreqCount field.
You could do all of this in a function so that you could call it from
whereever in your code that you need to.

4. Try this. Change query ZQ_S into a maketable query and see what
happens
when you run it. What is the data type of the Call_Date field in the new
table? Does your DCOUNT work against the new table?

Just throwing out some ideas.

Dale

On Jun 21, 5:32 pm, John W. Vinson
I have a table T_call_log that has a date/time field (long format). I
want to do a short-date comparison so I have a query:

Rather than two queries, going to string and back, might it not be
simpler to
use a single query, with a calculated field

DateValue([call_date])

You can apply your criteria to this calculated field and it will trim
off
the
time portion.

Or, better if the table is large and call_date is indexed, you can use
a
criterion

= DateValue([Enter date:]) & < DateAdd("d", 1, DateValue([Enter
date:])

John W. Vinson [MVP]

TX for your reply!

I've tried various permutations including datevalue() to no avail.
I have some additional information:

note:
T_call_log.call_started is defined as a date/time field, and
contains LONG date (date+time) data.

query ZQ_S2:
SELECT DateValue([call_started]) AS call_date
FROM T_call_log
WHERE (((T_call_log.call_started) Is Not Null))
ORDER BY T_call_log.call_started;

NOW:
DCount("[call_date]","ZQ_S2","[call_date] = #6/13/07#" ) = a valid
number - this works no problem.

BUT:
query ZQ_S:
SELECT DateValue([call_started]) AS call_date
FROM T_contact_list LEFT JOIN T_call_log ON T_contact_list.contact_id
= T_call_log.contact_id
WHERE (((T_call_log.call_started) Is Not Null))
ORDER BY T_call_log.call_started;

NOW:
DCount("[call_date]","ZQ_S","[call_date] = #6/13/07#" ) = DATA
MISMATCH ERROR!?!?!?!?!?!?

IT'S THE SAME FRIGGIN QUERY!! IT'S THE SAME FRIGGIN <B>KNOWN</B> DATE
FIELD!!!!
(except for a join)
HOW THE HECK can a <b>KNOWN</b> date field NOT be a date data type
just because there's a friggin join!?!?!?!?!?!!?
(a join, by the way, THAT HAS NOTHING TO DO WITH THE FRIGGIN DATE
FIELD!)

Bob
PS:
apologies if this is a duplicate, my first post didn't appear.
Probably got censored, because I flamed microsoft.
( I didn't use any nasty language, but maybe they don't like the
stupidity of their programmers shown in the light of day