Display three largest records 1

[Jayzxtx]

I am trying to enter in a zip code in a combo box to show which service agents are the closest to the customers based on mileage. The milage would then be used to calculate the travel cost. How would I filter my results to just show the three cheapest milage costs? Below is my code I have been working with. I have gotten the results to show in subform, but I am not sure what VB code I should use to filter the 3 cheapests cost.

Appreciate the help


Private Sub command11_Click()
Me.Combo14.Value = Null


End Sub




Private Sub Detail_Click()
End Sub


Private Sub CalculateZip_Click()
Dim sqlString As String

If Not IsNull(Me.Combo14.Value) Then
sqlString = sqlString & " [Zodiaq Zip Locations_Zip] = " & Combo14.Value & " AND "
End If

If Len(sqlString) > 5 Then
sqlString = Left(sqlString, Len(sqlString) - 5)
Else
End If


Me.SA_Distance_Calc_subform6.Form.FilterOn = True
Me.SA_Distance_Calc_subform6.Form.Filter = sqlString
Me.SA_Distance_Calc_subform6.Form.Requery
Me.Refresh
' MasterForm.DoCmd.OpenQuery SQLCommand
On Error GoTo Err_CalculateZip_Click
' DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70

Exit_CalculateZip_Click:
Exit Sub

Err_CalculateZip_Click:
MsgBox Err.Description
Resume Exit_CalculateZip_Click


End Sub

 

[JerryKlmns]

Your subform6 record source should be based on a query like

SELECT TOP 3 Field1, Field2, ....
FROM ...
WHERE [Zodiaq Zip Locations_Zip] = Forms!MainFormName!SA_Distance_Calc_subform6!Combo14.Value
ORDER BY DistanceCalculatedField;

 

[Jayzxtx]

Jerry,

Thanks but I am still a little unclear as to where to insert the query. I have been trying to insert the top 3 query expression yesterday with little sucess. I keep on getting syntax errors. Below I am going to past where I believe is the correct place to put the qurery. Could you please look at the code and tell me what I am doing wrong. Thank you again.

Private Sub CalculateZip_Click()
Dim sqlString As String
Dim strGetSQL As String
If Not IsNull(Me.Combo14.Value) Then
sqlString = sqlString & "[Zodiaq Zip Locations_Zip] = " & Combo14.Value & " AND "
End If




If Len(sqlString) > 5 Then
sqlString = Left(sqlString, Len(sqlString) - 5)
Else
End If





Me.SA_Distance_Calc_subform6.Form.FilterOn = True
Me.SA_Distance_Calc_subform6.Form.Filter = sqlString


Me.SA_Distance_Calc_subform6.Form.Requery
Me.Refresh



'MasterForm.DoCmd.OpenQuery SQLCommand


strGetSQL = "SELECT TOP 3 [Service Agent],State,city" _
& "FROM Forms!SA_Distance_Calc_subform6" _
& "WHERE [Zodiaq Zip Locations_Zip] = Forms!SA_Distance_Calc_subform6!Combo14.Value" _
& "ORDER BY [Travel Cost];"


Me.SA_Distance_Calc_subform6.Form.FilterOn = True
Me.SA_Distance_Calc_subform6.Form.Filter = strGetSQL


Me.SA_Distance_Calc_subform6.Form.Requery
Me.Refresh



'MasterForm.DoCmd.OpenQuery SQLCommand
On Error GoTo Err_CalculateZip_Click
' DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70




Exit_CalculateZip_Click:
Exit Sub

Err_CalculateZip_Click:
MsgBox Err.Description
Resume Exit_CalculateZip_Click

End Sub

 

[JerryKlmns]

Jayzxtx

Let 's take on step at a time.You have a zip code for the delivery and a zip code that is your sending position. You 'll have to create a general query that computes that distance cost of every service agent. If you include the "TOP 3" along with the ordering of the computed distance cost, you 'll get what you need. Who you feed the delivery zip code now? Inlude a WHERE clause for the delivery zip code. Built that query for a true zip code and run it, ie
WHERE [Zodiaq Zip Locations_Zip] = 12345

If the result is correct then you set the following criteria in the query grid
WHERE [Zodiaq Zip Locations_Zip] = Forms!YourMainFormName!Combo14
Change to the SQL view, copy all and go to the subform property sheet at the Data tab and paste that to the Record Source property. I think that 's it.

Give it a try and post back.