Append Query Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using the QBE, I've created an append query and attached it to a button on a
form. Whenever I click on the button I get an error: "Data type mismatch".
I've copied the SQL statements of the query:

INSERT INTO tblCurriculum ( lngProgCurrlmNo, lngCourseNo )
SELECT tblProgCurrlm.lngProgCurrlmNo, tblCourses.lngCourseNo
FROM tblCourses, tblProgCurrlm
WHERE (((tblProgCurrlm.lngProgCurrlmNo)='"& Me.cboProgCurrlmNo &"') AND
((tblCourses.txtSelected)="Y"));

Any idea why this error is happening? Thanks.
ck
 
Right off-hand, you've put quotes around the value Me.cboProgCurrlmNo.
Judging by the name of the field you are setting this criteria for, I
suspect it should be a long integer. By placing quotes around it, you're
treating it as text. Try removing the single quotes. Also, the value of a
combo box comes from the bound column. Verify that the bound column is a
number column or, if that's the wrong column, you'll need to specify which
column you want.
WHERE (((tblProgCurrlm.lngProgCurrlmNo)='"& Me.cboProgCurrlmNo &"') AND
Click to expand...
WHERE (((tblProgCurrlm.lngProgCurrlmNo)="& Me.cboProgCurrlmNo &") AND
 
Thanks Wayne for pointing that out. Trouble is, I've removed the single quote
as you have suggested and I'm still getting that "Data type mismatch in
criteria expression" error. I've double-checked and the combo box is bound to
column one which, as you correctly guessed, is a long number.

If I removed all the quotes, leaving only Me.cboProgCurrlmNo, I get a Enter
Parameter Value dialog box. When I enter the value manually, the query works.
Any other ideas? Thanks.
ck
 
Try stepping through the code and see what value is being picked up for
Me.cboProgCurrlmNo. You may want to try

Debug.Print Me.cboProgCurrlmNo

before you get to the SQL statement. Also, I don't see a link between
tblCourses and tblProgCurrlm to associate the records between the two
tables. This may be causing more records to be returned than expected, some
of which may have Null values or other problems.
 
Thanks again, Wayne. Maybe I should have mentioned that the SQL statement was
generated using the "Create query in Design View", saved with a query name
and then associated with a button.

I'd spent the past couple of days and nights trying with every combinations
of double-quotes, single-quotes, ampersands, etc but no luck. Out of
frustration, I use the full reference to a form like
Forms!frmForm1!cboProgram instead of the Me.cboProgram and it worked. I
still don't know why. Anyway, still, thanks for your pointers.
ck
 
The difference depends on "where" the SQL is being run. You can concatenate
in the Me.cboProgCurrlmNo if you are putting the SQL together in code,
because what really happens is that VBA winds up placing the value of
cboProgCurrlmNo into the SQL, not Me.cboProgCurrlmNo. If you pass the string
to the querydef and the querydef is left to pick up the value then, yes, you
have to pass it as if you created it in the query, which would be the full
path to the form. The query doesn't know who "Me" is. In fact, controls on
the form don't know who "Me" is (make a calculated control, it won't accept
"Me"). "Me" only works in the VBA behind a form or report.
 
Back
Top