Unconcatenate two fields 3

[surfbum3000]

I have 2 fields that need to be unconcatenated.

[tblAppts].[STARTDATE]
[tblAppts].[STARTTIME]

The STARTDATE field Data Type is Date/Time. The STARTTIME field Data Type is Text.

STARTDATE is in this format: M/D/YYYY
STARTTIME is Military Time format: HH:MM (15:00)

I need Month one field, Day another field, and Year a third field. Also, I need Hour one field and Minutes another field.

Thanks.

 

[Golom]

It's certainly possible but highly inadvisable to do that. It is not advised because you then have 5 unrelated fields which, at least in theory, could be modified independently of one another and you must assume the responsibility of ensuring that (for example) the "Day" field is still valid when the "Month" field is changed.

Rather than making more fields, I would collapse the "StartDate" and "StartTime" fields into a single field containing both date and time. You can then extract any component you want with
[tt]
Second(dtField)
Minute(dtField)
Hour(dtField)
Day(dtField)
Month(dtField)
Year(dtField)
[/tt]
With seperate fields you also lose the ability to use the date and time related functions (e.g. DateDiff, DateAdd, DateSerial, etc.) to perform date or time related calculations.

 

[lespaul]

That was my first thought too, Golom, but you explained it much clearer than my muddled thought!

Star for you!

les

 

[surfbum3000]

How do I "...collapse the "StartDate" and "StartTime" fields into a single field containing both date and time.

 

[Golom]

This way
[blue][tt]
UPDATE tbl SET [StartDate] = [StartDate] + cDate ( [StartTime] )
[/tt][/blue]

Be sure you run this ONLY ONCE.

 

[surfbum3000]

The code you suggested did work. I now have the STARTDATE field formatted as 4/6/2004 11:00:00 AM. I probably should have explained why I need the seperate fields. I export this table to a .csv file, which is then uploaded to another software program that uses Text-to-Speech. We have a need to record the day of the week, the month, the date of the appointment, and the time in Spanish and Vietnamese. For the software to recognize these variables, there would need to be a seperate field for each. I need a file with the month, day, hour, and minutes in a seperate field. That will allow the software to translate these fields to another language. The file will be created, and then exported, and subsequently imported into the Telephone Calling Software so I would think the possibility of getting the data "mixed up" would be very low. Wrong?

 

[Golom]

OK. Write a Query of the form
[blue][tt]
Select Format ( [StartDate], "dddd" ) As DayOfWeek,
Format ( [StartDate], "MMMM" ) As MonthName,
Format ( [StartDate], "yyyy-mm-dd" ) As [TheDate],
... Other Fields ...
From tbl
[/tt][/blue]
Save that as a query and then export it to your external application. You can of course add fields for Year, Hours, Minutes, etc., as required.

 

[surfbum3000]

Your reply was very helpful. This is the format that did work:

ApptHour: Format([STARTTIME],"hh")
ApptMinutes: Format([STARTTIME],"nn")
ApptMonth: Format([StartDate],"mm")
ApptDay: Format([StartDate],"dd")
ApptYear: Format([StartDate],"yyyy")

I now have seperate fields for each. Thank you very much.

 

[surfbum3000]

To seperate the LastName, FirstName field in a table:

LastName: Left([CLIENT_NAME],InStr([CLIENT_NAME],", ")-1)

FirstName: Mid([Name],InStr([Name]," ")+1)