Complex (for me) checking values in fields to perform calcs in others

  • Thread starter Thread starter ehorde
  • Start date Start date
E

ehorde

Hi all..

I am creating a Cash Flow Projection report in access that inspects
the "completed dates" of sheduled draws to calculate a remaining
balance, but I am having problems with it.

The idea is:
---------------------
Iff [Draw1CompletedDate] = Null then Me!txtBalance = [MortgageAmount]

Else

If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] = Null then
Me!txtBalance = [MortgageAmount] -
[Draw1Amount]

Else

If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] <> Null AND
[Draw3CompletedDate] = Null then Me!txtBalance = [MortgageAmount] -
[Draw1Amount] - [Draw2Amount]

Else

If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] <> Null AND
[Draw3CompletedDate] <> Null then Me!txtBalance = [MortgageAmount] -
[Draw1Amount] - [Draw2Amount] - [Draw3Amount]

-----------------

I am sure I have butchered this, but this is what I need to do. I get
"syntax error" "missing operator" errors.

Thanks..
 
First, try using the isnull() function to check for Nulls, rather than = or
<>,
for example if isnull([Draw1CompletedDate]) then ...

Your code structure has to be either:

If condition1 then
if condition2 then
if condition3 then
else
endif
endif
endif

or:

if condition1 then
elseif condition2 then
elseif condition3 then
else
endif

and they are not the same. (note there is nothing after the 'then')

In the first structure, if condition1 fails, none of the others will be
checked. If condition1 passes, then condition2 will be checked - if
condition2 fails, condition3 will not be checked.

In the second structure, if condition1 fails, then condition2 will be checked,
and if that fails, then condition3 etc.

Which you use depends on what you need to do.

Hope this helps

John


Hi all..

I am creating a Cash Flow Projection report in access that inspects
the "completed dates" of sheduled draws to calculate a remaining
balance, but I am having problems with it.

The idea is:
---------------------
Iff [Draw1CompletedDate] = Null then Me!txtBalance = [MortgageAmount]

Else

If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] = Null then
Me!txtBalance = [MortgageAmount] -
[Draw1Amount]

Else

If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] <> Null AND
[Draw3CompletedDate] = Null then Me!txtBalance = [MortgageAmount] -
[Draw1Amount] - [Draw2Amount]

Else

If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] <> Null AND
[Draw3CompletedDate] <> Null then Me!txtBalance = [MortgageAmount] -
[Draw1Amount] - [Draw2Amount] - [Draw3Amount]

-----------------

I am sure I have butchered this, but this is what I need to do. I get
"syntax error" "missing operator" errors.

Thanks..
Click to expand...
 
Many thanks John!

You got me pointed in the right direction...

Final Code ended up being:

*******************************

Dim strMort As String

Dim strDraw1 As String
Dim strDraw2 As String
Dim strDraw3 As String
Dim strDraw4 As String
Dim strDraw5 As String

Dim strDraw1Date As String
Dim strDraw2Date As String
Dim strDraw3Date As String
Dim strDraw4Date As String
Dim strDraw5Date As String

Dim strBal As String

strMort = Me.[Mortgage]

If IsNull(Me.[Actual Draw Date - Bank Draw 1]) Then
strDraw1Date = ""
Else
strDraw1Date = (Me.[Actual Draw Date - Bank Draw 1])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 2]) Then
strDraw1Date = ""
Else
strDraw1Date = (Me.[Actual Draw Date - Bank Draw 2])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 3]) Then
strDraw1Date = ""
Else
strDraw1Date = (Me.[Actual Draw Date - Bank Draw 3])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 4]) Then
strDraw1Date = ""
Else
strDraw1Date = (Me.[Actual Draw Date - Bank Draw 4])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 5]) Then
strDraw1Date = ""
Else
strDraw1Date = (Me.[Actual Draw Date - Bank Draw 5])
End If

strDraw1 = Me.[Bank Draw 1 Amount]
strDraw2 = Me.[Bank Draw 2 Amount]
strDraw3 = Me.[Bank Draw 3 Amount]
strDraw4 = Me.[Bank Draw 4 Amount]
strDraw5 = Me.[Bank Draw 5 Amount]

If strDraw1Date = "" Then
strBal = strMort
ElseIf strDraw1Date <> "" & strDraw1Date = "" Then
strBal = strMort - strDraw1
ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date =
"" Then
strBal = strMort - strDraw1 - strDraw2
ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date <>
"" & strDraw4Date = "" Then
strBal = strMort - strDraw1 - strDraw2 - strDraw3
ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date <>
"" & strDraw4Date <> "" & strDraw5Date = "" Then
strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4
ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date <>
"" & strDraw4Date <> "" & strDraw5Date <> "" Then
strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4 -
strDraw5
End If

Me.[Text127] = strBal
 
Hi -

I see a problem with that code.

You have five separate If-then-else constructs, each of which sets
strDraw1Date to either blank or to a value. Therefore the initial value of
strDrawDate1 will depend on Me.[Actual Draw Date - Bank Draw 5] - and only
that. I suspect you may forgotten to change strDraw1Date to strDraw2Date,
strDraw3Date etc in the second to 5th if-then-else blocks.

John




Many thanks John!

You got me pointed in the right direction...

Final Code ended up being:

*******************************

Dim strMort As String

Dim strDraw1 As String
Dim strDraw2 As String
Dim strDraw3 As String
Dim strDraw4 As String
Dim strDraw5 As String

Dim strDraw1Date As String
Dim strDraw2Date As String
Dim strDraw3Date As String
Dim strDraw4Date As String
Dim strDraw5Date As String

Dim strBal As String

strMort = Me.[Mortgage]

If IsNull(Me.[Actual Draw Date - Bank Draw 1]) Then
strDraw1Date = ""
Else
strDraw1Date = (Me.[Actual Draw Date - Bank Draw 1])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 2]) Then
strDraw1Date = ""
Else
strDraw1Date = (Me.[Actual Draw Date - Bank Draw 2])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 3]) Then
strDraw1Date = ""
Else
strDraw1Date = (Me.[Actual Draw Date - Bank Draw 3])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 4]) Then
strDraw1Date = ""
Else
strDraw1Date = (Me.[Actual Draw Date - Bank Draw 4])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 5]) Then
strDraw1Date = ""
Else
strDraw1Date = (Me.[Actual Draw Date - Bank Draw 5])
End If

strDraw1 = Me.[Bank Draw 1 Amount]
strDraw2 = Me.[Bank Draw 2 Amount]
strDraw3 = Me.[Bank Draw 3 Amount]
strDraw4 = Me.[Bank Draw 4 Amount]
strDraw5 = Me.[Bank Draw 5 Amount]

If strDraw1Date = "" Then
strBal = strMort
ElseIf strDraw1Date <> "" & strDraw1Date = "" Then
strBal = strMort - strDraw1
ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date =
"" Then
strBal = strMort - strDraw1 - strDraw2
ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date <>
"" & strDraw4Date = "" Then
strBal = strMort - strDraw1 - strDraw2 - strDraw3
ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date <>
"" & strDraw4Date <> "" & strDraw5Date = "" Then
strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4
ElseIf strDraw1Date <> "" & strDraw1Date <> "" & strDraw3Date <>
"" & strDraw4Date <> "" & strDraw5Date <> "" Then
strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4 -
strDraw5
End If

Me.[Text127] = strBal
Click to expand...
 
You are right, right after my last post I had to correct those
typo's. I'm still having another problem with the code though, not
quite sure what it is or how to describe it yet.
 
I getting Type Mismatches here.. (arrow)

---------------------
If strDraw1Date = "" Then
strBal = strMort
-> ElseIf strDraw1Date <> "" & strDraw2Date = "" Then
strBal = strMort - strDraw1
ElseIf strDraw1Date <> "" & strDraw2Date <> "" & strDraw3Date =
"" Then
strBal = strMort - strDraw1 - strDraw2
ElseIf strDraw1Date <> "" & strDraw2Date <> "" & strDraw3Date <>
"" & strDraw4Date = "" Then
strBal = strMort - strDraw1 - strDraw2 - strDraw3
ElseIf strDraw1Date <> "" & strDraw2Date <> "" & strDraw3Date <>
"" & strDraw4Date <> "" & strDraw5Date = "" Then
strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4
ElseIf strDraw1Date <> "" & strDraw2Date <> "" & strDraw3Date <>
"" & strDraw4Date <> "" & strDraw5Date <> "" Then
strBal = strMort - strDraw1 - strDraw2 - strDraw3 - strDraw4 -
strDraw5
End If
 
While it would be nice, you can't use "&" as a shorthand for "AND", so your
elseif becomes:

ElseIf strDraw1Date <> "" and strDraw2Date = "" Then

and the same for the others, of course.

John
 
Sharing final report's code. Many thanks to John for helping me out
with this one!

Mahalo...Eric

------------------------------------------
Option Compare Database

Private Sub Report_Activate()

' String Setup

Dim strMort As String
Dim strPool As String

Dim strDraw1 As String
Dim strDraw2 As String
Dim strDraw3 As String
Dim strDraw4 As String
Dim strDraw5 As String

Dim strPoolDraw1 As String
Dim strPoolDraw2 As String
Dim strPoolDraw3 As String

Dim strDraw1Date As String
Dim strDraw2Date As String
Dim strDraw3Date As String
Dim strDraw4Date As String
Dim strDraw5Date As String

Dim strPoolDraw1Date As String
Dim strPoolDraw2Date As String
Dim strPoolDraw3Date As String

Dim strMortBal As String
Dim strPoolBal As String

strMortBal = ""
strPoolBal = ""

If IsNull(Me.[Mortgage]) Then
strMort = ""
Else
strMort = (Me.[Mortgage])
End If

If IsNull(Me.[Pool Loan]) Then
strPool = ""
Else
strPool = (Me.[Pool Loan])
End If

'Mortgage Draw Section

If IsNull(Me.[Actual Draw Date - Bank Draw 1]) Then
strDraw1Date = ""
Else
strDraw1Date = (Me.[Actual Draw Date - Bank Draw 1])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 2]) Then
strDraw2Date = ""
Else
strDraw2Date = (Me.[Actual Draw Date - Bank Draw 2])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 3]) Then
strDraw3Date = ""
Else
strDraw3Date = (Me.[Actual Draw Date - Bank Draw 3])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 4]) Then
strDraw4Date = ""
Else
strDraw4Date = (Me.[Actual Draw Date - Bank Draw 4])
End If

If IsNull(Me.[Actual Draw Date - Bank Draw 5]) Then
strDraw5Date = ""
Else
strDraw5Date = (Me.[Actual Draw Date - Bank Draw 5])
End If

strDraw1 = Me.[Bank Draw 1 Amount]
strDraw2 = Me.[Bank Draw 2 Amount]
strDraw3 = Me.[Bank Draw 3 Amount]
strDraw4 = Me.[Bank Draw 4 Amount]
strDraw5 = Me.[Bank Draw 5 Amount]

If strDraw1Date = "" Then
strMortBal = strMort
Else
If strDraw1Date <> "" And strDraw2Date = "" Then
strMortBal = strMort - strDraw1
Else
If strDraw1Date <> "" And strDraw2Date <> "" And
strDraw3Date = "" Then
strMortBal = strMort - strDraw1 - strDraw2
Else
If strDraw1Date <> "" And strDraw2Date <> "" And
strDraw3Date <> "" And strDraw4Date = "" Then
strMortBal = strMort - strDraw1 - strDraw2 - strDraw3
Else
If strDraw1Date <> "" And strDraw2Date <> "" And
strDraw3Date <> "" And strDraw4Date <> "" And strDraw5Date = "" Then
strMortBal = strMort - strDraw1 - strDraw2 -
strDraw3 - strDraw4
Else
If strDraw1Date <> "" And strDraw2Date <> ""
And strDraw3Date <> "" And strDraw4Date <> "" And strDraw5Date <> ""
Then
strMortBal = strMort - strDraw1 - strDraw2 -
strDraw3 - strDraw4 - strDraw5
End If
End If
End If
End If
End If
End If

'Pool Draw Section

If IsNull(Me.[Actual Draw Date - Pool Draw 1]) Then
strPoolDraw1Date = ""
Else
strPoolDraw1Date = (Me.[Actual Draw Date - Pool Draw 1])
End If

If IsNull(Me.[Actual Draw Date - Pool Draw 2]) Then
strPoolDraw2Date = ""
Else
strPoolDraw2Date = (Me.[Actual Draw Date - Pool Draw 2])
End If

If IsNull(Me.[Actual Draw Date - Pool Draw 3]) Then
strPoolDraw3Date = ""
Else
strPoolDraw3Date = (Me.[Actual Draw Date - Pool Draw 3])
End If

If IsNull(Me.[Draw Amount - Pool Draw 1]) Then
strPoolDraw1 = ""
Else
strPoolDraw1 = (Me.[Draw Amount - Pool Draw 1])
End If

If IsNull(Me.[Draw Amount - Pool Draw 2]) Then
strPoolDraw2 = ""
Else
strPoolDraw2 = (Me.[Draw Amount - Pool Draw 2])
End If

If IsNull(Me.[Draw Amount - Pool Draw 3]) Then
strPoolDraw3 = ""
Else
strPoolDraw3 = (Me.[Draw Amount - Pool Draw 3])
End If

If strPoolDraw1Date = "" Then
strPoolBal = strPool
Else
If strPoolDraw1Date <> "" And strPoolDraw2Date = "" Then
strPoolBal = strPool - strPoolDraw1
Else
If strPoolDraw1Date <> "" And strPoolDraw2Date <> "" And
strPoolDraw3Date = "" Then
strPoolBal = strPool - strPoolDraw1 - strPoolDraw2
Else
If strDraw1PoolDate <> "" And strPoolDraw2Date <> ""
And strPoolDraw3Date <> "" Then
strPoolBal = strPool - strPoolDraw1 - strPoolDraw2 -
strPoolDraw3
End If
End If
End If
End If


Me.[Text127] = Format$(strMortBal, "currency")
Me.[Text146] = Format$(strPoolBal, "currency")


End Sub
 
Back
Top