Re: Type mismatch in ADO Recordset Open
From: Graham R Seach (gseach_at_NOSPAMpacificdb.com.au)
Date: 06/25/04
- Next message: Jonathan Parminter: "RE: Refresh form"
- Previous message: Allen Browne: "Re: Conditional formating v. "The expression you entered has a field ... that access can't recognize""
- In reply to: Chuck: "Re: Type mismatch in ADO Recordset Open"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 25 Jun 2004 11:22:32 +1000
Chuck,
Enclosing the variable in quotes as I suggest won't sorce Access to search
for the literal value "strISBN".
Run this test, and you'll see what I mean:
Dim strSQL As String
Dim strISBN As String
strISBN = "ABC"
strSQL = "Where [Book Information].[ISBN: #] = " & strISBN
Debug.Print strSQL
strSQL = "Where [Book Information].[ISBN: #] = """ & strISBN & """"
Debug.Print strSQL
In the first Debug.Print, you can see that the string value supplied by
strISBN is not explicitly declared in the SQL clause as a string, because it
is not enclosed in quotes as it is in the second Debug.Print.
You put 3 quotes on the left and 4 on the right, because whenever you want
to literally use the quote character in a string, you must declare it twice.
Access knows that if it's declared twice, it should be represented as a
literal character. The third " (on the left) closes the string as normal.
You use four " characters on the right for the same reason; the first one
opens the string, the next two declare a literal quote character, and the
last one closes the string.
If you're getting a compile error, it's not because of the literal strings.
It's because there is a problem elsewhere. Specifically what is the compile
error pointing to?
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
"Chuck" <chuckp@hnoc.org> wrote in message
news:OPUSLAgWEHA.2844@TK2MSFTNGP11.phx.gbl...
> Thanks Graham,
>
> I'm not sure I understand what your suggesting -- strISBN is a variable of
> type string. Why would I enclose it in quotes? I set the variable by using
> the value in a bound textbox on the form. I then run a SQL query as a
> parameter in the Open method for the recordset. That query is in quotes,
> although it may have broken across lines in the post to the group. I was
> just appending the variable to the explicit string. If I enclose the
> variable name in quotes, aren't I just telling Access to search the ISBN:
#
> field of the Book Information table for the literal value "strISBN"? Also,
> why the three quotes and the four? In any case, when I give it to Access
> that way I get a compile error. (Sorry about the field names -- I
inherited
> this database from someone else.)
>
> Chuck
>
> "Graham R Seach" <gseach@NOSPAMpacificdb.com.au> wrote in message
> news:OAt74#ZWEHA.212@TK2MSFTNGP11.phx.gbl...
> > I haven't looked at the rest of your code, but you should enclose
strISBN
> in
> > quotes (three on the left and four on the right), so Access knows to
treat
> > it as a string.
> >
> > Where [Book Information].[ISBN: #] = """ & strISBN & """"
> >
> > Regards,
> > Graham R Seach
> > Microsoft Access MVP
> > Sydney, Australia
> >
> > Microsoft Access 2003 VBA Programmer's Reference
> > http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
> >
> >
> > "Chuckp" <chuckp@hnoc.org> wrote in message
> > news:OzHXdlZWEHA.1356@TK2MSFTNGP09.phx.gbl...
> > > I'm entirely new to ADO in Access 2000 and I'm simply trying to
> reposition
> > a
> > > cursor back to the correct record in a form following a requery. I'm
> going
> > > bit by bit (haven't gotten to the part where I actually have to
display
> > the
> > > record in the form) but the I get a "Data type mismatch in criteria
> > > expression" error when I try to open the recordset with the query
below:
> > >
> > > Dim cnn As ADODB.Connection
> > > Dim rstview As ADODB.Recordset
> > > Dim strISBN As String
> > >
> > > Set rstview = New ADODB.Recordset
> > >
> > > If Me.[Order: #] <> "" Then
> > > Forms!frmBook![Order#] = Me.[Order: #]
> > > strISBN = Forms!frmBook![ISBN: #]
> > > Forms!frmBook.Requery
> > > rstview.ActiveConnection = CurrentProject.Connection
> > > rstview.CursorType = adOpenStatic
> > > rstview.Source = "Select [Book Information].* from [Book
> Information]
> > > Where [Book Information].[ISBN: #] = " & strISBN
> > > ^ this is the line it doesn't like
> > > rstview.Open
> > > rstview.Close
> > > Set rstview = Nothing
> > > Else
> > > MsgBox ("You must put a value in the Order Number field")
> > > End If
> > >
> > > The idea is to get frmBook to display the updated [ISBN: #] as well as
> to
> > > get a subform on the calling form to show updates.
> > >
> > > Chuck
> > >
> > >
> >
> >
>
>
- Next message: Jonathan Parminter: "RE: Refresh form"
- Previous message: Allen Browne: "Re: Conditional formating v. "The expression you entered has a field ... that access can't recognize""
- In reply to: Chuck: "Re: Type mismatch in ADO Recordset Open"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
