For my payroll I use a form parameter query to generate payslips in simple I filter the dates like below on two separate queries:
(1) Query One <01/02/2018 ( this means that my query will pick data before 1st February 2018)
(2) Query two <=28/02/2018 ( this means that my query will pick data before or 28th February 2018)
Now because of different days in a month when the transactions are keyed in I have been using two date parameters example START DATE & END DATE control boxes. This works very well, but in most cases users tend use for 01/02/2018 to 31/05/2018 which distorts the payslips.
Instead of the above method I want use Months & Year, example <02/2018 and on the other query also <=02/2018 and provide only one control box for date selection on a form. The challenges are as follows:
(1) Is it possible to use DatePart(“M”,”YYYY”[Paydate]) to convert a date like 01/02/2018 to give us 02/2018, if it is, can it filter the transactions as before??????
(2) I want to maintain one date control box on a parameter form only or drop the other one, how do I format this control so that the date entered should appear as M/YYYY (02/2018) like the actual converted date on the filtered underlying query above?
Please note that our payroll is calculated on cumulative basis that is the reason why I use two queries with the date with one behind and the other one ahead and then subtract the two queries to get the actual pay for the month.