Brand new user having searching problems...

[Aeriol]

Well, I have searched this forum, the missing manual and AW help but can't find the answer.

With much pulling of hair I have managed to set up a database with 25 columns and 550 records for my husband's business. Now I'm trying to set up searches and monthly reports with no experience at all.

I'm having trouble searching a date field by month. For example I have a date field in this format: 00/00/00 consisting of 3 years. How do I write a search/formula to say just find all the records for July? I am coming to AW from MSWorks4 (mac) where it was called a filter and was easy to do. For the life of me I can't seem to figure this out.

Aeriol

[haumann]

Well, I have searched this forum, the missing manual and AW help but can't find the answer.

With much pulling of hair I have managed to set up a database with 25 columns and 550 records for my husband's business. Now I'm trying to set up searches and monthly reports with no experience at all.

I'm having trouble searching a date field by month. For example I have a date field in this format: 00/00/00 consisting of 3 years. How do I write a search/formula to say just find all the records for July? I am coming to AW from MSWorks4 (mac) where it was called a filter and was easy to do. For the life of me I can't seem to figure this out.

Aeriol

Select "Match" from the "Organize" menu. To highlight all records having a date entry of July, enter the following formula: MONTH('Date')=7

and click OK. Go back to the "Organize" menu and choose "Hide Unselected" to hide all the records that do not have a July date. Note that the above formula assumes your date field is named, "Date". Use whatever name you've given that field (you don't have to type it, just select it from the list of your field names in the "Match" dialog. It will insert the needed single quotes automatically).

John@was

[Barry]

Well, I have searched this forum, the missing manual and AW help but can't find the answer.

With much pulling of hair I have managed to set up a database with 25 columns and 550 records for my husband's business. Now I'm trying to set up searches and monthly reports with no experience at all.

I'm having trouble searching a date field by month. For example I have a date field in this format: 00/00/00 consisting of 3 years. How do I write a search/formula to say just find all the records for July? I am coming to AW from MSWorks4 (mac) where it was called a filter and was easy to do. For the life of me I can't seem to figure this out.

Aeriol

Select "Match" from the "Organize" menu. To highlight all records having a date entry of July, enter the following formula: MONTH('Date')=7

and click OK. Go back to the "Organize" menu and choose "Hide Unselected" to hide all the records that do not have a July date. Note that the above formula assumes your date field is named, "Date". Use whatever name you've given that field (you don't have to type it, just select it from the list of your field names in the "Match" dialog. It will insert the needed single quotes automatically).

John@was

John's formula also requires that your 'Date' field is a Date Type field.

If you want to use the Find (shift-command-F) command rather than match, you can add a field which extracts the MONTH or the MONTHNAME from the date in a Date type field, then use the new field to enter your search criterium. The formulas are:

MONTH('Date') to obtain the number of the month
MONTHNAME(MONTH('Date')) to obtain the name of the month

One other change I would strongly suggest is changing your System date preferences to include "Always show century". While this requires typing an extra pair of digits in each date entry, it avoids the necessity for AppleWorks (or the OS) to guess whether your two-digit year is in the 20th or 21st century (and allows you to use dates outside the 100 year range from--approximately--1981 to 2080).

Regards,
Barry

[Aeriol]

I tried the Match and it does work but when I try to use it in a named search so I can set up a few reports, it doesn't. Do I have to write the search in some other format?

This is my Match term:

AND(MONTH('COMPL')=7,YEAR('COMPL')=2005)


How do I write this for a named search in the "Compl" field/column?

[Barry]

I tried the Match and it does work but when I try to use it in a named search so I can set up a few reports, it doesn't. Do I have to write the search in some other format?

This is my Match term:

AND(MONTH('COMPL')=7,YEAR('COMPL')=2005)


How do I write this for a named search in the "Compl" field/column?

Try recording a named search with two requests.
(Use your own numeric date format--my example uses my setting of yyyy/mm/dd)

New Search
Name the search
Click on 'Compl'
Type <2005/07/01
Click Find from All
Check Omit
Press command-R for a second request
Click on 'Compl'
Type >2005/07/31
(All and Omit should still be selected/checked)
Click Store

Regards,
Barry

[Aeriol]

Sorry for not answering sooner, I took a long weekend. That did the trick and thanks again.