Date/Time

[dunkingman]

I have a table with a text field titled ID. The values in this field are typed in by the user and are supposed to be in the format mmddyyhhmm. There are values in the field like 0000003271 and 0000000001. I don't know how this could be, but how do I change these values into an understandable date, like mm/dd/yy hh:mm? Also (or even if I can't do what I just asked), I tried setting the value of this field (in a form) to =Now in VB, but I got an error message stating that the field isn't big enough to display this. I set the "Can Grow" property to Yes, but it still received the same error message. How can I display the date as "= Now," or whatever property I set the ID field to? Thanks in advance for any help

 

[D-Fresh]

Well as long as the format is going to be set, you can just use string functions to convert the characters, like so...

'For the Date...
CDate(Left([ID], 2) & "/" & mid([ID], 3,2) & "/" & mid([ID],5,2))

'For the Time...
CDate(mid([ID], 7,2) & ":" & Right([ID], 2))

That should convert the string for you. Hope this helps.

Doug

 

[dunkingman]

Thank you D-Fresh, but before I try it. . . What property were you referring to being "set?" Also, do I put the expression in the expression builder or VB (or SQL)? Lastly, since the data (Date/Time) is going to display in one field, how could I use two seperate functions (one for date and one for time) to put them in one field. Do I put one function right after another. Sorry about my lack of knowledge regarding this subject. Thanks

 

[D-Fresh]

Alright, I apologize for being so vague.. What I meant was fixed.. As in always being 10 characters and always in the form mmddyyhhmm. If that is true, then you can use what I gave you... If you want to put the value in one field, then you can just use...

'AfterUpdate event of the textbox
Private Sub ID_AfterUpdate()
Dim TempDate As Date

TempDate = CDate(CDate(Left(Me![id], 2) & "/" & Mid(Me![id], 3, 2) & "/" & Mid(Me![id], 5, 2)) & _
" " & CDate(Mid(Me![id], 7, 2) & ":" & Right(Me![id], 2)))

Me![id] = TempDate
End Sub

'BeforeUpdate event of the textbox
Private Sub ID_BeforeUpdate(Cancel As Integer)

If Len(Me![id]) <> 10 Or Not IsNumeric(Me![id]) Then
msgbox("Sorry, invalid format... Must use 'mmddyyhhmm'")
Cancel = True
End If

End Sub

This will check to make sure the format is correct that the user entered(the string is 10 characters and numeric) and will not allow the update if it is wrong. If it does update, then the function will combine the two into a long date format... Hope this helps you.

Doug

 

[dunkingman]

Thank you for giving me an detailed answer. It worked perfectly. Is there any way to get the existing values in the table to automatically be converted to this easier-to-read format? Thanks for the help.

 

[D-Fresh]

I think your best bet is to run an update query... Update the value to that long cdate(..) function that I gave you. You will need a where clause also, so it doesn't convert anything that has already been converted. I think your best bet is to use a where clause of...

mid([ID],3,1)<>"/"

This will check if it is already a date, and if not, will update the value. Be sure to back up your table first.. Just in case... Hope this helps.

Doug

 

[dunkingman]

D-Fresh, you've been such a help, thank you for your effort in helping, but . . . I've just been informed that the ID text box on the form is supposed to autofill not with the more understandable format that you gave me, but with the original format of mmddyyhhmm. I honestly hate to ask you for more help, but one last time, can you help me figure out how to make the text box autofill with the current date/time in the format mmddyyhhmm? I'll try not to ask another question for a while if this works.

Thanks, and sorry about the change in plans

[This message has been edited by dunkingman (edited 07-02-2001).]

 

[D-Fresh]

Pain in the $@%!! Just kidding.. My clients and boss change the specs on me all the time too, I know how it is... This is actually much easier to do than what you were originally looking for... I seperated and commented everything for you so it's a little easier to understand... here goes..


Private Sub Form_BeforeInsert(Cancel As Integer)

Dim TempDate As Date
Dim TempTime As Date
Dim strTemp As String

'Get Current Date
TempDate = Date

'Get Current Time
TempTime = Time

'Get Month
strTemp = Format(Month(TempDate), "00")

'Concatenate Day
strTemp = strTemp & Format(Day(Date), "00")

'Concatenate Year
strTemp = strTemp & Right(Year(Format(TempDate, "mm/dd/yyyy")), 2)

'Concatenate Hour
strTemp = strTemp & Format(Hour(TempTime), "00")

'Concatenate Minute
strTemp = strTemp & Format(Minute(TempTime), "00")

'Set text box value to string
Me!ID = strTemp

End Sub

Put this code in the BeforeInsert event of your form... That way, everytime a new record is added, it will automatically populate the ID field with what you're looking for, mmddyyhhmm. Hope this helps... And don't worry about posting more questions... That's what we're all here for.

Doug

[This message has been edited by D-Fresh (edited 07-02-2001).]

 

[Rich]

Why can't you just set the field to Date/Time and general date, with the default value to Now()?

 

[dunkingman]

D-Fresh, as with your last function, this one worked perfectly. I put the function you gave me in the FormLoad Event so it would display on first run. Do you see this as a potential problem? I also put it in the BeforeInsert Event so it would display after each entry. That was very generous of you to stay with me on this question considering the specs completely changed from what I first asked. Thank you again for all your help.

[This message has been edited by dunkingman (edited 07-03-2001).]

[This message has been edited by dunkingman (edited 07-03-2001).]

[This message has been edited by dunkingman (edited 07-03-2001).]

 

[D-Fresh]

You know, Rich makes a good point.. I was too caught up in trying to convert the original way you gave me that I didn't see the simplicity in this. In your table definition, set the ID field to a date/field type and set the format to "mmddyyhhnn". Then in your form, set the ID textbox's format to "mmddyyhhnn" and set the default value to Now. This will do it for you, no need for a function. In case your wondering, "nn" stands for minutes, since "mm" is already used for the month. Sorry for running you astray for no reason and thanks Rich. A little tip, you should also lock the ID Field so no one can change it. Best of luck.

Doug

[This message has been edited by D-Fresh (edited 07-03-2001).]

 

[dunkingman]

Thanks D-Fresh and Rich very much, but. . . It doesn't display until after the first entry is made. Do you know any way to fix this? Also, if I change the format of the existing ID field, 1360 records that are not in this format ("mmddyyhhnn") will be deleted. Any suggestions?

[This message has been edited by dunkingman (edited 07-03-2001).]

 

[D-Fresh]

What type is the ID field defined to be? Date/Time, String? This could still work... And if you set the default value of the ID textbox in your form to now() and the format to "mmddyyhhnn" then it should automatically populate, and you won't need my previous function... Make sure you have the setting correct.

Doug

 

[dunkingman]

It was text. Some of the values wouldn't covert to date/time. Thanks