?
Solved

data type mismatch in Access

Posted on 2005-03-31
9
Medium Priority
?
1,540 Views
Last Modified: 2008-02-01
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
Comment
Question by:johnnywong71
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 36

Assisted Solution

by:SidFishes
SidFishes earned 200 total points
ID: 13676390
should work

WHERE CDate(al1.datefield)>#Date()#
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 800 total points
ID: 13676594
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
 
LVL 8

Assisted Solution

by:jkorz
jkorz earned 400 total points
ID: 13676642
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 600 total points
ID: 13678456
SELECT CDate(al1.datefield) AS Expr1
FROM al1
WHERE CDate(Nz(al1.datefield,"0"))>Date()
0
 
LVL 1

Author Comment

by:johnnywong71
ID: 13682098
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
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 600 total points
ID: 13682116
What is the SQL that you used?
0
 
LVL 1

Author Comment

by:johnnywong71
ID: 13682152
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
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 600 total points
ID: 13682169
You have too many CDates.Try:
SELECT CDate(al1.datefield) AS Expr1 FROM al1 WHERE CDate(Nz(al1.datefield,"0")) > Date()"
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 800 total points
ID: 13682223
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question