Trying To Find Closest Match

[DMT Dave]

Hi Guy's after a stressful week, im struggling with this

I am trying to find a mileage from 3 months ago up to date (today), the dates records may not match so anything from closest date to 08/12/12 to 08/03/23 and calculate what mileage difference is

The following is coming back with incorrect result, can't the the forest for trees!!!!

Dim dtStart As Date, dtEnd As DateDim iMilesStart As Integer, iMilesEnd As Integer, iMilesTotal As Integer


dtStart = InputBox("Enter Start Date ?" & vbCrLf & vbCrLf & _
    "Default is 3 Months", "ENTER START DATE", DateAdd("m", -3, Now()))
    
dtEnd = InputBox("Enter End Date ?" & vbCrLf & vbCrLf & _
    "Default is Now", "ENTER END DATE", Format(Now(), "dd-mmm-yyyy"))
    
iMilesStart = Nz(DMin("Mileage", "tblServiceData", "[FuelDate] > #" & dtStart & "#"), 0)
iMilesStart = Nz(DMin("Mileage", "tblServiceData", "[FuelDate] < #" & dtEnd & "#"), 0)
    
    Forms!frmService!txtStart = dtStart
    Forms!frmService!txtMilesStart = iMilesStart
    Forms!frmService!txtEnd = dtEnd
    Forms!frmService!txtMilesEnd = iMilesEnd
    Forms!frmService!txtMilesTotal = iMilesEnd - iMilesStart

[pbaldy]

For starters, you realize you set iMilesStart twice and never set iMilesEnd?

[pbaldy]

Oh, and for the end I think you'd want DMax, not DMin.

[Welshgasman]

For starters, you realize you set iMilesStart twice and never set iMilesEnd?

Bad copying and pasting Dave?

[DMT Dave]

Yes agree, i need Ctrl+Alt+Delete on the side of my head

I can clearly see what Paul pbaldy pointed out....

[DMT Dave]

Hi Guy's yes fixed the issue now thanks to you guy's noticing, also i had some over flow in values when up to a 6 digit number we as set as integer, change it to Long and perfect

there are no decimals or anything and In my experience i thought Integer is a whole number and Long can be split into decimals etc.... ?

However, this works great

Dim dtStart As Date, dtEnd As DateDim lMilesStart As Long, lMilesEnd As Long, lMilesTotal As Integer
Dim sVeh As String


sVeh = Me.Vehicle


dtStart = InputBox("Enter Start Date ?" & vbCrLf & vbCrLf & _
    "Default is 3 Months", "ENTER START DATE", Format(DateAdd("m", -3, Now()), "dd-mm-yyyy"))
    
dtEnd = InputBox("Enter End Date ?" & vbCrLf & vbCrLf & _
    "Default is Now", "ENTER END DATE", Format(Now(), "dd-mm-yyyy"))
    
lMilesStart = Nz(DMin("Mileage", "tblServiceData", "[FuelDate] > #" & dtStart & "# And [Vehicle] = '" & sVeh & "'"), 0)
    Forms!frmService!txtStart = dtStart
lMilesEnd = Nz(DMax("Mileage", "tblServiceData", "[FuelDate] < #" & dtEnd & "# And [Vehicle] = '" & sVeh & "'"), 0)
    
    Forms!frmService!txtMilesStart = lMilesStart
    Forms!frmService!txtEnd = dtEnd
    Forms!frmService!txtMilesEnd = lMilesEnd
    Forms!frmService!txtMilesTotal = lMilesEnd - lMilesStart

[Minty]

About the number types:

Data Type	Description	Decimal precision	Storage size
Byte	Numbers from 0 to 255 (no fractions). Use Number | Byte in preference to Yes/No (True=-1, False=0)	None	1 byte
Decimal	Numbers from -10^28-1 through 10^28-1 (.mdb)	28	12bytes
Integer	Numbers from -32,768 to 32,767 (no fractions).	None	2 bytes
Long Integer	(Default) Numbers from -2,147,483,648 to 2,147,483,647 (no fractions).	None	4 bytes
Single	Numbers from -3.402823E38 to -1.401298E-45 for negative values and from 1.401298E-45 to 3.402823E38 for positive values.	7	4 bytes
Double	Numbers from -1.79769313486231E308 to -4.94065645841247E–324 for negative values and from 4.94065645841247E-324 to 1.79769313486231E308 for positive values.	15	8 bytes
Replication ID	Globally unique identifier (GUID)	N/A	16 bytes

From here https://ss64.com/access/syntax-datatypes.html