Append query and Type Mismatch

F

Fev

Hi
I really appreciate the help I have received so far! I can run this
append query from the Query and it works fine. I have copied the text
into VBA for a command button and I keep getting a "Type Mismatch"
error:

SQL1 = "INSERT INTO tblLSMDetail ( ProjectLSM, ProjectArea, ProjectID,
ProjectEthnic, ProjectGender, ProjectAgeGroup )" & _
"SELECT tblProjectLSM.ProjectLSMID, tblProjectArea.ProjectAreaID,
tblProjectLSM.ProjectID, tblProjectEthnic.ProjectEthnicID,
tblProjectGender.ProjectGenderID,
tblProjectAgeGroup.ProjectAgeGroupID" & _
"FROM tblProjectArea, tblProjectLSM, tblProjectEthnic,
tblProjectGender, tblProjectAgeGroup" & _
"WHERE (((tblProjectLSM.ProjectID)=[Forms]![frmSampleBuilder]!
[ProjectID]))"
I would greatly appreciate some more help
Thanks
Heather
 
J

John W. Vinson

SQL1 = "INSERT INTO tblLSMDetail ( ProjectLSM, ProjectArea, ProjectID,
ProjectEthnic, ProjectGender, ProjectAgeGroup )" & _
"SELECT tblProjectLSM.ProjectLSMID, tblProjectArea.ProjectAreaID,
tblProjectLSM.ProjectID, tblProjectEthnic.ProjectEthnicID,
tblProjectGender.ProjectGenderID,
tblProjectAgeGroup.ProjectAgeGroupID" & _
"FROM tblProjectArea, tblProjectLSM, tblProjectEthnic,
tblProjectGender, tblProjectAgeGroup" & _
"WHERE (((tblProjectLSM.ProjectID)=[Forms]![frmSampleBuilder]!
[ProjectID]))"
I
Click to expand...

You're missing some critically important blanks. The lines that you're
concatenating don't have trailing blaks so you're ending up with meaningless
items like ProjectAgeGroupIDFROM and tblProjectAgetGroupWHERE. Try including
blanks in the string pieces you're putting together:

SQL1 = "INSERT INTO tblLSMDetail ( ProjectLSM, ProjectArea, ProjectID, " & _
"ProjectEthnic, ProjectGender, ProjectAgeGroup ) " & _
"SELECT tblProjectLSM.ProjectLSMID, tblProjectArea.ProjectAreaID, " & _
"tblProjectLSM.ProjectID, tblProjectEthnic.ProjectEthnicID, " & _
"tblProjectGender.ProjectGenderID, " & _
"tblProjectAgeGroup.ProjectAgeGroupID " & _
"FROM tblProjectArea, tblProjectLSM, tblProjectEthnic, " & _
"tblProjectGender, tblProjectAgeGroup " & _
"WHERE (((tblProjectLSM.ProjectID)=[Forms]![frmSampleBuilder]![ProjectID]))"
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
F

Fev

SQL1 = "INSERT INTO tblLSMDetail ( ProjectLSM, ProjectArea, ProjectID,
ProjectEthnic, ProjectGender, ProjectAgeGroup )" & _
"SELECT tblProjectLSM.ProjectLSMID, tblProjectArea.ProjectAreaID,
tblProjectLSM.ProjectID, tblProjectEthnic.ProjectEthnicID,
tblProjectGender.ProjectGenderID,
tblProjectAgeGroup.ProjectAgeGroupID" & _
"FROM tblProjectArea, tblProjectLSM, tblProjectEthnic,
tblProjectGender, tblProjectAgeGroup" & _
"WHERE (((tblProjectLSM.ProjectID)=[Forms]![frmSampleBuilder]!
[ProjectID]))"
I
Click to expand...

You're missing some critically important blanks. The lines that you're
concatenating don't have trailing blaks so you're ending up with meaningless
items like ProjectAgeGroupIDFROM and tblProjectAgetGroupWHERE. Try including
blanks in the string pieces you're putting together:

SQL1 = "INSERT INTO tblLSMDetail ( ProjectLSM, ProjectArea, ProjectID, " & _
"ProjectEthnic, ProjectGender, ProjectAgeGroup ) " & _
"SELECT tblProjectLSM.ProjectLSMID, tblProjectArea.ProjectAreaID, " & _
"tblProjectLSM.ProjectID, tblProjectEthnic.ProjectEthnicID, " & _
"tblProjectGender.ProjectGenderID, " & _
"tblProjectAgeGroup.ProjectAgeGroupID " & _
"FROM tblProjectArea, tblProjectLSM, tblProjectEthnic, " & _
"tblProjectGender, tblProjectAgeGroup " & _
"WHERE (((tblProjectLSM.ProjectID)=[Forms]![frmSampleBuilder]![ProjectID]))"
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com
Click to expand...

Hi John
Thanks for the pointers, I have added extra spaces in at the end of
each line, but still get the Type mismatch error. My code now reads:
SQL1 = " INSERT INTO tblLSMDetail ( ProjectLSM , ProjectArea ,
ProjectID , ProjectEthnic , ProjectGender , ProjectAgeGroup ) " &
_
" SELECT tblProjectLSM.ProjectLSMID ,
tblProjectArea.ProjectAreaID , tblProjectLSM.ProjectID ,
tblProjectEthnic.ProjectEthnicID , " & _
" tblProjectGender.ProjectGenderID ,
tblProjectAgeGroup.ProjectAgeGroupID " & _
" FROM tblProjectArea, tblProjectLSM, tblProjectEthnic,
tblProjectGender, tblProjectAgeGroup " & _
" WHERE (((tblProjectLSM.ProjectID) = [Forms]![frmSampleBuilder]!
[ProjectID ])) "
Thanks
Heather
 
J

John W. Vinson

Thanks for the pointers, I have added extra spaces in at the end of
each line, but still get the Type mismatch error. My code now reads:
SQL1 = " INSERT INTO tblLSMDetail ( ProjectLSM , ProjectArea ,
ProjectID , ProjectEthnic , ProjectGender , ProjectAgeGroup ) " &
_
" SELECT tblProjectLSM.ProjectLSMID ,
tblProjectArea.ProjectAreaID , tblProjectLSM.ProjectID ,
tblProjectEthnic.ProjectEthnicID , " & _
" tblProjectGender.ProjectGenderID ,
tblProjectAgeGroup.ProjectAgeGroupID " & _
" FROM tblProjectArea, tblProjectLSM, tblProjectEthnic,
tblProjectGender, tblProjectAgeGroup " & _
" WHERE (((tblProjectLSM.ProjectID) = [Forms]![frmSampleBuilder]!
[ProjectID ])) "
Thanks
Heather
Click to expand...

Whoa. I may see the problem.

Are tblProjectArea, tblProjectLSM, tblProjectEthnic etc. all ***DIFFERENT
TABLES***? Do they have any relationships, or are they one-row tables, or
what? You have some sort of monster multitable cartesian join going on if
these are multirow tables.

What is the datatype of tblProjectLSM.ProjectID, and does that table have any
relationships to these other tables?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
F

Fev

Thanks for the pointers, I have added extra spaces in at the end of
each line, but still get the Type mismatch error.  My code now reads:
SQL1 = " INSERT INTO tblLSMDetail ( ProjectLSM ,  ProjectArea ,
ProjectID ,  ProjectEthnic ,  ProjectGender ,  ProjectAgeGroup )  " &
_
" SELECT  tblProjectLSM.ProjectLSMID ,
tblProjectArea.ProjectAreaID ,  tblProjectLSM.ProjectID ,
tblProjectEthnic.ProjectEthnicID , " &  _
" tblProjectGender.ProjectGenderID ,
tblProjectAgeGroup.ProjectAgeGroupID  " &  _
" FROM  tblProjectArea, tblProjectLSM,  tblProjectEthnic,
tblProjectGender,  tblProjectAgeGroup  " &  _
" WHERE  (((tblProjectLSM.ProjectID) = [Forms]![frmSampleBuilder]!
[ProjectID ])) "
Thanks
Heather
Click to expand...

Whoa. I may see the problem.

Are tblProjectArea, tblProjectLSM, tblProjectEthnic etc. all ***DIFFERENT
TABLES***? Do they have any relationships, or are they one-row tables, or
what? You have some sort of monster multitable cartesian join going on if
these are multirow tables.

What is the datatype of tblProjectLSM.ProjectID, and does that table haveany
relationships to these other tables?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -
Click to expand...

Hi John
Thanks for the ongoing support. The ProjectID is a long Integer, as
there is a relationship to the ProjectID field as an Autonumber in the
db. When I run the query as an update query from the QBE grid, I am
getting the exact result that I require. It is only in the VBA code
that I get the Mismatch type error. I can run the query from a button
on the form as well with no ill effects. It would just be neater to
run both the query and some other code from a single command button.
I hope I have answered all your questions.
Thanks
Heather
 
J

John W. Vinson

Hi
I really appreciate the help I have received so far! I can run this
append query from the Query and it works fine. I have copied the text
into VBA for a command button and I keep getting a "Type Mismatch"
error:

SQL1 = "INSERT INTO tblLSMDetail ( ProjectLSM, ProjectArea, ProjectID,
ProjectEthnic, ProjectGender, ProjectAgeGroup )" & _
"SELECT tblProjectLSM.ProjectLSMID, tblProjectArea.ProjectAreaID,
tblProjectLSM.ProjectID, tblProjectEthnic.ProjectEthnicID,
tblProjectGender.ProjectGenderID,
tblProjectAgeGroup.ProjectAgeGroupID" & _
"FROM tblProjectArea, tblProjectLSM, tblProjectEthnic,
tblProjectGender, tblProjectAgeGroup" & _
"WHERE (((tblProjectLSM.ProjectID)=[Forms]![frmSampleBuilder]!
[ProjectID]))"
I would greatly appreciate some more help
Thanks
Heather
Click to expand...

Hrm. So you're not getting the error from the execution of the query, but in
the code! How is SQL1 dimensioned (or is it)? If you step through the code in
debug mode, what line triggers the error and what is highlighted?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append Query Type Mismatch (Apologies if this is a duplicate post) 1

Top