Query based on combobox selection of month and year

[vicissitude]

I wonder if someone could help me with this.

I have 2 combo boxes on a form. One with Month and one with Year.

I am trying to get a query to look at the combo boxes and return values from a table and thus generate a report.

Table has amongst others, these fields; [Name], [StartDate], [LeaveDate], [Status]


I need the query to return values which:

1. Include persons who have a [StartDate] which is before or equal to the form combobox month and year (included within the combobox month)

2. Reject persons who have [LeaveDate] which is After combobox month/ year date but includes those who have a [LeaveDate] within the combobox month.

3. And if possible also include persons who have [Status]= Current. This however may interfere with a person being included in combobox month if [LeaveDate] is within that month, but useful if someone forgets to fill in [LeaveDate].



Where I am stuck is.... I have these query fields but i do not know how to combine them to get what I want. Do I use DateDiff? Do I subtract one from the other? Do I need to combine month and year first? format it into something access can recognise and then subtract one from other?.......


Query Field; StartDateYear: year([StartDate])

Query Field; StartDateMonth: Month([StartDate])

Query Field; LeaveDateYear: Year([LeaveDate])

Query Field; LeaveDateMonth: Month([LeaveDate])

These are my criteria pointing to form comboboxes (SelectMonth returns month number. year is 4 digit)

(NZ([Forms]![Training_Hours_Main].[SelectYear]))
(NZ([Forms]![Training_Hours_Main].[SelectMonth]))

Any help would be greatly appreciated.

 

[namliam]

Use the dateserial function to convert the month and year back to a real date, then base you query of that... When working with dates, work with dates, not with months and years.

 

[vicissitude]

Thanks for the pointers!

Would you have any ideas about how i would say:

Include [LeaveDate] if within SelectedDate month?


Thanks!

 

[vicissitude]

Ok I think i got it.


Convert everything back to normal date format as you said Mailman and then do a iif(DateDiff ("m", [LeaveDate], [SelectedDate]))=0

Thanks for the help!

 

[namliam]

No no no....

You are totaly negating my advice...
When working with dates, work with dates, not with months and years.

When working with dates, work with dates, not with months and years.

When working with dates, work with dates, not with months and years.

When working with dates, work with dates, not with months and years.

When working with dates, work with dates, not with months and years.

When working with dates, work with dates, not with months and years.

When working with dates, work with dates, not with months and years.

Yes you are 'working' with the real date but then converting it back using Datediff
You need to work with the 'real date' period... not use datediff, yes you need functions but doing this with datediff is plain bad.

Dateserial(youryear, YourMonth + 1, 0)
will return the last day of the selected month, then simply puting a criteria on LeaveDate < Dateserial() will do the trick

However I totally do not understand the requirement of:

Reject persons who have [LeaveDate] which is After combobox month/ year date

Usually you will if someone leaves say in March and your reporting for April not want to see them, while this requirement says you do want to see them.
While someone leaving in May will get excluded while IMHO they should get included.