Format time field

[gstylianou]

Hi all,

I need your help for this:

I have a Number filed (Single type) with input mask 99:99 for hours. The problem is the report and actually those fields for hours because i'm getting eg 0600 or 1000 without the upper / lower fines ) (displayed as a numbers)

Is there a way to displayed as normal time ? (eg.06:00 and 10:00)

thanks

 

[essaytee]

Straight from the immediate window:

? now
12/04/2019 11:55:33 PM 
? format(Now(),"hh:nn")
23:55

num = 0600
? format(num,"##:##")
6:00

? format(num,"0#:##")
06:00

 

[gstylianou]

Straight from the immediate window:

? now
12/04/2019 11:55:33 PM 
? format(Now(),"hh:nn")
23:55

num = 0600
? format(num,"##:##")
6:00

? format(num,"0#:##")
06:00

Thanks Steave but,

What i must do is: Lets say into Text1 is 0600, how can i modify the above code in order to have the remain result?

eg. format(Text1,"hh:nn") Is that correct?

 

[theDBguy]

Thanks Steave but,

What i must do is: Lets say into Text1 is 0600, how can i modify the above code in order to have the remain result?

eg. format(Text1,"hh:nn") Is that correct?

If the value is Text, you could either convert it to a number and do what's already suggested or try Format(Text1,"@@@").

 

[MajP]

format(Text1,"hh:nn") Is that correct?

That one would not work, because that format "hh:nn" can only be correctly applied to a datetime value. 0600 is a string and not a datetime value. The computer stores the datetime value for 6:00 AM as .25.

 

[essaytee]

Thanks Steave but,

What i must do is: Lets say into Text1 is 0600, how can i modify the above code in order to have the remain result?

eg. format(Text1,"hh:nn") Is that correct?

The immediate window is your friend and very useful for checking out quick solutions. I, for one, can not remember every in-built function but in the back of my mind, I know something is there. Usually, it's a quick Google search, that in the main sends me to the Microsoft Docs, and then I open up the immediate window for testing/confirmation.

? Now
13/04/2019 9:56:42 AM 

strNum = "0600"
? Format(strNum,"00:00")
06:00

? Format("0600","00:00")
06:00

? Format("9999","00:00")
99:99

---- Doesn't work   (actually it does work, but not the intended result)
? Format("0600","hh:nn")
00:00
? Format("9999","hh:nn")
00:00

As you can see, you can format a text value to be separated by a colon. The issue is, with what I've posted above, the result may not be a valid time of day. As others said above, you will need to work with date/time variables in order to correctly retrieve "hh:nn".

 

[The_Doc_Man]

When you put in a string, you can format it but there are rules regarding use of "hh" and "nn" and "ss" that are different than for formats like "@@@" or "0#:##" because of the special uses of H, N, and S in format strings. The time-of-day formatting characters require inputs in DATE data format because they are going to perform sexagesimal translation (base 60). Putting in a LONG or SINGLE of 600 will not correctly lend itself to sexagesimal formatting.

You have essentially already killed the process because using a field of type SINGLE is applying the wrong data type to the problem. That data type is only susceptible to decimal formatting rules. You have supplied inputs incompatible with your true goal. The solution offered by essaytee is a workaround. As long as you have inputs as you do, you will not find a much better solution.

 

[gemma-the-husky]

@OP

Why are you using a number. Why not use a date/time variable. That will automatically stop you entering values above 59:59 (To be honest, it might actually treat 75:00 as 1:15:00) but that would be obvious to the user.