I would set the search criterion to be:
"<" & ( Get ( CurrentDate ) + 30 )
You were looking for any items expiring in the next calendar month, that is 1/10/2012...31/10/2012
(I never thought that tecnhique would work, so I've learned something!)
In a script:
Enter Find Mode
Set Field [YourTable::YourDateFIeld ; "<" & Get ( CurrentDate ) + 30 ]
Set Error Capture [on]
How about my Expiry_30_Days field in my GUI_Product Table...what will be relationship to give me a correct number of expiring product for the next month? thank you.
I couldn't understand the calculation you had:
If( IsEmpty (Expiry_Date) and not IsEmpty (Expiry_Date) ; 0 ;1 )
...so I assumed it was the script 'Next Month Expiring Product' that you were referring to. When I looked at it it is searching for records where the expiry date is during the next calendar month. I was suggesting you changed the search criterion in it.
I tried to put "<" & ( Get ( CurrentDate ) + 30 ) in my search criteria it gives me all the products that was expired from 2006 - to 2012 (past months.) I used this as my basis "if( IsEmpty (Expiry_Date) and not IsEmpty (Expiry_Date) ; 0 ;1 )" that if the expiry date is empty in my field (Expiry_30_Days) in my GUI_Product table.