Results 1 to 3 of 3

Macro Where Clause Help

  1. #1
    CT_AccessHelp is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    40

    Macro Where Clause Help

    Hi, I have been messing with this for awhile now and thought I would ask for help. Please see my provided image. I am trying to open a form to edit a record and my search is not picking up the first and last name, only the last name. Any help with adding [first] to the Where clause and not get an error would be much appreciated. Thank you
    Attached Thumbnails Attached Thumbnails Access where clause.png  

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,433
    AFAIK, Access macros and queries cannot see combo or list box columns, only the value in the box. One option:

    Have a textbox pull info from the listbox: =Listboxname & " " & Listboxname.Column(1)

    Have a field in query that concatenates the two fields: FullName: [Last Or Business] & " " & [First]

    The where clause: "[FullName] ='" & textboxname & "'"

    This assumes listbox first column is not actually a hidden column containing a key.

    This would be much simpler if you used unique identifier such as autonumber PK/FK in criteria instead of descriptive text.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CT_AccessHelp is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    40
    Solved! Thank you!!!!!


    Quote Originally Posted by June7 View Post
    AFAIK, Access macros and queries cannot see combo or list box columns, only the value in the box. One option:

    Have a textbox pull info from the listbox: =Listboxname & " " & Listboxname.Column(1)

    Have a field in query that concatenates the two fields: FullName: [Last Or Business] & " " & [First]

    The where clause: "[FullName] ='" & textboxname & "'"

    This assumes listbox first column is not actually a hidden column containing a key.

    This would be much simpler if you used unique identifier such as autonumber PK/FK in criteria instead of descriptive text.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. SQL WHERE clause with an AND in VBA
    By CharissaBelle in forum Forms
    Replies: 3
    Last Post: 04-14-2016, 04:29 PM
  2. Replies: 10
    Last Post: 11-25-2014, 06:35 PM
  3. Replies: 15
    Last Post: 03-07-2013, 09:39 PM
  4. Where clause
    By Amerigo in forum Queries
    Replies: 2
    Last Post: 03-30-2011, 07:34 AM
  5. Help on WHERE clause
    By QBCM in forum Programming
    Replies: 1
    Last Post: 12-19-2005, 08:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums