type mismatch and odd data returned from pass-through query

[galorin]

I am using the following pass-through query to MySQL(Tried it as a view as well with identical results) Everything is getting passed back correctly, except for the case result. MySQL returns the correct info, but Access can't seem to latch on to that.

select q.date_received
, q.quote_id
, q.distributor
, q.builder
, q.add1
, q.date_returned
, case
 when q.Rate_ID = 0 then q.GlulamValue
 when q.Rate_ID = 1 then q.GlulamValue
 when q.Rate_ID = 2 then (q.fix_price + q.GlulamValue)
 when (q.Rate_ID = 3
  or q.Rate_ID = 4) then ((t.Rate_Charge * sum(h.hours)) + q.GlulamValue)
 when q.Rate_ID = 5 then q.GlulamValue
 when (q.Rate_ID >= 6 and q.Rate_ID <= 10) then ((t.Rate_Charge * sum(w.`Hours Worked`)) + q.GlulamValue)
 else '0.00'
 end as charge
from quotations AS q
left join HouseType AS h on h.quote_id = q.quote_id and h.deleted = 0
left join Billing AS b on b.Quote_ID = q.quote_id
left join hoursworked as w on h.ht_id = w.ht_id
left join rates as t on t.Rate_ID = q.Rate_ID
where q.builder is not null
and b.InvoiceNo = 'qre06-1123jm' [COLOR=green] InvoiceNo is the only part that gets changed [/color]
group by q.quote_id
UNION
select q.date_received
, q.quote_id
, d.Company AS Distributor
, bu.Company AS Builder
, q.add1
, q.date_returned
, case
 when q.Rate_ID = 0 then q.GlulamValue
 when q.Rate_ID = 1 then q.GlulamValue
 when q.Rate_ID = 2 then (q.fix_price + q.GlulamValue)
 when (q.Rate_ID = 3
  or q.Rate_ID = 4) then ((t.Rate_Charge * sum(h.hours)) + q.GlulamValue)
 when q.Rate_ID = 5 then q.GlulamValue
 when (q.Rate_ID >= 6 and q.Rate_ID <= 10) then ((t.Rate_Charge * sum(w.`Hours Worked`)) + q.GlulamValue)
 else '0.00'
 end as charge
from quotations AS q
left join HouseType AS h on h.quote_id = q.quote_id and h.deleted = 0
left join companies AS bu on bu.Comp_ID = q.build_id
left join companies AS d on d.Comp_ID = q.dist_id
left join Billing AS b on b.Quote_ID = q.quote_id
left join hoursworked as w on h.ht_id = w.ht_id
left join rates as t on t.Rate_ID = q.Rate_ID
where (isnull(q.builder) or q.builder like '')
and b.InvoiceNo = 'qre06-1123jm' [COLOR=green] InvoiceNo is the only part that gets changed [/color]
group by q.quote_id

I have to union it because the way data used to be stored is different from how it is currently stored. The problems seem to be with the case statement. MySQL returns proper values, i.e

date_received	quote_id	distributor	builder	add1	date_returned	charge
12/06/2008	2384	A A Builders	A A Builders	12	17/06/2008	147500

but when Access tries to get at this data, the charge comes back as rectangle rectangle rectangle tilde tilde. I would like to be able to use this, and not do it in VBA. What's going on here?

 

[PHV]

Try to cast the charge derived column as string in the query.

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

 

[galorin]

Thanks for the hint, but it's still returning the same junk data. I've had to do it in VBA, as I can't seem to get it to work any other way. Here's the code as it does what it should, just not the way I want it to.

Private Sub Form_Load()
Dim I As Integer

Dim desVal As Double [COLOR=green]'design cost totals[/color]
Dim gluVal As Double [COLOR=green]'glulam/joist value totals[/color]
Dim subTot As Double [COLOR=green]' used for calculating hours[/color]

SQLout = "select * from invoices where invoiceno = '" & Forms!billing.InvNo & "'"

For I = Me.Breakdown.ListCount - 1 To 0 Step -1
    Me.Breakdown.RemoveItem I
Next I

Me.Breakdown.AddItem Item:="Date Received;Quote ID;Builder;Address;Date Returned;Design Cost;Glulam/Joist Value"

Call connectDB
Call getData
desVal = 0
gluVal = 0

If Not rst.EOF Then
    rst.MoveFirst
    
    If Forms!billing.Unpaid = True Then
    Me.Dist.Enabled = False
Else
    Me.Dist = rst.Fields(3)
End If
    Do
    Select Case rst.Fields(7)
    Case Is = 0
        gluVal = gluVal + rst.Fields(12)
        Me.Breakdown.AddItem Item:="" & rst.Fields(1) & ";" & rst.Fields(2) & ";" & rst.Fields(4) & ";" & rst.Fields(5) & _
        ";" & rst.Fields(6) & ";Not Assigned;" & rst.Fields(12) & ""
    Case Is = 1
        gluVal = gluVal + rst.Fields(12)
        Me.Breakdown.AddItem Item:="" & rst.Fields(1) & ";" & rst.Fields(2) & ";" & rst.Fields(4) & ";" & rst.Fields(5) & _
        ";" & rst.Fields(6) & ";Free of Charge;" & rst.Fields(12) & ""
    Case Is = 2
        desVal = desVal + rst.Fields(8)
        gluVal = gluVal + rst.Fields(12)
        Me.Breakdown.AddItem Item:="" & rst.Fields(1) & ";" & rst.Fields(2) & ";" & rst.Fields(4) & ";" & rst.Fields(5) & _
        ";" & rst.Fields(6) & ";" & rst.Fields(8) & ";" & rst.Fields(12) & ""
    Case 3 To 4
        subTot = rst.Fields(9) * 31.25
        desVal = desVal + subTot
        gluVal = gluVal + rst.Fields(12)
        Me.Breakdown.AddItem Item:="" & rst.Fields(1) & ";" & rst.Fields(2) & ";" & rst.Fields(4) & ";" & rst.Fields(5) & _
        ";" & rst.Fields(6) & ";" & subTot & ";" & rst.Fields(12) & ""
    Case 6 To 10
        subTot = rst.Fields(10) * rst.Fields(11)
        desVal = desVal + subTot
        gluVal = gluVal + rst.Fields(12)
        Me.Breakdown.AddItem Item:="" & rst.Fields(1) & ";" & rst.Fields(2) & ";" & rst.Fields(4) & ";" & rst.Fields(5) & _
        ";" & rst.Fields(6) & ";" & subTot & "" & rst.Fields(12) & ""
     Case Else
        Me.Breakdown.AddItem Item:="" & rst.Fields(1) & ";" & rst.Fields(2) & ";" & rst.Fields(4) & ";" & rst.Fields(5) & _
        ";" & rst.Fields(6) & ";No Charge" & "" & rst.Fields(12) & ""
    End Select
     rst.MoveNext
    Loop Until rst.EOF
End If
cn.CLose

Me.Tcost = desVal
Me.Glulam = gluVal
End Sub

And so the above makes sense, here is the sql used to create the invoices view.

select b.InvoiceNo
, q.date_received
, q.quote_id
, q.distributor
, q.builder
, q.add1
, q.date_returned,q.Rate_ID
, q.Fix_Price
, sum(h.Hours) AS Assigned
, sum(w.`Hours Worked`) AS `Worked`
, r.rate_charge
, q.glulamvalue
from quotations AS q 
left join HouseType AS h on h.quote_id = q.quote_id and h.deleted = 0
left join Billing AS b on b.Quote_ID = q.quote_id
left join hoursworked as w on h.ht_id = w.ht_id
left join rates as r on q.rate_id = r.rate_id
where q.builder is not null 
and q.billed = 1
group by q.quote_id
UNION
select b.InvoiceNo
, q.date_received
, q.quote_id
, d.Company AS Distributor
, bu.Company AS Builder
, q.add1
, q.date_returned,q.Rate_ID
, q.Fix_Price
, sum(h.Hours) AS Assigned
, sum(w.`Hours Worked`) AS Worked
, r.rate_charge
, q.glulamvalue
from quotations AS q 
left join HouseType AS h on h.quote_id = q.quote_id and h.deleted = 0
left join companies AS bu on bu.Comp_ID = q.build_id
left join companies AS d on d.Comp_ID = q.dist_id
left join Billing AS b on b.Quote_ID = q.quote_id
left join hoursworked as w on h.ht_id = w.ht_id
left join rates as r on q.rate_id = r.rate_id
where isnull(q.builder)
and q.billed = 1
group by q.quote_id

Probably more than one way to skin a cat with this one, but the above works in a short amount of time. I may wind up re-writing it if the load proves too great.