joining crosstab queries

  • Thread starter Thread starter Joop
  • Start date Start date
J

Joop

Hi All,

I have a rather complex query (I think) that inner joins the results of 2
crosstab queries and appends the results to an existing table. I designed
that created that query in design mode. When I execute it from the database
window it works fine. When I paste the query in VBA-code and execute it with
rcdPVT.Open SQLString, cn, etc. I get a error message: "too few parameters".
The Query looks like this:

'SQLString = "INSERT INTO TblPVTotaal ( Franchisenemer, Winkelnaam, san,
Leverancier, Productgroep, Merk, INKQ1, INKQ2, INKQ3, INKQ4, FBQ1, FBQ2,
FBQ3, FBQ4 )" _
' & " SELECT QryFB.Franchisenemer, QryFB.Winkelnaam,
IIf([QryFB]![Sanitair]=True,'S','NS') AS san, QryFB.Leverancier,
QryFB.Productgroep, QryFB.Merk," _
' & " QryINK.[200301], QryINK.[200302], QryINK.[200303],
QryINK.[200304], QryFB.[200301], QryFB.[200302], QryFB.[200303],
QryFB.[200304]" _
' & " FROM QryINK INNER JOIN QryFB ON (QryINK.Winkelnaam
= QryFB.Winkelnaam) AND (QryINK.Franchisenemer = QryFB.Franchisenemer)" _
' & " AND (QryINK.FldInkBonAI = QryFB.FldInkBonAI) WHERE
(((QryINK.WinkelCode)='2518'));"

I can't execute the query with docmd.openquery because I want to be able to
change fieldnames in the code. This because the fieldnames in the result of
a crosstab query may change..

Does anybody have a solution??

regards Joop Aarts
 
Place a break point in the code followed by:
Debug.Print SQLString
You can then copy and paste the sql into a blank query to search for an
error. It may be that your line is too long. Try something like:
SQLString = "INSERT INTO....."
SQLString = SQLString & " SELECT QryFR....."
etc.
 
Back
Top