Convert text to date

after converting the excel file to table it stores one of the field as text
i need to convert to date and select today date from the query
please look at the code.
I am getting the error :Data type mismatch in criteria selection
please help


SELECT 
(Format(CDate([Query1].[C]),"mm/dd/yyyy")) AS C, 
Query1.N
FROM Query1
WHERE ((((Format(CDate([Query1].[C]),"mm/dd/yyyy")))="4/6/2010"));

Open in new window

Roman FAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roman FAuthor Commented:
Forgot to say, normaly without criteria , i will get
09/29/2009
12/29/2009
01/29/2010
04/06/2010
0
Rey Obrero (Capricorn1)Commented:
do the formatting first then convert to date

SELECT
(CDate(Format([Query1].[C]),"mm/dd/yyyy")) AS C,
Query1.N
FROM Query1
WHERE ((((CDate(Format([Query1].[C]),"mm/dd/yyyy")))=#4/6/2010#));
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roman FAuthor Commented:
thank you
I got : Wrong number of arguments used with function in query expression
(CDate(Format([Query1].[C]),"mm/dd/yyyy"))
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Rey Obrero (Capricorn1)Commented:
just copied your post, try this

SELECT
CDate(Format([Query1].[C],"mm/dd/yyyy")) AS C,
Query1.N
FROM Query1
WHERE CDate(Format([Query1].[C],"mm/dd/yyyy"))=#4/6/2010#;
0
Roman FAuthor Commented:
Data type mismatch in criteria selection
0
Patrick MatthewsCommented:
rfedorov,

Please post the SQL for Query1, and also a screenshot of the table design for the source table.  Looks to me like what you think may be text is really a date, or vice versa.

Also note that an expression like:

    WHERE Format(CDate([Query1].[C]),"mm/dd/yyyy") = "4/6/2010"

is doomed to failure, as the Format is specifying two-digit month and day, whereas "4/6/2010" is using one-digit month and day...

Patrick
0
Rey Obrero (Capricorn1)Commented:
you may have Null values or empty cells in your field [C]

SELECT
CDate(Format([Query1].[C],"mm/dd/yyyy")) AS C,
Query1.N
FROM Query1
WHERE CDate(Format([Query1].[C],"mm/dd/yyyy"))=#4/6/2010# And [Query1].[C] <> null
0
Roman FAuthor Commented:
thank you , i do not have any nulls
but still does not work
same error
0
Rey Obrero (Capricorn1)Commented:
rfedorov,
try the query using a Table instead of Query1
or post the SQL of Query1 ,
is [C] a calculated value?



0
Roman FAuthor Commented:
matthewspatrick:
all fields are text
WHERE Format(CDate([Query1].[C]),"mm/dd/yyyy") = "4/6/2010" -- i run for "4/6/2010"  and for "04/06/2010"
Query 1
select * from table1
0
Rey Obrero (Capricorn1)Commented:
do a compact and repair of your db, and run the query again

if the query still fail, there is something wrong with the records in your table..

upload a copy of your db with the table concern..
0
GRayLCommented:
Have you tried:

SELECT CDate([C]) FROM myTable WHERE CDate([C]) = Date();
0
GRayLCommented:
That should be:

SELECT CDate([C]) FROM Query1 WHERE CDate([C]) = Date();
0
Roman FAuthor Commented:
Ok, i am using the table and select from the table
field3 is actual data field from the table

SELECT
Cdate(Field3) AS C
FROM Sheet1
WHERE Cdate(Field3) =  Date();
working and gives me 4/7/2010. Wonder why?

SELECT Sheet1.Field1 AS A, Sheet1.Field2 AS B, CDate(Field3) AS C
FROM Sheet1
WHERE (((CDate([Field3]))=#4/6/2010#));
working too
SELECT Sheet1.Field1 AS A, Sheet1.Field2 AS B, CDate(Field3) AS C
FROM Sheet1
WHERE (((CDate([Field3]))=#04/06/2010#));
working too



SELECT Sheet1.Field1 AS A, Sheet1.Field2 AS B, CDate(Field3) AS C
FROM Sheet1
WHERE (((CDate([Field3]))=Format(Now-1,"mm/dd/yyyy")));

gives nothing
0
Rey Obrero (Capricorn1)Commented:
Format(Now-1,"mm/dd/yyyy")))  will give you a string value


SELECT Sheet1.Field1 AS A, Sheet1.Field2 AS B, CDate(Field3) AS C
FROM Sheet1
WHERE (((CDate([Field3]))=cdate(Format(Now-1,"mm/dd/yyyy"))));
0
Roman FAuthor Commented:
Thank you
0
GRayLCommented:
Thanks, glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.