Hi, I have a portal displaying transactions. I want to filter the transactions so that I'm only looking at the current year.

the records I'm displaying in the portal have a DATE field (the field is imported from a mysql db as mm/dd/yy hh:mm:ss) and the information in my FM db is formatted as mm/dd/yyyy

I'm filtering using the calculation

If ( Transactions:_transDate >"01/01/2012" ; 1 ; 0 )

However this is not filtering out transactions from 2011

What am I doing wrong?

Thanks,

jim

What am I doing wrong?

"01/01/2012" is a text string, not a date. Try =

Year ( Transactions:_transDate ) > 2011

---

BTW, you say the field is a Date field and that the imported data is in mm/dd/yy hh:mm:ss format. That's not a valid format for a Date field in Filemaker, and you might have a problem there.

Edited February 2, 201213 yr by comment

Thanks, your solution works.

===

I thought that text imported into a field defined as Date would make it so. How would you import that data to make it a Date?

OR is there a better way to import this data?

I have another field date_of_birth that is defined as Date (mm/dd/yyyy) and imported from mysql data in the form mm/dd/yyyy - in that case it is a Date?

I thought that text imported into a field defined as Date would make it so.

Yes, if your file is set to use m/d/y as the date entry format, and your text file has dates in the same format. But you have described a format that includes a time element. This would be best imported into a Timestamp field. If the target field is of type Date, the time element will be discarded - or (if Filemaker is unable to make the conversion) the imported data will not be recognized as date.

I see, so my date_of_birth is being used correctly?

I have another field date_of_birth that is defined as Date (mm/dd/yyyy) and imported from mysql data in the form mm/dd/yyyy - in that case it is a Date?

Last question if I import this transaction_date as a timestamp, does it allow for searching or filtering based just on the m/d/y part of the timestamp or must I always include both the m/d/y and h:m:s portion of the timestamp whenever I want to use it? (the Help docs do not cover this very extensively)

Thanks again.

Edited February 2, 201213 yr by jimlongo

When searching, you can leave out the time component, or use a * wildcard.