Control Source problem. (1 Viewer)

[mtp]

Please help!
When printing out the report, I want it automatic appear the closedate of that month (like 11/17/00). What should I put in the Control Source?

 

[Neal]

Where does the close date come from? If you open the report from a form that has a text box containing the date, you set that form and and text box as the control source. I.e. = forms!YourFormName!TextBoxName

 

[mtp]

CloseDate is a field in CloseMonth table. The problem is that I can't use a form to pick a close date. When printing it out, the report has to know the close date for that month. And in the report's design, I did try =DateAdd("m",-1,Date()) It gave the month right but the date. How does it know the right close date and print out (not every close dates are the same for every month)? Please help!

[This message has been edited by mtp (edited 12-14-2000).]

 

[Chris RR]

You need to look up the fiscal month ending date that you have stored in your accounting months table.

In this example, ACCTGMOS_TABLE is my table, and I need Report_Date (a field on my form) to fall between DT_ACCTNG_MO_START and DT_ACCTNG_MO_END. If it does, I pick up DT_ACCTNG_MO_END.

Something like this:
=DLookUp("[DT_ACCTNG_MO_END]","ACCTGMOS_TABLE","([Report_date] >= [DT_ACCTNG_MO_START] ) And ( [Report_date] <= [ORAD_ACCTGMOS_T1]![DT_ACCTNG_MO_END]) ")

 

[mtp]

Chris, here is what I had in my ControlSource:

=DLookUp("[EndOfMonth]","dbo_FCLOSEMONTH","([CLOSEDATE]>=[StartOfMonth]) And ([CLOSEDATE] <= [dbo_FCLOSEMONTH]![EndOfMonth])")

It couldn't accept that much expression in a ControlSource. Did I do it right?
(StartOfMonth and EndOfMonth are two functions I created in Module)

 

[Chris RR]

I think that the problem is in the criteria part of your Dlookup.

In my example, I am telling Access that the record I'm looking for will have my screen date ([Report_Date]) greater than the month beginning date on the table ([DT_ACCTG_MO_START]) and less than the ending date on the table ([DT_ACCTG_MO_END]).

I don't know that you can use functions instead those table fields...what do the functions do?

This isn't too much code for the Expression Builder in the versions I've used (95 and 2000). Unfortunately, I've written some expressions that were MUCH longer than this...

 

[mtp]

The 2 functions are used to print out the previous month. And I got that work out but the program couldn't print out the exact date for previous month's closedate. Any more help would be appreciate.