Looking for best way to change string to date

[Middlemarch]

I want to store the Current date and retrieve later. I have

Dim MySettings As String
Dim lbDate As Date
SaveSetting "B2", "Import", "LastBatch", Date
MySettings = GetSetting(appname:="B2", Section:="Import", Key:="LastBatch")
lbDate = DateValue(MySettings)

The problem is Date for me is Tue, 15 Aug 2023 and the above only works if I lose the "Tue, " part.


And the Date could be many formats on other computers.
Is there any way to make this bullet proof regardless of Date Format ?

[CJ_London]

your code is not very clear as to what you are doing, dates should be stored as numbers, not text. They can then be viewed in any format you want e.g.

?format(date(),"ddd, dd/mm/yyyy")
Tue, 15/08/2023

If you are actually storing as text we need to see examples of values stored by other computers. If you are only concerned about your dates and assuming you are talking about MySettings being the date string try this to return a string - replace "Tue, 15/08/2023" with MySettings

?mid("Tue, 15/08/2023",instr("Tue, 15/08/2023",",")+2)
15/08/2023

or this if you want to return a value you can sort and filter on etc
?cdate(mid("Tue, 15/08/2023",instr("Tue, 15/08/2023",",")+2))
15/08/2023

Is there any way to make this bullet proof regardless of Date Format ?

Yes - the field type should be date, not text - then use the format function or property to display in the format you require

So suggest:
1. change the GetSetting function to return a date, not a string
2. remove mySettings as not required
3. change lbDate = DateValue(MySettings) to lbDate =GetSetting(appname:="B2", Section:="Import", Key:="LastBatch")

[Middlemarch]

Can I make GetSetting return a Date ? Seems to be a string only.
Your instr example replies on "," being present which it may not.
This might be okay

SaveSetting "B2", "Import", "LastBatch", Year(Date) & "," & Month(Date) & "," & Day(Date)
MySettings = Split(GetSetting(appname:="B2", Section:="Import", Key:="LastBatch"), ",")
lbDate = DateSerial(MySettings(0), MySettings(1), MySettings(2))

Or using CSTR of large number representing the date, converting to/from it. (once I find how to do that!)

[CJ_London]

Can I make GetSetting return a Date ? Seems to be a string only.

it's your function which you have not shown the code for or explained how the data is stored, so cannot help with that

Your instr example replies on "," being present which it may not.

you didn't provide examples of all the other alternatives - and as I said, it should be stored as a number anyway

[Middlemarch]

GetSetting isn't my function - it's part of Access. It doesn't seem to return anything other than a string. You can specify a default return value if no value is returned.

[Micron]

As mentioned, a thorough sample of possible date formats would be needed to see what can be suggested. It may be that a date converter function can return a date based on character length, e.g. if it is more than 11 then drop the leading characters and work with what is left. That would only apply to cases like Tue, 15 Aug 2023.
Tue, 15 Aug. 2023 would not work based on that logic due to the period (Aug.)

If there is a date function that can deal with the leading day name whether in short form or not then I'm not aware of it.
EDIT - another way might be to split the string using space as separator. If the array has 4 elements, there is a day value so take elements 2, 3 and 4 (yes, I know that the array is zero based).
Again, lots of ways to approach but knowing all the possible date variations is key.

[Middlemarch]

Will Year(Date) & "," & Month(Date) & "," & Day(Date) not work on all/any date Formats. Then use those values in DateSerial() ?
Seems to be working okay but there may be a problem not obvious yet.

[Micron]

If by "Date" you mean your string I'd say no. Remember - Date is a function that returns the current date. What you posted will work because Date returns a valid date. If you are using it as a variable name you should not be doing that. So no, this will not work:
Year("Tue, 15 Aug 2023") & "," & Month("Tue, 15 Aug 2023") & "," & Day("Tue, 15 Aug 2023")

Here is an example of what I was suggesting using your one and only provided format example. Note that there may be a lot of logical tests that would need to be employed, such as determining if a component is numeric or not.

Function ConvertDate(strDateIn As String) As Date
Dim ary

ary = Split(strDateIn, " ")
If UBound(ary) > 0 Then
     Select Case UBound(ary)
          Case 3 'date has 4 parts assumed to be as Tue, 15 Aug 2023
               ConvertDate = CDate(ary(1) & " " & ary(2) & " " & ary(3))
     End Select
End If

End Function

This is only practical if you are stuck with string dates and where some formats can't be converted to valid dates or date portions.
Result:
?convertdate("Tue, 15 Aug 2023")
8/15/23

[CJ_London]

GetSetting isn't my function - it's part of Access.

Learn something every day - so you are storing a value in the windows registry? Probably explains why it returns a string

But if you are storing in the windows registry, that is unique to you so I don't see how that can vary.

just tried your code in the immediate window and it returns

SaveSetting "B2", "Import", "LastBatch", Date


?GetSetting(appname:="B2", Section:="Import", Key:="LastBatch")
16/08/2023

So if in windows settings you have set the short date format to include the day, that is what you will get.

So are you trying to write code that will work on a multitude of systems?

If so then try

SaveSetting "B2", "Import", "LastBatch", clng(Date)


?cdate(GetSetting(appname:="B2", Section:="Import", Key:="LastBatch"))
16/08/2023

now you have a date value (not string) which the cdate will format per windows settings - having run save settings with my standard dd/mm/yyyy regional setting, I then went to the registry to change the format to ddd, dd/mm/yyyy and then ran getsettings - you can see it has returned the new format

?cdate(GetSetting(appname:="B2", Section:="Import", Key:="LastBatch"))
Wed, 16/08/2023

[Middlemarch]

Brilliant CJ! That's where i was stumbling to. I knew there was some way to use that 'large number' representing the date but had forgotten.
So have changed to that. My way was working* but this is better
>But if you are storing in the windows registry, that is unique to you so I don't see how that can vary.
It's updated by those of us using this app. So unique to each User.
Many thanks, and also to Macron.

* But not confirmed with a different Windows setting.

[CJ_London]

just curious - why are you using the windows registry to store what is presumably a temporary value?

[Middlemarch]

It works well and is presumably there to be used. The reqistry entry calls it VB and VBA Settings
Do you think there's something wrong with this?

[CJ_London]

Not necessarily, I was not aware of it (or if I was I have completely forgotten about it). Normally I would store a temporary value in a global or tempvar or for longer term or unstable environments in a db property or perhaps an object property, maybe even a table in either FE or BE, depending in requirements. I can see benefits if the saved value is to be used across a number of different apps - including excel, word etc although they can all retrieve from a table or property.

The only thing I can think of that I use to modify the registry is code to set a trusted location so will investigate whether this might be a simpler method.