If Then statement

[Krash878]

I have a form that needs to have an If Then statement added into the code to make 0=1.
the code is as follows

Private Sub SequenceNo_AfterUpdate()
Dim MyDB As Database
Dim Rec As Recordset
Dim SQLString As String

SQLString = "SELECT tblChargeBack.SequenceNumber, DateDiff('d',[StartDate],[DateClosed]) AS Elapsed, ([Elapsed]*(0.06/365)*[Amount]) AS Total FROM tblChargeBack INNER JOIN tblCloseItem ON tblChargeBack.SequenceNumber = tblCloseItem.SequenceNo WHERE tblChargeBack.SequenceNumber ='" & SequenceNo & "'"
Set MyDB = CurrentDb()
Set Rec = MyDB.OpenRecordset(SQLString, dbOpenSnapshot)
InterestFee = Rec!Total
End Sub


There are two dates that make the [Elapsed] but some times the two dates are the same. In that case I get an error. Where in the code would I put the statement,

IF([Elapsed]=0,"1","[Elapsed]&quot

, I am not sure if the statement is correct either. I am on access 2000 if that helps.

Thanks
Kenny

 

[TTThio]

You can do either add 1 in the formula:
DateDiff('d',[StartDate],[DateClosed])+ 1 AS Elapsed

or in the statement using "IIF"

 

[tlbroadbent]

Add the IIF function in the query as indicated.

SQLString = "SELECT tblChargeBack.SequenceNumber, DateDiff('d',[StartDate],[DateClosed]) AS Elapsed, (IIF([Elapsed]=0,1,"[Elapsed]&quot*(0.06/365)*[Amount]) AS Total ...


What error were you getting with the original query? Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Krash878]

Private Sub SequenceNo_AfterUpdate()
Dim MyDB As Database
Dim Rec As Recordset
Dim SQLString As String

SQLString = "SELECT tblChargeBack.SequenceNumber, DateDiff('d',[StartDate],[DateClosed]) AS Elapsed, (IIF([Elapsed]=0,1,'[Elapsed]')*(0.06/365)*[Amount]) AS Total FROM tblChargeBack INNER JOIN tblCloseItem ON tblChargeBack.SequenceNumber = tblCloseItem.SequenceNo WHERE tblChargeBack.SequenceNumber ='" & SequenceNo & "'"
Set MyDB = CurrentDb()
Set Rec = MyDB.OpenRecordset(SQLString, dbOpenSnapshot)
InterestFee = Rec!Total
End Sub



It is a compile error that I get.

It does not want to run now at all.

When I do not have the IIF statement then it runs fine except when the dates are the same.

 

[tlbroadbent]

Sorry, but I inadvertantly added double quotes (&quot to the query that shouldn't have been there. You changed them to single quotes ('). Remove the single quotes around [Elapsed].

SQLString = "SELECT tblChargeBack.SequenceNumber, DateDiff('d',[StartDate],[DateClosed]) AS Elapsed, (IIF([Elapsed]=0,1,[Elapsed])*(0.06/365)*[Amount]) AS Total FROM tblChargeBack INNER JOIN tblCloseItem ON tblChargeBack.SequenceNumber = tblCloseItem.SequenceNo WHERE tblChargeBack.SequenceNumber ='" & SequenceNo & "'"

I apologize for not being more careful. Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Krash878]

by god it works.

Thanks
Kenny