Converting Text to Date

[LostCait]

I am sure this is an easy one for some of you, but not being an expert in SQL or in Access2000 so I am stuck!

I have a linked SQL table in Access that has a field containing dates formatted as text. The dates are text in the form 20050817.

In my query I need to change this column of data into a date field in the form mm/dd/yyyy, so that users can input two dates and I can use them to query the column. I tried using
CAST(fieldname as datetime),
but it turned data like 20050517 into 08/15/05 - not the right date at all. I then read some of the answers here, and I tried
CDATE(MID(fieldname,5,2)&"/"&Right(fieldname,2)&"/"&Left(fieldname,4)).
Access came back to tell me that MID was not a valid function.

How can I accomplish what I am trying to do?
Cait

 

[dhookom]

If Mid() doesn't work in Access then check your references

http://allenbrowne.com/ser-38.html.

Your question is confusing since CAST() would be used on SQL Server and MID() is Access/Jet.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LostCait]

Sorry for the long delay - got sidetracked with another fire. We all know how that goes!

The Access table is a linked ODBC table on our SQL server. The query is a pass-through to that table. If I try to import the table to Access, the date field shows as a memo format. However, I do not want to import the table on load each time because several people could be logged into this at any given time of the day. I just want them to be able to access the data on the SQL server.

I tried using CAST(fld29 as datetime) in a query that brings back only certain fields on the table, and it looks like it works (it creates dates) until I go to query the field. If I ask for something done between say 8/1/05 and 8/2/05, it brings me back a list, but if I look at the raw fld29 the dates are actually like 20050729, 20050610 and the cast date is wrong.

I need to be able to query that field as a date - have the users put in a start and end date to a report and get the output. I am at a loss, since I don't know too much about either system and got thrown this as a bandaid device for the users.

Any more help would be truly appreciated. . .
Cait

 

[dhookom]

Pass-throughs use the SQL syntax of the other system, not JET sql. You should look at the linked table in design view to see the actual data type of the fld29. Is it text or decimal or long integer?

Mid() doesn't work in a pass-through. You need to use Substring().

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]