johnnywong71
asked on
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))>D
, it did day "Data type mismatch in criterial expression"
How could I fix it?
Thanks!
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))>D
, it did day "Data type mismatch in criterial expression"
How could I fix it?
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
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"
I want to find a query such as "SELECT CDate(al1.datefield) AS Expr1 FROM al1 WHERE CDate(CDate(al1.datefield)
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!