Jump to content
Sign in to follow this  
falkaholic

date range find; not working... batman!

Recommended Posts

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

tom@tgparker.com

http://www.tgparker.com/filemaker

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

HTH,

Dan

Share this post


Link to post
Share on other sites

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

tom@tgparker.com

http://www.tgparker.com/filemaker

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.