Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations dencom on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

change 5pm to 17, format time 1

Status
Not open for further replies.
tonyx666

tonyx666

MIS
Apr 13, 2006
214
GB
ok, i need to change the format of certain times

here is my code at the moment, PHV helped me format the date to make 21-21st and 3-3rd etc

now i need to alter the time format

in my db, the time is in two fields.. hours and minutes

infact, you can see the form here..
http://www.londonheathrowcars.com/taxi-booking.asp

i am dealing with the pickup time..

in my db i need to change

1PM to 13
1AM to 01
Midday to 12
Midnight to 24

etc..

can anyone suggest a method

here is the code that emails the fields to my customer

Code: 
Option Compare Database

Private Sub emailbutton_Click()
On Error GoTo send_Err

Dim strToWhom As String
Dim strMsgBody As String
Dim strSubject As String
Dim temp As Integer

temp = Right([jobday], 1)

strSubject = "London Heathrow Cars - Booking Confirmation"

If Len(Me.emailadd & vbNullString) = 0 Then
    MsgBox ("Forgot an email address")
    Me.emailadd.SetFocus
Else
    strToWhom = Me.emailadd
    strMsgBody = "FAO " & Me.title & " " & Me.myname & vbCrLf & vbCrLf & _
    "Thank you for your booking request via our website. Details of the requested transfer and our fare are as follows:" & vbCrLf & vbCrLf & _
    "Job Date: " & Me.jobday & Nz(Choose(IIf(Me.jobday < 21, Me.jobday, Me.jobday Mod 10), "st", "nd", "rd"), "th") & " " & Me.jobmonth & " " & Me.jobyear & vbCrLf & _
    "Job Time: " & Me.jobhour & " " & Me.jobminutes & " hrs" & vbCrLf & vbCrLf & _
    "Pickup From: " & Me.padd & vbCrLf & vbCrLf & _
    "Destination: " & Me.dadd & vbCrLf & vbCrLf & _
    "Price: £" & Me.price & vbCrLf & vbCrLf & _
    "All of our drivers are courteous, experienced and smartly presented. Our vehicles are clean, modern, comfortable and fitted with satellite navigation technology for you peace of mind."

DoCmd.SendObject , , , strToWhom, , , strSubject, strMsgBody, True
End If

send_Err:
If Err.Number = 2501 Then
MsgBox "THIS EMAIL HAS NOT BEEN SENT!", vbInformation, "Notice!"
End If
End Sub

London Heathrow Cars
 
Sort by date Sort by votes
How about:
Code: 
IIf(Len(Me.jobhour) > 4, IIf(Me.jobhour = "Midnight", "24", "12"), IIf(Right(Me.jobhour, 2) = "AM", Left(Me.jobhour, 2), Val(Left(Me.jobhour, 2)) + 12))
Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
Job Time: " & (Val(Me.jobhour) - 12 * (Right(Me.jobhour, 2) = "PM") + IIf(Me.jobhour Like "Mid*", 12 * (1 - (Me.jobhour = "Midnight")), 0)) & " " & ...


BTW, why the following ?
temp = Right([jobday], 1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote


I assume that me.jobhr is a number between 0 and 24...
Code: 
select case me.jobhr
   case 0, 24
     DB_Hr = "Midnight"
   case 12
     DB_Hr = "Midday"
   case is >12
     DB_Hr = me.jobhr & " PM"   
   case else
     DB_Hr = me.jobhr & " AM"   
end select
DB_Hr now contains the WEB format that is equivalent to the HOUR.

Skip,

[glasses] [red][/red]
[tongue]
 
Upvote 0 Downvote
yes, thanks again phv, this is almost it.. if it is 2am though, at the moment it will display as

2 05 hrs

i actually need it to be

02 05 hrs

so basically i need a 0 before all the 1-9am hours

here is what the code looks like at the moment

thank you so much for this

Code: 
Option Compare Database

Private Sub emailbutton_Click()
On Error GoTo send_Err


Dim strToWhom As String
Dim strMsgBody As String
Dim strSubject As String
Dim temp As Integer

temp = Right([jobday], 1)

strSubject = "London Heathrow Cars - Booking Confirmation"

If Len(Me.emailadd & vbNullString) = 0 Then
    MsgBox ("Forgot an email address")
    Me.emailadd.SetFocus
Else
    strToWhom = Me.emailadd
    strMsgBody = "FAO " & Me.title & " " & Me.myname & vbCrLf & vbCrLf & _
    "Thank you for your booking request via our website. Details of the requested transfer and our fare are as follows:" & vbCrLf & vbCrLf & _
    "Job Date: " & Me.jobday & Nz(Choose(IIf(Me.jobday < 21, Me.jobday, Me.jobday Mod 10), "st", "nd", "rd"), "th") & " " & Me.jobmonth & " " & Me.jobyear & vbCrLf & _
    "Job Time: " & (Val(Me.jobhour) - 12 * (Right(Me.jobhour, 2) = "PM") + IIf(Me.jobhour Like "Mid*", 12 * (1 - (Me.jobhour = "Midnight")), 0)) & " " & Me.jobminutes & " hrs" & vbCrLf & vbCrLf & _
    "Pickup From: " & Me.padd & vbCrLf & vbCrLf & _
    "Destination: " & Me.dadd & vbCrLf & vbCrLf & _
    "Price: £" & Me.price & vbCrLf & vbCrLf & _
    "All of our drivers are courteous, experienced and smartly presented. Our vehicles are clean, modern, comfortable and fitted with satellite navigation technology for you peace of mind."

DoCmd.SendObject , , , strToWhom, , , strSubject, strMsgBody, True
End If

send_Err:
If Err.Number = 2501 Then
MsgBox "THIS EMAIL HAS NOT BEEN SENT!", vbInformation, "Notice!"
End If
End Sub

London Heathrow Cars
 
Upvote 0 Downvote
Job Time: " & [!]Format[/!](Val(Me.jobhour) - 12 * (Right(Me.jobhour, 2) = "PM") + IIf(Me.jobhour Like "Mid*", 12 * (1 - (Me.jobhour = "Midnight")), 0))[!], "00")[/!] & " " & Me.jobminutes & " hrs" & vbCrLf & vbCrLf & _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
this is red in the code and is giving me syntax error

Code: 
Option Compare Database

Private Sub emailbutton_Click()
On Error GoTo send_Err


Dim strToWhom As String
Dim strMsgBody As String
Dim strSubject As String
Dim temp As Integer

temp = Right([jobday], 1)

strSubject = "London Heathrow Cars - Booking Confirmation"

If Len(Me.emailadd & vbNullString) = 0 Then
    MsgBox ("Forgot an email address")
    Me.emailadd.SetFocus
Else
    strToWhom = Me.emailadd
    strMsgBody = "FAO " & Me.title & " " & Me.myname & vbCrLf & vbCrLf & _
    "Thank you for your booking request via our website. Details of the requested transfer and our fare are as follows:" & vbCrLf & vbCrLf & _
    "Job Date: " & Me.jobday & Nz(Choose(IIf(Me.jobday < 21, Me.jobday, Me.jobday Mod 10), "st", "nd", "rd"), "th") & " " & Me.jobmonth & " " & Me.jobyear & vbCrLf & _
    "Job Time: " & Format(Val(Me.jobhour) - 12 * (Right(Me.jobhour, 2) = "PM") + IIf(Me.jobhour Like "Mid*", 12 * (1 - (Me.jobhour = "Midnight")), 0)), "00") & " " & Me.jobminutes & " hrs" & vbCrLf & vbCrLf & _
    "Pickup From: " & Me.padd & vbCrLf & vbCrLf & _
    "Destination: " & Me.dadd & vbCrLf & vbCrLf & _
    "Price: £" & Me.price & vbCrLf & vbCrLf & _
    "All of our drivers are courteous, experienced and smartly presented. Our vehicles are clean, modern, comfortable and fitted with satellite navigation technology for you peace of mind."

DoCmd.SendObject , , , strToWhom, , , strSubject, strMsgBody, True
End If

send_Err:
If Err.Number = 2501 Then
MsgBox "THIS EMAIL HAS NOT BEEN SENT!", vbInformation, "Notice!"
End If
End Sub

London Heathrow Cars
 
Upvote 0 Downvote
OOps, sorry for the typo:
"Job Date: " & Me.jobday & Nz(Choose(IIf(Me.jobday < 21, Me.jobday, Me.jobday Mod 10), "st", "nd", "rd"), "th") & " " & Me.jobmonth & " " & Me.jobyear & vbCrLf & _
"Job Time: " & Format(Val(Me.jobhour) - 12 * (Right(Me.jobhour, 2) = "PM") + IIf(Me.jobhour Like "Mid*", 12 * (1 - (Me.jobhour = "Midnight")), 0), "00") & " " & Me.jobminutes & " hrs" & vbCrLf & vbCrLf & _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

I
  • Locked
  • Question
How to get 2 codes to work together.
Replies
6
Views
624
hermanlaksko
hermanlaksko
TrekBiker
  • Locked
  • Question
Spurious Change of Fonts
Replies
3
Views
442
dhookom
dhookom
PrimoSteve
  • Locked
  • Question
Cannot add Image to Command Button?
Replies
9
Views
850
PrimoSteve
PrimoSteve
renigar
  • Locked
  • Question
Trying to add hyperlinks to pdfs through form entry
Replies
0
Views
471
renigar
renigar
MacroAlan
  • Locked
  • Question
Navigation Bar is too short to read
Replies
5
Views
537
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top