DATE ERROR IN QUERY "DATA MISMATCH...ETC.

[Silvio]

Hello, I am using an expression to extract the date from a field which has
date and time in it [DAssigned]. To extract only the date I am using the
following expression:

Date: DateValue([DAssigned])

Then to limit the number of record to the one from today's date, I have
entered the following expression as criteria:

=CDate(Date())

However, when i try to run the query I am getting the following error
message: "Data type mismatch in criteria expression."

What I am doing wrong here?

 

[Gary Walter]

this should work

Field: DateOnly: DateValue([Dassigned])
Table:
Sort:
Show: <checked>
Criteria: Date()
Or:

you tried to use "Date" as an alias
for your column

 

[Silvio]

Gary, I am getting the same error message: "Data type mismatch in criteria
expression."

:-(

this should work

Field: DateOnly: DateValue([Dassigned])
Table:
Sort:
Show: <checked>
Criteria: Date()
Or:

you tried to use "Date" as an alias
for your column

Hello, I am using an expression to extract the date from a field which has
date and time in it [DAssigned]. To extract only the date I am using the
following expression:

Date: DateValue([DAssigned])

Then to limit the number of record to the one from today's date, I have
entered the following expression as criteria:

=CDate(Date())

However, when i try to run the query I am getting the following error
message: "Data type mismatch in criteria expression."

What I am doing wrong here?

 

[Gary Walter]

I don't know....

would you mind doing a test in Immediate Window
(ALT-F11 to go to Debug Window)

hit ENTER after typing in each line

Dassigned=Now()
?DateValue(Dassigned)
?DateValue(Dassigned)=Date()

here are my results:

Dassigned=Now()
?DateValue(Dassigned)
6/14/2006
?DateValue(Dassigned)=Date()
True

where is your query Dassigned coming from?

is it a date/time field in an Access table?

or in linked table to some other db other
than Access?

What is the full SQL of your query, your Access
version, table structure, sample data?

Gary, I am getting the same error message: "Data type mismatch in criteria
expression."

:-(

this should work

Field: DateOnly: DateValue([Dassigned])
Table:
Sort:
Show: <checked>
Criteria: Date()
Or:

you tried to use "Date" as an alias
for your column

Hello, I am using an expression to extract the date from a field which
has
date and time in it [DAssigned]. To extract only the date I am using
the
following expression:

Date: DateValue([DAssigned])

Then to limit the number of record to the one from today's date, I have
entered the following expression as criteria:

=CDate(Date())

However, when i try to run the query I am getting the following error
message: "Data type mismatch in criteria expression."

What I am doing wrong here?

 

[Silvio]

Gary I am not sure where to type/test your conde once I press alt+F11.
Anyway, this is the full SQL code for that query: SELECT Docs.Facilitator,
Docs.[No], Docs.StreetName, Docs.DAssigned, DateValue([DAssigned]) AS DateOnly
FROM Docs
WHERE (((Docs.Facilitator) Is Not Null) AND
((DateValue([DAssigned]))=Date()));

The query is taking the date from a linked table (Docs) from annother Access
2003 Database on the server and the front-end is an Access 2003 as well. The
[Dassigned] is a date/time field. Some time when I run the query ti shown the
records, then I get that message and then all the records show question
marks instead of data.

Gary, I am getting the same error message: "Data type mismatch in criteria
expression."

:-(

this should work

Field: DateOnly: DateValue([Dassigned])
Table:
Sort:
Show: <checked>
Criteria: Date()
Or:

you tried to use "Date" as an alias
for your column

Hello, I am using an expression to extract the date from a field which has
date and time in it [DAssigned]. To extract only the date I am using the
following expression:

Date: DateValue([DAssigned])

Then to limit the number of record to the one from today's date, I have
entered the following expression as criteria:

=CDate(Date())

However, when i try to run the query I am getting the following error
message: "Data type mismatch in criteria expression."

What I am doing wrong here?

 

[Gary Walter]

Gary I am not sure where to type/test your conde once I press alt+F11.

at bottom of page is typically the Immediate Window

if its not showing, hit CTRL+G

it was mostly to check that Date()/DateValue
were working on your machine as expected
outside the query.

if so, then I would suspect it must have something to
do with your data.

Have you compacted/repaired backend lately?

Would you mind starting a new database and import
just this table?

Then double-check that this new table's
Dassigned field is type Date/Time.

Then try your query on it?

or this query...

SELECT
Docs.Facilitator,
Docs.[No],
Docs.StreetName,
Docs.DAssigned
FROM Docs
WHERE
(Docs.Facilitator Is Not Null)
AND
(DAssigned >=Date())
AND
(DAssigned < Date() +1);

Anyway, this is the full SQL code for that query: SELECT Docs.Facilitator,
Docs.[No], Docs.StreetName, Docs.DAssigned, DateValue([DAssigned]) AS
DateOnly
FROM Docs
WHERE (((Docs.Facilitator) Is Not Null) AND
((DateValue([DAssigned]))=Date()));

The query is taking the date from a linked table (Docs) from annother
Access
2003 Database on the server and the front-end is an Access 2003 as well.
The
[Dassigned] is a date/time field. Some time when I run the query ti shown
the
records, then I get that message and then all the records show question
marks instead of data.

Gary, I am getting the same error message: "Data type mismatch in
criteria
expression."

:-(

this should work

Field: DateOnly: DateValue([Dassigned])
Table:
Sort:
Show: <checked>
Criteria: Date()
Or:

you tried to use "Date" as an alias
for your column

Hello, I am using an expression to extract the date from a field
which has
date and time in it [DAssigned]. To extract only the date I am using
the
following expression:

Date: DateValue([DAssigned])

Then to limit the number of record to the one from today's date, I
have
entered the following expression as criteria:

=CDate(Date())

However, when i try to run the query I am getting the following error
message: "Data type mismatch in criteria expression."

What I am doing wrong here?

 

[Silvio]

This is my result:

Dassigned=Now()
?DateValue(Dassigned)
6/14/2006
?DateValue(Dassigned)=Date()
True

It appear to work fine as far I can see.

I don't know....

would you mind doing a test in Immediate Window
(ALT-F11 to go to Debug Window)

hit ENTER after typing in each line

Dassigned=Now()
?DateValue(Dassigned)
?DateValue(Dassigned)=Date()

here are my results:

Dassigned=Now()
?DateValue(Dassigned)
6/14/2006
?DateValue(Dassigned)=Date()
True

where is your query Dassigned coming from?

is it a date/time field in an Access table?

or in linked table to some other db other
than Access?

What is the full SQL of your query, your Access
version, table structure, sample data?

Gary, I am getting the same error message: "Data type mismatch in criteria
expression."

:-(

this should work

Field: DateOnly: DateValue([Dassigned])
Table:
Sort:
Show: <checked>
Criteria: Date()
Or:

you tried to use "Date" as an alias
for your column

Hello, I am using an expression to extract the date from a field which
has
date and time in it [DAssigned]. To extract only the date I am using
the
following expression:

Date: DateValue([DAssigned])

Then to limit the number of record to the one from today's date, I have
entered the following expression as criteria:

=CDate(Date())

However, when i try to run the query I am getting the following error
message: "Data type mismatch in criteria expression."

What I am doing wrong here?

 

[Silvio]

Thank you Gary, your code seems to work fine. I was able to test and verify
the data in the Immediate Windows and it got the same result you got, so I am
not sure what the problem really relay. I tried to import the data into a new
table and tested but this time I got a message saying that the expression was
complex to evaluate ...etc. Anyway your method seems to work fine. Thank you
so much.

Silvio

Gary I am not sure where to type/test your conde once I press alt+F11.

at bottom of page is typically the Immediate Window

if its not showing, hit CTRL+G

it was mostly to check that Date()/DateValue
were working on your machine as expected
outside the query.

if so, then I would suspect it must have something to
do with your data.

Have you compacted/repaired backend lately?

Would you mind starting a new database and import
just this table?

Then double-check that this new table's
Dassigned field is type Date/Time.

Then try your query on it?

or this query...

SELECT
Docs.Facilitator,
Docs.[No],
Docs.StreetName,
Docs.DAssigned
FROM Docs
WHERE
(Docs.Facilitator Is Not Null)
AND
(DAssigned >=Date())
AND
(DAssigned < Date() +1);

Anyway, this is the full SQL code for that query: SELECT Docs.Facilitator,
Docs.[No], Docs.StreetName, Docs.DAssigned, DateValue([DAssigned]) AS
DateOnly
FROM Docs
WHERE (((Docs.Facilitator) Is Not Null) AND
((DateValue([DAssigned]))=Date()));

The query is taking the date from a linked table (Docs) from annother
Access
2003 Database on the server and the front-end is an Access 2003 as well.
The
[Dassigned] is a date/time field. Some time when I run the query ti shown
the
records, then I get that message and then all the records show question
marks instead of data.

Gary, I am getting the same error message: "Data type mismatch in
criteria
expression."

:-(

:

this should work

Field: DateOnly: DateValue([Dassigned])
Table:
Sort:
Show: <checked>
Criteria: Date()
Or:

you tried to use "Date" as an alias
for your column

Hello, I am using an expression to extract the date from a field
which has
date and time in it [DAssigned]. To extract only the date I am using
the
following expression:

Date: DateValue([DAssigned])

Then to limit the number of record to the one from today's date, I
have
entered the following expression as criteria:

=CDate(Date())

However, when i try to run the query I am getting the following error
message: "Data type mismatch in criteria expression."

What I am doing wrong here?

 

[Gary Walter]

it sure seems to point to a problem with your data.

do you get any records returned
from this query?

SELECT
Docs.Facilitator,
Docs.[No],
Docs.StreetName,
Docs.DAssigned
FROM Docs
WHERE
(DAssigned IS NULL)
OR
(NOT
IsDate([DAssigned]));

Thank you Gary, your code seems to work fine. I was able to test and
verify
the data in the Immediate Windows and it got the same result you got, so I
am
not sure what the problem really relay. I tried to import the data into a
new
table and tested but this time I got a message saying that the expression
was
complex to evaluate ...etc. Anyway your method seems to work fine. Thank
you
so much.

Silvio

Gary I am not sure where to type/test your conde once I press alt+F11.

at bottom of page is typically the Immediate Window

if its not showing, hit CTRL+G

it was mostly to check that Date()/DateValue
were working on your machine as expected
outside the query.

if so, then I would suspect it must have something to
do with your data.

Have you compacted/repaired backend lately?

Would you mind starting a new database and import
just this table?

Then double-check that this new table's
Dassigned field is type Date/Time.

Then try your query on it?

or this query...

SELECT
Docs.Facilitator,
Docs.[No],
Docs.StreetName,
Docs.DAssigned
FROM Docs
WHERE
(Docs.Facilitator Is Not Null)
AND
(DAssigned >=Date())
AND
(DAssigned < Date() +1);

Anyway, this is the full SQL code for that query: SELECT
Docs.Facilitator,
Docs.[No], Docs.StreetName, Docs.DAssigned, DateValue([DAssigned]) AS
DateOnly
FROM Docs
WHERE (((Docs.Facilitator) Is Not Null) AND
((DateValue([DAssigned]))=Date()));

The query is taking the date from a linked table (Docs) from annother
Access
2003 Database on the server and the front-end is an Access 2003 as
well.
The
[Dassigned] is a date/time field. Some time when I run the query ti
shown
the
records, then I get that message and then all the records show
question
marks instead of data.

:

Gary, I am getting the same error message: "Data type mismatch in
criteria
expression."

:-(

:

this should work

Field: DateOnly: DateValue([Dassigned])
Table:
Sort:
Show: <checked>
Criteria: Date()
Or:

you tried to use "Date" as an alias
for your column

Hello, I am using an expression to extract the date from a field
which has
date and time in it [DAssigned]. To extract only the date I am
using
the
following expression:

Date: DateValue([DAssigned])

Then to limit the number of record to the one from today's date, I
have
entered the following expression as criteria:

=CDate(Date())

However, when i try to run the query I am getting the following
error
message: "Data type mismatch in criteria expression."

What I am doing wrong here?