?
Solved

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?

Posted on 2011-04-29
6
Medium Priority
?
341 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#];
0
Comment
Question by:zimmer9
  • 3
  • 2
6 Comments
 
LVL 7

Assisted Solution

by:shambalad
shambalad earned 668 total points
ID: 35491433
SELECT Format(Dateserial(mid([a].[ValueDate],1,4),mid([a].[ValueDate],5,2),mid([a].[ValueDate],7,2) ),"mm/dd/yyyy") AS yr
FROM tblBanksConv AS b, IMNYCashBreaks AS a
WHERE left(a.BalancePool,3) & mid(a.BalancePool,5)=b.[GLAcct#];
0
 
LVL 2

Assisted Solution

by:CurtHugo
CurtHugo earned 664 total points
ID: 35491448
Use the Mid, Right and Left functions to format the date, i.e. FormattedDate

SELECT a.ValueDate, Mid([a.ValueDate],5,2) & "/" & Right([a.ValueDate],2) & "/" & Left([a.ValueDate],4) AS FormattedDate
FROM tblBanksConv AS b, IMNYCashBreaks AS a
WHERE left(a.BalancePool,3) & mid(a.BalancePool,5)=b.[GLAcct#];
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 668 total points
ID: 35491736
Or just use Format twice:
SELECT 
  Format(Format(a.[ValueDate],"0000\/00\/00"),"mm\/dd\/yyyy")
FROM 
  tblBanksConv AS b, 
  IMNYCashBreaks AS a
WHERE 
  left(a.BalancePool,3) & mid(a.BalancePool,5)=b.[GLAcct#]; 

Open in new window


/gustav
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:shambalad
ID: 35491976
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
0
 
LVL 7

Expert Comment

by:shambalad
ID: 35492124
Never mind that last one. just another case of RTFM.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 35492132
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question