Filter criteria by date

[Xine]

I need some advice:

- My "tbl Donations" has a "year" field (YYYY) and a "month" field (based on a lookup of Jan, Feb, Mar....).
- All records in the table have a year value, but only some of the records have a month value.
- My date filtering/sorting (until now) have been based on year only and that's usually all I need except...



For this one task, I'd like have a Year + Month filter/sort that will still return values, even though month isn't in numerical format at present, AND will still include values where the Month is null if so long as the Year is within range.

What's the best way to go about this mess?

[orange]

The best way to handle Dates is to use a Date/Time datatype. That is the purpose of that datatype.

What exactly is the status of this database? Design, development, operational/production??

[Xine]

I started this database 5 years ago to better organize our Foundations donation history which previously lived as an incomplete history on Excel with 3 sheets showing 3 different sorts of the same information. I now have 20 years of data, but some of the details of the older donations are unknown (like the month the donation was made for example). When I started out, I thought I knew all the pieces of the database but as my job has evolved, I've added new features so it's never been complete but it does work. My job is changing once again taking me away from Foundation matters so I need to start implementing user interface so I'm getting quite the mental workout as of late.

This particular question relates to returning donation entries within a certain date range in order to find/add/change cheque numbers issued by the finance dept.

[orange]

I suppose the place to start is to show us the tables and relationships of what you have.
Go to the relationships window, expand the tables so you can see all fields. Then make a jpg of the screen and attach it to a post.
Readers will have to "understand your database" before offering focused comments.
Good luck.

[Xine]

I'm not sure a date/time datatype field is helpful if not all my records have months. I'm thinking I have to store the values of the months in numerical format in the table rather than trying to make a temporary conversion in a query. It just seems like a lot of work for this one instance.

[Xine]

Well I just bit the bullet and switched the month format to numerical in the table source. It will now query with or without month info.