I have a simple query, which consists of a few project dates, which in return need to be charged based on completion.
That said, I wanted to create a CHARGE field which would calculate the total charge based on the projects completed (Estimates & Layouts). Just to make this complicated, there are 2 branches that get discounted charges. Below, I have included the if statements that I desire to take affect, I'm not sure how to implement them into calculating my field. That is, I have no idea where to include these if statements for my report.
If [Branch] = "580" Then
[Charge] = "0"
If Month([Estimate Completed]) = Month(Now()) Then
[Charge] = [Charge] + 40
End If
ElseIf [Branch] = "585" Then
[Charge] = "0"
If Month([Estimate Completed]) = Month(Now()) Then
[Charge] = [Charge] + 40
End If
Else
[Charge] = "0"
If Month([Estimate Completed]) = Month(Now()) Then
[Charge] = [Charge] + 100
End If
If Month([Layout Completed]) = Month(Now()) Then
[Charge] = [Charge] + 100
End If
End If
Can somebody please help me with this? Thanks!
Look like two folks been working on this at the same time. JConsulting is right blank values do cause headaches
Heres a FUNCTION in VBA for you to try out and test against your data
Paste it into a new or existing vba module
You place a call to the function by adding the function to the query Grid as a field like this
ChargeBand: charge([Branch],[Estimate Complete],[Estimate Layout])
If you have branch,Estimate Complete and Estimate Layout in the query as columns then the above sybtax line will automatically use those columns
It relies on those values being passed to the function. The code checks for zero length strings and date testing in other words NO values as JConsulting rightly said. This function is new and specific to you, so needs thorough TESTING to ensure it returns the calculations you want. I have tried it this end and it reproduces ok but I don't know your data soooo usual disclaimers apply
Interestingly enough you if you intend to reproduce anything in a table I am assuming you are not bothered about historical values ie what about when a fixed data value of [Estimate Complete] becomes three months old any calculations as of NOW will drop off if it is in a query if you don't have an anchor? If you are using the current calculation to UPDATE a fixed value then fair enough but watch out for any unexpected results in that area
Good luck with your project
Regards
Jim
- Option Explicit
-
-
Function charge(ByRef strBranch, ByRef dtEstComplete, ByRef dtLayoutComplete) As Long
-
On Error GoTo Err:
-
'declare some variables
-
Dim prmok As Boolean
-
'initialise the charge value and flag variable
-
charge = 0
-
prmok = False
-
'make sure we have parameters passed in to work with if not exit out
-
If IsNull(strBranch) Or strBranch = "" Then
-
charge = 1
-
Exit Function
-
End If
-
'we got here so we must have a branch code
-
'if either one or the other date variable parameters has a value set a flag to true
-
If IsDate(dtEstComplete) Or IsDate(dtLayoutComplete) Then prmok = True
-
'and proceed to work the logic
-
If prmok Then '<<<means if value is true then do something
-
'first side of the IF must be 585 0r 580
-
If strBranch = "580" Or strBranch = "585" Then
-
'which if either are then return a charge value
-
'based on whether the estimate date OR the layout date has something in it
-
If Month(dtEstComplete) = Month(Now()) Or Month(dtLayoutComplete) = Month(Now()) Then
-
charge = charge + 40
-
End If
-
Else
-
'so its any other branch then at this point
-
'who wll be charged the higher rate if either or estimate/layout
-
'dates have something in them
-
If Month(dtEstComplete) = Month(Now()) Or Month(dtLayoutComplete) = Month(Now()) Then
-
charge = charge + 100
-
Else
-
' we must return something if there is an unforeseen value
-
' like a PREVIOUS month ie: prior to the current month OR a FUTURE date
-
' here so return zero
-
charge = 0
-
End If
-
End If
-
Else
-
'prm flag shows false problem with dates? shouldnt be at this point zero out anyway if so
-
Err:
-
charge = 0
-
End If
-
-
End Function
10
