Linked Table Date is Text

[kujospam]

I'm trying to create a query that will convert the text fields that have dates in them to dates. I cannot change the table this is linked to, our group does not own it. I tried doing a cdate() on it, and it displays the test as a date. But then when I try to run a query based off of this one, to give me inbetween dates it returns all dates as if it is a text. I know my second query works, because I copy the table and changed the text to date fields and it works then. Any advice?

 

[JHB]

Show the query (both).

 

[kujospam]

The below no longer works.
Update Database Query, I realize this is not an update yet.
SELECT Secure_Access1.*, CDate([StartDate]) AS DateConverted
FROM Secure_Access1;

2nd query is
SELECT [Update Database].*, [Update Database].DateConverted
FROM [Update Database]
WHERE ((([Update Database].DateConverted)>=[enter date]));

 

[pr2-eugin]

Try DateSerial mixing with Mid function.. Or Format..

 

[kujospam]

The Date is stored as 2013-02-15 13:28:48
I tried this, but it is giving me compile errors.
SELECT DateSerial(Mid(StartDate,1,4),Mid(StartDate,9,10),Mid(StartDate,6,7)) AS Expr1
FROM Secure_Access1;
The Format I'm getting the same type of errors. I'm not seeing what I'm doing wrong.

 

[pr2-eugin]

What is the compiler error you are getting? If I used the Format in the immediate window I get the desired result..

? Format("2013-02-15 13:28:48", "Short Date")
15/02/2013
? DateValue("2013-02-15 13:28:48")
15/02/2013

Your Mid function is a bit off.. It should have read..

? DateSerial(Mid("2013-02-15 13:28:48",[COLOR=Red][B]1,4[/B][/COLOR]),Mid("2013-02-15 13:28:48",[COLOR=Red][B]6,2[/B][/COLOR]), Mid("2013-02-15 13:28:48",[COLOR=Red][B]9,2[/B][/COLOR]))
15/02/2013

 

[kujospam]

I got the above to work with the Date Serial, the problem is is still treats that field as if it is a text. I need it to be a date/time field. I'm getting datatype mismatch on later quries, even when I set the new field to be a date/time field.

 

[kujospam]

I uploaded a very slimmed down version of the database. I hope you can see what I'm trying to do. The Table cannot change, It normally would be a linked table. I'm trying to get it so that the "date" fields will become date/time fields. :banghead: Then to do a search on that for everything that is above that date. I'm not sure if it would just be easier if I could just create a new table with a button that just changes the date fields into true date/time fields. But that looks just as confusing to me. Thank you for any help and the help I have been given so far.

 

[Brianwarnock]

I do not have the level of Access to open an accdb , however I am struggling to understand your problem as you have a method of converting your text date/time to a real date/time, you can use that method in all your queries, or you can run a make table query containing all of the original fields plus your converted field, and then do all of your work against that table.

Brian

 

[SOS]

Your first query is correct but an Input box returns TEXT so you have to encapsulate that in the CDate as well.

 

[kujospam]

Thank you. I see what you mean. I had to do a CDate([From Date]), or around my parameter box. I didn't know I could do that, although I probably should of.

I wish you could see the data types of fields in queries. Even though I know you cannot "change" them as you would/could in a table.