Specifiying < as parameter in a query 2

[MrMode]

I have two text boxes and list box on an unbound form.

I am using a Postcode table that allows me to find all postcodes within a given radius.

I use a query to populate the list box after updating the first text box with a postcode. I then want to further limit the results when I enter a distance in the second text box.

I have been able to set up the starting postcode as a parameter, which is resolved with a reference to the correct text box on the form. I can hard code the distance as 'less than x' (<15 etc), but cannot set up the 'distance' as a parameter... For some reason I cannot think this through!

Here is the query:

SELECT PC1.PostCode, PC2.PostCode, GreatCircleDistance([PC1].[lattitude],[PC1].[Longitude],[PC2].[lattitude],[PC2].[Longitude],True,True) AS Distance
FROM Postcodes AS PC1, Postcodes AS PC2
WHERE (((PC1.PostCode)=[Start]) AND ((GreatCircleDistance([PC1].[lattitude],[PC1].[Longitude],[PC2].[lattitude],[PC2].[Longitude],True,True))<15));

Here is the afterupdate on the form for the first text box:

Private Sub Text0_AfterUpdate()

Me.List4.RowSource = _
"SELECT PC1.PostCode, PC2.PostCode, GreatCircleDistance([PC1].[lattitude],[PC1].[Longitude],[PC2].[lattitude],[PC2].[Longitude],True,True) AS Distance " & vbCrLf & _
"FROM Postcodes AS PC1, Postcodes AS PC2 " & vbCrLf & _
"WHERE PC1.PostCode = " & Chr(34) & Me.Text0 & Chr(34)

End Sub


If I try to set up the value as a parameter all I get is a blank result returned:

WHERE (((PC1.PostCode)=[Start]) AND ((GreatCircleDistance([PC1].[lattitude],[PC1].[Longitude],[PC2].[lattitude],[PC2].[Longitude],True,True)) Like (GreatCircleDistance([PC1].[lattitude],[PC1].[Longitude],[PC2].[lattitude],[PC2].[Longitude],True,True))<[enter value]));

 

[SkipVought]

hi,

please get rid of those stupid characters in your code, where they do not belong.

WHERE (((PC1.PostCode)=[Start]) AND ((GreatCircleDistance([lattitude1],[Longitude1],[lattitude2],[Longitude2],True,True))<[Distance1]));

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PHV]

What about this ?

Me!List4.RowSource = _
 "SELECT PC1.PostCode,PC2.PostCode,GreatCircleDistance(PC1.lattitude,PC1.Longitude,PC2.lattitude,PC2.Longitude,True,True) AS Distance" & _
 " FROM Postcodes PC1,Postcodes PC2 WHERE PC1.PostCode='" & Me!Text0 & "'" & _
 " AND GreatCircleDistance(PC1.lattitude,PC1.Longitude,PC2.lattitude,PC2.Longitude,True,True)<" & Me![your distace textbox]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrMode]

Thanks, quite right - removed.

Unfortunately your suggestion is not working, it asks me to input all the longitude and latitudes. There are some strange smilies in the code above that I supplied earlier, it should be...

WHERE (((PC1.PostCode)=[Start]) AND ((GreatCircleDistance([PC1].[lattitude],[PC1].[Longitude],[PC2].[lattitude],[PC2].[Longitude],True,True))<15));

Why does putting a parameter in place of <15 not work?

 

[PHV]

Did you try my suggestion ?
Note: you have to put the real name of the textbox in the code.

Why does putting a parameter in place of <15 not work
The parameter (textbox) should have a value of 15 (not <15)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrMode]

Thank you.

It works!

Thank you very much.

 

[MrMode]

Can I bug you again?

Your solution is working perfectly.

Now that you have limited the list to postcodes within a specific radius, I need to use those postcodes to populate another listbox, but keep getting "" instead of any values...

Me.list11.rowsource = _
"SELECT tblUKPostcodes.PostalBrick " & _
"FROM tblUKPostcodes " & _
"where postalbrick ='" & me.list4 & "'"

Why can I not pick out the value that the listbox is displaying and use it as the parameter for another listbox? I have managed it on other forms without issues, but this one is driving me nuts!

Thanks

 

[Andrzejek]

If you have, let's say, 10 items (PostalBricks) in your [tt]list4[/tt], which one do you want to be used in your
[tt]"where postalbrick ='" & me.list4 & "'"[/tt] ?

Have fun.

---- Andy

 

[MrMode]

all of them

The idea is to find all companies that are in each of those postcodes... (or those selected in a multi select (simple) list).

For example, I have 10 postcodes within 10 miles of x.
Not all postcodes will have companies in.
Where there are companies in each of the postcodes, I want them to appear in another listbox (list11)
These can then also be selected in a multiselect (simple) listbox

List4 is producing the list of postcodes within the specified radius.
I cannot seem to grab hold of the second column where those within the parameter are and use them to filter the companies I want to look at.

Does that help?

 Private Sub Text2_AfterUpdate()
Me!List4.RowSource = _
        "SELECT PC1.PostCode, PC2.PostCode, GreatCircleDistance(PC1.lattitude,PC1.Longitude,PC2.lattitude,PC2.Longitude,True,True) AS Distance" & _
        " FROM Postcodes PC1,Postcodes PC2 WHERE PC1.PostCode='" & Me!Text0 & "'" & _
        " AND GreatCircleDistance(PC1.lattitude,PC1.Longitude,PC2.lattitude,PC2.Longitude,True,True)<" & Me!Text2
        
   Me.List11.RowSource = _
       "SELECT tblUKPostcodes.PostalBrick " & _
"FROM tblUKPostcodes where tblUKPostcodes.PostalBrick " & _
        "WHERE tblUKPostcodes.PostalBrick ='" & Me.List4 & "'"
End sub

This is what debug print spits out for List11 when this code runs:

SELECT tblUKPostcodes.PostalBrick FROM tblUKPostcodes where tblUKPostcodes.PostalBrick WHERE tblUKPostcodes.PostalBrick =''

 

[dhookom]

You need some code to loop through the selected items from the list box. This code will build an addition to a where clause like:

AND ColorName IN ('Red','Green','Yellow')

Based on a call to the function like:

  BuildIn(Me.lboColors, "ColorName","'")

Function BuildIn(lboListBox As ListBox, _
        strFieldName As String, strDelim As String) As String
[COLOR=#4E9A06]    'send in a list box control object[/color]
    Dim strIn As String
    Dim varItem As Variant
        
    If lboListBox.ItemsSelected.Count > 0 Then
        strIn = " AND " & strFieldName & " In ("
        For Each varItem In lboListBox.ItemsSelected
            strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & ", "
        Next
        [COLOR=#4E9A06]'remove the last ", " and add the ")"[/color]
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If
    BuildIn = strIn

End Function

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

Thanks dhookhom

That is a stage after what I was after, but this is what I currently have as the afterupdate event for List4 (that has the postcodes in it) based on the code suggestion you gave me before. I am not quite sure how to use the function suggestion.

This is what I currently have (which works in another form perfectly)

Private Sub List4_AfterUpdate()

    Dim strSQLRowSource As String
    Dim strWhere As String
    Dim varItem
    
    strSQLRowSource = "SELECT tblCompany.[Company Name], tblCompany.PostalBrickID, tblUKPostcodes.PostalBrick " & _
                        "FROM (tblCompany INNER JOIN tblCompany.PostalBrickID = tblUKPostcodes.ID" & _
                        " WHERE ~ ;"
    strWhere = " tblUKPostcodes.PostalBrick IN ("
    For Each varItem In List4.ItemsSelected
        strWhere = strWhere & Me.List4.ItemData(varItem) & ","
    Next
    
    strWhere = Left(strWhere, Len(strWhere) - 1) & ") "
    Me.List11.RowSource = Replace(strSQLRowSource, "~", strWhere)
    
    Debug.Print Me.List11.RowSource 'for troubleshooting
    
End Sub

However, it is throwing up a parameter box every time I select a postcode, and if I select more than one, it throws up a parameter box for each...

I do get the results I want if I reinput each postcode into each parameter box based on each selection I make...

I would like to update List11 as I make selections in Text0 and Text2 based on List4 first.

Then I would like to update List11 based on postcodes selected in List4. If you can help me with this last section, that would be most appreciated.

Thanks

 

[Andrzejek]

Try thi:

strWhere = Left(strWhere, Len(strWhere) - 1) & ") "[blue]
Debug.Print Replace(strSQLRowSource, "~", strWhere)[/blue]
Me.List11.RowSource = Replace(strSQLRowSource, "~", strWhere)

and SEE if your Select statement is correct.

Is your PostalBrick field defined as a Number or a Text?

Have fun.

---- Andy

 

[MrMode]

Will do, it is a text field

 

[MrMode]

Sorry.

Yes, text field. I have added chr(34) either side and now it works.

Doh

Thanks again