How to Test Function in Immediate Window

[sara]

I am trying my first functions in my code. I have a set of queries
that runs to create temp tables with the right data for some reports.
They can run for a long time, so I want the user to know what is in the
tables (date parameters) before running all the queries. This way, if
the data in the tables is not for the time period the user needs for
reporting, s/he knows to recreate the data.

I just want to find the data in the tables and display it to the user -
probably will do a YES/NO to continue or not.

So, I wrote the following function, and I want to test it in the
immediate window (without running the whole process from the form). I
just can't figure it out. I don't understand the use of "passing
parameters" - when you call the function (which I'll have to do from my
code as well) what do I "pass" to the function and what does that look
like?

I also want to find the MAX Salesdate on the tmptblMTDSales. Can I do
this in DLOOKUP too?

Hope someone can help - it's very confusing to me and Help doesn't
explain for me.

Thanks

Code:
Function GetMoQtrYear(Mo EndDate As Date)
' Find the dates on the Temp Tables. If they are not right, tell the
user to rerun
' Merch Pct incr/decr to put the right data in the tables used in the
dashboard reports

Dim FiscalMonth As Integer ' Fiscal Month on MTD Table
Dim FiscalMYear As Integer ' Fiscal Year on MTD Table
Dim WEDate As Date ' Max Week End date on MTD Table
Dim FiscalQYear As Integer ' Fiscal Year on QTD Table
Dim FiscalQuarter As Integer ' Fiscal Quarter on QTD Table
Dim FiscalYYear As Integer ' Fiscal Year on YTD Table

' Get the Fiscal Month, Year and Last (max) week end date on MTD to
see if it's the
' right data for the week end requested to process
FiscalMonth = DLookup("[FiscalMonthNum]", "tmptblMTDSales Data", _
"[WeekEndDate]= Forms![frmPrintReports]!GetWkMoDate")
FiscalMYear = DLookup("[FiscalYearNum]", "tmptblMTDSales Data", _
"[WeekEndDate]= Forms![frmPrintReports]!GetWkMoDate")
' How to get MAX week end date?
' WEDate = DLookup("[WeekEndDate]", "tmptblMTDSales Data", _
' "[WeekEndDate]= Forms![frmPrintReports]!GetWkMoDate")

' Get the Quarter and Year on the Qtr Table (Max sales date needed
too?)
FiscalQYear = DLookup("[FiscalYearNum]", "tmptblQTDSales Data", _
"[SalesDate]= Forms![frmPrintReports]!GetWkMoDate")
FiscalQuarter = DLookup("[FiscalQtrNum]", "tmptblQTDSales Data", _
"[SalesDate]= Forms![frmPrintReports]!GetWkMoDate")

' Get the Fiscal Year on the Year table (Max sales date needed too?)
FiscalYear = DLookup("[FiscalYearNum]", "tmptblYTDSales Data", _
"[SalesDate]= Forms![frmPrintReports]!GetWkMoDate")

' Show all dates to the user. If not right, stop the code so they can
run the data gathering
MsgBox "Fiscal Month and Year on MTD Table is " & FiscalMonth & " "
& FiscalMYear _
& "Fiscal Quarter and Year on QTD Table is " & FiscalQuarter &
" " & FiscalQYear _
& "Fiscal Year on YTD Table is " & FiscalYear _
& "If any of these is NOT right, rerun Merch Pct to create
Table Data"

End Function

[sara]

Jana -
( and to Lyle, too - your comments kept my energy for this up!)

I'm liking this a lot!!!

I've gotten everything to work - thank you SO very much - but I'm not
done. I:

1* Learned what vbCrLF was - am happily using that.
Basically, I figured that one out myself, but now wanted to see if
there were other "useful" literals, and can't find anything on this in
Help or my several books. Are there others? Is there a place I can
look?

2* Started to put in error handling, and decided to post first, since
this is working. But I wanted you to know that I plan to reorganize
the whole print code to make sure all Dimensions are at the top and
that I am being more consistent with the names of variables. I
'discovered' that if I dim the variable at the top, once, I can use it
in the entire module! Basic, I'm sure, but I guess I didn't ever
really 'get' that. And I'm making sure every sub has error handling,
and that I'm using CASE where I can, rather than tedious IF THEN ELSE
statments for a simple check of a value. Have to figure out what
errors to trap; luckily for me I haven't experienced any yet in my
testing. (Poor testing, that!) Fortunately, we are not in the office
on Monday, so I can spend the day cleaning up all this code.

3* I put in the Min and Max code for Year; it's great. Wasn't sure if
it was really necessary for the user, but it WAS necessary for me to be
sure I really learned all this, so the user gets it! (That's the
privilege I have for being the only 'programmer' in this company) This
is how I learn best - following an example that is real-life, and my
life.

4* It probably took me the longest time to really understand the
setting the function to true when there was a problem. Until I worded
the situation as I just did here, it seemed inverted - 'False' is 'bad'
- so the result should be 'False'. I've got it now!

5* I am also finding other places to use functions. The big thing
here is that I'm not so afraid of them anymore - that's REALLY big for
me.

Again, thanks for bearing with me. I can't find the right words to
express my delight in the success my fingers have had here - or the
gratitude for your time, effort and patience. I don't know if you
needed all this detail, but after all the time you spent on my behalf,
I wanted you to know what I really did with and for your efforts.

Happy New Year.
Until next time -
sara

[Jana]

Sara:

Right now, at this very moment, I am doing a touchdown dance just for
you! :)

1* Learned what vbCrLF was - am happily using that. --You can find information on other constants in VB by searching for
'Visual Basic Constants' in Help.
2* I 'discovered' that if I dim the variable at the top, once, I can use it
in the entire module! Basic, I'm sure, but I guess I didn't ever
really 'get' that. --For more explanation of when/where to Dim a variable, search for
Scope in Help. In addition to module level variables, there are global
variables you can define that will work across different modules.
Global variables can be handy when properly used, but can be a
nightmare to track when debugging. Use them carefully and only when
you really need them, and you'll be fine.
4* It probably took me the longest time to really understand the setting the function to true when there was a problem.
--Easier to understand if you think of the function answering the
question "Do I need to rebuild the data?"
5* I am also finding other places to use functions. --Yay! I knew that once you wrapped your brain around how to use them,
you'd see applications for them everywhere :)
Again, thanks for bearing with me.

--You are quite welcome!!!! I am so very glad I could help you out. I
am a firm believer in the Karma Credit Plan. Since I have found so
many answers in this group, I think it's only fair to try and help
others out when I can. Guess that's what makes a group work!

Have a wonderful holiday season,
Jana