westereo86
asked on
Moving From Access 2010 to Filemaker Pro 12 Convert Query To Script
I am currently using access 2010 and have a Query that i need to replicate in Filemaker Pro 12, Basically i need to be able to specify a start date and end date and have filemaker search by different fields to return the correct results.
Current Access Script is as follows:
SELECT [Former Hillcrest Staff].[Employee ID], [Former Hillcrest Staff].[First Name], [Former Hillcrest Staff].[Last Name], [Former Hillcrest Staff].[Hire Date], [Former Hillcrest Staff].[Termination Date], [Former Hillcrest Staff].Address, [Former Hillcrest Staff].City, [Former Hillcrest Staff].State, [Former Hillcrest Staff].Zip, [Former Hillcrest Staff].[Cell Phone #], [Former Hillcrest Staff].[Birth Date], [Former Hillcrest Staff].[Job Code], [Former Hillcrest Staff].[License Type], [Former Hillcrest Staff].[License Expiration], [Former Hillcrest Staff].[License #]
FROM [Former Hillcrest Staff]
WHERE ((([Former Hillcrest Staff].[Hire Date])<=[Start Date:]) AND (([Former Hillcrest Staff].[Termination Date])>=[End Date:])) OR ((([Former Hillcrest Staff].[Hire Date]) Between [Start Date:] And [End Date:]) OR (([Former Hillcrest Staff].[Termination Date]) Between [Start Date:] And [End Date:]))
thanks in advance
Current Access Script is as follows:
SELECT [Former Hillcrest Staff].[Employee ID], [Former Hillcrest Staff].[First Name], [Former Hillcrest Staff].[Last Name], [Former Hillcrest Staff].[Hire Date], [Former Hillcrest Staff].[Termination Date], [Former Hillcrest Staff].Address, [Former Hillcrest Staff].City, [Former Hillcrest Staff].State, [Former Hillcrest Staff].Zip, [Former Hillcrest Staff].[Cell Phone #], [Former Hillcrest Staff].[Birth Date], [Former Hillcrest Staff].[Job Code], [Former Hillcrest Staff].[License Type], [Former Hillcrest Staff].[License Expiration], [Former Hillcrest Staff].[License #]
FROM [Former Hillcrest Staff]
WHERE ((([Former Hillcrest Staff].[Hire Date])<=[Start Date:]) AND (([Former Hillcrest Staff].[Termination Date])>=[End Date:])) OR ((([Former Hillcrest Staff].[Hire Date]) Between [Start Date:] And [End Date:]) OR (([Former Hillcrest Staff].[Termination Date]) Between [Start Date:] And [End Date:]))
thanks in advance
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Will Loving
cogitive is correct that the simplest way to manage this Find is to create a calculation field, however, it's not necessary to have a special layout with required fields on it, at least not since at least FileMaker Pro 7 and possibly earlier. A Find Records [ ] script step with the singe Find Request for the calc field is all that's necessary unless you want post record processing like going to a list view if there are multiple records or a Form view if there is only one.
ASKER
Thanks for the information, could someone clarify in more detail how to write/setup the calculation field?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.