IF SUM(column)<= 0 then

[TheEvilSam]

(Solved) IF SUM(column)<= 0 then

Hi All

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
Dim SQLToTable As String
Dim SQLMoney As Variant

'SQLPay and SQLToTbl work fine and do what I want them to do. sqlpay adds the value in the text box to tblCalc after negatizing it.
SQLPay = "INSERT INTO TblCalc(SalePriceTotal) VALUES (-'" & TxtPayment & "')"
SQLToTable = "INSERT INTO TblTotalSale (CurrentSaleID, SalePrice, Item) SELECT CurrentSaleID, SalePrice, Item FROM TblCurrentSale"
'This is the area I am strugling with. does not need to select 1 or 0, just need to be 'able to diferentiate in the code block below
SQLMoney = "IF (SUM(SalePriceTotal) FROM TblCalc) <= 0 SELECT '1' ELSE '0'"

DoCmd.SetWarnings False
DoCmd.RunSQL SQLPay
DoCmd.RunSQL SQLMoney
'here is where the differences come into play.
If SQLMoney = 1 Then
DoCmd.RunSQL SQLToTable
Me.TxtPayment = ""
Me.Refresh
DoCmd.OpenReport "rptCalc"

Else

'TxtPayment is where users enter the amount tendered. this returns the txtbox to its 'empty state
Me.TxtPayment = ""
Me.Refresh
Me.Refresh
End If

DoCmd.SetWarnings True

End Sub

 

[Jeanette Cunningham]

First create a saved query that will calculate the SUM(SalePriceTotal) FROM TblCalc

Save this query and use it in the vba code as I show below.

------------------------
Dim SQLPay As String
Dim SQLToTable As String
Dim SQLMoney As Variant
Dim curSum As Currency

'SQLPay and SQLToTbl work fine and do what I want them to do. sqlpay adds the value in the text box to tblCalc after negatizing it.
SQLPay = "INSERT INTO TblCalc(SalePriceTotal) VALUES (-'" & TxtPayment & "')"
SQLToTable = "INSERT INTO TblTotalSale (CurrentSaleID, SalePrice, Item) SELECT CurrentSaleID, SalePrice, Item FROM TblCurrentSale"

curSum = DLookup("QueryFieldName for the sum", "QueryName of the saved query")


If curSum <= 0 Then
SQLMoney = "one sql string here"
Else
SQLMoney = "a different sql string here"
End If

'continue with the rest of your code here
----------------------

 

[TheEvilSam]

Oh My God. You are a genius.

I made SQLMoney = "Yes" if <=0 and "no" for else and it works perfectly when using those as parameters for the rest of the code.

Thank you so much, I would never have thought of that....