Problem with building a Query in VBA

[CD Tom]

I have a query that I change the table in depending on the selection of the customer. I have done this before and it works fine except now I need to have a DMin statement and I keep getting an error on it.Heres the code

Set StageStsMM = db.QueryDefs("StageStsMM")
        StageStsMM.SQL = "SELECT " & vst & ".SASS_number, Params.Number_stages, " & vst & ".Shoot_date AS shootdate, " & vst & ".ShooterNumber, " & vst & ".Class AS Class, Shooting_Class.Description, Shooting_Class.ScoringCheck, " & _
        "" & vst & ".Time1 , " & vst & ".TTime1, " & vst & ".Misses1, " & vst & ".Penelties1, " & vst & ".Time2, " & vst & ".TTime2, " & vst & ".Misses2, " & vst & ".Penelties2, " & vst & ".Time3, " & vst & ".TTime3, " & _
        "" & vst & ".Misses3, " & vst & ".Penelties3, " & vst & ".Time4, " & vst & ".TTime4, " & vst & ".Misses4, " & vst & ".Penelties4, " & vst & ".Time5, " & vst & ".TTime5, " & vst & ".Misses5, " & vst & ".Penelties5, " & _
        "" & vst & ".Time6, " & vst & ".TTime6, " & vst & ".Misses6, " & vst & ".Penelties6, " & vst & ".Time7, " & vst & ".TTime7, " & vst & ".Misses7, " & vst & ".Penelties7, " & vst & ".Time8, " & vst & ".TTime8, " & _
        "" & vst & ".Misses8, " & vst & ".Penelties8, " & vst & ".Time9, " & vst & ".TTime9, " & vst & ".Misses9, " & vst & ".Penelties9, " & vst & ".Time10, " & vst & ".TTime10, " & vst & ".Misses10, " & vst & ".Penelties10, " & _
        "" & vst & ".Time11, " & vst & ".TTime11, " & vst & ".Misses11, " & vst & ".Penelties11, " & vst & ".Time12, " & vst & ".TTime12, " & vst & ".Misses12, " & vst & ".Penelties12, " & vst & ".Time13, " & vst & ".TTime13, " & vst & ".Misses13, " & vst & ".Penelties13, " & vst & ".Time14, " & _
        "" & vst & ".TTime14, " & vst & ".Misses14, " & vst & ".Penelties14, " & vst & ".Time15, " & vst & ".TTime15, " & vst & ".Misses15, " & vst & ".Penelties15, " & vst & ".Time16, " & vst & ".TTime16, " & vst & ".Misses16, " & _
        "" & vst & ".Penelties16, " & vst & ".Time17, " & vst & ".TTime17, " & vst & ".Misses17, " & vst & ".Penelties17, " & vst & ".Time18, " & vst & ".TTime18, " & vst & ".Misses18, " & vst & ".Penelties18, " & vst & ".TotalTime, " & _
        "Params.Shoot_date, Params.Club_name, Params.RegionalName, Master.Alias, Params.MisseTime, Params.PenaltiesTime, DMin("[tTime1]","[" & vst & "]","[tTime1] > 0 and class = '" & [Class] & "'") AS mTime1, " & _
        "DMin("[tTime2]","[" & vst & "]","[tTime2] > 0 and class = '" & [Class] & "'") AS mTime2, DMin("[tTime3]","[" & vst & "]","[tTime3] > 0 and class = '" & [Class] & "'") AS mTime3, DMin("[tTime4]","[" & vst & "]","[tTime4] > 0 and class = '" & [Class] & "'") AS mTime4, " & _
        "DMin("[tTime5]","[" & vst & "]","[tTime5] > 0 and class = '" & [Class] & "'") AS mTime5, DMin("[tTime6]","[" & vst & "]","[tTime6] > 0 and class = '" & [Class] & "'") AS mTime6, DMin("[tTime7]","[" & vst & "]","[tTime7] > 0 and class = '" & [Class] & "'") AS mTime7, " & _
        "DMin("[tTime8]","[" & vst & "]","[tTime8] > 0 and class = '" & [Class] & "'") AS mTime8, DMin("[tTime9]","[" & vst & "]","[tTime9] > 0 and class = '" & [Class] & "'") AS mTime9, DMin("[tTime10]","[" & vst & "]","[tTime10] > 0 and class = '" & [Class] & "'") AS mTime10, " & _
        "DMin("[tTime11]","[" & vst & "]","[tTime11] > 0 and class = '" & [Class] & "'") AS mTime11, DMin("[tTime12]","[" & vst & "]","[tTime12] > 0 and class = '" & [Class] & "'") AS mTime12, DMin("[tTime13]", " & _
        "[" & vst & "]","[tTime13] > 0 and class = '" & [Class] & "'") AS mTime13, DMin("[tTime14]","[" & vst & "]","[tTime14] > 0 and class = '" & [Class] & "'") AS mTime14, DMin("[tTime15]","[" & vst & "]","[tTime15] > 0 and class = '" & [Class] & "'") AS mTime15, " & _
        "DMin("[tTime16]","[" & vst & "]","[tTime16] > 0 and class = '" & [Class] & "'") AS mTime16, DMin("[tTime17]","[" & vst & "]","[tTime17] > 0 and class = '" & [Class] & "'") AS mTime17, DMin("[tTime18]","[" & vst & "]","[tTime18] > 0 and class = '" & [Class] & "'") AS mTime18, " & _
        "" & vst & ".MSafety1, " & vst & ".MSafety2, " & vst & ".MSafety3, " & vst & ".MSafety4, " & vst & ".MSafety5, " & vst & ".MSafety6, " & vst & ".MSafety7, " & vst & ".MSafety8, " & vst & ".MSafety9, " & vst & ".MSafety10, " & vst & ".MSafety11, " & vst & ".MSafety12, " & _
        "" & vst & ".MSafety13, " & vst & ".MSafety14, " & vst & ".MSafety15, " & vst & ".MSafety16, " & vst & ".MSafety17, " & vst & ".MSafety18, " & vst & ".Shootorder1, " & vst & ".Shootorder2, " & vst & ".Shootorder3, " & _
        "" & vst & ".Shootorder4, " & vst & ".Shootorder5, " & vst & ".Shootorder6, " & vst & ".Shootorder7, " & vst & ".Shootorder8, " & vst & ".Shootorder9, " & vst & ".Shootorder10, " & vst & ".Shootorder11, " & vst & ".Shootorder12, " & _
        "" & vst & ".Shootorder13, " & vst & ".Shootorder14, " & vst & ".Shootorder15, " & vst & ".Shootorder16, " & vst & ".Shootorder17, " & vst & ".Shootorder18, " & vst & ".MatchDQ FROM Params, Master INNER JOIN (Shooting_Class INNER JOIN " & vst & " ON Shooting_Class.Class = " & vst & ".Class) ON Master.SASS_number = " & vst & ".SASS_number " & _
        "WHERE (((Shooting_Class.ScoringCheck)=False) AND ((" & vst & ".MatchDQ)=False))"     

the first Dmin statement gives an error "Expected end of statement"

I've copied the sql statement directly from the query only changing the table name to the " & Vst & " which is replaced with the correct selected table name.
I have never used the Dmin statement before in a query build in VBA so can someone tell me how that statement should be?

Thanks again for your help.

[PhilOfWalton]

I think your table name is wrong in the DMin statements

It should be of the form

DMin("[tTime2]", "vst", "Criteria")
 

where Criteria as whatever you want

If you type "DMin("[tTime2]", "vst", "Criteria")"
into the immediate window, you should see if this is giving the correct result.

Quite frankly, with a monster SQL statement like that, I tend to build it in the Query Builder and add additional statements until I have the complete SQL which I paste into my VBA

Phil

[CD Tom]

I'll give that a try and let you know.
Thanks

[CD Tom]

That didn't work either still get the same error. Any other suggestions.

[NeoPa]

I'm surprised you still need to be told, at this stage after >400 posts, that posting VBA that creates SQL is less than bright.

Please read How to Debug SQL String first.

If you're still having problems then certainly feel free to share the SQL string that's failing.

As a further tip - Using Domain Aggregate functions within SQL is very rarely a recommended approach. That said, get your basics sorted out first before moving on to anything which would be classed as optimisation.

[NeoPa]

Having looked at Phil's comment it seems clear you're using double-quotes (") within your intended SQL as well as in the VBA used to create that string. That's never going to work out for you. See Quotes (') and Double-Quotes (") - Where and When to use them.

@Tom.
I think of you as a very long-term member. Would I be right in thinking you've just recently moved towards SQL work after working in non-SQL based areas before now? If so, read the two linked threads as you should find them very helpful.