Select query problems... 1

[SnayJ]

Ok, so I'm trying to loop thru a query and pull out the data in the first field of the query. Then concatenate that data into a string to be used in Google Maps. The data represent addresses for stops on a trip. If I use a simple Select Query like "SELECT * FROM STOREtbl" - it works fine. But I want to only select certain records. I created a query that pulls the right information, but when I use that query SQL vs the simple one; I get an error. (Run-Time Error 3061... Too Few Parameters, Expected 1.) Here is my code.

Private Sub PRINTMAPlbl_Click()
    Dim dbs As DAO.Database
    Dim rsSQL As DAO.Recordset
    Dim strSQL As String
    Dim BUILDURL As String
    BUILDURL = "[URL unfurl="true"]https://www.google.com/maps/dir/"[/URL]
    Set dbs = CurrentDb
    strSQL = "SELECT DISTINCT STOREtbl.STOREADDRESS, STOPtbl.STOPSUFFIX FROM TRIPtbl, STOREtbl INNER JOIN STOPtbl ON STOREtbl.STOREID = STOPtbl.STOREID WHERE (((TRIPtbl.TRIPID) = [Forms]![MAINfrm]![MAINSUBfrm]![TRIPbox].[Value]) And ((STOPtbl.TRIPID) = [Forms]![MAINfrm]![MAINSUBfrm]![TRIPbox].[Value])) ORDER BY STOPtbl.STOPSUFFIX;"
    Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    With rsSQL
        rsSQL.MoveFirst
        If Not (rsSQL.EOF) Then
            Do Until rsSQL.EOF = True
                BUILDURL = BUILDURL & Replace(rsSQL.Fields(0), " ", "+") & "/"
                rsSQL.MoveNext
            Loop
        Else
            MsgBox "There are no records in the recordset."
        End If
    End With
    If Right(BUILDURL, 1) = "/" Then
        BUILDURL = Left(BUILDURL, Len(BUILDURL) - 1)
    End If
    Application.FollowHyperlink BUILDURL
End Sub

Appreciate any help you can offer.


Jeff

 

[SkipVought]

Hi,

So 1) what's the data type for TRIPID and 2) is it the same in both tables?


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SnayJ]

Yes, it's an autonumber in 1 table and Number in the other. Does type of Number matter?

 

[SkipVought]

And you're supplying that value from a form?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SnayJ]

Yes...it's from a listbox and the value of the listbox is the same Autonumber.

 

[SkipVought]

    strSQL = "SELECT DISTINCT STOREtbl.STOREADDRESS, STOPtbl.STOPSUFFIX 
FROM TRIPtbl, STOREtbl INNER JOIN STOPtbl ON STOREtbl.STOREID = STOPtbl.STOREID 
WHERE (((TRIPtbl.TRIPID) = [highlight #FCE94F]" &[/highlight] 
[Forms]![MAINfrm]![MAINSUBfrm]![TRIPbox].[Value] [highlight #FCE94F]&
"[/highlight])  And ((STOPtbl.TRIPID) = [highlight #FCE94F]" &[/highlight] 
[Forms]![MAINfrm]![MAINSUBfrm]![TRIPbox].[Value] [highlight #FCE94F]& 
"[/highlight])) 
ORDER BY STOPtbl.STOPSUFFIX;"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SnayJ]

You are the man... Appreciate it Skip. I've been coming here for years when I need help and you've pulled thru for me many a time. Thanks a lot.

 

[SkipVought]

Hey, turn around. Nice to see your back! ;-)

Glad I could help.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

You could (probably) help yourself by doing this:

...
strSQL = "SELECT DISTINCT ...[blue]
Debug.Print strSQL[/blue]
Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
...

and see in the Immediate Window the outcome of your SELECT Sql
Just copy it and paste into (Access?) to see if it works the way you want.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[DjangMan]

Just as an aside, Skip's suggestion also allows VBA to process a long string without truncating the overall string value. By appending multiple strings you overcome a 'limitation' that VBA and VB have. I wish I could find a reference for this but I can't at the moment - I've just encountered cases where breaking up a string assignment allows it to store very long values. I've found this by using debug.print and discovered that the sql being run is select blah, blah, really long query from table where somefie. <- The query was truncation for no reason that I could see.

I'm often coding SQL statements so I write them in a style similar to below: Each field on its own line prefixed with a comma so I can add a new field to the query without having to remember to add the comma to the prior line. WHERE 1=1 allows me to append new criteria and simply prefix each new criterion with "AND" (unless it is more complicated than that). & vbCrLf at the end of each line prevents me from making spacing mistakes and makes the resulting debug.print output easier to parse for my brain.

strSQL = "SELECT " & vbcrlf
strSQL = strSQL & "DISTINCT " & vbcrlf
strSQL = strSQL & "STOREtbl.STOREADDRESS" & vbcrlf
strSQL = strSQL & ", STOPtbl.STOPSUFFIX " & vbcrlf
strSQL = strSQL & "FROM TRIPtbl, STOREtbl " & vbcrlf
strSQL = strSQL & "INNER JOIN STOPtbl ON STOREtbl.STOREID = STOPtbl.STOREID " & vbcrlf
strSQL = strSQL & "WHERE 1=1 " & vbcrlf
strSQL = strSQL & "AND (((TRIPtbl.TRIPID) = " & vbcrlf
strSQL = strSQL & "[Forms]![MAINfrm]![MAINSUBfrm]![TRIPbox].[Value] & " & vbcrlf
strSQL = strSQL & ")  And ((STOPtbl.TRIPID) = " & vbcrlf
strSQL = strSQL & "[Forms]![MAINfrm]![MAINSUBfrm]![TRIPbox].[Value] & " & vbcrlf
strSQL = strSQL & ")) " & vbcrlf
strSQL = strSQL & "ORDER BY STOPtbl.STOPSUFFIX;" & vbcrlf

PS: I didn't check to see if I broke the syntax by re-formatting the SQL.

 

[Andrzejek]

Just as an aside to DjangMan's aside,
In this scenario I also add a Space at the beginning of the line, too:

strSQL = "SELECT " & vbcrlf
strSQL = strSQL & " DISTINCT " & vbcrlf
strSQL = strSQL & " STOREtbl.STOREADDRESS" & vbcrlf
strSQL = strSQL & " , STOPtbl.STOPSUFFIX " & vbcrlf
strSQL = strSQL & " FROM TRIPtbl, STOREtbl " & vbcrlf
strSQL = strSQL & " INNER JOIN STOPtbl ON STOREtbl.STOREID = STOPtbl.STOREID " & vbcrlf
strSQL = strSQL & " WHERE 1=1 " & vbcrlf
strSQL = strSQL & " AND (((TRIPtbl.TRIPID) = " & vbcrlf
strSQL = strSQL & " [Forms]![MAINfrm]![MAINSUBfrm]![TRIPbox].[Value] & " & vbcrlf
strSQL = strSQL & " )  And ((STOPtbl.TRIPID) = " & vbcrlf
strSQL = strSQL & " [Forms]![MAINfrm]![MAINSUBfrm]![TRIPbox].[Value] & " & vbcrlf
strSQL = strSQL & " )) " & vbcrlf
strSQL = strSQL & " ORDER BY STOPtbl.STOPSUFFIX;"

Just in case the Space is missing at the end of the previous line.

Have fun.

---- Andy

There is a great need for a sarcasm font.