Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1549
  • Last Modified:

data type mismatch in Access

I try to pull out data from a table(a linked table) .
I want to get all the date  from the al1 table where the date is after today.
Table al1 contains a field "datefield" which text format likes "23-May-2005"

If I run this query:
SELECT CDate(al1.datefield) AS Expr1
FROM al1;
,it worked fine

But I run this query:
SELECT CDate(al1.datefield) AS Expr1
FROM al1
WHERE (((CDate(al1.datefield))>Date()));
, it did day "Data type mismatch in criterial expression"

How could I fix it?

Thanks!
0
johnnywong71
Asked:
johnnywong71
  • 3
  • 2
  • 2
  • +2
7 Solutions
 
SidFishesCommented:
should work

WHERE CDate(al1.datefield)>#Date()#
0
 
aikimarkCommented:
Does your table allow Null values in the [datefield] column?

Run the following:
SELECT al1.datefield AS Expr1
FROM al1
WHERE (IsDate(al1.datefield)=False) Or (al1.datefield Is Null);
0
 
jkorzCommented:
try this:

SELECT CDate([al1].[datefield]) AS Expr1
FROM al1
WHERE (((CDate([al1].[datefield]))>Date()));

check the values in the table, if you have any invalid values, cdate won't convert it and that would explain the DT mismatch

you should have the datefield field as a date/time type instead of a string though, you can then change the format to 'medium time' and it will look exactly the same as it does now but you won't have to use cdate
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
shanesuebsahakarnCommented:
SELECT CDate(al1.datefield) AS Expr1
FROM al1
WHERE CDate(Nz(al1.datefield,"0"))>Date()
0
 
johnnywong71Author Commented:
Actually,
the orginal link file is txt format.  It is a report from the other application.(I cannot change it).
So, the datefield include the null value.  
I created a query to filter the null data in datefield.  

orginal report:
item       datefield
-----      -------------
   re       port

1           12-May-05
2           03-Apr-05
3           14-Apr-05
4           30-Mar-05


table Al1    (SELECT CDate(al1.datefield) AS Expr1 FROM al1;)
item       datefield
-----      -------------
1           12-May-05
2           03-Apr-05
3           14-Apr-05
4           30-Mar-05

If I put ">Date()"  or ">#Date()#", they didn't run.   (Not include ")
Need help!



0
 
shanesuebsahakarnCommented:
What is the SQL that you used?
0
 
johnnywong71Author Commented:
I want the output is

I want to find a query such as "SELECT CDate(al1.datefield) AS Expr1 FROM al1 WHERE CDate(CDate(al1.datefield) > Date()"
and get the result like this.

item       datefield
-----      -------------
1           12-May-05
2           03-Apr-05
3           14-Apr-05

But it always say "data type mismatch in crteria expression"
0
 
shanesuebsahakarnCommented:
You have too many CDates.Try:
SELECT CDate(al1.datefield) AS Expr1 FROM al1 WHERE CDate(Nz(al1.datefield,"0")) > Date()"
0
 
aikimarkCommented:
Simpler (and maybe faster) would be:

SELECT CDate(al1.datefield) AS Expr1
FROM al1
WHERE CDate(al1.datefield) > Date()
And al1.datefield Is Not Null
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now