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

G

Guest

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

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
 
G

Guest

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

:-(

Gary Walter said:
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?
Click to expand...
Click to expand...
 
G

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?



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

:-(

Gary Walter said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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.

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

:-(

Gary Walter said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

Silvio said:
Gary I am not sure where to type/test your conde once I press alt+F11.
Click to expand...

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.

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

:-(

Gary Walter said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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.

Gary Walter said:
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?



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

:-(

Gary Walter said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 Walter said:
Silvio said:
Gary I am not sure where to type/test your conde once I press alt+F11.
Click to expand...

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.

Silvio said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

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]));

Silvio said:
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 Walter said:
Silvio said:
Gary I am not sure where to type/test your conde once I press alt+F11.
Click to expand...

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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date criteria problem 16
Converted from LINUX time ok, but having issues with the between date. 4
Data type mismatch in criteria expression 5
Current Date in Query 12
DateValue & Type Mismatch Error 2
data mismatch 2
Null Value results in Data Mismatch 4
Date Expression 2

Top