Format field

[prasadgov]

I have a field coming in as 240713 (July13th). How to format it as 07/13/2024 0:00?
Secondly. how to format 20240628082808 as 06/28/2024 08:28:08?

Finally, I have a field for which I use Mid function as Eff_Settlement:Mid([Expr1],344,8)
It works on when the value is 50000000,
But when value is 6000000, or 60000, I need to strip the comma. How to modify my query?

TIA

 

[June7]

1. Will year always be 2000 or later?
Format(Format("240713", "20##/##/##"), "MM/DD/YYYY")
Format(Format("20240628082808","####/##/## ##:##:##"),"General Date")

2. Val(Mid([Expr1],344,8))

 

[prasadgov]

1. Will year always be 2000 or later?
Format(Format("240713", "20##/##/##"), "MM/DD/YYYY")
Format(Format("20240628082808","####/##/## ##:##:##"),"General Date")

2. Val(Mid([Expr1],344,8))

When I tried Format(Format("20240628080614","####/##/## ##:##:##"),"General Date"), I got the result as 202406280/8/0 6:1:4
Also in design view, Format(Format("20240628080614","####/##/## ##:##:##"),"General Date") transforms into Format(Format("20240628080614","#\/#\/# #\:#\:#"),"General Date")

Secondly, I have a field which is as USD17910566,58 which I format using Mid function to get 17910566,58 and then use Replace to replace comma with decimal to make the value as 17910566.58. But there are few values such as USD47462875, or USD7728154,4 or USD7728,
How to use the Mid Function effectively to identify both the no of characters as well as replace commas. If there is no digits following commas how do I replace it with ".00"

 

[Gasman]

Really?

? Format(Format("20240628080614","####/##/## ##:##:##"),"General Date")
28/06/2024 08:06:14

I would write a function for your second problem.
You test for the ",". If that is the last character you append ".00".

Break it down into small steps, one at a time.

 

[theDBguy]

When I tried Format(Format("20240628080614","####/##/## ##:##:##"),"General Date"), I got the result as 202406280/8/0 6:1:4

Are you trying to get a date value back? If so, could you give this one a try?

cdate(format("20240628080614","0000-00-00 00:00:00"))

 

[June7]

Sorry, I tested expressions in Immediate Window and they worked there.
However, not working in query - I used wrong mask character.
theDBGuy CDate espression works in query. And you get a true date value, not a string.
CDate(Format("240713","\2000-00-00"))
CDate(Format("20240628080614","0000-00-00 00\:00\:00"))

Don't use Format or FormatNumber function to force display of .00 as the result is a string, not a number.
Val(Replace(Mid([Expr1],344,8), ",","."))
If you really want to display .00 in query, can apply Format property to field with Access query designer. Otherwise, format textbox in report.

 

[prasadgov]

Sorry, I tested expressions in Immediate Window and they worked there.
However, not working in query - I used wrong mask character.
theDBGuy CDate espression works in query. And you get a true date value, not a string.
CDate(Format("240713","\2000-00-00"))
CDate(Format("20240628080614","0000-00-00 00\:00\:00"))

Don't use Format or FormatNumber function to force display of .00 as the result is a string, not a number.
Val(Replace(Mid([Expr1],344,8), ",","."))
If you really want to display .00 in query, can apply Format property to field with Access query designer. Otherwise, format textbox in report.

Thank you!

I do not see the "Solved" button or Option for this thread. This is Solved

 

[Gasman]

Thank you!

I do not see the "Solved" button or Option for this thread. This is Solved

Unless that has disappeared in the upgrade, it was top right of thread perhaps under Thread Tools?

 

[June7]

I don't see Thread Tools dropdown.

 

[Gasman]

I don't see Thread Tools dropdown.

That might be AF then?
Hard to see unless one has started a thread?

 

[June7]

In AF it always shows at top of thread. Guess AW handles it differently.

 

[Gasman]

There used to be something here, as I have used it myself in the past.
It might be an addin, in which case it has been removed until confirmed it works with 2.3, the version we are now using here.