I have a database that is being used to track classified ads in a weekly newspaper which is released every Thursday. The entry form allows the operator to enter the 'Start Date' and the 'Number of Weeks' and the 'Stop Date' is mathematically calculated. (Each date is also associated with a unique 'Issue Number' so that 11/04 - Issue 19, 11/11 - 20, 11/18 - 21 and so on) Here is where the dilema comes into play. At the end of each week, which actually falls on the preceding Tuesday, I need a way to output all of the classifieds that would be active for that week to a report. I can easily do this for the week in which the classified was entered because that information is stored, but I don't know how to reference classifieds after that initial date. In other words, I need a way to get all classifieds between the two dates and including the 'Stop Date'... something like this:
Active Classifieds = Between [StartDate] & [StopDate] including [StopDate]
I don't know the best way to handle this, so I was hoping you guys could give some suggestions. I've also considered using Date() to create a query criteria similar to this:
[StopDate] = ">= Date()"
This would output all classifieds that are less than or equal to the current date... the only problem is that if the [StopDate] is null (as it may be), the query fails to pull that classified. Any help or suggestions on this would be greatly appreciated and I will certainly provide more examples or a sample copy of the DB if it will clarify my rambling. TIA.
Active Classifieds = Between [StartDate] AND [StopDate] including [StopDate]
First off, your statement is indicating "Greater than OR Equal To" Date() rather than "Less than OR Equal To" Date() (corrected below). The Nz function utilized below will handle Null for [StopDate]. If [StopDate] is Null then it will automatically be considered Current Date.
Ok, this is the database that I have to work with. It was started by someone else and I've been trying to fix it so I can't answer all questions about it. I've been having to hold SHIFT when I open it because the login doesn't work properly. The main form for the entire thing is called "Accounts". In other words, it's a mess and I apologize.