Methods to break apart this string into several fields... (from a rookie)

[ddc_operations]

Hey All,
I have time data in the following format, which is annoying, to say the least:
days.hours:minutes:seconds (i.e. 2 days, 3 hours, 4 mins, 12 seconds would be: 2.03:04:12) and there are no leading zeroes if there is no entry for any one of the d/h/m/s parts (i.e. 3 hours, 4 mins, 12 seconds would be 3:04:12).
I would like use a query to split this into four new fields (days, hours, minutes, seconds).
I was able to get seconds using Right$ and grabbing the last two digits, but I don't know how to handle taking pieces from the middle of the string, or accounting for zeroes that don't exist, or shorter periods of time that would not have all four pieces of data within the string.


Is there a way to grab a specific number of characters delimited by the "." or ":", while still handling the situations where the time is less than a day?
Is there a better way to do this/any ideas?
Thanks for your help! Please go easy on me; I'm pretty new to Access and trying to get my hands dirty pretty quickly.

[Welshgasman]

I would replace . with : then use the split() function

tt="2.03:04:12"
tt=replace(tt,".",":")
? tt
2:03:04:12
arrTt = split(tt,":")
? ubound(arrtt)
 3

Then check ubound() of the array from the split. If 3 as above, then you have days, if less, then adjust to suit. So only mins & secs, the value would be 1 as array starts from zero.

HTH

[ddc_operations]

Thanks for the response... As I said, I am a novice, so apologies for the following question:

I have only used the pre-coded functions to this point in Access, and have not hand-coded anything. Where and how would I enter code? I am relatively comfortable coding in general and will read up on syntax (any resources you recommend?) in the mean time.

Thanks again!

[Welshgasman]

Until we hear more, not sure what to say?
You could write a function that would return one of those values?
You could write a function to return all of those values to tempvars or global variables.?

You have to supply some context on how you want those values?
If it is only needed in one place then possibly no function needed, just code in place?

[CJ_London]

you could use the datepart function

for hours/mins/seconds with a day
?datepart("d",mid("2.03:04:12",instr("2.03:04:12", ".")+1))
3
?datepart("n",mid("2.03:04:12",instr("2.03:04:12", ".")+1))
4
?datepart("s",mid("2.03:04:12",instr("2.03:04:12", ".")+1))
12

and if no day, same result
?datepart("h",mid("03:04:12",instr("03:04:12",".") +1))
3
?datepart("n",mid("03:04:12",instr("03:04:12",".") +1))
4
?datepart("s",mid("03:04:12",instr("03:04:12",".") +1))
12

and for days
?val(left("2.03:04:12",instr("2.03:04:12",".")))
2
?val(left("03:04:12",instr("03:04:12",".")))
0

[Gicu]

@ddc_operations: could you please post a small sample with some of the variations possible; in your example I see you have leading zeros on the minutes but not on the hours....
Cheers,

[ddc_operations]

Thank you (both, Ajax and Welshgasman). I understand the algorithm, and know logically what I need to do, and do understand the functions as you describe, but I think I may be in over my head in terms of implementing the solution. I could use a really broken down description of where an how to enter code within MSAccess. In my previous experience with building and using Access, I had simple, clean data and was able to do all of the manipulations and calculations through the GUI MS Access provides, and by building reports, without hand-coding anything, so TBH, I have zero idea where I would even go within the program to write the code, or how to effectively call/run it from a query. I am sorry for the very low-level questions, but that's where I'm at. If you can help, I'd appreciate it, but if it's the wrong place for this type of question, I totally understand. If you can point me to a resource that is more geared toward my level of knowledge, that'd be great.

Thx.

[June7]

Programming can be with macros or VBA. Macros can be embedded or general. VBA can be behind form/report module or in general module. There are many tutorials online and books.

Start with
https://support.microsoft.com/en-us/...7-70649e33be4f
https://www.access-programmers.co.uk...amming.159756/
http://baldyweb.com/FirstVBA.htm

[Welshgasman]

Best to follow request of post #6 then.

[Gicu]

Please have a look at the attached demo; it uses a custom VBA function using the built in Split() function as suggested by Welshgasman and in which we go descending within the array (from seconds to minutes to hours to days).

The custom function is used in Query1 to return the various time parts as you mentioned.

Cheers,
Vlad

[ddc_operations]

I may have figured out a messy workaround thanks to how you all have made me consider the problem!...

In query design, I first replaced the "." in the string with a ":" (even though in retrospect, this wasn't necessary)
Then I used a series of Left$() and Right$() functions to isolate and peel off characters from the string, creating a new field for each variable (days, hours, mins, secs) in the process.

Definitely not as clean as you all were suggesting, but got the result I was after.

I will download and look through the demo right now, Gicu... I am sure it will show me a better method!

Thanks all! I'll continue to plug away and learn - I appreciate the help!

[ddc_operations]

Vlad,

This is awesome - it does exactly what I had wanted to do, AND - more importantly - gives me a live example from which to learn more about coding in VBA, and how to call modules from within queries (which looking at it, is pretty simple, but I had never done it before, so I was unsure). I can't thank you enough

[Gicu]