Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Problems when trying to count visits to a library

    Hello,

    I am trying to add up the number of visits booked to for library. The total for the selected library is displayed displayed in a textbox (text14). The library visits to be counted is selected from a listbox called "lst_libraries".
    Code:
    Me.Text14.Value = DCount("libraryID", "visit", "libraryID = '" & Me.lst_libraries.Value & "'"
    But when I select a row on the listbox I get the error -
    Run Time Error 3464 - Data Type mismatch in criteria expression
    .

    The centreID field is an AutoNumber Long Increment.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Try:

    Me.Text14.Value = DCount("[libraryID]", "visit", "[libraryID] = " & Me.lst_libraries.Value & "") (notice there's no ')

    or

    Me.Text14.Value = DCount("[libraryID]", "visit", "[libraryID] = '" & Me.lst_libraries.column(X) & "'") (where X is the appriopriate column)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by pkstormy View Post
    Try:
    Me.Text14.Value = DCount("[libraryID]", "visit", "[libraryID] = " & Me.lst_libraries.Value & "") (notice there's no ')
    Cool, that has sorted it

    What difference does adding the squared brackets and removing the apostrophe do - apart from making it work?

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The square brackets are there to make sure MSAccess recognizes it as a field name (they 'may' not be needed but I use them following the example MSAccess uses.)

    " & Me.lst_libraries.Value & "" means you are matching against integer type values
    whereas
    '" & Me.lst_libraries.Value & "'" are for string type matches.
    and
    #" & Me.lst_libraries.Value & "#" would be for date type matches.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    Thankyou very much for your help

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You're welcome.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •