Open a report for a specifice close month. (1 Viewer)

[mtp]

I have a table of a closemonth (the dates for every closemonth are different. Like close month for Oct: 10/20/00, and for Nov: 11/17/00 and so on till the year 2001). I have problem when running the query that know what month should I want to open the data for that month (This program can't use a list of choosing closemonth). I want to run a report for 11/17/00 but it open the one in 10/20/00. I tried every possible criterias (DateAdd, DatePart etc.) but still couldn't get what I want. I think I can't use the DateAdd or DatePart to do because the closedate in every months are different. Any help would be appreciate!

 

[Richie]

Need a bit more info on exactly what you are trying to do. Are you saying that in your example if the close date is for Nov that you wan't records between this close date and the last close date and why are you storing the close date in a table?

 

[mtp]

The table in data (FCLOSEMONTH has 3 fields: CloseDate, CloseMonth, CloseYear)is stored all the close dates for every month. And a table Date Range (has 6 fields: CloseDate, Previous Close Curr Yr, Current Close Prev. Yr, PClose Prev. Yr, Curr Yr. Begin, Pre. Yr Begin) is stored the date/time. I have a query (Make Date Range) to run the date in Macro. When running the Macro, first it starts open the query Make Date Range using the command Create Date Range Table. when the month comes the program should know what close date to do the report (using the tables). Or more clear, this's a program that run a report every month. And not every month has the same close date. How does it know the month, date and year to run the report?

 

[Richie]

I'm not quite sure about you first table structure but I assume that you have close dates entered for for all the dates you require fomatted as posted in which case why do you have two more fields duplicating the months and years from these dates? If this table is storing all the close dates you do not need another table to store information you already have

[This message has been edited by Richie (edited 12-08-2000).]