Can anyone help me figure out what i've done wrong?

[K. Reed]

Hello
i am hoping someone can chime in
i am purely just building this budget database for fun, but i want to figure it out dammit!

i have two columns on the same table [Cost] and [Frequency]
one is my cost the other is how often i pay.
it is a dropdown from another table that contains: Weekly, Bi-Weekly, Monthly, yearly

i am trying to build a column or query that will look at those two columns and figure out my monthly requirements


IIf([Frequency]="Yearly",[Cost]/12,IIf([Frequency]="Bi_Weekly",[Cost]*26/12,IIf([Frequency]="Weekly",[Cost]*52/12,[Cost])))

it only seems to return my false result (the cost) i can't get it to do any of the math
this statement layout works in excel, so obviously i am missing something

can anyone tell me what it is??

 

[Andrzejek]

Could that be the issue...

table that contains: Weekly, Bi[highlight #FCE94F]-[/highlight]Weekly, Monthly, [highlight #8AE234]y[/highlight]early
vs
IIf([Frequency]="[highlight #8AE234]Y[/highlight]early",[Cost]/12,IIf([Frequency]="Bi[highlight #FCE94F]_[/highlight]Weekly",[Cost]*26/12,IIf([Frequency]="Weekly",[Cost]*52/12,[Cost])))

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[K. Reed]

No, my typing is poor but the IIF matches the table with Capitals and _

i thought of that too

if this helps
when i put it in my table it just produces the cost
when i put it in my query it gives an #error

 

[Andrzejek]

That looks to me like a good candidate for a User Defined Function.
You just pass a Frequency As String and Cost as Currency, and return (whatever) As Currency.

Public Function GetCostBack(ByRef strFreq As String, ByRef curCost As Currency) As Currency
Dim curReturn As Currency

Select Case strFreq
    Case "Yearly"
        curReturn = curCost
    Case "Monthly"
        curReturn = [red]???[/red]  [green]'curCost / 12 ?[/green]
    Case "Bi-Weekly"
        curReturn = curCost * (26/12)
    Case "Weekly"
        curReturn = curCost * (52/12)
    Case Else[red]
        curReturn = -1[/red]
End Select

GetCostBack = curReturn

End Function

A LOT easier to write and maintain, IMO

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[K. Reed]

Cool

now i will try to figure out how to use it!
thanks!!

 

[Andrzejek]

>how to use it

Like any other Function:
[tt]
Select Frequency, Cost, [blue]GetCostBack(Frequency, Cost)[/blue] As MyCalculatedValue
From MyTable
Where ...
[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[K. Reed]

i haven't used VBA before, i am not sure where to put my column names

strfreq is [frequency] correct?
curcost = [cost]

is that how i would replace them?

 

[Andrzejek]

Please, read the information in the link provided just above the VBA code in my post.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[K. Reed]

i did
it doesn't go into how the code refences the table

i will keep hunting for more in for on that

thanks for your help

OK
so it puts in data, but still not correct.....
i will keep looking into it

 

[Andrzejek]

The code in UDF does NOT "refences the table"
The same as MIN(), MAX(), Count(), or any other Function

>it puts in data, but still not correct.....
Can you decipher the code I gave you?
If the data is not correct, that's where the problem is: in the GetCostBack function

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[K. Reed]

the code looks correct
does the math the way i want
but still only produces the final value none of the others
i modified the code slightly as i think there was one too many in there


Public Function GetCostBack(ByRef strFreq As String, ByRef curCost As Currency) As Currency
Dim curReturn As Currency

Select Case strFreq

Case "Yearly"
curReturn = (curCost / 12)
Case "Bi-Weekly"
curReturn = (curCost * (26 / 12))
Case "Weekly"
curReturn = (curCost * (52 / 12))
Case Else
curReturn = curCost
End Select

GetCostBack = curReturn

End Function


The brackets around the math are my testing as it still only produces the 'ourCost' output

 

[K. Reed]

does it matter that the Frequency column is a drop chosen from another table?

 

[Andrzejek]

only produces the final value none of the others" I don't know what you mean by that...?

If you have this GetCostBack function in the standard Module and (I hope) it works, you should be able to use it in your Select statement.

Could you show me your Select statement where you have Frequency and Cost fields included which creates something like:
[pre]
... ID Frequency Cost ...
2 Yearly 10000
3 Bi-Weekly 5000
4 Yearly 7000
5 Monthly 2000
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

Check what exactly the field [Frequency] keeps. Any spaces, query that displays other than stored values?

combo

 

[Andrzejek]

If combo is right and you have some spaces in [Frequency], you may want to [blue]add this[/blue]:

...
Select Case [blue]Trim([/blue]strFreq[blue])[/blue]
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

final value none of the others"

That's why I would have this instead:

Select Case strFreq
    Case "Yearly"
        curReturn = (curCost / 12)
    Case "Bi-Weekly"
        curReturn = (curCost * (26 / 12))
    Case "Weekly"
        curReturn = (curCost * (52 / 12))[blue]
    Case "Monthly"
        curReturn = curCost
    Case Else
        curReturn = -1[/blue]
End Select

So, if my output would have any values of -1, I would know the [Frequency] is messed up :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[K. Reed]

so, yes it returns -1 when i add that

The Frequency table has two columns
an ID
and the periods (Weekly, Yearly, etc)
all the spelling matches

is it a problem to have the column and table named the same?
i am changing it now just in case..

 

[K. Reed]

OK
so i changed the column from a table reference to a list box
and now the IIF runs the first true (yearly), but any of the others come back as the false

more digging and now they all work EXCEPT a Bi_Weekly (i think)
thank for everyones help!!

i think i'll figure it out now

 

[Andrzejek]

>The Frequency table has two columns
>an ID
>and the periods (Weekly, Yearly, etc)

If your table looks like this:
[pre]
ID Frequency
1 Yearly
2 Monthly
3 Bi-Weekly
4 Weekly
[/pre]
and you use it as a reference to other table, then use its ID to determine the calculated Cost
Dealing with Numbers is a LOT easier and safer than with Text.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson