Assign variable

[beaurou]

I have a table that looks like this, What I want to achieve is to assign 4 variables X1, X2, Y1 and Y2.

Let say a user input -12 for X, so i need X1=-15, X2=-11, Y1=-100 and Y2=-60 or for user input 10 the variable will be X1=8.2, X2=18.9, Y1=25 and Y2=60



Basicaly I need to use the value of the record before and the record after from the user request

XY_Data_Point_Number	X	Y
1	-15	-100
2	-11	-60
3	-5.5	-23
4	-3.6	-10
5	0	0
6	1.6	4
7	2.3	10
8	8.2	25
9	18.9	60
10	31	100

[June7]

Could use domain aggregate expressions:

x1 = DMax("X", "tablename", "X<" & [userXinput])
x2 = DMin("X", "tablename", "X>" & [userXinput])

DP1 = DLookup("XY_Data_Point_Number", "tablename", "X=" & x1)
DP2 = DLookup("XY_Data_Point_Number", "tablename", "X=" & x2)

y1 = DLookup("Y", "tablename", "XY_Data_Point_Number=" & DP1)
y2 = DLookup("Y", "tablename", "XY_Data_Point_Number=" & DP2)

[ranman256]

If you use a query, the fields are the variables.

[beaurou]

June7

I insert your code in mine and I receive always an error, I think I did point to the srtSQL

Private Sub CalculatePLA()
Dim LoSchedName As String 'Schedule name for lookup
Dim HiSchedName As String 'Schedule name for high temperature
Dim LowTemp As Single 'Low Temp value
Dim HighTemp As Single 'High Temp value
Dim PLATC As Single 'PLA temp corrected
Dim Orderknots As Variant 'Order Knots user input
Dim PPD As Single 'Calculated PPD
Dim strSQL As String 'SQL string
Dim PLA As Single 'Calculated PLA
Dim PLAfound As Boolean 'PLA data exist
Dim x1 As Single
Dim x2 As Single
Dim y1 As Single
Dim y2 As Single
Dim DP1 As Single
Dim DP2 As Single

'Check if Order Knots is numeric
If (Not IsNumeric(TxtOrderKnots.Value)) Then
'Not numeric so display message
MsgBox "Order Knots must be a numeric value", vbOKOnly, "Input values"
'Set focus to order knots and clear out value
TxtOrderKnots.SetFocus
TxtOrderKnots.Text = ""
'Exit from routine
Exit Sub
End If

'Check if CIT is numeric
If (Not IsNumeric(TxtCIT.Value)) Then
'Not numeric so display message
MsgBox "CIT must be a numeric value", vbOKOnly, "Input values"
'Set focus to CIT and clear out value
TxtCIT.SetFocus
TxtCIT.Text = ""
'Exit from routine
Exit Sub
End If
'Order Knots user input

Orderknots = TxtOrderKnots.Value

strSQL = "SELECT [1335_Schedule XY Data].[XY_Data_Point_Number], [1335_Schedule XY Data].[X], [1335_Schedule XY Data].[Y] " & _
"FROM [1335_Schedule Information]INNER JOIN [1335_Schedule XY Data]" & _
"ON [1335_Schedule Information].[Table_ID] = [1335_Schedule XY Data].[Table_ID] " & _
"WHERE [1335_Schedule Information].[Schedule_Name]='ship_speed_ppd';"

x1 = DMin("X", "strSQL", "X<" & [Orderknots])
x2 = DMin("X", "strSQL", "X>" & [Orderknots])

DP1 = DLookup("XY_Data_Point_Number", "1335_Schedule XY Data", "X=" & x1)
DP2 = DLookup("XY_Data_Point_Number", "1335_Schedule XY Data", "X=" & x2)

y1 = DLookup("Y", "1335_Schedule XY Data", "XY_Data_Point_Number=" & DP1)
y2 = DLookup("Y", "1335_Schedule XY Data", "XY_Data_Point_Number=" & DP2)
PPD = ((y1 - y2) / (x1 - x2)) * Orderknots + (-((y1 - y2) / (x1 - x2)) + x1 + y1)

lblPPD.Value = Format(PPD, ".0")
End Sub

[June7]

Can't use an SQL string in domain aggregate function. You are mixing methods. Use one or the other. Even if this were possible, the strSQL variable would not be within quote marks. Variables are never within quotes. Anything within quotes is considered literal text.

Use SQL string to open a recordset
The SQL string needs spaces so things don't run together. One in front of INNER and one at the end of that line.
"FROM [1335_Schedule Information] INNER JOIN [1335_Schedule XY Data] " & _

OR

Use domain aggregates
Instead of strSQL, refer to an Access table or query object.