Date formatting issues with search script

[jt196]

Folks I'm having a bit of headache with a search script. I want to search a date range from today to 90 days in the future. 

Currently I've made a script with a step saying: 

Insert Calculated Result [Select; "date field"; ( Get (CurrentDate) & "..." (Get (CurrentDate) + 90)]

Being in the UK, I'm working with a file that was created on a US computer - the month and day are the wrong way round. I've changed the text on the system to use system settings but that still doesn't solve it! Frankly, that's quite ridiculous! Surely a bug like this that has been around for a VERY long time must have been ironed out of the software code? 

From what I understand, to avoid this, I need to create a clone of the whole database copying everything across to stop this calculation showing up the MM DD round the wrong way. I don't really want to have this headache, so I tried creating Calculation Fields in my Products table (that actually show up the right way round) and using them in the above code, but the search field just show up as empty aside from the "..." in it. 

Are there any workarounds for this to save me the headache of cloning a rather complex database with a few dozen tables? 

[comment]

1 hour ago, jt196 said:

From what I understand, to avoid this, I need to create a clone of the whole database copying everything across

You understand correctly, except there is nothing to "copy across". You need to import all records from the original file into the clone (separately for each table).

 

1 hour ago, jt196 said:

Are there any workarounds for this to save me the headache of cloning a rather complex database with a few dozen tables? 

IIRC, if you use Set Field[] instead of Insert Calculated Result[], the result will be unaffected by this bug. Alternatively, you could calculate the entire result as text, e.g. =

Let ( [
now = Get (CurrentDate) ;
fut = Get (CurrentDate) + 90 
] ;
Day ( now ) & "/" & Month ( now ) & "/" & Year ( now ) 
& "..." & 
Day ( fut ) & "/" & Month ( fut ) & "/" & Year ( fut ) 
)

 

1 hour ago, jt196 said:

Surely a bug like this that has been around for a VERY long time must have been ironed out of the software code? 

Alas, that is not true - and not only for this particular one.

[jt196]

Dude that's great thanks. All working now. Got my predictive order schedule working!

Seems like that whole date thing could be sorted out in 10 minutes or so of an afternoon, but what do I know, I'm just a lowly user!