Need Help with Choosing and Syntax for: case() or IF...THEN...ELSE

[dbellinger]

ACCESS 2003 -
First time posting.

I am working on a database and part of it is a budget report. Each month has
a different budget (Jan is 1500, Feb is 200, Mar & May ==> Dec is 500, and
Apr is 1100). I have a field that is calculated from:
=Format$([Invoice_Date],"mmmm",0)
that pulls (as shown) the month out of a medium date field in an Invoice
table. What I want to do is write some snippet that will use that result to
determine the appropriate budget (as above). The field that the "calculated"
month resides in is called month_label and the field that I want the budget
to appear in is called budget_label (even though they are actually text
fields).

I am having trouble getting either case or if...then...else statements to
work. I may be putting it in the wrong place or putting it in wrong. Any help
is greatly appreciated.

 

[Douglas J. Steele]

Try: Choose(Month([Invoice_Date], 1500, 200, 500, 1100, 500, 500, 500, 500,
500, 500, 500, 500)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"(e-mail address removed)"

 

[Dymondjack]

I can't say exactly how to set this up for you without some more example, but
I can give a brief rundown on how it works:

1) Select Case

Select Case Me.Month_Label
Case "Jan"
Me.Budget_Label = "1500"
Case "Feb"
Me.Budget_Label = "200"
Case "March"
...
...
...
Case Else
'If anything else happens, put it here
End Select

Notice the use of the Me. keyword. This refers to the current form/report,
and is followed by whatever control name you are using to read/write values
to that particular control.

The Case statement checks a variable (in this case, Me.Month_Label) and
performs action based on what it finds. The Case Else statement is optional
(though I generally use it for error handling just in case something didnt
show up that was supposed to).


2) IF... THEN... ELSEIF/ELSE

If Me.Month_Label = "Jan" Then
Me.Budget_Label = "1200"
ElseIf Me.Month_Label = "Feb" Then
Me.Budget_Label = "200"
ElseIf ....
....
....
Else
...
End If

Elseif and Else are optional in the If/Then statement. You mentioned that
the Budget_Label is a text field, hence the quotes around each number
("1200", "200", "500"). If you want to enter an actual(calculable) number to
this field, remove the quotes.
Ex.

Case "January"
Me.Budget_Label = 1200


This assumes that the code you are entering is part of the Form/Report. If
this is in a standalone module, the Me keyword will not work, and you will
have to explicitly define what control to use:

Forms!<formname>.Controls!Budget_Label = "1200"


Hope this helps...

-Jack

 

[Beetle]

I would suggest creating a small table to hold your budget amounts like;

tblBudgetAmounts
*************
MonthID (PK)
BudgetAmount (currency field)

The data would look like;

MonthID BudgetAmount
1 1500
2 200
3 500
4 1100
5 500
etc.

Then you could use something like the following to retrieve the appropriate
amount;

DLookup("BudgetAmount", "tblBudgets", "MonthID=" _
& Month([YourDateField]))

That way, when your budget amounts change, you simply change the value
in the table. No need to go back and rewrite any code.

Just as a point of information, there is no medium date field in Access.
A Date/Time field is a Date/Time field. Short Date, Medium Date, etc. are
just ways of formatting the value for display purposes.

 

[Dymondjack]

From a different standpoint, if you are trying to figure out if you have the
actual code in the right place, you would ideally have this in an Event
Procedure of the Form/Report (in this case, I would guess in the OnOpen
event of a report).

To do this, open the Report in design view, hit ALT+ENTER for the properties
window, click on the Events tab, and you will see a list of events related to
whatever part of the report is selected (for the OnOpen event, you will need
to click the square in the upper-righthand corner of the design report
design).

Take a look in the Event list, and you should see the On Open event. Click
in the right end of this row (... box), and you'll get a dialog with three
options. You want the bottom one, Code Builder. When you do this, you
should see the VBA Window open (ALT+F11 will open the window as well).

At that point, you should see this:



Private Sub Form_Open(Cancel As Integer)

End Sub

Between these two lines is where your code should (probably) be, so you
would end up with something like this:



Private Sub Form_Open(Cancel As Integer)
Select Case Me.Month_Label
Case "Jan"
...
...
End Select
End Sub


This will tell your form that when it opens, do whatever code is in this
procedure.

hth
-jack

 

[John W. Vinson]

ACCESS 2003 -
First time posting.

I am working on a database and part of it is a budget report. Each month has
a different budget (Jan is 1500, Feb is 200, Mar & May ==> Dec is 500, and
Apr is 1100). I have a field that is calculated from:
=Format$([Invoice_Date],"mmmm",0)
that pulls (as shown) the month out of a medium date field in an Invoice
table. What I want to do is write some snippet that will use that result to
determine the appropriate budget (as above). The field that the "calculated"
month resides in is called month_label and the field that I want the budget
to appear in is called budget_label (even though they are actually text
fields).

I am having trouble getting either case or if...then...else statements to
work. I may be putting it in the wrong place or putting it in wrong. Any help
is greatly appreciated.

Assuming that you'll still be in business in 2010 (with the economy as it is,
who knows...!?), and that the January 2010 budget might be different from
January 2009 (and that it might be known and of interest before the end of the
year)... I'd really recommend a Budgets table with fields BudgetDate
(Date/time, containing the first of the month) and Budget. You could then use

=DLookUp("[budget]", "[budgets]", "[BudgetDate] = #" &
DateSerial(Year([Invoice_Date]), Month([Invoice_Date]), 1) & "#")

 

[dbellinger]

Thanks for showing both ways!

I can't say exactly how to set this up for you without some more example, but
I can give a brief rundown on how it works:

1) Select Case

Select Case Me.Month_Label
Case "Jan"
Me.Budget_Label = "1500"
Case "Feb"
Me.Budget_Label = "200"
Case "March"
...
...
...
Case Else
'If anything else happens, put it here
End Select

Notice the use of the Me. keyword. This refers to the current form/report,
and is followed by whatever control name you are using to read/write values
to that particular control.

The Case statement checks a variable (in this case, Me.Month_Label) and
performs action based on what it finds. The Case Else statement is optional
(though I generally use it for error handling just in case something didnt
show up that was supposed to).


2) IF... THEN... ELSEIF/ELSE

If Me.Month_Label = "Jan" Then
Me.Budget_Label = "1200"
ElseIf Me.Month_Label = "Feb" Then
Me.Budget_Label = "200"
ElseIf ....
...
...
Else
...
End If

Elseif and Else are optional in the If/Then statement. You mentioned that
the Budget_Label is a text field, hence the quotes around each number
("1200", "200", "500"). If you want to enter an actual(calculable) number to
this field, remove the quotes.
Ex.

Case "January"
Me.Budget_Label = 1200


This assumes that the code you are entering is part of the Form/Report. If
this is in a standalone module, the Me keyword will not work, and you will
have to explicitly define what control to use:

Forms!<formname>.Controls!Budget_Label = "1200"


Hope this helps...

-Jack

ACCESS 2003 -
First time posting.

I am working on a database and part of it is a budget report. Each month has
a different budget (Jan is 1500, Feb is 200, Mar & May ==> Dec is 500, and
Apr is 1100). I have a field that is calculated from:
=Format$([Invoice_Date],"mmmm",0)
that pulls (as shown) the month out of a medium date field in an Invoice
table. What I want to do is write some snippet that will use that result to
determine the appropriate budget (as above). The field that the "calculated"
month resides in is called month_label and the field that I want the budget
to appear in is called budget_label (even though they are actually text
fields).

I am having trouble getting either case or if...then...else statements to
work. I may be putting it in the wrong place or putting it in wrong. Any help
is greatly appreciated.

 

[dbellinger]

Thanks, Beetle. I understood that month/date fields are formatted a certain
way, I just didn't know if it would make a difference in t he code.

Also, thanks for the code snippet, worked beautifully for my project. I had
initially wanted t o set it up this way but had given up on it.

I would suggest creating a small table to hold your budget amounts like;

tblBudgetAmounts
*************
MonthID (PK)
BudgetAmount (currency field)

The data would look like;

MonthID BudgetAmount
1 1500
2 200
3 500
4 1100
5 500
etc.

Then you could use something like the following to retrieve the appropriate
amount;

DLookup("BudgetAmount", "tblBudgets", "MonthID=" _
& Month([YourDateField]))

That way, when your budget amounts change, you simply change the value
in the table. No need to go back and rewrite any code.

Just as a point of information, there is no medium date field in Access.
A Date/Time field is a Date/Time field. Short Date, Medium Date, etc. are
just ways of formatting the value for display purposes.
--
_________

Sean Bailey

ACCESS 2003 -
First time posting.

I am working on a database and part of it is a budget report. Each month has
a different budget (Jan is 1500, Feb is 200, Mar & May ==> Dec is 500, and
Apr is 1100). I have a field that is calculated from:
=Format$([Invoice_Date],"mmmm",0)
that pulls (as shown) the month out of a medium date field in an Invoice
table. What I want to do is write some snippet that will use that result to
determine the appropriate budget (as above). The field that the "calculated"
month resides in is called month_label and the field that I want the budget
to appear in is called budget_label (even though they are actually text
fields).

I am having trouble getting either case or if...then...else statements to
work. I may be putting it in the wrong place or putting it in wrong. Any help
is greatly appreciated.