trouble with dates

[Monarch Watch]

Hi All,

I'm a newbie working on a past due script. What I would like to do is to set it up so that when I perform the script it finds all records that have an invoice date from 18 months ago to the present. How do I do this without have to restore the find each time?

Thanks!

Sarah

[BobWeaver]

Put these steps into your script:

Enter Find Mode

Insert Calculated Result [DateField, ">="&Date(Day(Status(CurrentDate)),Month(Status(CurrentDate))-18,Year(Status(CurrentDate)))]

Perform Find

Note that in both the Enter Find Mode and Perform Find steps, you must make sure that the "Restore Find Request" option is UNchecked.

[CobaltSky]

Hello Sarah,

If what you really want to do is to find "all records that have an invoice date from 18 months ago to the present", then you may strike a problem using the method that Bob has suggested.

It will find all records where the date is later than eighteen months ago, but will include records (if there are any) with their date in the future, rather than stopping at "the present", as you've indicated you require.

I suspect Bob is assuming that invoices with future dates are not possible - but whereas they are not common, there are businesses who operate thier systems that way, and its possible, given the way you've phrased your question that this may be such a case?

Irrespective of that, a further issue is that in the formula Bob suggests, the date is not being converted to text, plus parameters of the Date( ) function are reversed, so it will fail (or, at any rate, produce unexpected outcomes). The correct syntax of the Date( ) function is: Date (month, day, year)

An alternative version that will work and will do what you've actually said you want would be:

Enter Find Mode [ ]



Insert Calculated Result ["InvoiceDate", "DateToText(Date(Month(Status(CurrentDate)) - 18, Day(Status(CurrentDate)), Year(Status(CurrentDate)))) & "..." & DateToText(Status(CurrentDate))"]



Perform Find [Replace Found Set]

[Monarch Watch]

Ray,

I tried the code you gave me, but it didn't do quite what I expected -- it pulled up a record from 1998 & inserted this in the field: 9/4/1998730797. . . 5/8/03. My only option was to revert the field to continue & once I did I was just left on that record. Any ideas?

[CobaltSky]

Hi Sarah,

from your description it sounds as though there may be one or two things amiss in your script - as it is apparently finding a record on some other criteria (not the inserted date range) and then attempting to insert the criteria into the field after the 'Find' has been performed.

I am attaching an example file which has a copy of the script in it, which shows how it should work. If you have a look at the way the script is set up in it and compare it to yours, hopefully that will help you to get yours functioning correctly.

DateFind.zip

[Monarch Watch]

Hi Ray,

Thanks for the file. I was missing a few parantheses & had a stray perform find function lurking in my script. I got everything straightened out & it works perfectly. I think I understand what we're asking it to do - I was even able to adjust the script to search for invoice dates that are 3-18 months in the past.

Thank you for your help!

Sarah