how do I convert numbers in a text field to dates in Access 2000

[chazer1]

I am working with a SQL club membership database that was setup by someone else.
When imported to my PC the date fields are set as text and displays numbers(yyyymmdd) instead of date fields. Membership is annual and determined by month joined and I need to be able to contact members due for renewal at the end of each month.

How do I convert them into workable Access 2000 dates?

I used to run a query in Access 2000 to search for all Renewal_Dates(input by myself) that were equal to the current month using:
((DateDiff("m",[Renewal_Date],Date()))=0))

 

[Pat Hartman]

If the data is imported into Access, the best thing is to convert the string to a date. Add a new column to the table for the date datatype and use an update query to convert the string to a proper date.

CDate(Right(strDate,2) & "/" & Mid(strDate,5,2) & "/" Left(strDate,4))

If you are linking to the table, you'll need to use the above expression in your queries to convert the string to a date each time you need it.

 

[chazer1]

Still having problems with converting numbers to dates

Every time I use this expression I get a message saying there is an error in the syntax.

Where can I read about the Cdate funtions and what the code means? I'm no expert so it needs to be written for a non techie.

 

[Pat Hartman]

When you want to find info on VBA functions, you need to open any code module and ask for help from the VB editor menu.

Did you change the column name "strDate" to your own column name?

 

[chazer1]

Date Conversion continued...

I'm using an update query and I have changed the "strdate" name to be the name of the column that has the dates set as yyyymmdd (in my table dateExpired) like below:

cdate(right(dateExpired,2) & "/" & Mid(dateExpired,5,2) & "/" Left(dateExpired,4))

When I try to save it I get a message saying you may have entered an opperand without an opperator. Sorry if I'm just being dumb!

I would like to be able to either overwrite the original "dateExpired" column or write the new date to a column called Newdate within the same table.

 

[Jon K]

It needs one more &.

CDate(Right([dateExpired],2) & "/" & Mid([dateExpired],5,2) & "/" & Left([dateExpired],4))
.

 

[chazer1]

Many Thanks to you all

Thank you so much..... I've got it working fine now.