Linked Table Date is Text (1 Viewer)

kujospam

Registered User.
Local time
Today, 01:00
Joined
Jun 12, 2013
Messages
15
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

Have been here a while
Local time
Today, 06:00
Joined
Jun 17, 2012
Messages
7,732
Show the query (both).
 

kujospam

Registered User.
Local time
Today, 01:00
Joined
Jun 12, 2013
Messages
15
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]));
 

kujospam

Registered User.
Local time
Today, 01:00
Joined
Jun 12, 2013
Messages
15
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

Super Moderator
Local time
Today, 05:00
Joined
Nov 30, 2011
Messages
8,494
What is the compiler error you are getting? If I used the Format in the immediate window I get the desired result..
Code:
? 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..
Code:
? 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

Registered User.
Local time
Today, 01:00
Joined
Jun 12, 2013
Messages
15
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

Registered User.
Local time
Today, 01:00
Joined
Jun 12, 2013
Messages
15
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.
 

Attachments

  • Raised Floor Access.accdb
    496 KB · Views: 120

Brianwarnock

Retired
Local time
Today, 05:00
Joined
Jun 2, 2003
Messages
12,701
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

Registered Lunatic
Local time
Yesterday, 22:00
Joined
Aug 27, 2008
Messages
3,517
Your first query is correct but an Input box returns TEXT so you have to encapsulate that in the CDate as well.
 

kujospam

Registered User.
Local time
Today, 01:00
Joined
Jun 12, 2013
Messages
15
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.
 

Users who are viewing this thread

Top Bottom