Re: Find in a date range

In article
<3ae4e065-eecf-454c-9073-8ffd822f6473@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
buck.matthew74@xxxxxxxxx wrote:

On Feb 26, 9:25 pm, Helpful Harry <helpful_ha...@xxxxxxxxxxxxxxxx>
wrote:
In article
<62bbe55f-fc4f-46aa-a539-32c493bf4...@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,

buck.matthe...@xxxxxxxxx wrote:
FM 8.5

I want to create a script that will select all records in the past
week, past month, this year.
Please help.

Thank you
Matthew

You'll need three Scripts, but they all should be the same as how you
would perform the Find manually ... with a slight hitch that FileMaker
is a little picky about date formats.

Manually to find the "past week" records you would:

   - Enter Find mode

   - Go to the date field and type in the date 7 days ago
     followed by the date range symbol (ie. "...") and
     then "today's" date

   - Finally perform the Find

The equivalent Script would then be something like:

   Enter Find Mode []
   Set Field [DateField,
              GetAsText(Get(CurrentDate) - 7) & "..."
              & GetAsText(Get(CurrentDate))]
   Perform Find []

(Note: Due to a bug in FileMaker, you may need to swap Set Field to
another function in some versions of FileMaker, eg. Insert Calculated
Result in the older versions)

For the "past month" and "this year" are slightly more compliced
because you have to play around with the Date functions of Date, Month,
Day and Year, but the basic idea is the same.

For "past month" you have to subtract a "month" from "today's" date ...
but it really depends whether you mean an actual calendar month (eg. 15
October to 15 November) which may vary in the number of days / weeks or
a simpler "4 week" month.

Assuming you mean a calendar month the calculation part of the Set
Field above has to work out the current month and subtract 1. To do
this it needs to pull apart the date into ots separate day, month and
year parts using the appropriate functions, subtract the 1 month, and
then put the parts back together into a date using the Date function.
eg.
     GetAsText(  
               Date(
                    Month(Get(CurrentDate) - 1), Day(Get(CurrentDate)),
                          Year(Get(CurrentDate))
                   )
              )
     & "..." & GetAsText(Get(CurrentDate))

It's a similar calculation for the "past year", except you are
subtracting 1 from the year instead of the month.

Thankfully you don't have to worry about changeovers from one year to
the previous one (eg. December 2007 ... January 2008) since FileMaker's
date functions will handle this itself.

The past 7 days solution works but the past month (30 day solution
does not work) - it returns the same result as the past 7 days
solution. Any idea why?

Ops! Sorry, that's my mistake. :o(

There's a typo in the Calculation for the "past month" - the "-1"
should be outside the bracket / parentheses because you're wanting to
subtract one from the month, not the date.
eg.
GetAsText(  
               Date(
                    Month(Get(CurrentDate)) - 1, Day(Get(CurrentDate)),
                          Year(Get(CurrentDate))
                   )
              )
     & "..." & GetAsText(Get(CurrentDate))

BUT,
this calculation goes back one whole calendar month, not 30 days. If
you want 30 days you can use the same calculation as the "past week"
and simply subtract 30 instead of 7.




One query I have concerns the day month year format. For some reason
Americans like to use the Month Day Year format (very illogical
Captain) instead of a year month day or day month year format. Does
this matter to the calculation. My date format displays as; 23 - Feb -
2008 .

Nope. Filemaker stores it's dates internally in an independant format,
it's only when being displayed that the date uses the system format /
field formatting options that you have chosen.

But the Date function must have it's parameters in American order.
ie.
Date (MonthNumber, DayNumber, YearNumber)

If you are giving your databases to other people who may have a
different system date format, then you should add a script that is run
on opening (via the Preferences option) that runs the command "Set Use
System Formats [On]". That way any fields using the default formatting
will look as they want on their own computer.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
.

Relevant Pages

  • Re: Find in a date range
    ... I want to create a script that will select all records in the past ... Due to a bug in FileMaker, you may need to swap Set Field to ... Assuming you mean a calendar month the calculation part of the Set ... Field above has to work out the current month and subtract 1. ...
    (comp.databases.filemaker)
  • Time Calculations
    ... I need a calculation to subtract the start time from the finish time when the ... time is in the format of hours:minutes such as Start time as 1:40, ...
    (microsoft.public.project)
  • Re: Measure between date time A to date time B
    ... Subtract the later from the earlier and format the result as either ... calculation. ... Stewart:) ...
    (microsoft.public.excel.misc)
  • Evasion with OLE2 Fragmentation
    ... In the case of file format flaws, we support evasion at every level, including techniques like IP fragmentation, alternate MIME encodings, HTTP compression, and data randomization within the files themselves. ... While working on Strike coverage for MS09-017, we discovered a simple way to bypass mainstream anti-virus and IPS signatures for malicious Office documents. ... In order to detect a file format exploit, the parsing software needs to understand OLE2, locate the correct entry containing the document contents, and parse through that content to locate the specific structure that triggers the exploit. ... Once we modifed the script to use 64 byte writes instead of 512, we only see detection in 7 out of 40 products. ...
    (Pen-Test)
  • Re: Form Protection with Excel Object Embedded
    ... the best field to base the calculation on might be the one in the example titled 'Calculate the # Days Difference ... The date input format is of no consequence, provided both dates have a day, month and year- you might want to enforce this via the ... Excel workbook, though. ... At most, you'll need to insert a paragraph break immediately before the workbook, then format the workbook ...
    (microsoft.public.word.application.errors)