• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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
0
Fordraiders
Asked:
Fordraiders
  • 3
1 Solution
 
HugoHiaslCommented:
I am not a Excel  specialist... but you are aware that you have semicolons in the 4th, 5th and 6th block?
0
 
FordraidersAuthor Commented:
No,
But Thanks the query still is going to zero records..even though I know indivudually the sql have results.
0
 
FordraidersAuthor Commented:
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.
0
 
FordraidersAuthor Commented:
Thanks for the heads up !
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now