INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Select query problems...

Select query problems...

(OP)

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.

CODE

Private Sub PRINTMAPlbl_Click()
    Dim dbs As DAO.Database
    Dim rsSQL As DAO.Recordset
    Dim strSQL As String
    Dim BUILDURL As String
    BUILDURL = "https://www.google.com/maps/dir/"
    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

RE: Select query problems...

Hi,

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Select query problems...

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

RE: Select query problems...

And you're supplying that value from a form?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Select query problems...

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

RE: Select query problems...

CODE

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;" 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Select query problems...

(OP)
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.

RE: Select query problems...

Hey, turn around. Nice to see your back! winky smile

Glad I could help.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Select query problems...

You could (probably) help yourself by doing this:

CODE

...
strSQL = "SELECT DISTINCT ...
Debug.Print strSQL
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.

RE: Select query problems...

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.

CODE

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.

RE: Select query problems...

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

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close