Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-16-2010, 10:23 AM   #1
floridamussels
Newly Registered User
 
Join Date: Dec 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
floridamussels is on a distinguished road
Converting dates from 3-column to single column

Is it possible to concatenate three fields, each containing either month, day or year into a single field with MM-DD-YYYY format? If so, how?

floridamussels is offline   Reply With Quote
Old 12-16-2010, 10:47 AM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,841 Times in 1,587 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Converting dates from 3-column to single column

To display in a query you could use

NewFieldNameHere:[MonthFieldNameHere] & "-" & [DayFieldNameHere] & "-" & [YearfieldNameHere]

or you could convert it to a date by using

DateSerial ([YearFieldNameHere], [MonthFieldNameHere], [DayFieldNameHere]

It all depends on what you are planning to use it for. (plus it isn't the best to store dates in that broken out of format unless you absolutely have to.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 12-16-2010, 06:34 PM   #3
floridamussels
Newly Registered User
 
Join Date: Dec 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
floridamussels is on a distinguished road
Re: Converting dates from 3-column to single column

Bob - Thanks for your help. I began with a three column format because month and day are sometimes missing for a given record.

I'm unsure how to design the query and where to enter the string you provided. I tried creating a select query from the table containing these fields and entering the string you provided as the "criteria" but ":" is an invalid character. I must admit, I'm a novice when it comes to Access so please forgive my lack of experience.

Thanks again for providing the previous advise.

floridamussels is offline   Reply With Quote
Old 12-16-2010, 09:16 PM   #4
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,841 Times in 1,587 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Converting dates from 3-column to single column

It doesn't go in the criteria. It goes in the FIELD area.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 12-17-2010, 11:34 AM   #5
floridamussels
Newly Registered User
 
Join Date: Dec 2010
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
floridamussels is on a distinguished road
Re: Converting dates from 3-column to single column

Bob - Thanks again. The first option (display in query) worked like a dream. Also wanted to try the second option. Do I enter this command in the field area too? And will this create a new field with the converted date? or will it convert the 3-column format to a single column? I need to keep the existing 3-column format for other applications.

floridamussels is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate Column Values from Multiple Rows into a Single Column erncelen Queries 50 12-14-2018 10:20 AM
Converting 2 Field Data set (column 1 tag) into a 53 Column database Patrick McNally Visual Basic 7 02-25-2010 02:17 PM
Concatenate results of a single column query russcami Queries 2 03-14-2007 09:38 AM
Converting column data to rows RedSkies Modules & VBA 3 08-31-2006 02:06 PM
Concantenating records within a single column monkeytunes Queries 3 05-28-2006 10:29 AM




All times are GMT -8. The time now is 06:33 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World