Jump to content

Find Empty Date Field


This topic is 7245 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7245 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

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