We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How do you create a SQL Select statement to read a field with a data type of text and a field value of 20110426 so that the result set is displayed as 04/26/2011?

zimmer9
zimmer9 asked
on
Medium Priority
356 Views
Last Modified: 2012-05-11
I am working with Access 2003 using an MDB type file.

I want to SELECT fields from 2 tables and save these values into another table.

I SELECT from a table titled IMNYCashBreaks and a field titled "ValueDate" with a field type of TEXT and the value is 20110426.

I stored the SELECT results into a table titled tblOpenItems and a field titled "processdate"
with a field type of Text and the value should be stored as 04/26/2011.
I want to retain the field as a TEXT field because I am adding records to an existing table.

Do you know how to revise the following SELECT statement so that it converts a value of
20110426 for the field ValueDate into a converted value of 04/26/2011 as a text field?

SELECT a.[ValueDate]
FROM tblBanksConv AS b, IMNYCashBreaks AS a
WHERE left(a.BalancePool,3) & mid(a.BalancePool,5)=b.[GLAcct#];
Comment
Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Gustav -
For my edification, could you point me towards some documentaion on that  Format(a.[ValueDate],"0000\/00\/00")? I've not seen that before.
Thanks,
Todd
Never mind that last one. just another case of RTFM.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes, Format is a powerful function.

First:
Format(a.[ValueDate],"0000\/00\/00")
returns the string "20110426" as "2011/04/26" which can be converted to a date/time value:

datDate = DateValue("2011/04/26")

However, that isn't necessary because the outer Format - using "mm\/dd\/yyyy" as the format - expects a date/time value and does the conversion.

Finally:
Format( <date value > ,"mm\/dd\/yyyy")
formats the date value to the desired string format: "04/26/2011".

/gustav
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.