?
Solved

Capturing minutes and seconds only?

Posted on 2011-02-21
17
Medium Priority
?
265 Views
Last Modified: 2012-05-11
Hi all,

How would I do this?  I have a time field which I have set the format to nn:ss but entering 05:45 becomes 05:45:00 - How do I get around this?  

Regards

Chris
0
Comment
Question by:smods
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +4
17 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34941617
enter it as 00:05:45?
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34941623
you might need to give more detail?  could you convert your time to just seconds and store it as an integer?
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34941647
Chris,

Try this format:

=Format(Now(),"m:s")

Sincerely,
Ed
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:smods
ID: 34941684
Hi guys,

Sorry I wasn't clear! It's for capturing an average transaction time.  If possible I wouldn't want to enter it as 00:05:45

Regards

Chris
0
 
LVL 77

Expert Comment

by:peter57r
ID: 34941720
There isn't any way of entereing just minutes and seconds into a time field.  As you have found , Access will always treat such an entry as hours and minutes.

You can use the technique suggested by Sudonim or you can use two fields, one for minutes and one for seconds and then build the time value in code.

Or you can use the afterupdate event of the entry and rebuild the time using..

me.textboxname= timeserial(0, Hour(me.textboxname), minute(me.textboxname))
0
 

Author Comment

by:smods
ID: 34941747
could you convert your time to just seconds and store it as an integer?

So would I enter the time but in seconds? The two text box solutions doesn't sound ideal.  How would I do this?
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34941780
I am sorry Chris for my first post.

Yeap, I agree with Peter. Date/Time field allows only entry for a day and a time.

If you want to view elapsed time and showing it in minute:seconds then you change the date/time field into a number and enter the elapsed time in seconds. Then, calculate the value into minute:second for display purposes.

Ed

0
 

Author Comment

by:smods
ID: 34941809
Thanks Ed.  How would I do that?

Regards

Chris
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34941887
Chris,

Here it is:

Private Sub t1_AfterUpdate()
Me.MinSec = Me.t1 / 86400
End Sub

t1 = Field name of where you enter the elapsed values in seconds
MinSec = Unbound field that display the minutes:seconds

You need to format the MinSec field with "m:s".

Sincerely,
Ed
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34941929
Replace  Me.MinSec = Me.t1 / 86400 witht the code below instead:


Me.MinSec = Int(Me.t / 60) & ":" & (Me.t - (60 * Int(Me.t / 60)))

Ed
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34941935
Chris,

Pratima's post at ID:34941778 provided the codes I posted above. You can refer that link for more info.

Ed
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 34942409
Here is how:

1. Apply an inputmask of:

"00:"00:00

2. Use this code:
Private Sub txtTime_Click()

  If Me!txtTime.SelStart < 3 Then
    Me!txtTime.SelStart = 3
  End If
  Me!txtTime.SelLength = 1
  
End Sub

Private Sub txtTime_Enter()

  Me!txtTime.SelStart = 3
  Me!txtTime.SelLength = 1
  
End Sub

Open in new window


/gustav
0
 
LVL 40

Expert Comment

by:als315
ID: 34942444
Why not to use something like this?
DB26835639.accdb
0
 

Author Closing Comment

by:smods
ID: 34942689
Works like a charm!

Many thanks!!

Chris
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 34942713
You are welcome!

/gustav
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 34942881
nice work all.
that will teach me to take a long lunch break :)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question