By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,131 Members | 1,437 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,131 IT Pros & Developers. It's quick & easy.

Expression to convert linked text field to number and / or date format

P: n/a
I have a MSAccess database that is linked to a SQL server. I cannot
change the SQL server datatype as I am not the only one that uses the
database. I'm trying to convert the text field to a valid date or
number format so that the field will report correctly. Currently the
text field is appears as follows:

Test Date: YYYYMMDD (i.e. 20061206)

I have used a CDate format, but am getting a type mismatch error when I
attempt to run the query.

The following is the CDate expression that I'm using: Full Date:
CDate(Mid([Test Date],5,2) & "/" & Mid([Test Hire Date],7,2) & "/" &
Left([Test Hire Date],4))

I'm trying to resolve the mismatch error as well as the conversion
problem. If anyone knows how this can be done, that would be VERY
helpful.

Thanks... Beckster

Dec 7 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a

Beckster6701 wrote:
I have a MSAccess database that is linked to a SQL server. I cannot
change the SQL server datatype as I am not the only one that uses the
database. I'm trying to convert the text field to a valid date or
number format so that the field will report correctly. Currently the
text field is appears as follows:

Test Date: YYYYMMDD (i.e. 20061206)

I have used a CDate format, but am getting a type mismatch error when I
attempt to run the query.

The following is the CDate expression that I'm using: Full Date:
CDate(Mid([Test Date],5,2) & "/" & Mid([Test Hire Date],7,2) & "/" &
Left([Test Hire Date],4))

I'm trying to resolve the mismatch error as well as the conversion
problem. If anyone knows how this can be done, that would be VERY
helpful.

Thanks... Beckster
Year:Left$(FieldName,4)
Month: Mid$(FieldName,5,2)
Day: Right$(FieldName,2)
then use DateSerial to put the pieces together.

Dec 7 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.