Date range find

[sob]

i'm having trouble with finding invoice records without a certain date range.

i have 2 global date fields in a "Totals" layout. they are "Start Date" and "End Date".

i'm not sure what kind of relationship to set up between the "Totals" table and the "Invoices" table for that date range find to work. I have it set up a relationship for "Totals::ID = Invoices::ID".

In the "Totals" layout i also have a portal set to show records from the "Invoices" table it show just about every field.

I have a scipt attached to a button that will take the start date and end date, and perform a date range find and it looks like this:

Enter Find Mode []

If [ not Is Empty ( Totals::Start Date ) or isEmpty ( Totals::End Date )]

Set Field [invoices::Date; GetAsText ( Totals:: Start Date ) & "..." & GetAsText ( Totals::End Date )]

Else

Show All Records

End If

Perform Find []

I've looked at several posts and can't find what i'm looking for and i can't find my problem. the search does perform it just says no results were returned. But on a search where results should come back it says the same thing. for example, i have an invoice created 6/29/2005 and do a search for "1/1/2005...7/4/2005" and nothing returns. Any help would be greatly appreciated.

Nick.

[-Queue-]

Perform your find in a layout based on Invoices, not Totals. Also, your logic is a little off.

Allow User Abort [Off]

Set Error Capture [On]

If [not (IsEmpty(Totals::StartDate) or IsEmpty(Totals::EndDate))]

Enter Find Mode [ ]

Set Field [invoices::Date; Totals::StartDate & "..." & Totals::EndDate]

Perform Find [ ]

If [not Get(FoundCount)]

Show All Records

End If

Else

Show All Records

End If

Note that 7 does not require GetAsText here. It converts dates as necessary.

[sob]

ok i have created a new layout that is based on Invoices and changed the script to reflect yours. the new layout is not using a portal its the columnar/list layout template. the date start and end are in the header and so is the find button. it will report results but not the right results it just shows all records all the time. any clue whats going on? also in the old layout i changed it to be based on Invoices. but the script still returns nothing in the portal. i'm so stumped. i'm a filemaker noobie =).

[-Queue-]

Verify that your Date field is actually a date field and not text.

[sob]

yes the invoices field is a date field aswell as the start and end date fields they are set to global. don't know if that has anything to do with it. i just read somewhere that they should be globals =x.

[sob]

i found out the problem. it was with the IF statement in the script. the script now looks like this:

Allow User Abort [Off]

Set Error Capture [On]

#?= does not equal symbol. won't show up in the forum =P.

If [Totals::StarDate ? "" & Totals::EndDate ? ""]

Enter Find Mode []

Set Field [invoices::Date; Totals::StartDate & "..." & Totals::EndDate]

Perform Find []

Else

Show All Records

End If

thx for the help -Queue-

[-Queue-]

Yes, <> "" should perform the same as IsEmpty(field) that I have in the script above and you had originally. The only problem with yours was that the 'not' did not include the full test, only the first field. Though in your test, & should be AND.