IF SUM(column)<= 0 then

[theevilsam]

Hey. This is a bit of a long question but hopefully I have included enough for you to help me



I am making a till system for a project in access and I have hit a block.

*Background*
There are numerous forms covered in buttons. clicking a button adds data to a table (TblCurSale) including description and price of item. each form also has a "total" button which sends you to the payment screen, doing so copies the data from the TblCurSale to another table (TblCalc)

TblCalc has columns SaleID, Item(name of item), SalePrice. the report auto adds the sale price column

The total form has two sub reports on it, the TBLCurSale and TblCalc.

On the total screen there is a text filed in which users an input money and then press pay which inputs that figure into the TblCalc as a negative number and then refreshes the page so the new total comes up. at the bottom of the subreport.


*Problem*
I need an IF vba code so that I can put it so that when the total of the SalePrice column <= 0 I can run a few lines of code. what I have so far is below, so any help would be greatly appreciated.

Private Sub Pay_Click()

Dim SQLPay As String 'SQLPay works fine
Dim SQLToTable As String 'SQLToTable works fine
Dim SQLMoney As Variant 'on adding this All hell broke loose... 

SQLPay = "INSERT INTO TblCalc(SalePriceTotal) VALUES (-'" & TxtPayment & "')"
SQLToTable = "INSERT INTO TblTotalSale (CurrentSaleID, SalePrice, Item) SELECT CurrentSaleID, SalePrice, Item FROM TblCurrentSale"
SQLMoney = "IF (SUM(SalePriceTotal) FROM TblCalc) <= 0 SELECT "1" ELSE "0"" 

'SQLMoney gives a syntax error, but I think it goes deeper than that. I just 'think I've got it completely wrong

DoCmd.SetWarnings False
DoCmd.RunSQL SQLPay
DoCmd.RunSQL SQLMoney

If SQLMoney = 1 Then
DoCmd.RunSQL SQLToTable
Me.TxtPayment = ""
Me.Refresh
DoCmd.OpenReport "rptCalc"

Else

Me.TxtPayment = ""
Me.Refresh
Me.Refresh
End If

DoCmd.SetWarnings True

End Sub

[June7]

Use DSum function. You can set variable or put right in the If Then condition.

If DSum("SalePriceTotal","TblCalc") <= 0 Then

[theevilsam]

Thanks. This works

I'm still making stupid novice mistakes... Thank god there are people out there to help