Find Empty Date Field

[Daniel Shanahan]

I need help with a Find script. In a DB there are "loanable" items.

There is a "Date Out" field, a "Date Due" field, and "Returned" field.

What I'd like to do is find items loaned during a certain period (eg. Date Out: <10/1/2001

AND

Date In: empty

So far I've created another field called Date Out Text which is a calculation field which = Date Out with the result as a text. This allows me to do a wildcard find (*) and select omit from that layout.

The problem is, I'm trying to do this through a script. It is part of our billing system and I'd like the secretary to simply choose a radio button (1st Quarter, 2nd Quarter, etc) and have the results.

Any ideas?

[kenneth2k1]

Hello:

For what you described, you might want to try this in your script:

Enter Find Mode [ ]

Set Field ["Date Out", "<10/1/2001"]

Set Field ["Date In", "="]

Perform Find

Problem with this is that I think you will have to make a different script for each loan period, since you have to set the "Date Out" field to be "less than" a certain date.

Perhaps someone else might be able to help you with the radio button part, that seems more difficult. You could attach each to a button and call them "1st Quarter", 2nd Quarter" etc.

Let me know how it turns out!

Ken

[Thom]

You can do this without a script by using a relationship.

Create a calc field:

quarter(calc,text) =

Year(dateOUT) &

(Int((Month(dateOUT) - 1) / 3) + 1) &

(not IsEmpty(dateIN))

Create some global fields:

_whichYear(global,text)

_whichQuarter(global,text)

_whichStatus(global,text)

Create another calc field:

whichPeriod(calc,text) =

_whichYear & TextToNum(_whichQuarter) &

Case(

_whichStatus="Out", "0",

_whichStatus="In", "1",

"0

[Daniel Shanahan]

I feel pretty embarrassed! I didn't realize that simply putting an "=" in a date field is like putting a "*" in a text field and selecting omit. I found this out by accident as I was looking at someone else's script on another DB.

Thanks for the help though. I think I have the billing thing down. I've created two global fields. One is gQuarter and has a value list of 1st Quarter, 2nd Quarter, etc. The other is gYear.

Since the dates of the quarter are static, I can include those in the script (eg. First quarter would look like: Date ("1", "1", gYear). I made four such scripts - one for each quarter.

It was a little more involved, but for brevities sake, I think that will suffice.

Again, thanks for the help.