Need help with SQL code

[tegb]

I have a form with two text boxes(XNAC & XNAC1) and 1 comman button(Save). When the save button is clicked, i want to run a query to bring back the sum of amount where XNAC(from my table) matches either XNAC or XNAC1. the code runs but the total(sum) that it brings back is incorrect. what am i doing wrong? Please help.


Private Sub Save_Click()
Dim db As Database
Set db = CurrentDb
Dim rstemp As Recordset
Dim tblpool As Recordset
Dim daysOpen As Integer
Dim rstemp60 As Recordset
Dim rstemp6190 As Recordset
Dim rstemp120 As Recordset
Dim rstemp180 As Recordset
Dim ctl As Control
Set tblpool = db.OpenRecordset("Report_History_Table")
tblpool.AddNew
tblpool![XNAC] = XNAC.Value
tblpool![XNAC2] = XNAC2.Value
'tblpool![DateModified] = Date
If IsNull(Me.XNAC2) Then
'Get 60plus aging
Set rstemp = db.OpenRecordset("Select sum(invamt) AS [Sixtyplus] from Debits where XNAC Like '" & [Forms]![Request lookup and update]![XNAC] & "*' and INVAMT > 0 and (DateDiff('M',InvDate,Now()))>1")
With rstemp
.MoveFirst
tblpool![SixtyPlus_Acceptdt] = !Sixtyplus

End With
'DoCmd.RunMacro "Update_accept_date_aging"

Set rstemp1 = db.OpenRecordset("Select sum(invamt) AS [Sixtyoneninety] from Debits where XNAC Like '" & [Forms]![Request lookup and update]![XNAC] & "*' and INVAMT > 0 and (DateDiff('M',InvDate,Now()))=2")
With rstemp1
.MoveFirst
tblpool![Sixtyoneninety_Acceptdt] = !Sixtyoneninety

End With

'Get 120plus aging
Set rstemp2 = db.OpenRecordset("Select sum(invamt) AS [onetwentyplus] from Debits where XNAC Like '" & [Forms]![Request lookup and update]![XNAC] & "*' and INVAMT > 0 and (DateDiff('M',InvDate,Now()))>3")
With rstemp2
.MoveFirst
tblpool![OnetwentyPlus_Acceptdt] = !onetwentyPlus

End With
'Get 180plus aging
Set rstemp3 = db.OpenRecordset("Select sum(invamt) AS [oneeightyplus] from Debits where XNAC Like '" & [Forms]![Request lookup and update]![XNAC] & "*' and INVAMT > 0 and (DateDiff('M',InvDate,Now()))>5")
With rstemp3
.MoveFirst
tblpool![OneeightyPlus_Acceptdt] = !OneeightyPlus

End With
Else
'Get aging with XNAC and XNAC2
Set rstemp = db.OpenRecordset("Select sum(invamt) AS [Sixtyplus] from Debits where XNAC Like '" & [Forms]![Request lookup and update]![XNAC] & "*' or XNAC Like '" & [Forms]![Request lookup and update]![XNAC2] & "*' and INVAMT > 0 and (DateDiff('M',InvDate,Now()))>1")
With rstemp
.MoveFirst
tblpool![SixtyPlus_Acceptdt] = !Sixtyplus

End With
'DoCmd.RunMacro "Update_accept_date_aging"

Set rstemp1 = db.OpenRecordset("Select sum(invamt) AS [Sixtyoneninety] from Debits where XNAC Like '" & [Forms]![Request lookup and update]![XNAC] & "*' or XNAC Like '" & [Forms]![Request lookup and update]![XNAC2] & "*' and INVAMT > 0 and (DateDiff('M',InvDate,Now()))=2")
With rstemp1
.MoveFirst
tblpool![Sixtyoneninety_Acceptdt] = !Sixtyoneninety

End With

'Get 120plus aging
Set rstemp2 = db.OpenRecordset("Select sum(invamt) AS [onetwentyplus] from Debits where XNAC Like '" & [Forms]![Request lookup and update]![XNAC] & "*' or XNAC Like '" & [Forms]![Request lookup and update]![XNAC2] & "*' and INVAMT > 0 and (DateDiff('M',InvDate,Now()))>3")
With rstemp2
.MoveFirst
tblpool![OnetwentyPlus_Acceptdt] = !onetwentyPlus

End With
'Get 180plus aging
Set rstemp3 = db.OpenRecordset("Select sum(invamt) AS [oneeightyplus] from Debits where XNAC Like '" & [Forms]![Request lookup and update]![XNAC] & "*' or XNAC Like '" & [Forms]![Request lookup and update]![XNAC2] & "*' and INVAMT > 0 and (DateDiff('M',InvDate,Now()))>5")
With rstemp3
.MoveFirst
tblpool![OneeightyPlus_Acceptdt] = !OneeightyPlus

End With
End If
tblpool.Update
MsgBox " Saved"

 

[WayneRyan]

tegb,

In the SQL marked in red, you reference [XNAC2],
but at the start you say you have (XNAC & XNAC1).

Also, your logic you may need parentheses in your Where clause.

You have --> A or B and C and D.

I think you should have --> (A or B) and C and D

As it currently is, you'll return all records like [Forms]![Request lookup and update]![XNAC]
regardless of the INVAMT or INVDATE.

hth,
Wayne

 

[tegb]

thanks, that helped but

when the form first loads & XNAC2 is left blank during update, the above code works perfectly fine however after saving and form clears, if i try to do just the same thing - leave XNAC2 blank it goes back to the same problem - adds up everything

 

[WayneRyan]

t,

Just add something like this to your where clause:

" And Forms!YourForm!XNAC2 Is Not Null"

hth,
Wayne