Complicated Budget Tool created in Excel trying to inc. into Access

[kmims44]

I have been asked to try to simplify this "budget tool" and incorpate it into Access and I'm not sure where to start because there are SO MANY calculations and data. Don't have ZIP on this computer so I can't attach the file but I can email it to somebody if I get an email address...This is a new job so I'm desperately in need of help.

[Robeen]

I did a project a couple of years ago where one of the requirements was to replace a spreadsheet with a Form.
We eventually got it to work, but I'll tell you now - all those formlas [ok formulae - ] . . . if you have a complex spreadsheet - you are going to have a painstaking job trying to replicate the spreadsheet.

Do you have any specific questions about how to proceed?

To begin with - make the Form match the spreadsheet as far as number of columns, headers etc.

Next - you could use the 'After Update' event of each field in the Form to perform the calculations/actions that the spreadsheet performs.

It would be a really good idea to really understand everything the spreadsheet is doing before you start programming.
Remember . . . cells way across the spreadsheet react to cells further to the left . . . so your AfterUpdate event for each field in the Form will have to do EVERYTHING that Excel does when a cell is updated . . .

It didn't take me long to figure out why there is a Microsoft Access and a totally different application -> Microsoft Excel.
Good luck!!


Do one thing at a time and get it working.
Don't worry about getting 'the whole thing' done. Just do one field [Cell] at a time and test it . . . and then move to the next field.

Let us know how it's going!

[kmims44]

ok we have up to 3 prices per part number, the table that has the part numbers has 3 seperate columns to identify them, now what i want to do is create a field that finds the lowest price among the 3 (GREATER THAN ZERO) for some reason I cant seem to wrap my brain around how to write this in access instead of excel

[Robeen]

1. There could be a 0 in one or more of the price fields?
And you want to find the lowest number - greater than 0 - out of those three price fields?
Where are you trying to create the field that identifies that lowest price?

Are you trying to do this in a query, in a Form . . . in the Table itself [not a good idea].
?

[kmims44]

i was attempting to do it in a query and yes xero could be in more than 1 field

[Robeen]

Ok.
You could try using the IIF() function in a new field in your query.

[kmims44]

I did try I'm obviously not setting up the formula correctly

[Robeen]

What if all three are zero?

[Robeen]

hmm . . . tricksy one it is.

I'm working on it.

[Robeen]

I'm thinking of putting this in a new query field to call a function - and then do the logic in the function:
Lowest_Price: Find_Lowest_Price([Price1],[Price2],[Price3])
Not sure if you've used functions before . . . if you have perhaps you could try creating your own function. Let me know.

I'm still working on it.

[Robeen]

Ok . . . try this.
If you don't already have a Module, you'll have to create one.
Then - type in "Option Explicit" at the top.

Then - paste in the following [you can take out the comments - they were to help me keep track of the logic as I was going]:

Function Find_Lowest_Price(Price1 As Currency, Price2 As Currency, Price3 As Currency) As Currency

If Price1 = 0 Then
    If Price2 = 0 Then
        If Price3 = 0 Then
            Find_Lowest_Price = 0                       'All three 0.
        Else
            Find_Lowest_Price = Price3                  'Price1 = 0, Price2 = 0, Price3 > Zero.
        End If
    Else                                                '*** Price1 = 0, Price2 > Zero.
        If Price3 = 0 Then
            Find_Lowest_Price = Price2                  'Price1 = 0, Price2 > 0, Price3 = 0.
        Else                                            '*** Price3 > Zero
            If Price2 > Price3 Then
                Find_Lowest_Price = Price3
            Else
                Find_Lowest_Price = Price2
            End If
        End If
    End If
Else                                                    '*** Price1 > 0
    If Price2 = 0 Then
        If Price3 = 0 Then
            Find_Lowest_Price = Price1                  'Price1 > 0, Price2 = 0, Price3 = 0.
        Else                                            'Price1 > 0, Price2 = 0, Price3 > 0.
            If Price3 > Price1 Then
                Find_Lowest_Price = Price1
            Else                                        '*** Price3 < Price1.
                Find_Lowest_Price = Price3
            End If
        End If
    Else                                                '*** Price1 > 0, Price2 > 0.
        If Price2 > Price1 Then
            Find_Lowest_Price = Price1
        Else
            Find_Lowest_Price = Price2
        End If
    End If
End If

End Function

Now - when you run your query - if you've put a field in there that calls this function:

Lowest_Price: Find_Lowest_Price([Price1],[Price2],[Price3])

the field will display a 0 if all three are 0's - OR - if there are two 0's - it will show the one that is not 0 - and if there are two or more prices greater than 0 - it will show the smallest one.

Let me know if this works.

[kmims44]

How do I call that function in a field? Am I supposed to create a new field in the table that has that data? I'm confused as to where to put the field and how run the module within it.

[Robeen]

Yes - you create a new field in your query - with this in it:

Lowest_Price: Find_Lowest_Price([Price1],[Price2],[Price3])

When the query runs and it sees this:
Find_Lowest_Price([Price1],[Price2],[Price3])
. . . it 'knows' that there is a function called 'Find_Lowest_Price()' and that the function needs [Price1],[Price2],[Price3] in order to work.
The new field will have 'Lowest_Price' as its header.

Just follow my instructions and you'll see how it works.
Let me know if you have other questions.
All the best.

[kmims44]

GOT IT! THANKS SO MUCH for your help...I got another problem now maybe you can help with
I am attempting to create a form that has the following: multiple combo boxs to select "EmployeeAlias" (which is just first inital and last name of the employees) to the right of each combo box I have text boxes labeled "Hours Needed" where the user can enter a numerical value. and box to the right of that that calculates "LaborRate*Hours Needed". I have succussfully accomplished 1 but I want the user to be able to select a different employee on another line below the first and for it to calculate the cost based on that employees personal rate separately.