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

rfedorovAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor 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
 
rfedorovAuthor Commented:
Forgot to say, normaly without criteria , i will get
09/29/2009
12/29/2009
01/29/2010
04/06/2010
0
 
rfedorovAuthor Commented:
thank you
I got : Wrong number of arguments used with function in query expression
(CDate(Format([Query1].[C]),"mm/dd/yyyy"))
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
rfedorovAuthor Commented:
Data type mismatch in criteria selection
0
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
rfedorovAuthor 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
 
rfedorovAuthor 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
 
GRayLConnect With a Mentor Commented:
That should be:

SELECT CDate([C]) FROM Query1 WHERE CDate([C]) = Date();
0
 
rfedorovAuthor 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
 
rfedorovAuthor Commented:
Thank you
0
 
GRayLCommented:
Thanks, glad to help.
0
All Courses

From novice to tech pro — start learning today.