Query/Report - Calculate using If statements

[captainmorgan]

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!

[JConsulting]

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!

The IIF statement would look like this

MyCharge:IIF(Month([Estimate Completed]) = Month(Now()) AND ([Branch] = "580" OR [Branch]="585"), [Charge] = [Charge] + 40, IIF(Month([Estimate Completed]) <> Month(Now()) AND ([Branch] = "580" OR [Branch]="585"), [Charge] = 0,IIF(Month([Estimate Completed]) = Month(Now()) AND ([Branch] <> "580" OR [Branch]<>"585"),[Charge] = [Charge] + 100,[Charge] = 0)))

You can put it into a query grid. If you have null values in these fields, it's going to cause you problems.
J

[Jim Doherty]

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