Results 1 to 10 of 10
  1. #1
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368

    finding a value in a recordset

    Hi everyone.
    I find myself stuck in a section of my project. Seeing as though the final query of my project will take roughly 15-20 mins to run (its massive), I am trying to take care of all the possible issues that can arise BEFORE the query is run. One of them is checking to see if one of the search criteria, a zip code in this case, actually exists in a table.

    Since the table is so large, what I want to happen is the following:
    1. take the zip code stored in Me.txtZip
    2. open the "address" and search ONLY the zipCode field (again, trying to save as much time as possible) for Me.txtZip


    3. If it exists, move one, else, msgbox telling the user its not there and to exit sub.

    I know how to search the whole table, but the issues I'm coming accross are searching just that field, and also how to output what's essentially a boolean "exists" yes/no. Any help is appreciated.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I feel like I must be missing something. I would open a recordset on an SQL statement that included a criteria on the zip:

    "SELECT Count(*) AS HowMany FROM..."

    Testing the value of that returned field tells you if you have any.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Allow me to clarify. The txtbox will hold a value that the user enters. lets say its 12345. i need to check my table to see if the zip code 12345 even exists in the table. Reason being, I dont want to have the user to run a 20 minute query and then return no results because the zip code wasnt in our area.

    Further, I dont just want to search the whole table for 12345 because it may show up as a house number. I want to search only the zipCode field of my Address table.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, I knew what you meant:

    SELECT Count(*) AS Howmany FROM TableName WHERE ZipCodeField = 12345

    will only search the zip field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    *facepalm* this is what happens when youve been entrenched in .BOF and .EOF for the last few days. You start to only think of things that way.

    now how do i save the result as a variable to do a quantitative analysis with (if result = ...)

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you've opened a recordset on that SQL:

    VariableName = rs!HowMany
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    opened the recordset with:
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Address", dbOpenDynaset)

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No:

    Set rs = db.OpenRecordset("That SQL Statement", dbOpenDynaset)

    You'd obviously concatenate in the zip code from the form. You probably want the snapshot option too, which might make it a bit faster.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    that worked. thank you sir. Thankfully, I didn't need that table open for anything else...which leads to another question. Can you open up two instances of a table? rs1 = the table, rs2 = a subset of the same table like we have with our query.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sure, though in certain circumstances (not yours) I'd imagine you could run into locking issues, just as when you have two forms open bound to the same table. In your situation, the second recordset shouldn't lock anything because it's a read-only result anyway.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Finding Median in Query
    By randolphoralph in forum Queries
    Replies: 1
    Last Post: 02-19-2010, 11:18 AM
  2. Finding Tables
    By Rick West in forum Access
    Replies: 1
    Last Post: 01-06-2010, 10:41 AM
  3. Replies: 1
    Last Post: 11-13-2009, 03:03 AM
  4. Need help in finding Macro definition
    By sdondeti in forum Access
    Replies: 11
    Last Post: 10-31-2009, 04:52 PM
  5. Finding data that doesn't match
    By dlhayes in forum Queries
    Replies: 1
    Last Post: 11-11-2006, 08:14 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
  •  
Other Forums: Microsoft Office Forums