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 ?
Thanks for any help !
fordraiders
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
Thanks for any help !
fordraiders
Microsoft Excel
Last Comment
Fordraiders
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
I did not extend the trsql = strsql & " beyond the first sql statement.
Thanks for at least alerting me to the other...sent off bells.
ASKER
Thanks for the heads up !
But Thanks the query still is going to zero records..even though I know indivudually the sql have results.