date range find; not working... batman!

[falkaholic]

I'm making an invoice report. I'm making find a range of date to show totals etc.

I've made a new layout with global date fields for the user to enter the start and end date.

The script to preform the find goes a little somthing like this:

Enter Find Mode []

Insert Calculated Resault [ select, Date, "gDate1 & "..." & gDate2]

Preform Find[]

Go To Layout ["summary"]

Sort [restore]

Enter Brose Mode.

when i run this with vaild dates FM said it couldn't find any records.

thanks for any input

[falkaholic]

Tom,

thanks,

but that doesn't seem to work either.

I bet it something i'm doing wrong at my end. Could it have somthing to do with date formats being different?

[JPaul]

quote:

---

Originally posted by eric f:

I'm making an invoice report. I'm making find a range of date to show totals etc.

I've made a new layout with global date fields for the user to enter the start and end date.

The script to preform the find goes a little somthing like this:

Enter Find Mode []

Insert Calculated Resault [ select, Date, "gDate1 & "..." & gDate2]

Preform Find[]

Go To Layout ["summary"]

Sort [restore]

Enter Brose Mode.

when i run this with vaild dates FM said it couldn't find any records.

thanks for any input

---

Hi Eric,

the following piece of code does work fine in my searches, then i have corrected your code as follows:

Insert Calculated Result [ select, Date, DateToText(gDate1) & "..." & DateToText(gDate2)]

Where Date is the real date field in which perform the search, while gDate1 and gDate2 are the two DATE type globals used to store the user typing.

Have a nice job and regards

[tlsparker]

Can you manually enter find mode, type the date range in the date field, i.e. 01/01/2002...01/31/2002 and find the dates that way?

If so, try placing a pause in the script after the paste command to see what is going into the field (standard script debugging technique). Make sure the global fields are of type text. If nothing works, email me the file and I'll look at it real quick.

Tom

[email protected]

http://www.tgparker.com/filemaker

[falkaholic]

OK. now this is getting weird (which I'm sure is my fault)

anyway.. manual find works great.

using DateToText() doesn't work. weither the global is a text or date; or if i use Insert Calculaton[] or Set Field[]

I'm going to go bang my head against the wall for a bit.

for the record i'm useing FM 5.5v2

[falkaholic]

Looks like banging my head agaisnt the wall worked!

I changed the Date in the invoice to text. Which i should have done along time ago (and i think someone suggested).

So thanks for all you help people!

[danjacoby]

Instead of "InsertCalculatedResult", I'd use the "SetField" command. For some reason, those "Insert..." commands are unreliable.

HTH,

Dan

[falkaholic]

hmm. i was under the inpresssion that Set Field [] wouldn't work at all.....

but i'll give it a try.

[falkaholic]

it seems that no matter what i do. it only finds one record.

Can anyone think of anything else?

[tlsparker]

I think there are a few ways to solve this, but here is what I have used in the past:

Create three global text fields: gDate1, gDate2, and gDateText. Users enter the start date in gDate1 and the end date for the range in gDate2. In your script:

set field["gDateText","gDate1&"..."&gDate2"]

Go to Field[select,perform,"gDateText"]

Copy

Enter Find Mode

Go to Field[select,perform,"Date"]

Paste

Perform Find

This method has always given me the best results for date ranges because of how picky date fields are, although I have been using it since 4.0 and there may be more ways to do it now. Here, you simply simulate typing the range into the field.

Tom

[email protected]

http://www.tgparker.com/filemaker

[Thom]

You should NOT change the invoice date field to text. Searching for a range of dates will not work on a text field, unless the dates are formatted as YYYY-MM-DD with leading zeroes for single digit months and days.

Insert Calculated Result[] works well, but the date field must be on the current layout. Set Field[] will not work, because a range is not a valid date.

Try the following script (the data type of all fields is date):

Go to Layout[layout with invoiceDate field]

Enter Find Mode []

Insert Calculated Result[select, invoiceDate, DateToText(gDate1) & "..." & DateToText(gDate2)]

Pause[]

Perform Find[]

Go to Layout[display results layout]

The script expects that the user has entered dates in gDate1 and gDate2.

The Pause[] will stop the script so that you can confirm that the date range is entered properly in invoiceDate. Once you see that the script works, remove the Pause[] step.

[falkaholic]

arrrggghhh!

i changed back and it didn't work.

BUT i found out why, cuz i was using MM/DD/YY instead of MM/DD/YYYY so it was looking for 1902!!! *******. should have thought of that a long time ago too. that why it wasn't finding any records.

thanks all for your help and pateince