Merging Date and Time, is this possible????

[Robert88]

To anyone that can help? Not sure if it is possible?

I have managed to successfully import into a table non-delimeted data as seen in the link

http://www.access-programmers.co.uk/forums/showthread.php?t=96996

the data went into fldDate(Datatype: Date), fldHours(Datatype Text) & fldMins (Datatype Text).

I even managed to successfully, in the link above, merge the two string fields (fldHours & fldMins) in a function in order to give me a field time with a query using the function.

However I have realised I need date and time all in one field and was wondering if this is possible?

This is so I can place the data in date/time order.

I have tried extending the function at the bottom of the link above but keep getting type mismatch. It seems that it does not like me merging Datatypes Date and Text together. Is there a way around this?

Look forward to anyones help here.

Robert88

 

[namliam]

Public Function MakeDateTime(fldDate as string, fldHours As String, fldMins As String) As Date

' This function concatenates fldDate, fldHrs and fldMins and converts three
' Strings into one field as a date/time field.

    MakeTime = dateserial(left(fldDate,4), mid(fldDate,5,2), right(fldDate,2)) + timeserial(fldHours, fldMins,0)

' Alternatively you could use...
'     MakeTime = dateserial(left(fldDate,4), mid(fldDate,5,2), right(fldDate,2)) & " " & fldHours & ":" & fldMins
' But otherone is "better"

End Function

Seasons greetings from Amsterdam

The Mailman

 

[Robert88]

Thanks for the code but it gives the same error.

Runtime Error 13
Type mismatch

I think there is also an error in your code (red below)?

' This function concatenates fldDate, fldHrs and fldMins and converts three
' Strings into one field as a date/time field.

    [COLOR="Red"]MakeDateTime[/COLOR] = dateserial(left(fldDate,4), mid(fldDate,5,2), right(fldDate,2)) + timeserial(fldHours, fldMins,0)

' Alternatively you could use...
'     [COLOR="Red"]MakeDateTime[/COLOR] = dateserial(left(fldDate,4), mid(fldDate,5,2), right(fldDate,2)) & " " & fldHours & ":" & fldMins
' But otherone is "better"

Correct me if I am wrong, Any ideas?

Robert88

 

[Brianwarnock]

If the date field is already in date format and the other fields are separate fields wont the code be

datetime=flddate+timeserial(fldhours,fldmins,0)

Brian

 

[Robert88]

Tried

Public Function MakeDateTime(fldDate As String, fldHours As String, fldMins As String) As Date

' This function concatenates fldDate, fldHrs and fldMins and converts three
' Strings into one field as a date/time field.

    MakeDateTime = fldDate + TimeSerial(fldHours, fldMins, 0)

' Alternatively you could use...
'     MakeDateTime = DateSerial(Left(fldDate, 4), Mid(fldDate, 5, 2), Right(fldDate, 2)) & " " & fldHours & ":" & fldMins
' But otherone is "better"

End Function

Still Runtime error 13

Robert88

 

[Brianwarnock]

flddate As Date or Variant not as string

 

[namliam]

Correct me if I am wrong, Any ideas?

Nope you are right offcourse...

Public Function MakeDateTime(fldDate As String, fldHours As String, fldMins As String) As Date

' This function concatenates fldDate, fldHrs and fldMins and converts three
' Strings into one field as a date/time field.

    MakeDateTime = DateSerial(Left(fldDate, 4), Mid(fldDate, 5, 2), Right(fldDate, 2)) + TimeSerial(fldHours, fldMins, 0)

' Alternatively you could use...
'     MakeDateTime = DateSerial(Left(fldDate, 4), Mid(fldDate, 5, 2), Right(fldDate, 2)) & " " & fldHours & ":" & fldMins
' But otherone is "better"

End Function

works just fine for me...

?makedatetime("20051105","15","45")
11/5/2005 3:45:00 PM

That offcourse is assuming the 3 fields are all 3 strings...

 

[Brianwarnock]

I agree , but he said in his first post fldDate datatype date

Brian

 

[namliam]

Yep well right you are... I think I took it from the linked thread...

Apollogies...

Public Function MakeDateTime(fldDate As Date, fldHours As String, fldMins As String) As Date

' This function concatenates fldDate, fldHrs and fldMins and converts three
' Strings into one field as a date/time field.

    MakeDateTime = fldDate + TimeSerial(fldHours, fldMins, 0)

' Alternatively you could use...
'     MakeDateTime = DateSerial(Left(fldDate, 4), Mid(fldDate, 5, 2), Right(fldDate, 2)) & " " & fldHours & ":" & fldMins
' But otherone is "better"

End Function

 

[Robert88]

Thank you............

I take my hat off to you both and thankyou.

I was using fldDate as String in initial function statement instead of fldDate as Date, thanks Brianwarnock & Namliam.

All is working now, cool

Robert88