Incomplete Syntax Clause (syntax error)

[ajetrumpet]

I think you all probably know by now that I'm working on a project. At any rate though, have something I can't get my head around again:

Set rs = db.OpenRecordset("SELECT * FROM '" & pTblName & "' " & _
                          "WHERE ['" & pFldName & "'] = '" & pName & "' " & _
                          "ORDER BY '" & sDateFld & "'", dbOpenDynaset)

can anyone spot the error? the immediate window shows single quotes around the concats, so if Access is reading it that way it's not the correct input. thanks!

[SteveH2508]

Is sDateFld a date? If so your delimiters should be #

[ajetrumpet]

Is sDateFld a date? If so your delimiters should be #

steve. I don't think they should, right? does this makes sense...?

select * from table

ORDER BY #[order_date]#

that's not right. is that what you're saying? a date field in an order by clause is not enclosed in # signs. that's only for a criteria clause, which I have none here that contain dates.

[ajetrumpet]

steve,

this is the solution that access accepts:

Set rs = db.OpenRecordset("SELECT * FROM " & tbl & " " & _
                          "WHERE [" & pfld & "] = '" & pname & "' " & _
                          "ORDER BY [" & dtefld & "]", dbOpenDynaset)

this also gives the correct sql printout in the immediate window. problem solved. but my question is now, why is this not following the correct concatenation conventions of string variables? it's reading it like I'm using number variables instead of string variables. is this right?

[pbaldy]

The only place you would use the delimiter is around the value for the criteria. You would never have delimiters around the table name or the field name in any clause. In other words, the only place you'd have a delimiter is around the value being looked for:

WHERE FieldName = 'Paul'