SQL to modify (1 Viewer)

[syedadnan]

i have a sql below what i need runsum figure with 2 decimal places.

Private Sub Form_Close()
Dim StrSql As String

StrSql = "UPDATE (SELECT Purchase.Product, Purchase.PQty, Purchase.PRunTot "
StrSql = StrSql & "FROM Purchase ORDER BY Purchase.Product, Purchase.PDate, "
StrSql = StrSql & "Purchase.BatchNo) AS PurchaseSorted SET PurchaseSorted.PRunTot "
StrSql = StrSql & "= UpdateRunSum([product],[PQty]);"



BeginRunSumUpdate

CurrentDb.Execute StrSql

End Sub

 

[Ranman256]

If the fields is real, then you can show only 2 decimals with either :
Format(field,"0.00")
Or
Truncate

 

[syedadnan]

If the fields is real, then you can show only 2 decimals with either :
Format(field,"0.00")
Or
Truncate

yes the field in table is single and fixed to 2 decimal
but i want to know how this format function could be inserted in sql ???

 

[Ranman256]

In the update box of the query.
Or for select queries, on the top row display box.
Where you put the field, just type in the FORMAT(.....

 

[syedadnan]

In the update box of the query.
Or for select queries, on the top row display box.
Where you put the field, just type in the FORMAT(.....

Hmm absoultely no getting ,,, as this is not query its a sql on form close event now after closing the form it is updating table with 1 2 3 or so on but if i enter 0.25 in form then it showing 0 in table

 

[Ranman256]

Then the field may be set to integer. (Or long)
It must be a real number, single, double,curenccy

 

[syedadnan]

Then the field may be set to integer. (Or long)
It must be a real number, single, double,curenccy

Tested and it was already set to integar and fixed with 2 decimal still result is 0 for 1.25

 

[Minty]

Ranman was telling you to change it to either real, single , double or currency. Integer means exactly that an integer (whole) number.

 

[syedadnan]

Ranman was telling you to change it to either real, single , double or currency. Integer means exactly that an integer (whole) number.

hmm a bit confused now,,

i changed it like this;

Filed Size : double
Format : Fixed
Decimal Places : 2

Is else you people are talking about ?

 

[Minty]

No - that should do what you want.

 

[syedadnan]

No - that should do what you want.

is it correct what i did with the table field ? or kindly advise what to do

 

[Minty]

You are correct - you have changed it to double, which should store the value as you need it to. You can prove it by simply typing 1.25 or similar straight into that field in the table.

 

[syedadnan]

You are correct - you have changed it to double, which should store the value as you need it to. You can prove it by simply typing 1.25 or similar straight into that field in the table.

But the result is still 0 for 1.25 .. i think the sql i post earlier need some formating but i am not perfect in sql...

 

[Minty]

But the result is still 0 for 1.25 .. i think the sql i post earlier need some formating but i am not perfect in sql...

If you can't manually change the stored value directly in the table then you haven't made the correct change to the table design.
Your SQL doesn't look quite right to me, but that's immaterial if the table won't store the data.

 

[syedadnan]

If you can't manually change the stored value directly in the table then you haven't made the correct change to the table design.
Your SQL doesn't look quite right to me, but that's immaterial if the table won't store the data.

I have test the table and manually it is storing what i need but not through SQL

 

[Minty]

I can't see how your SQL statement works. What is the code behind the UpdateRunSum function. It's probably return an integer.
I also doubt the logic behind storing a running sum at all, it should always be calculated.

 

[syedadnan]

I can't see how your SQL statement works. What is the code behind the UpdateRunSum function. It's probably return an integer.
I also doubt the logic behind storing a running sum at all, it should always be calculated.

Thanks for your time really.. hmm it was my fault but many thanks to you because your last update divert my mind to the correct direction and found where is updaterumsum ohhh i found it in another module and then i changed some long to double and that start working.. here is the code below.. thanks again its your win really..Option Compare Database

Global ToLineSum As Double
Global Pr As Long

Public Function BeginRunSumUpdate()
ToLineSum = 0
Pr = 0
End Function


Public Function UpdateRunSum(Prd As Double, ivalue) As Double
If Pr = Prd Then
ToLineSum = ToLineSum + ivalue
UpdateRunSum = ToLineSum
Else
ToLineSum = 0
Pr = Prd
ToLineSum = ivalue
UpdateRunSum = ToLineSum
End If
End Function