Recordset Help

[DSCartwright]

Hi, Basically I want to use the record set query to highlight and bypass any records which don't contain any data, I initially tried to do this but it did not like the queries which where set up in the form. What I am unsure of is how to embed my SQL query into the the recordset function as it is throwing out errors, this is the first time i have tried to use this so apologies if my question is a little vague. But here is what I am working on. Also should mention this needs to be Access 2000 compatible. regards

Private Sub preview_Click()
On Error GoTo Err_preview_Click
    
    count_valid_matters
    
    Dim docname As String
    Dim criterion As String, MyDb  As Database, MySet As DAO.Recordset
          
    Set MyDb = DBEngine.Workspaces(0).Databases(0)
    Set MySet = MyDb.OpenRecordset("Parameters", DB_OPEN_DYNASET)

(This is what i currently have, this is the SQL statement i would like to embed into Set MySet = MyDb.OpenRecordset)

SELECT matters.[counter-mt], [Client-ct] & " " & [Surname-ct] & " " & [Forenames-ct] & ":" & Format([Matter-mt],"0000") & "-" & [Description-mt] AS [No], [Surname-ct] & " " & [Forenames-ct] & ":" & Format([Matter-mt],"0000") & "-" & [Description-mt] AS Name
FROM ((([Parameters] INNER JOIN practice ON Parameters.[practice-us] = practice.[practice-co]) INNER JOIN clients ON practice.[practice-co] = clients.[Practice-ct]) INNER JOIN matters ON clients.[counter-ct] = matters.[counterct-mt]) INNER JOIN timetran ON matters.[counter-mt] = timetran.[countermt-tt]
WHERE (((timetran.[date-tt])>=[forms]![billing guide form]![startdate-s] And (timetran.[date-tt])<=[forms]![billing guide form]![enddate-s]) AND ((timetran.[billedstatus-tt])<>[Forms]![billing guide form]![excludebilled-s]))
GROUP BY matters.[counter-mt], [Client-ct] & " " & [Surname-ct] & " " & [Forenames-ct] & ":" & Format([Matter-mt],"0000") & "-" & [Description-mt], [Surname-ct] & " " & [Forenames-ct] & ":" & Format([Matter-mt],"0000") & "-" & [Description-mt];

 

[DSCartwright]

also if there was an alternative to this method i would be open to try it, regards

 

[jdraw]

" it is throwing out errors" is not very helpful. What exactly is the error number and/or the message?

Parameters is Not a good choice for a table/query name. It can have special meaning.

For some tutorial help with vba and sql see Martin Green's site at
http://www.fontstuff.com/access/index.htm

and then Access and SQL Part 1: Setting the SQL Scene at the link

Post back with specific questions or comments once you've seen the tutorial.

 

[DSCartwright]

Okay, I decided that there was no need to use parameters as another query finds what i need to cover parameters, however through removing that my SQL statement is carrying another error which is "JOIN expression not supported. 3296.

This is my code.

Set MySet = MyDb.OpenRecordset("SELECT matters.[counter-mt], [Client-ct] & ' ' & [Surname-ct] & ' ' & [Forenames-ct] & ':' & Format([Matter-mt],'0000') & '-' & [Description-mt] AS [No], [Surname-ct] & ' ' & [Forenames-ct] & ':' & Format([Matter-mt],'0000') & '-' & [Description-mt] AS Name " _
& "FROM ((Practice INNER JOIN clients ON practice.[practice-co] = clients.[Practice-ct]) INNER JOIN clients ON practice.[practice-co] = clients.[Practice-ct]) INNER JOIN matters ON clients.[counter-ct] = matters.[counterct-mt]) INNER JOIN timetran ON matters.[counter-mt] = timetran.[countermt-tt] " _
& "GROUP BY matters.[counter-mt], [Client-ct] & ' ' & [Surname-ct] & ' ' & [Forenames-ct] & ':' & Format([Matter-mt],'0000') & '-' & [Description-mt], [Surname-ct] & ' ' & [Forenames-ct] & ':' & Format([Matter-mt],'0000') & '-' & [Description-mt]", DB_OPEN_DYNASET)

Any ideas what may be causing it?

regards

 

[DSCartwright]

Ok I have eliminated the problem i was having above, I had double posted something in to the SQL statement by accident. however now when i try to execute my query i am getting this error.

Syntax Error (Missing Operator) in expression. 3077

This is my code

Set MySet = MyDb.OpenRecordset("SELECT matters.[counter-mt], [Client-ct] & ' ' & [Surname-ct] & ' ' & [Forenames-ct] & ':' & Format([Matter-mt],'0000') & '-' & [Description-mt] AS [No], [Surname-ct] & ' ' & [Forenames-ct] & ':' & Format([Matter-mt],'0000') & '-' & [Description-mt] AS Name " _
& "FROM ((Practice INNER JOIN clients ON practice.[practice-co] = clients.[Practice-ct]) INNER JOIN matters ON clients.[counter-ct] = matters.[counterct-mt]) INNER JOIN timetran ON matters.[counter-mt] = timetran.[countermt-tt] " _
& "GROUP BY matters.[counter-mt], [Client-ct] & ' ' & [Surname-ct] & ' ' & [Forenames-ct] & ':' & Format([Matter-mt],'0000') & '-' & [Description-mt], [Surname-ct] & ' ' & [Forenames-ct] & ':' & Format([Matter-mt],'0000') & '-' & [Description-mt]", DB_OPEN_DYNASET)

any ideas what is wrong with it? through doing some background reading is highlights this would be down to the apostrophes but as I'm not comfortable in the Recordset I'm not sure what is wrong.

regards