Solved Subtract from previous field (1 Viewer)

[zelarra821]

Good night from Spain. I want to subtract a field from the previous field, I don't know if I explain myself. I have this function, which works for me in another database, but for some unknown reason, it doesn't in this one.

Function CalcularDiferencia(CampoARestar As String, Id As Double) As Double
    Dim regAnterior As Double
    If IsNull(CampoARestar) Then Exit Function
    regAnterior = Nz(DMax(Replace(CampoARestar, ",", "."), "TGasControl_lecturas para calefacccion", "Id<" & Id), 0)
    If regAnterior = 0 Then
        CalcularDiferencia = regAnterior
    Else
        CalcularDiferencia = CampoARestar - regAnterior
    End If
End Function

The field to subtract (CampoARestar) is cubic meters, and I want to know the difference with the last date entered.

Thanks.

 

[MajP]

I would not use a function but a subquery

Calculate the difference between two figures

Hi there. I have the milage of my car accumulating in one column each time I fill with fuel in a table. I'd like to calculate the mileage for each refill. Thanks in advance

www.access-programmers.co.uk

Denormalized database: update adjacent records

Hi gentlemens, I suppose you will kill me for question regarding denormalized DB, but only you can help me. I have table where I store Car log book: id Date car_id Odometer (absolute value) 1 12.10.2022 1 10 000 etc Now, as is probably evident, I store odometer values as absolute...

www.access-programmers.co.uk

 

[CJ_London]

which works for me in another database, but for some unknown reason, it doesn't in this one.

doesn't help us to help you - what does this mean? you get an error? wrong result? something else?

perhaps show some data - as it is and how you want it to be. You mention you want the previous date, but your dmax is using ID

 

[June7]

This assumes cubic meters, ID, and date are always increasing with each record. Either ID or date should work.

 

[zelarra821]

I would not use a function but a subquery

Calculate the difference between two figures

Hi there. I have the milage of my car accumulating in one column each time I fill with fuel in a table. I'd like to calculate the mileage for each refill. Thanks in advance

www.access-programmers.co.uk

Denormalized database: update adjacent records

Hi gentlemens, I suppose you will kill me for question regarding denormalized DB, but only you can help me. I have table where I store Car log book: id Date car_id Odometer (absolute value) 1 12.10.2022 1 10 000 etc Now, as is probably evident, I store odometer values as absolute...

www.access-programmers.co.uk

Function CalcularDiferencia(Id As Double) As Double
    Dim rst As DAO.Recordset
    Dim strSql As String

    strSql = "SELECT [m3]-(SELECT top 1 m3 As Diferencia" _
            & " FROM [TGasControl_lecturas para calefacccion] AS A" _
            & " WHERE A.m3 < [TGasControl_lecturas para calefacccion].m3" _
            & " ORDER BY m3 DESC) AS Diferencia)" _
            & " FROM [TGasControl_lecturas para calefacccion]" _
            & " WHERE ((([TGasControl_lecturas para calefacccion].Id)=" & Id & "))"
    Set rst = CurrentDb.OpenRecordset(strSql)
    If Not (rst.EOF And rst.BOF) Then
         CalcularDiferencia = rst("Diferencia")
    End If
    rst.Close
    Set rst = Nothing
End Function

I have created this function, but I get an error that the SELECT statement includes a reserved word. As much as I've tried changing things, I can't find the key to make it work correctly.

 

[MajP]

I am not suggesting doing that. I would simply make working query like I suggested that has ID, and calculated Diferencia. Then make a simple function.

Function CalcularDiferencia(Id As Double) As Double
calcularDiferencia = dlookup("Diferencia","YourQuery","ID = " & ID
End Function

 

[June7]

Seems the original function should work. Alternatively, as suggested, could build nested query object. Then if you need calculated value for a particular ID or date, use DLookup to pull.

Here is another example http://allenbrowne.com/subquery-01.html#AnotherRecord

If you still need help, advise provide sample data. Could even attach db for analysis.

 

[zelarra821]

Fixed. Why do I get values like 3.01000000000001, when they are subtractions of three decimal places? I have set the cubic meter field as double.

 

[MajP]

Floating point arithmetic

VBA Numbers - Floating Point

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

The Single and Double data types are very precise, that is, they make it possible for you to specify extremely small or large numbers. However, these data types are not very accurate because they use floating-point mathematics. Floating-point mathematics has an inherent limitation in that it uses binary digits to represent decimals. Not all the numbers within the range available to the Single or Double data type can be represented exactly in binary form, so they are rounded. Also, some numbers cannot be represented exactly with any finite number of digits, pi, for example, or the decimal resulting from 1/3.
Because of these limitations to floating-point mathematics, you might encounter rounding errors when you perform operations on floating-point numbers. Compared to the size of the value you are working with, the rounding error will be very small. If you do not require absolute accuracy and can afford relatively small rounding errors, the floating-point data types are ideal for representing very small or very large values. On the other hand, if your values must be accurate - for example, if you are working with money values - you should consider one of the scaled integer data types.

 

[CJ_London]

I'll ust comment that ID as a double does not make a good field for creating relationships.

You have just posted the reason why - doubles are not precise.

 

[zelarra821]

So which one do I have to use instead of double? I have the ID in the table as an Integer, but the cubic meter as a double.

 

[CJ_London]

I presume this is an access back end? Normally it would be an autonumber which is a long. If it is an integer - which limits you to 32 thousand odd records, why are you declaring it as a double?

Function CalcularDiferencia(Id As Double) As Double

Also means you are not using the autonumber functionality which means there is no guarantee that the ID's will increment in order. I presume you are using something like the dmax+1 functionality to increment the ID, but that is not infallible, particularly in a multi user environment. Plus sometimes it is possible that records do not get entered in the right order which can also have an impact on your calculation

 

[zelarra821]

I have already solved the ID by putting it Long.

Now I have another problem. In the query with the subquery I use to calculate the difference, it shows me some subtraction with many decimal places, when there should only be three. The problem is not in VBA, because I don't use it to calculate the difference, but SQL. I have read the text that MajP has passed, and from what I understand, to solve it I must use an Integer number, but it does not store decimals. Could you tell me exactly what the solution is, or is this simply unsolvable and has to stay that way? Thank you very much.

 

[KitaYama]

I normally use currency data type when I need decimal.
Then set the format of the text box to General Number.

 

[MajP]

Now I have another problem. In the query with the subquery I use to calculate the difference, it shows me some subtraction with many decimal places, when there should only be three.

None of that should matter. That is just a formatting issue since to practical difference from 2.98 and 2.9799999999. If you need to display the values then wrap the display with a rounding or formatting function.

 

[zelarra821]

Ok, thanks to all