asked on

sql union works in access but not in vba

excel vba 2010:

I have a union query that combines 6 sql statements...that works fine in access.

When i put it in Excel vba:

The union query will always return "0" records...if any of the union statements contain "0" records ?

strsql = "SELECT tblXrefInfo_G.Priority_Id, tblXrefInfo_G.MATCH_TYPE, tblXrefInfo_G.Item, WwgCore.WWGDESC, WwgCore.RICHTEXT, WwgCore.WWGMFRNAME, WwgCore.WWGMFRNUM, WwgCore.SPIN, WwgCore.REDBOOKNUM, WwgCoreExtra.UOM, WwgCoreExtra.UOMQty, WwgCore.WA_PRICE, tblXrefInfo_G.MFRNAME, tblXrefInfo_G.MFRNUM, WwgCore.XREF, tblXrefInfo_G.COMPDESC, WwgCoreExtra.GREENProduct, WwgCoreExtra.PRIVATE_LABEL, WwgCoreExtra.COUNTRYOFORIGIN, WwgCoreExtra.california_prop_65_org_label, WwgCoreExtra.california_prop_65_wht_label, WwgCoreExtra.item_gsa_compliant_flag, WwgCoreExtra.VAN_ELIGIBLE, WwgCoreExtra.CustomerShipQty, WwgCoreExtra.CustomerWillcallQty, WwgCoreExtra.Low_Lead_Compliance" & vbCrLf
strsql = strsql & " FROM (tblXrefInfo_G INNER JOIN WwgCoreExtra ON tblXrefInfo_G.Item = WwgCoreExtra.ITEM) INNER JOIN WwgCore ON tblXrefInfo_G.Item = WwgCore.ITEM" & vbCrLf
strsql = strsql & " WHERE tblXrefInfo_G.MFRNUM = '" & rng & "'" & vbCrLf
strsql = strsql & "Union " & vbCrLf
strsql = "SELECT tblXrefInfo_B.Priority_Id, tblXrefInfo_B.MATCH_TYPE, tblXrefInfo_B.Item, WwgCore.WWGDESC, WwgCore.RICHTEXT, WwgCore.WWGMFRNAME, WwgCore.WWGMFRNUM, WwgCore.SPIN, WwgCore.REDBOOKNUM, WwgCoreExtra.UOM, WwgCoreExtra.UOMQty, WwgCore.WA_PRICE, tblXrefInfo_B.MFRNAME, tblXrefInfo_B.MFRNUM, WwgCore.XREF, tblXrefInfo_B.COMPDESC, WwgCoreExtra.GREENProduct, WwgCoreExtra.PRIVATE_LABEL, WwgCoreExtra.COUNTRYOFORIGIN, WwgCoreExtra.california_prop_65_org_label, WwgCoreExtra.california_prop_65_wht_label, WwgCoreExtra.item_gsa_compliant_flag, WwgCoreExtra.VAN_ELIGIBLE, WwgCoreExtra.CustomerShipQty, WwgCoreExtra.CustomerWillcallQty, WwgCoreExtra.Low_Lead_Compliance" & vbCrLf
strsql = strsql & " FROM (tblXrefInfo_B INNER JOIN WwgCoreExtra ON tblXrefInfo_B.Item = WwgCoreExtra.ITEM) INNER JOIN WwgCore ON tblXrefInfo_B.Item = WwgCore.ITEM" & vbCrLf
strsql = strsql & " WHERE tblXrefInfo_B.MFRNUM = '" & rng & "'" & vbCrLf
strsql = strsql & "Union " & vbCrLf
strsql = "SELECT tblXrefInfo_I.Priority_Id, tblXrefInfo_I.MATCH_TYPE, tblXrefInfo_I.Item, WwgCore.WWGDESC, WwgCore.RICHTEXT, WwgCore.WWGMFRNAME, WwgCore.WWGMFRNUM, WwgCore.SPIN, WwgCore.REDBOOKNUM, WwgCoreExtra.UOM, WwgCoreExtra.UOMQty, WwgCore.WA_PRICE, tblXrefInfo_I.MFRNAME, tblXrefInfo_I.MFRNUM, WwgCore.XREF, tblXrefInfo_I.COMPDESC, WwgCoreExtra.GREENProduct, WwgCoreExtra.PRIVATE_LABEL, WwgCoreExtra.COUNTRYOFORIGIN, WwgCoreExtra.california_prop_65_org_label, WwgCoreExtra.california_prop_65_wht_label, WwgCoreExtra.item_gsa_compliant_flag, WwgCoreExtra.VAN_ELIGIBLE, WwgCoreExtra.CustomerShipQty, WwgCoreExtra.CustomerWillcallQty, WwgCoreExtra.Low_Lead_Compliance" & vbCrLf
strsql = strsql & " FROM (tblXrefInfo_I INNER JOIN WwgCoreExtra ON tblXrefInfo_I.Item = WwgCoreExtra.ITEM) INNER JOIN WwgCore ON tblXrefInfo_I.Item = WwgCore.ITEM" & vbCrLf
strsql = strsql & " WHERE tblXrefInfo_I.MFRNUM = '" & rng & "'" & vbCrLf
strsql = strsql & "Union" & vbCrLf
strsql = "SELECT tblXrefInfo_C.Priority_Id, tblXrefInfo_C.MATCH_TYPE, tblXrefInfo_C.Item, WwgCore.WWGDESC, WwgCore.RICHTEXT, WwgCore.WWGMFRNAME, WwgCore.WWGMFRNUM, WwgCore.SPIN, WwgCore.REDBOOKNUM, WwgCoreExtra.UOM, WwgCoreExtra.UOMQty, WwgCore.WA_PRICE, tblXrefInfo_C.MFRNAME, tblXrefInfo_C.MFRNUM, WwgCore.XREF, tblXrefInfo_C.COMPDESC, WwgCoreExtra.GREENProduct, WwgCoreExtra.PRIVATE_LABEL, WwgCoreExtra.COUNTRYOFORIGIN, WwgCoreExtra.california_prop_65_org_label, WwgCoreExtra.california_prop_65_wht_label, WwgCoreExtra.item_gsa_compliant_flag, WwgCoreExtra.VAN_ELIGIBLE, WwgCoreExtra.CustomerShipQty, WwgCoreExtra.CustomerWillcallQty, WwgCoreExtra.Low_Lead_Compliance" & vbCrLf
strsql = strsql & " FROM (tblXrefInfo_C INNER JOIN WwgCoreExtra ON tblXrefInfo_C.Item = WwgCoreExtra.ITEM) INNER JOIN WwgCore ON tblXrefInfo_C.Item = WwgCore.ITEM" & vbCrLf
strsql = strsql & " WHERE tblXrefInfo_C.MFRNUM = '" & rng & "';" & vbCrLf
strsql = strsql & "Union" & vbCrLf
strsql = "SELECT tblXrefInfo_V.Priority_Id, tblXrefInfo_V.MATCH_TYPE, tblXrefInfo_V.Item, WwgCore.WWGDESC, WwgCore.RICHTEXT, WwgCore.WWGMFRNAME, WwgCore.WWGMFRNUM, WwgCore.SPIN, WwgCore.REDBOOKNUM, WwgCoreExtra.UOM, WwgCoreExtra.UOMQty, WwgCore.WA_PRICE, tblXrefInfo_V.MFRNAME, tblXrefInfo_V.MFRNUM, WwgCore.XREF, tblXrefInfo_V.COMPDESC, WwgCoreExtra.GREENProduct, WwgCoreExtra.PRIVATE_LABEL, WwgCoreExtra.COUNTRYOFORIGIN, WwgCoreExtra.california_prop_65_org_label, WwgCoreExtra.california_prop_65_wht_label, WwgCoreExtra.item_gsa_compliant_flag, WwgCoreExtra.VAN_ELIGIBLE, WwgCoreExtra.CustomerShipQty, WwgCoreExtra.CustomerWillcallQty, WwgCoreExtra.Low_Lead_Compliance" & vbCrLf
strsql = strsql & " FROM (tblXrefInfo_V INNER JOIN WwgCoreExtra ON tblXrefInfo_V.Item = WwgCoreExtra.ITEM) INNER JOIN WwgCore ON tblXrefInfo_V.Item = WwgCore.ITEM" & vbCrLf
strsql = strsql & " WHERE tblXrefInfo_V.MFRNUM = '" & rng & "';" & vbCrLf
strsql = strsql & "Union" & vbCrLf
strsql = "SELECT tblXrefInfo_GP.Priority_Id, tblXrefInfo_GP.MATCH_TYPE, tblXrefInfo_GP.Item, WwgCore.WWGDESC, WwgCore.RICHTEXT, WwgCore.WWGMFRNAME, WwgCore.WWGMFRNUM, WwgCore.SPIN, WwgCore.REDBOOKNUM, WwgCoreExtra.UOM, WwgCoreExtra.UOMQty, WwgCore.WA_PRICE, tblXrefInfo_GP.MFRNAME, tblXrefInfo_GP.MFRNUM, WwgCore.XREF, tblXrefInfo_GP.COMPDESC, WwgCoreExtra.GREENProduct, WwgCoreExtra.PRIVATE_LABEL, WwgCoreExtra.COUNTRYOFORIGIN, WwgCoreExtra.california_prop_65_org_label, WwgCoreExtra.california_prop_65_wht_label, WwgCoreExtra.item_gsa_compliant_flag, WwgCoreExtra.VAN_ELIGIBLE, WwgCoreExtra.CustomerShipQty, WwgCoreExtra.CustomerWillcallQty, WwgCoreExtra.Low_Lead_Compliance" & vbCrLf
strsql = strsql & " FROM (tblXrefInfo_GP INNER JOIN WwgCoreExtra ON tblXrefInfo_GP.Item = WwgCoreExtra.ITEM) INNER JOIN WwgCore ON tblXrefInfo_GP.Item = WwgCore.ITEM" & vbCrLf
strsql = strsql & " WHERE tblXrefInfo_GP.MFRNUM = '" & rng & "';"

objMyRecordset.Open strsql, objMyConn, adOpenKeyset, adLockOptimistic
If objMyRecordset.RecordCount = 0 Then

Open in new window

Thanks for any help !

fordraiders

ASKER CERTIFIED SOLUTION

HugoHiasl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

No,
But Thanks the query still is going to zero records..even though I know indivudually the sql have results.

Fordraiders

ASKER

ok...I see what i did...
I did not extend the trsql = strsql & " beyond the first sql statement.

Thanks for at least alerting me to the other...sent off bells.

Fordraiders

ASKER

Thanks for the heads up !