Syntax error on dynamic query.

[stephen81]

I have a form set up with various options to select records. I'm trying to write a dynamic sql query which I can use as the record source. I've set it up as a function which I then set as the form's recordsource on load.

Everytime I open the form though, I get a Runtime error 3135. Syntax error in JOIN operation. This is the first time I've used the join in SQL and I really can't figure out what is wrong. Can anyone spot anything?

Public Function Gen_Filter(ByVal modnum As String, ByVal officer As Variant, ByVal tf As Variant, tt As Variant, cf As Variant, ct As Variant, ic As Variant) As String
On Error GoTo Err_Gen_Filter

Dim strsql As String
Dim strsql1 As String

strsql = "SELECT DISTINCT Standards.Standard_ID" & Chr(13) & Chr(10) & _
         "FROM Standards LEFT JOIN Standard_Responsible_Officers" & Chr(13) & Chr(10) & _
         "ON Standards.Standard_ID = Standard_Reponsible_Officers.Standard_ID " & Chr(13) & Chr(10) & _
         "WHERE Standards.Standard_ID <> 0 "

MsgBox strsql

         
If (Len(Nz(modnum, "")) <> 0) Then
strsql = strsql & "AND ((Standards.Module_Number) = " & modnum & ") " & Chr(13) & Chr(10)
End If
MsgBox strsql
If (Len(Nz(officer, "")) <> 0) Then
strsql = strsql & "AND ((Standard_Responsible_Officers.Responsible_Officer) = " & officer & ") " & Chr(13) & Chr(10)
End If
MsgBox strsql
If (Len(Nz(tf, "")) <> 0) Then
strsql = strsql & "AND ((Standards.Target_Date) >= " & tf & ") " & Chr(13) & Chr(10)
End If
MsgBox strsql
If (Len(Nz(tt, "")) <> 0) Then
strsql = strsql & "AND ((Standards.Target_Date) <= " & tt & ") " & Chr(13) & Chr(10)
End If

MsgBox strsql
If (ic = "-1") Then

    If (Len(Nz(cf, "")) <> 0) Then
    strsql = strsql & "AND ((Standards.Completed_Date) >= " & cf & ") " & Chr(13) & Chr(10)
    End If
    If (Len(Nz(ct, "")) <> 0) Then
    strsql = strsql & "AND ((Standards.Completed_Date) <= " & ct & ") " & Chr(13) & Chr(10)
    End If
Else
    strsql = strsql & "AND (len(Nz(Standards.Completed_Date,""""))= 0)"
End If
MsgBox strsql
strsql = strsql & ";"
Gen_Filter = strsql

Exit_Gen_Filter:
    Exit Function

Err_Gen_Filter:
    MsgBox Err.Description
    Resume Exit_Gen_Filter
    
End Function

 

[Mile-O]

It'll be bracketing. Build a query and observe the bracketing made by the Query Editor in SQL View.

You'll need to factor this into your code.

 

[stephen81]

Thanks for the suggestion. In checking the brackets against a query generated by query builder I noticed a shorter line. I had missed a letter out of one of the words!!

 

[stephen81]

Additional help required.

Now that I have that working, I need to use that query in another query. I have done this with the query builder and the sql code it generates looks like this.

SELECT Standards.Standard_ID, Standards.Module_Number, Standards.Standard_Number, Standards.Standard_Description, Standards.Standard_Action, Standards.Target_Date, Standards.Completed_Date, Standards.Evidence
FROM Sub_Sub_Form INNER JOIN Standards ON Sub_Sub_Form.Standard_ID = Standards.Standard_ID
ORDER BY Standards.Module_Number, Standards.Standard_Number;

Where sub_sub_form is the query I want to replace with my dynamic query. I can't just include all the fields in my original query because some of them are memo fields so it won't let me do that.

Can anyone point me in the right direction please?

 

[Pat Hartman]

When your SQL is not dynamic, there is no reason to build it in code. It is much more efficient and easier to build and test if you build stored querydefs. And since queries can reference other queries, that solves your final problem.

 

[stephen81]

My sub query is dynamic though. I'm happy to use a normal query but it needs to have my dynamic query as a sub query and I don't know how to include it with a normal query.

 

[Mile-O]

I've never built a dynamic SQL query using joins because of the problems posed by bracketing but....

...build a query as normal (not in code) that selects all of the possible tables from the different tables. Have very basic criteria if necessary.

When building your dynamic query simply select from the predefined querydef and apply criteria.


P.S. Chr(13) & Chr(10) can be replaced with the enumerated access constant vbCrLf.

 

[stephen81]

Sorry for being stupid but I don't really understand what you mean by

simply select from the predefined querydef

If you mean, write a normal select query then use that instead of my 'Standards' table as the source for my dynamic query then I don't think it will work that way round.

Three of the fields I need to return are memo fields. Unfortunately I can't include them in a SELECT DISTINCT query. That is the only reason I need two queries in the first place.

My plan was
Generate my dynamic query to return just one field, the Standard_ID field. This needs to be a DISTINCT query as part of it is based on a sub-table. If I don't include DISTINCT, it returns the same record as many times as there are related records in the sub-table.
I was then planning to use the resulting list of Standard_ID in a simple select query so I could retrieve all the fields I need in my form.

 

[Pat Hartman]

Sorry, I just looked at the first part of the query. I missed the stuff being added at the end.

Why do you keep appending Chr(13) and Chr(10)?

You seem to be missing spaces before the AND's.

(len(Nz(Standards.Completed_Date,""""))= 0) should be
Standards.Completed_Date Is Null

You may need to enclose the other dates with # delimiters.

 

[Mile-O]

And all these fields with names like_this within square brackets so that they look [like_this].

 

[stephen81]

I've tried the fields like_this with and without square brackets around them to no avail
and Pat, I've only included the Chr(13) and Chr(10) so I can see what it builds more clearly in my messagebox's. I've now replaced them with vbCrLf as per Mile-O-Phile's previous suggestion.
I haven't put spaces before the AND statements but I've put them at the end of each statement instead. I don't know if that makes a difference or not?

Anyway, touch wood I've got it sorted now. Not quite how I planned though. I've got the dynamic query creating a table, which I can then use in my second query.

I got this idea from experts-exchange where I also found out that you can't use SELECT in a FROM statement prior to Access 2000 I think, so my sub query idea wouldn't have worked anyway.

Thanks for all your help.