Re: run-time error 3464 (data type mismatch in criteria expression) long int vs. string?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 08/27/04 

Date: Fri, 27 Aug 2004 16:44:12 -0400

Your first post said .Value, this one says .RowSource.

Normally, to do what you're wanting to do, which is to remove all the items
from the list box, you want to set the Row Source to an empty string.

    Me.lstRoster.RowSource = ""

However, your code is not using the Row Source of the listbox to set the
list of items. It's using the Recordset of the listbox. Thus, try this:

    Set Me.lstRoster.Recordset = Nothing 

-- 
        Ken Snell
<MS ACCESS MVP>
"Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
news:E7KXc.4$cn2.3@nwrddc02.gnilink.net...
> No it didn't work:
>
> I tried lstRoster.rowSource="" and I tried lstRoster.rowSource = Null.
The
> first didn't do anything and the second produced Error 94: invalid use of
> Null.  also, lstRoster.Value = Null didn't do anything either.
>
>
>
>
>
> "Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
> news:udXD9kFjEHA.3608@TK2MSFTNGP09.phx.gbl...
> > Try setting it to Null instead of "".
> >
> > -- 
> >
> >         Ken Snell
> > <MS ACCESS MVP>
> >
> > "Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in message
> > news:XPIXc.133$O85.15@trnddc05...
> > > Yep!  That did it.  It was an AutoNumber, therefore a long integer.  I
> > > removed the quotes and BingBangBam!  It started working correctly.
> > >
> > > Now I'm trying to clear the cascaded list boxes in the event that the
> user
> > > changes a selection further up the line.  I'll look into it.  I tried
> > > setting lstRoster.Value="" but that didn't work; the VBA editor
demanded
> > an
> > > object.  I'll go to my trusty Access 2002 Desktop Developer's Handbook
> > which
> > > just came in from Amazon to see what I can learn about text boxes.
This
> > is
> > > a great book, but it's TOO much information.  Slowly-but-surely I
think
> > this
> > > book will begin to save my butt!  It'll take a long time to get
through
> > it.
> > > If I can't find the answer,  I'll look on the web then if I still
can't
> > find
> > > it I'll make a new post here.  Otherwise, Hey!  Thanks a million, Doug
> > > Steele and Ken Snell, for your help!
> > >
> > > Rich Hollenbeck
> > >
> > > "Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
> > > news:eusjzZDjEHA.3632@TK2MSFTNGP09.phx.gbl...
> > > > < DOH ! >
> > > >
> > > > It was just before bedtime when I read and answered this post, so
> you're
> > > > close, Doug, as to why I missed it! Thanks for the backup.
> > > > -- 
> > > >
> > > >         Ken Snell
> > > > <MS ACCESS MVP>
> > > >
> > > >
> > > >
> > > > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
> message
> > > > news:ej9LuGCjEHA.556@tk2msftngp13.phx.gbl...
> > > > > Ken: Time to get new glasses! <g> You missed the fact that
Richard's
> > > > putting
> > > > > quotes around the value returned from lstActivities.
> > > > >
> > > > > If ActivityID is a Long Integer, here's what you need, Richard:
> > > > >
> > > > >    StrSQL_Roster = "SELECT students.studentNumber AS [ID],
> > > students.lname
> > > > >  &', '& students.fname AS [Student], studentScores.Score FROM
> > students,
> > > > >  studentScores WHERE studentScores.activityID = " &
> > CLng(lstActivities)
> > > > >
> > > > > (FWIW, the semi-colon is never actually required)
> > > > >
> > > > >
> > > > > -- 
> > > > > Doug Steele, Microsoft Access MVP
> > > > > http://I.Am/DougSteele
> > > > > (no e-mails, please!)
> > > > >
> > > > >
> > > > >
> > > > > "Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in
> message
> > > > > news:eOa1SI%23iEHA.3972@tk2msftngp13.phx.gbl...
> > > > > > Yes, your assessment is probably correct. List boxes and combo
> boxes
> > > > will
> > > > > > convert other columns to strings in the row sources. You should
be
> > > able
> > > > to
> > > > > > fix this by casting the lstActivities with the CLng function:
> > > > > >
> > > > > >     StrSQL_Roster = "SELECT students.studentNumber AS [ID],
> > > > students.lname
> > > > > > &', '& students.fname AS [Student], studentScores.Score FROM
> > students,
> > > > > > studentScores WHERE studentScores.activityID = '" &
> > > CLng(lstActivities)
> > > > &
> > > > > > "';"
> > > > > >
> > > > > >
> > > > > > -- 
> > > > > >
> > > > > >         Ken Snell
> > > > > > <MS ACCESS MVP>
> > > > > >
> > > > > > "Richard Hollenbeck" <richard.hollenbeck@verizon.net> wrote in
> > message
> > > > > > news:dgxXc.5929$2B4.5019@trnddc06...
> > > > > > > I have three list boxes. Depending on what is selected in the
> > first
> > > > list
> > > > > > > box, the second will display different data.  Whatever is
> selected
> > > in
> > > > > the
> > > > > > > second will affect the third.  Now I have a fourth list box
> that's
> > > > data
> > > > > > > depends on the third.  The first three work beautifully.  The
> > forth
> > > is
> > > > a
> > > > > > > problem for me because it's pulling data from two different
> > tables.
> > > > The
> > > > > > > data in the fourth list box should look something like this:
> > > > > > >
> > > > > > > StudentID    STUDENT        SCORE
> > > > > > > 123              Doe, John            75
> > > > > > > 234              Blow, Joe            67
> > > > > > > 456              Rubble, Barney    78
> > > > > > > 678              Flintstone, Fred    89  etc. . .
> > > > > > >
> > > > > > > The ID and Student data comes from the [students] table, but
the
> > > score
> > > > > > comes
> > > > > > > from the [studentScores] table.  [studentScores] has a
composite
> > key
> > > > of
> > > > > > > [studentScores].[studentID] and [studentScores].[activityID],
> and
> > a
> > > > > third
> > > > > > > column, [studentScores].[score].  [activityID] is the bound
> column
> > > in
> > > > > the
> > > > > > > third list box lstActivities.
> > > > > > >
> > > > > > > MY PROBLEM:
> > > > > > > I'm getting the run-time error 3464 (data type mismatch in
> > criteria
> > > > > > > expression)  I think it might be because [activityID] is a
Long
> > > > Integer
> > > > > > and
> > > > > > > lstActivities may return a String?  Or maybe I need to write
an
> > > INNER
> > > > > JOIN
> > > > > > > into the query?  Any ideas?  Many thanks.  Here's my code:
> > > > > > >
> > > > > > > Private Sub lstActivities_Click()
> > > > > > >
> > > > > > >     Dim StrSQL_Roster As String
> > > > > > >     Dim dbRoster As DAO.Database
> > > > > > >     Dim rsRoster As DAO.Recordset
> > > > > > >
> > > > > > >     StrSQL_Roster = "SELECT students.studentNumber AS [ID],
> > > > > students.lname
> > > > > > > &', '& students.fname AS [Student], studentScores.Score FROM
> > > students,
> > > > > > > studentScores WHERE studentScores.activityID = '" &
> lstActivities
> > &
> > > > "';"
> > > > > > >
> > > > > > >     Set dbRoster = CurrentDb()
> > > > > > >     Set rsRoster = dbRoster.OpenRecordset(StrSQL_Roster,
> > > > dbOpenDynaset)
> > > > > > >     lstRoster.RowSourceType = "Table/Query"
> > > > > > >     Set lstRoster.Recordset = rsRoster
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: run-time error 3464 (data type mismatch in criteria expression) long int vs. string?
    ... first didn't do anything and the second produced Error 94: invalid use of ... >> changes a selection further up the line. ... >> just came in from Amazon to see what I can learn about text boxes. ...
    (microsoft.public.access.queries)
  • Re: Default value on form using conditional expression?
    ... the text boxes showing several different related things about your selection. ... when a student's name is selected from an alphabetical dropdown of students, ... The Combo Box would have the following as its ControlSource: ...
    (microsoft.public.access.forms)
  • remembering state even if I go back
    ... Occasionally I solicit feedback on a ... given point from my students and I wanted to display their feedback in my ... the top and side and leave the boxes empty. ... I set the font color to white so it becomes invisible. ...
    (microsoft.public.powerpoint)
  • RE: How to Count ? ? ?
    ... FROM Students INNER JOIN Exams ... in the 'Totals' row to create the computed Passes and Failures columns. ... Alternatively you could base a report on a query such as: ... To get grand totals for the whole report put identical text boxes in the ...
    (microsoft.public.access.gettingstarted)
  • Re: Nice fairly long work for an intermediate student
    ... the world's greatest music when compared to Mozart or Clementi, ... is still interesting and quite varied in my opinion. ... You can say that I don't understand what music is about and that's fine, but students on the other hand, would be unlikely to speak their mind with candor. ... That's lame since that was the first line of my first post. ...
    (rec.music.classical.guitar)