Hi All,
Here is my problem. I am creating a small database for people in our company to track Production Backup tapes on a daily basis. While I do not agree with their naming convention for the tapes, they want to stay with it. Each tape has a prefix of "BKU" followed by the present days date shown as "Tue Sep 03 02".
I want to automate this to the point that all the user has to do is enter a date in the txtBKDate field and the AfterUpdate event will produce
'BKU - Tue Sep 03 02' in the txtTapeNum field.
Here is what I have done. For starters, I want to format the date to a Long date. Here is my code:
Private Sub txtBKDate_AfterUpdate()
DateLG = Format(txtBKDate, "Long Date")
DateDY = Left(DateLG, 3)
DateYR = Right(DateLG, 2)
DateM3 = Mid(DateLG, 8, 4) 'Gets 3 char Month DateN2 = Mid(DateLG, 14, 2) 'Gets 2 char Day #
DateSp = " "
Me!txtTapeNum = "BKU - " & DateDY & DateSp & DateM3 & DateSp & DateN2 & DateSp & DateYR
End Sub
My issue? This works fine if I am entering, for example, 06/03/02. This gets converted to June 03,2002 and then the resulting txtTapeNum field shows:
'BKD - Mon Jun 03 02'
But .... What if the month is January and the day is Wednesday. The 'Mid' arguments that I have will not work then. How do I get it to format according to what day/month it is? The only thing I can think of, and I have tried, is to have 2 other fields that converts the date to day # and month # and then do checks against them. For example:
If txtCheckDay = 2 Or txtCheckDate = 6 And txtCheckMonth = 6 Or txtCheckMonth = 7 Then
Me!txtTapeNum = "BKU - " & DateDY & DateSp & DateM3 & DateSp & DateN2 & DateSp & DateYR
ElseIf
....Next Check
End If
There has to be an easier way than testing each day and month. Not that I mind, but that would be a lot of coding.
Any help would be great. Thanks!
Here is my problem. I am creating a small database for people in our company to track Production Backup tapes on a daily basis. While I do not agree with their naming convention for the tapes, they want to stay with it. Each tape has a prefix of "BKU" followed by the present days date shown as "Tue Sep 03 02".
I want to automate this to the point that all the user has to do is enter a date in the txtBKDate field and the AfterUpdate event will produce
'BKU - Tue Sep 03 02' in the txtTapeNum field.
Here is what I have done. For starters, I want to format the date to a Long date. Here is my code:
Private Sub txtBKDate_AfterUpdate()
DateLG = Format(txtBKDate, "Long Date")
DateDY = Left(DateLG, 3)
DateYR = Right(DateLG, 2)
DateM3 = Mid(DateLG, 8, 4) 'Gets 3 char Month DateN2 = Mid(DateLG, 14, 2) 'Gets 2 char Day #
DateSp = " "
Me!txtTapeNum = "BKU - " & DateDY & DateSp & DateM3 & DateSp & DateN2 & DateSp & DateYR
End Sub
My issue? This works fine if I am entering, for example, 06/03/02. This gets converted to June 03,2002 and then the resulting txtTapeNum field shows:
'BKD - Mon Jun 03 02'
But .... What if the month is January and the day is Wednesday. The 'Mid' arguments that I have will not work then. How do I get it to format according to what day/month it is? The only thing I can think of, and I have tried, is to have 2 other fields that converts the date to day # and month # and then do checks against them. For example:
If txtCheckDay = 2 Or txtCheckDate = 6 And txtCheckMonth = 6 Or txtCheckMonth = 7 Then
Me!txtTapeNum = "BKU - " & DateDY & DateSp & DateM3 & DateSp & DateN2 & DateSp & DateYR
ElseIf
....Next Check
End If
There has to be an easier way than testing each day and month. Not that I mind, but that would be a lot of coding.
Any help would be great. Thanks!
