problems with splitting date and time fields

[swaroop1012]

I have a database where there is a field "date time" and some of the entries of this field have the time and some don't. I wrote a query to display them.my job is to split them into two seperate fields of date and time. I used the commands

Date: Left([RECEPD],Instr([RECEPD]," ")-1)
Time: Mid([RECEPD],InStr([RECEPD]," ")+1)

where RECEPD is the actual field name for "date time".
Here i got a problem which is the entries with both time and date worked fine but the ones with only date gave error (like #Error) in the date field and displayed the date in the time field. can any one pls suggest me any ideas to overcome this problem. I need the solutions a bit quick as my deadline is approaching. thanks in advance.

 

[Mike375]

There will be neater ways for sure but what I do with this sort of thing is to use the Len().

Len([RECEPD]) counts the characters in the field.

Depending how your date is entered you will know a character count that means the time is not in the field and only the date.

You then either use IIF(Len([RECEPD])<10, Null,Left etc)

Or you can use your query to only show the records where Len()>10 and apply your left/right to those records as they will be the ones with date and time. Then simply update the field required for the date from the original field.

That is a rough explanation but you get the idea.

 

[DCrake]

I would suggest changing the format of the date/time field to General Date, by doing so all dates that do not have a time should be 01/01/2008 00:00:00 then you can use the left(10) and right(8) functions

Not tested but it could work.

Another option is that dates are actually numbers and the remainder part of the number is the time element. so if you use Mod to extract the remainder value of the number and convert this to a time format. if there is no remainder then there is no time element.

Again not tested

David

 

[DreamGenius]

The method you could use depends on the format of the field called [Date Time], if I've understood your question correctly.

If [Date Time] is a Date/Time field, then you can use Format([Date Time],"DD/MM/YY") for the Date portion and Format([Date Time],"HH:NN:SS") for the Time portion

If [Date Time] is a Text field, then you need to use Len([Date Time]) as suggested above to conditionally split the field. For instance, assuming your default format is DD/MM/YYYY HH:MM:SS, it'll look something like this:

If Len([Date Time) > 10 Then
   Me.DateField.Value = CDate(Left([Date Time],10))
   Me.TimeField.Value = CDate(Right([Date Time],8))
Else
   Me.DateField.Value = CDate(Left([Date Time],10))
End If

This doesn't test for all eventualities but you should get the idea.