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(MoEndDate 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]", "tmptblMTDSalesData", _
"[WeekEndDate]= Forms![frmPrintReports]!GetWkMoDate")
FiscalMYear = DLookup("[FiscalYearNum]", "tmptblMTDSalesData", _
"[WeekEndDate]= Forms![frmPrintReports]!GetWkMoDate")
' How to get MAX week end date?
' WEDate = DLookup("[WeekEndDate]", "tmptblMTDSalesData", _
' "[WeekEndDate]= Forms![frmPrintReports]!GetWkMoDate")

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

' Get the Fiscal Year on the Year table (Max sales date needed too?)
FiscalYear = DLookup("[FiscalYearNum]", "tmptblYTDSalesData", _
"[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

[salad]

sara wrote:

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(MoEndDate 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]", "tmptblMTDSalesData", _
"[WeekEndDate]= Forms![frmPrintReports]!GetWkMoDate")
FiscalMYear = DLookup("[FiscalYearNum]", "tmptblMTDSalesData", _
"[WeekEndDate]= Forms![frmPrintReports]!GetWkMoDate")
' How to get MAX week end date?
' WEDate = DLookup("[WeekEndDate]", "tmptblMTDSalesData", _
' "[WeekEndDate]= Forms![frmPrintReports]!GetWkMoDate")

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

' Get the Fiscal Year on the Year table (Max sales date needed too?)
FiscalYear = DLookup("[FiscalYearNum]", "tmptblYTDSalesData", _
"[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

You start off with
Function GetMoQtrYear(MoEndDate As Date)
and then you never use MoEndDate (the passed date value).

Then you use for the filters in Dlookup
Forms![frmPrintReports]!GetWkMoDate
Well, you need frmPrintReports open to get the date. Is GetWkMoDate the
same as MoEndDate...IOW, is MoEndDate the value you want to calc on?

If so, change all references of
Forms![frmPrintReports]!GetWkMoDate
to
MoEndDate

A typical Dlookup would look something like
FiscalQYear = DLookup("[FiscalYearNum]", "tmptblQTDSalesData", _
"[SalesDate]= #" & MoEndDate & "#"

Dates are surrounded by #'s, strings by " or ' and numbers with no
enclosed characters.

From the immediate window you could enter
? GetMoQtrYear(Date())
to test

Then in your form you can pass
Dim var As Variant
var = GetMoQtrYear(Forms!frmPrintReports!GetWkMoDate)

Functions usually return a value; a string, a true/false value, a
number, a date, etc. You really aren't returning anything in your
function since you never assign a value to the function to return.
Here's a function example

'the As Long descibles the type returned.
Public Function SumIt(lngA As Long, lngB As Long) As Long

'SumIt = assigns the values of lngA+lngB and returns
'the summed value to wherever you called it.
SumIt = lngA + lngB
End Function

From the Immediate window you can now enter
? Sumit(1,2)

[sara]

Thanks - so far!

First of all, I can do the function in the "training mode", (lngA,
lngB, etc) but I can't translate it into "real" work.

I've tried what you said here and have some questions and an answer to
your question.

First, The user enters a week ending date (for the report) into a field
on the form called GetWkMoDate. I do put dates on the temp tables, so,
I figured I could use this date to look up the values I need in the 3
temp tables.

I don't understand what to put in the variables in the Function
statement (it would help if it could be explained as, for example:
Function NameofFunction (NameOfVariable1PassedIn As VarType1,
NameofVariable2PassedIn as VarType2, etc. till all passed in are
defined) AS NameofVariable1withFunctionResult,
NameofVariable2withFunctionResult.

The confusion is related, also, to what do I dimension? I want to take
the DATE the user entered, pass it in to the function, and have the
function figure out the Mo, Year, Quarter, QYear and Year (and maybe
the MAX date on one or more of these tables), so I can tell the user
what these values are on the tables. The Mo, Year, Quarter, QYear and
Year are all Integers, and the SalesDate (if I do the MAX thing) is a
Date format.

How do I do this? Maybe I shouldn't even do a function? I also have to
put error handling in so that if the user wants, say, 12/3/05 and the
date on the table is 10/29/05, I can handle the error. I think the way
I have it, DLookup comes up with "Null" because 10/29/05 isn't on the
MTD table.

Does this make any sense? I wish there was a VBA class somewhere. I
have 5 or 6 books and am just not able to translate from simple
examples into the more complex world of reality.

I also tried to run the function with: ? GetMoQtrYear(Date()) after
changing my code and I got "Sub or Function not defined". (I put in As
Date in the Function statement and changed all the references to #" &
MoEndDate & "#" and made sure the form was open with the date typed in.

thank you very much.
sara

[Jana]

Sara:

I am a little confused about what you are trying to accomplish with
this function. It sounds like you are trying to get date values from
your temp tables and then tell your user which records have already
been built. Then, if they need more than what has been built, give
them the option to rebuild the data. Is that correct?

Please clarify so we can steer you in the right direction :)

Jana

[sara]

Yes, basically. Since several users can run reports at any time, it is
possible that the data in the tables needed to run this set of special
weekly reports will be for the wrong week.

SO, I want to take the date the user entered, and look up in the tables
to see if the date is there and tell them - if it's error number 94 -
the date isn't there.

If the date is found, tell the user all the important info - to make
sure it's what they want (the reports will reflect the correct data).

I can do this ALL with in-line code, but I think it's something I may
do again and again, so I wanted to build a function, which I can reuse
(Good practice, right? - I'm still VERY new at all this and want to
learn and do it right).

I ALSO want to be able to tell the user the MAX date on each table as
10/29/05 and 10/22/05 are both in Q3, FY 2006, BUT if the table ha
10/29/05 data and the user wants to rerun 10/22/05 report, the totals
will be wrong, since my DLOOKUP will work, will find 10/22/05, but it
won't tell the user that the tables have too much data and need to be
recreated.

Is this clear now? I so appreciate the help - I am the only
"programmer" type here (and am learning on my own). I fought for 2
years to get PC in the office and then a person to support them and
build a network. Retail - it's very slow accepting technology on any
level.

Thanks.
Sara

[Jana]

Sara:
OK, after some long and hard thought, here's what I've come up with:

Function RecreateData(MoEndDate As Date) As Boolean
' 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 MaxWEDate As Date ' Max Week End date on MTD Table
Dim MinWEDate As Date ' Minimum Week End date on MTD
Table
Dim MaxQSaleDate As Date ' Max Sales Date on QTD Table
Dim MinQSaleDate As Date ' Min Sales Date on QTD 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

Dim IsMyDtThere As Boolean ' True if chosen date exists in
data
Dim MyMsg As String ' Used to build Message Box text
Dim Response As Variant ' Used to capture user's response
to MsgBox

' Determine if the chosen date exists in MTD table
If Not IsNull(DLookup("[WeekEndDate]", "tmptblMTDSalesData", _
"[WeekEndDate]= #" & MoEndDate & "#")) Then
IsMyDtThere = True
Else
IsMyDtThere = False
End If

' Set function result to True if user's selected date doesn't exist in
' the MTD table and the user needs to recreate the data
If IsMyDtThere = False Then
RecreateData = True
MyMsg = "The Week End date you selected does not exist in the "
& _
"Month-to-Date table." & vbCrLf & vbCrLf & _
"Please rerun the Merch Pct incr/decr to put the right
" & _
"data in the tables before you run the report."
MsgBox MyMsg, vbCritical, "Week End Data Does Not Exist in
Table"
Exit Function
End If

' Get the latest and earliest Week End date in the MTD Table
MaxWEDate = DMax("[WeekEndDate]", "tmptblMTDSalesData")
MinWEDate = DMin("[WeekEndDate]", "tmptblMTDSalesData")

' Get the Fiscal Month and Year in MTD to see if it's the right data
for
' the week end requested to process
FiscalMonth = DLookup("[FiscalMonthNum]", "tmptblMTDSalesData", _
"[WeekEndDate]= #" & MoEndDate & "#")
FiscalMYear = DLookup("[FiscalYearNum]", "tmptblMTDSalesData", _
"[WeekEndDate]= #" & MoEndDate & "#")

' Get the Quarter and Year on the Qtr Table
FiscalQYear = DLookup("[FiscalYearNum]", "tmptblQTDSalesData", _
"[SalesDate]= #" & MoEndDate & "#")
FiscalQuarter = DLookup("[FiscalQtrNum]", "tmptblQTDSalesData", _
"[SalesDate]= #" & MoEndDate & "#")
MinQSaleDate = DMin("[SalesDate]", "tmptblQTDSalesData")
MaxQSaleDate = DMin("[SalesDate]", "tmptblQTDSalesData")

' Get the Fiscal Year on the Year table
' If Max sales date needed, Dim some variables and grab them like
' what was done for the QTD table
FiscalMYear = DLookup("[FiscalYearNum]", "tmptblYTDSalesData", _
"[SalesDate]= #" & MoEndDate & "#")

' Show all dates to the user. If not right, stop the code so they can
' run the data gathering

MyMsg = "The MTD Table has data for Week End dates between " & _
Format(MinWEDate, "mm/dd/yy") & " and " & _
Format(MaxWEDate, "mm/dd/yy") & " and the Fiscal Month " & _
"and Year is " & FiscalMonth & " " & FiscalMYear & vbCrLf &
_
"Fiscal Quarter and Year on QTD Table is " & FiscalQuarter &
_
" " & FiscalQYear & " and the sales dates range from " & _
Format(MinQSaleDate, "mm/dd/yy") & " to " &
Format(MaxQSaleDate, "mm/dd/yy") & vbCrLf & _
"Fiscal Year on YTD Table is " & FiscalMYear & _
vbCrLf & vbCrLf & vbCrLf & _
"Are all of these dates accurate?"
Response = MsgBox(MyMsg, vbYesNo + vbDefaultButton1, "Are These
Dates Correct?")
If Response = vbYes Then
RecreateData = False
Else
RecreateData = True
End If
End Function

Here's what this does:
This function now returns a true/false value to answer whether or not
the user should (or wants) to recreate the data. When you call the
function, feed it the week end date they input on the form. Then, when
it returns the result to your main code, you can stop your code if it
returns True. You would have lines something like this somewhere in
the code for your form:
Dim blnNeedToStop as Boolean

'Call the RecreateData function and set its result to
blnNeedToStop
blnNeedToStop = RecreateData(Forms![frmPrintReports]!GetWkMoDate)

'Check the result and stop if the data needs to be recreated
If blnNeedToStop = True Then
MsgBox "Cancelling your request so that you may rebuild the
data.",vbInformation,"Report Request Cancelled"
Exit Sub
End If

Note the usage of the DMax and DMin functions to obtain low and high
values for the dates in the MTD Table.

***DISCLAIMER*** I have NOT tested this by setting up the 3 tables with
appropriate fields and data, so this may not be the end all, be all
version of your function.

A little guidance for you: The whole purpose of a function is to
return a value after it is called, which is what we've got now. It
tells your main code whether or not the data needs to be recreated.
Also, the use of DLookup on a table with multiple records will give you
a random result from that table. So, if your QTD and YTD tables have
more than 1 possible value for the Fiscal Month and Fiscal Year, etc,
you cannot guarantee what will be returned by DLookup, only that
something WILL be returned if the criteria matched.

I made an assumption (pardon) that, if the Week End date they entered
didn't exist in the MTD table, that they should recreate the data and
don't really need to know anything more about what's in the tables. If
that is incorrect, you will need to adjust the code accordingly.

Some questions to ask you, since I don't know much about the data
creation process you've written. Is it possible that the WEDate DOES
exist in the MTD table, but DOES NOT exist in the QTD and/or YTD
tables? If yes, then you will need to take a look at the criteria for
your DLookups, because they will only work if the date exists in the
QTD and YTD tables. Do these tables only contain ONE month, ONE
quarter, and ONE year at a time, or could they have multiple periods?
If they only have one, then you can just eliminate the criteria in the
DLookups because it won't matter which record gets returned. If they
have multiples, you'll need to come up with a way to ensure that all is
as it should be and that it will always return a value.

Hope this gets you going in the right direction...
Whew!
Jana

[sara]

I'm going to work on this; it's a lot to digest. Thank you so very
much.

I'll read, learn, code and test, and then reply. Probably tomorrow, is
my guess.

Again, thanks!
Sara

[sara]

So far so good. I've learned a few things - DMax and DMin, along with
info on functions. I still have more work/testing to do, so I hope you
can hang in with me for a few more days. I think the most important
thing I've learned is that to do what I (often) want to do, using a
function, I need to do all the work in the function and send back just
a "pass/fail" to the code for the form, and then decide what to do.
I've been re-reading info I have on functions with this in mind - and
it seems to make sense.

The other way to use a function (the way it's described in most
books/help) is to return a value, say, after a calculation. I haven't
needed that, but I think I understand it. We'll see if I ever need to
use it.

Meanwhile, I continue to read, study and try to code this properly. I
greatly appreciate your help, along with the additional comments, like
about DLookup. I have to work on that one, too. Thank you again -
I'll be back in touch within a few days.

Sara

[Lyle Fairfield]

sara wrote:

Meanwhile, I continue to read, study and try to code this properly.

That's the most enjoyable part.

And the part that is likely to make you most successful.

And the part that is likely to cut you most slack here on CDMA.

So, IMO, it's a good plan!

[Jana]

Sara:

Just read your responses, glad to know you're digesting!! Here's
hoping you don't get ulcers :)

Your assessment of function utility is correct--use it when you want to
return a value, whether it be a calculated number, a string, or a
boolean. For instance, in my database I have fields for First Name,
Last Name and Middle Initial. Rather than always having to use an
expression every time I want the user's full name, I have a custom
function that I feed the name fields, and it spits back out the full
name. With my FullName function, I can also take into account that a
record may not always have a middle initial. As you progress in your
coding ability, you will find more and more uses for custom functions.

Happy coding, I'll continue to monitor this thread to see if you have
any questions.

Jana

[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