Syntax issue with query

[shank]

I have a function that's used in a query and I don;t think I'm getting the query syntax correct. The top works, but it's not sorting the string as I want it. I'm trying to add an ORDER BY in the statement and it causes an error with the function. But I don;t think the function is the problem. I've been fussing with the double and single quotes and not getting it right.



Can anyone see what I'm doing wrong? [SortKey] is numeric.

This Works: BuildStr("SELECT DISTINCT '""' & [Folders]![FileNam] & '""' FROM Folders"," ")


This does not: BuildStr("SELECT DISTINCT '""' & [Folders]![FileNam] & '""' FROM '""' & Folders & '""' ORDER BY '""' & [SortKey] & '"," ")

Thanks!

[ranman256]

in v1 you have folders as a hard string, not a variable.

in v2 you put FOLDERS outside the string. Correct:
ssql = "SELECT DISTINCT " & [Folders]![FileNam] & " FROM Folders ORDER BY " & [SortKey]

[shank]

Your SQL String does work: "SELECT DISTINCT " & [Folders]![FileNam] & " FROM Folders ORDER BY " & [SortKey]

But I'm still not able to get it incorporated with the function. It's got to be syntax.

This does not work: BuildStr("SELECT DISTINCT " & [Folders]![FileNam] & " FROM Folders ORDER BY " & [SortKey] & "," ")
I believe it's the characters following [SortKey], but for the life of me I cannot get the double and single quotes figured out.
[SortKey] is numeric so I'm guessing it's some combination of double and single quotes.

What am I doing wrong? Is there a way I can I see the resulting SQL in MS Access so I can see what the resulting string I'm

Thanks
kevin

[CJ_London]

BuildStr is not (so far as I am aware) a vba function. If all it does is concatenate a string together, I really don't see the benefit. What does it do? perhaps you can provide the code

[shank]

Below is the function. I use it to generate a string of PDF filenames that concatenate into one PDF.

Function BuildStr(pstrSQL As String, _
        Optional pstrDelim As String = "") _
        As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
'   this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'BuildStr("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'


'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset(pstrSQL)


'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
    'Dim rs As New ADODB.Recordset
    'rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
'====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
    End If
    BuildStr = strConcat
End Function

Thanks!

[pbaldy]

What am I doing wrong? Is there a way I can I see the resulting SQL in MS Access so I can see what the resulting string I'm

Here you go:

http://www.baldyweb.com/ImmediateWindow.htm

[shank]

I tried the following Debug.Print and get nothing.
Debug.Print BuildStr
Debug.Print strConcat
Debug.Print pstrSQL

The function errors: Syntax error in query. Incomplete query clause.
On this line: Set rs = db.OpenRecordset(pstrSQL)

The most recent query is: BuildStr("SELECT DISTINCT " & [Folders]![FileNam] & " FROM Folders ORDER BY '""' & [SortKey] & "," ")

I still think I'm getting something wrong with the syntax. Thoughts?

Thanks!

[pbaldy]

Try the 3rd one right before the line that errors. You are looking in the immediate window for the results?

[shank]

Yes, I'm looking in the intermediate window for results.


This statement works: BuildStr("SELECT DISTINCT '""' & [Folders]![FileNam] & '""' FROM Folders"," ")


... and I get this result in the intermediate window.
"Available Qty 646733 102019-102619.pdf" "Invoice Breakout 646733 102019-102619.pdf" "Invoice Summary 646733 102019-102619.pdf" "PA Invoice Reports 646733 102019-102619.pdf" "Returns 646733 102019-102619.pdf" "Sold Qty 646733 102019-102619.pdf"

This one does not work and I get no Debug.Print results: BuildStr("SELECT DISTINCT " & [Folders]![FileNam] & " FROM Folders ORDER BY '""' & [SortKey] & "," ")

I don't understand what you meant by "Try the 3rd one right before the line that errors"

I tried Debug.Print on these lines that are before the error line and nothing prints.
Debug.Print db
Debug.Print db.OpenRecordset(pstrSQL)


Did I misunderstand what you suggested?

Thanks!

[pbaldy]

I meant:

Debug.Print pstrSQL
db.OpenRecordset(pstrSQL)

db is just a database variable, nothing would print.

[shank]

OK. I now understand what you meant. Thanks!
Debug.Print pstrSQL
db.OpenRecordset(pstrSQL)


Just to test the above Debug.Print, I started with this one that does not have the [SortKey].
This one works: BuildStr("SELECT DISTINCT '""' & [Folders]![FileNam] & '""' FROM Folders"," ")
And I get the results I need. Great!
SELECT DISTINCT '"' & [Folders]![FileNam] & '"' FROM Folders
"Available Qty 646733 102019-102619.pdf" "Invoice Breakout 646733 102019-102619.pdf" "Invoice Summary 646733 102019-102619.pdf" "PA Invoice Reports 646733 102019-102619.pdf" "Returns 646733 102019-102619.pdf" "Sold Qty 646733 102019-102619.pdf"


This does not work: BuildStr("SELECT DISTINCT '""' & [Folders]![FileNam] & '""' FROM Folders ORDER BY '""' & [SortKey] "," ")
Debug.Print nets: SELECT DISTINCT '"' & [Folders]![FileNam] & '"' FROM Folders ORDER BY '"' & [SortKey]

I have been experimenting with the syntax and I either get syntax errors and cannot run it. Or the above Debug.Print nets.

In the end, the string I need will be like the above filenames between quotes "filename" "filename" etc...

There's got to be something simple I'm missing. Suggestions?

Thanks!

[pbaldy]

I wouldn't think you'd be wanting to pass the concatenation through, since the recordset uses the SQL directly. FileName being a field in the table it shouldn't have quotes around it in my mind. In other words, this returns exactly what I expect:

SELECT DISTINCT Car_description
FROM Car_type;

this returns the literal string "car_description":

SELECT DISTINCT "Car_description"
FROM Car_type;

Does this work?

BuildStr("SELECT DISTINCT " & [Folders]![FileNam] & " FROM Folders ORDER BY " & [SortKey], "")

[shank]

When I run your suggestion: BuildStr("SELECT DISTINCT " & [Folders]![FileNam] & " FROM Folders ORDER BY " & [SortKey], "")

I get the below Syntax error:
Syntax error (missing operator) in query expression 'Available Qty 646733 102019-102619.pdf'


Debug.Print nets: SELECT DISTINCT Available Qty 646733 102019-102619.pdf FROM Folders ORDER BY 3

This is the data in the table Folders:
SortKey FileNam
1 Invoice Summary 646733 102019-102619.pdf
2 Sold Qty 646733 102019-102619.pdf
3 Available Qty 646733 102019-102619.pdf
4 Returns 646733 102019-102619.pdf
5 PA Invoice Reports 646733 102019-102619.pdf
6 Invoice Breakout 646733 102019-102619.pdf

To stop the process I have to click END several times and Debug.Print will get these results:
SELECT DISTINCT Available Qty 646733 102019-102619.pdf FROM Folders ORDER BY 3
SELECT DISTINCT Invoice Breakout 646733 102019-102619.pdf FROM Folders ORDER BY 6
SELECT DISTINCT Invoice Summary 646733 102019-102619.pdf FROM Folders ORDER BY 1
SELECT DISTINCT PA Invoice Reports 646733 102019-102619.pdf FROM Folders ORDER BY 5
SELECT DISTINCT Returns 646733 102019-102619.pdf FROM Folders ORDER BY 4
SELECT DISTINCT Sold Qty 646733 102019-102619.pdf FROM Folders ORDER BY 2

In the end I shouldn't be seeing the [SortKey] value. And Filenames should be enclosed in double quotes.

Thoughts?

Thanks!

[pbaldy]

That's not from the SQL though, that's part of the result. Maybe I'm just hungry, it's almost dinner time. Can you attach the db here to play with?

[shank]

See attached MS Access 2019 Concat Test.mdb

If you run qry_UPD_BuildString it will update the table: Temp_PDF_BuildString
You'll see the string I need to end up with. This version does not include the [SortKey].

qry_SortKey_Test is the query I've been trying add the [SortKey]

I really appreciate your advice. No need to burn the midnight oil on this.

Thank you!