Is possible to have an input mask for hhh:mm and hh:mm?

[Cark]

I have a field in my table which is populated via a record entry form. The field in the table is called DelayTime which can vary from 00:01 (0 hours and 1 minute) to beyond 100:00 (100 hours and 0 minutes) but is never likely to exceed 999:59.

As I am a bit of a novice at Access and forms, I have struggled through and created workaround that sort of works, but now I would like to get input from the more Access able folk to kick it on a notch.

What I have done so far:

Field Properties:

• DelayTime = Short Text
• Field Size = 255
• Format = Blank
• Input Mask = 000:00;0;_
• Default Value = Blank
• Required = Yes
• Allow Zero Length = No
• Indexed = No

On the form:

• Object Type = Text Box
• Control Source = DelayTime
• Text Format = Plain Text
• Input Mask = 00:00;0;_
• Default Value = Blank

I am aware the input masks I have set up are different and that is because I couldn't find a way to solve the question posed in the thread title. As the majority of the delays I am inputting into the database are between 00:00 and 99:59 I have opted to have the mask set at 00:00;0;_ for the form and if I have any delays above 100 hours, I go into the table and enter the time manually there. It is a bit of an annoying and insecure process if I am to roll this database out across my department of non-access savvy users.

Is there a way I can make it so that as I type in the box, it fills the box from right to left? so if I were typing the keys in sequence 1, then 4, then 5, then 3, then 2 (to type 145:32) and I stopped before typing the final 2, it would should 14:53?

If you know anything about masks I would love to have a discussion on how to resolve this problem. It is probably a simple fix and I am just missing it.


I can supply a database example, but it would take time for me to strip back the confidential elements and I don't think it will help too much, but if it is required in order to get help I can do it when I have a break from work.

 

[Ranman256]

hours only come in hh.
23 is the biggest h.

 

[Cark]

Hi Ranman, thanks for the reply.

Because I am needing delay times to be logged which are greater than 23:59, I have made the DelayTime field a Short Text field so that I could enter data that is hhh:mm.

I am ok with having this as a text field at the moment as I am able to use calculated columns to split the hours and minutes into their parts and then also have a DelayTotalMins column.

My issue relates to the input mask feature I think.

 

[Minty]

I think I would store this simply as a number of minutes (a long integer), then use a function to display it as required. it makes the adding up a lot easier as well.
But - does it needs storing at all?

Are you able to calculate the delay from two other fields like startdate and enddate or something similar?? If so don't store it again.

 

[Cark]

Ideally I would like to have the delay time stored in various ways as I have various things latching on / using Excel exports from the database which will require different things. Therefore I have DelayTime (in hhh:mm format), DelayHours (hhh) DelayMins (mm) and DelayTotalMins (in long number format).

When filling in the form, I would like to be able to type in the DelayTime directly rather than have to have 2 boxes for delay hours and delay minutes. The delay time is actually just a time which is provided by another system and I just want the user to copy the number across as DelayTime rather than taking into account the actually delay start and delay end times.

After a bit more googling I came across linq's suggestion of using some visual basic to achieve something. Is this along the right line of thinking?

https://bytes(dot)com/topic/access/answers/757829-enter-number-right-left-input-mask

 

[arnelgp]

is something like in Form1 you want to achieve.

 

[Pat Hartman]

Ideally I would like to have the delay time stored in various ways as I have various things latching on / using Excel exports from the database which will require different things. Therefore I have DelayTime (in hhh:mm format), DelayHours (hhh) DelayMins (mm) and DelayTotalMins (in long number format).

Numeric data needs to be stored as a numeric format. You are mis-using the date data type because you are trying to use it to hold elapsed time. The date data type sores a POINT IN TIME, It does NOT store the elapsed time. You decide what UOM you want to use for elapsed time. If whole minutes works, you can convert it to hours and minutes by dividing by 60 and taking the remainder as minutes and format the two numbers with a : between them.

 

[Mark_]

@OP,

What is delaytime used FOR?

When filling in the form, I would like to be able to type in the DelayTime directly rather than have to have 2 boxes for delay hours and delay minutes. The delay time is actually just a time which is provided by another system and I just want the user to copy the number across as DelayTime rather than taking into account the actually delay start and delay end times.

Gives us how users interact with it, but what is its actual purpose? As is it does seem more like a computed value that you are seeing rather than something that should be stored. If you are doing a delay, you want to delay until a specific time. When it is to be delayed until is what you would normally save in a table. As such you may find it more convenient to use the built in functions to add hours and minutes to an existing start time to produce your DelayedStartTime. In all odds this will be far more useful than "Delaytime".

 

[Cark]

My database is used for storing information about flights that have been delayed. Simply put (I've omitted certain fields), I want to take down information for the following:

• Date
• Flight Number
• Departure Airport
• Delay Time
  • 
    
    I do not have the use to take down Scheduled departure date/time and Actual departure date/time as these would be extra columns that would only be used to calculate Delay Time. I would ideally like to be able to type in the delay time box on my form in a similar way to Arnel's example textbox as hhh:mm format or hh:mm format. In my table I then split this using calculated fields into the hours and minutes and total minutes. This then allows me to do calculations later on, but I do also have the requirement to keep delay time as a column in hhh:mm format to present later on hence why I want this style of textbox on the form.
    
    Arnel's top textbox in the example he attached seems to achieve what I am hoping for, but is there a way to tweak his example so that it does not allow users to type in anything that is non-numeric? I have had a play around with it and found I could type 23fgfg: and it would appear as 23fgf:g:

 

[jdraw]

You could use IsNumeric(yourControl) to determine if the entered value is a Number.
You might use additional logic for a reasonableness check (can't be negative, can't be more than XXX).

 

[Mark_]

Format your control as 0000\:00
In the beforeUpdate event,

Dim asTemp As String
    asTemp = Right(Me.YourFieldName, 2)
    If asTemp > 59 Then
        MsgBox "Cannot enter minutes greater than 59"
        Cancel = True
    End If

This should keep those pesky kids from putting in the wrong duration.

 

[arnelgp]

Here o ly numericand o ly 5 length allowsed.

 

[The_Doc_Man]

Let's be clear. DATE variables are an implied typecast of DOUBLE. They most certainly CAN hold elapsed time computed as (Date2 - Date1). Some folks take a narrow view of what you can do with DATE fields or variables, but they can do a lot more than many would give credit for.

Your problem here isn't the computation. It is the display routines, because THEY don't condone times greater than 23:59:59 with any of the "standard" formatting routines available in the library. Therefore, the best solution I have found for this problem is to "roll your own" format routine. Before I re-invent the wheel for you, search this forum for articles on "Elapsed Time" and I am sure you will come across discussions of formatting routines that will do pretty close to what you want.

 

[Pat Hartman]

Doc, when people want elapsed time, they expect it to show in hours and are very confused when it shows as 12/31/1899 05:12:03 because it was 29 hours 12 minutes and 3 seconds. So perhaps a better statement would have been, they do not display elapsed time because they will not display hours more than 23 or minutes more than 59.