Date application in Ms Access parameter queries

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.

Regards

Chris
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
"02/2018" is not a date, but you can convert it to obtain the first and last day of the month:

DatePrimo = CDate("02/2018")
DateUltimo = DateAdd("d", -1, DateAdd("m", 1, CDate("02/2018")))

Open in new window

0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Query One  <01/02/2018 ( this means that my query will  pick data before 1st February 2018)

for dates comparison in SQL, always format your date in mm/dd/yyyy (or yyyy/mm/dd) format instead of dd/mm/yyyy.
0
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
I'm asking about convert dates to month/year from a control like Payrolldate (01/02/2018) and (28/02/2018)

Regards

Chris
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
to convert date into text format, use Format function.

MS Access: Format Function (with Dates)
https://www.techonthenet.com/access/functions/date/format.php

in your case you want to compare values in mm/yyyy:

newDateFormat = Format(yourControl.Value, "mm/yyyy")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
That will be Format(Date, "mm\/yyyy") but you can't use that for anything else than display.

Use true date values to filter on. As I showed above.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.