Express MySQL nested if's in Access 1

[JJSRich]

Here's a query, written ion MySQL that I need to use in a subreport. Because it's a pass-through, I can't use it as a subreport, so I need to re-write it in Access's language. I am dynamically generating the query, so I am posting the VBA I use. I have highlighted the section giving me the headache. I'm pretty good with MySQL, not so good with Access.

Set q = DBEngine(0)(0).QueryDefs("qryHTWorked")

sqlstr = " SELECT w.designer"
sqlstr = sqlstr & " ,sum(w.hoursworked) as worked"
sqlstr = sqlstr & " ,w.ht_id"
[COLOR=red]sqlstr = sqlstr & " ,if(q.rate_id = 2 or 11 ,(28.25 * sum(w.hoursworked))"
sqlstr = sqlstr & "  ,if(q.hrate <> 0"
sqlstr = sqlstr & "   ,(q.hrate *sum(w.hoursworked)) ,28.25 * sum(w.hoursworked) ) ) as WorkValue"[/color]
sqlstr = sqlstr & " FROM quotations as q"
sqlstr = sqlstr & " left join compshort as b on q.build_id = b.comp_id"
sqlstr = sqlstr & " left join housetype as h on h.quote_id = q.quote_id"
sqlstr = sqlstr & " left join hoursworked as w on h.ht_id = w.ht_id"
sqlstr = sqlstr & " left join companies as c1 on c1.comp_id = q.dist_id"
sqlstr = sqlstr & " where q.date_rtn_final >= '" & toSQLDate(Forms!WeekRep!Range.Form!Start) & "'"
sqlstr = sqlstr & " and q.date_rtn_final <= '" & toSQLDate(Forms!WeekRep!Range.Form!End) & "'"
If Me.Filterbox = True Then
sqlstr = sqlstr & " and (q.distributor like '" & Me.dists & "' or c1.company like '" & Me.dists & "')"
End If
sqlstr = sqlstr & " group by w.designer, w.ht_id"

q.sql = sqlstr
q.Close

There is a more robust method elsewhere for calculating this value, that actually checks for a user-entered value, but I figure if I can get this simple version working, then I can stop using only hard-coded values, and should be able to get the more robust version working.

 

[PHV]

sqlstr = sqlstr & " ,Sum(w.hoursworked)*IIf(q.rate_id In (2,11) OR q.hrate=0,28.25,q.hrate) As WorkValue"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JJSRich]

Ok, so the In keyword does the or function in my OP. I like how you re-worked my logic, I guess I'll have to take a look at some of my other complex spots and see if I can simplify them similarly.

 

[JJSRich]

Blargh!

You have tried to execute a query that does not include the specified expression 'Sum([w].[hoursworked])*IIf([q].[rate_id] In (2,11) Or [q].[hrate]=0,28.25,[q].[hrate])' in an aggrigate function

But I don't want.. eh, what?

 

[PHV]

OK, try this instead:
sqlstr = sqlstr & " ,Sum(w.hoursworked*IIf(q.rate_id In (2,11) OR q.hrate=0,28.25,q.hrate)) As WorkValue"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JJSRich]

Thanks, found another solution though. Seein as I have a MySQL server here, and people are shouting, I've gone against convention. There's now a MySQL view that does exactly what I need, business logic on the database.. that's not right... Anyhow, it is working. Thanks for rendering assistance.