1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access - Random Sample

Discussion in 'Business Applications' started by gwoody, Jun 8, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. gwoody

    gwoody Thread Starter

    Joined:
    Jun 8, 2012
    Messages:
    22
    OBP - first I want to thank you for creating this for me. I am sorry I didn't come back sooner but I was sidetracked by another project.

    Second, I do have 1 issue: In the section that selects by the parsed license number
    I would like for it to group by parsed license number and state as I found that the license number is is not unique.

    What changes need to be made?

    I attempted to modified the Where clause, but it tells me nothing matches:
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    You have to be careful with the syntax, for instance the State may not need the extra quotes.
    Have you tried it without them?
     
  3. gwoody

    gwoody Thread Starter

    Joined:
    Jun 8, 2012
    Messages:
    22
    No - I have not. They are both text fields so I assumed I would need the extra quotes.

    I will try that now and see what happens
     
  4. gwoody

    gwoody Thread Starter

    Joined:
    Jun 8, 2012
    Messages:
    22
    I am still having issues
    I think my syntax is still wrong. Sorry I am used to coding in SQL only. DO I need the double quotes and the semi colon at the end?
    WIth the single quotes:
    Without the single quotes:
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    I will post you a working version tomorrow as I am out of time tonight.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    In the mean time here is the sort of Format that I use for adding an "And" to the where part of the statement.

    SQL = "SELECT LabourRates.* " & _
    "FROM LabourRates " & _
    "WHERE Date1 <= #" & Format(Me.Date, "mm/dd/yyyy") & "#" & _
    "And Date2 > #" & Format(Me.Date, "mm/dd/yyyy") & "#"


    Perhaps you can play with your version using that format.
     
  7. gwoody

    gwoody Thread Starter

    Joined:
    Jun 8, 2012
    Messages:
    22
    OBP - this is what I tried:
    It says there are no matching records
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    This worked for me.
    sql = "SELECT Orders_YTD_1.* " & _
    "FROM Orders_YTD_1 " & _
    "WHERE [Parsed License Number] = '" & rs.[Parsed License Number] & "' " & _
    "AND [State] = '" & rs.state & "' "

    see attached database.

    PS it has a message box telling what the name of the first State.
     

    Attached Files:

  9. gwoody

    gwoody Thread Starter

    Joined:
    Jun 8, 2012
    Messages:
    22
    OBP

    I get the pop up for the state, but I am still getting a message that states no current record.
     
  10. gwoody

    gwoody Thread Starter

    Joined:
    Jun 8, 2012
    Messages:
    22
    Could it be impacted by the size of my population?
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    It shouldn't make any difference, although the randomise sample could.
    I will take a look.
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Try adding
    msgbox FileCount
    between the 2 lines shown below to see how many records you get returned.

    FileCount = rst.RecordCount

    Erase answers()
     
  13. gwoody

    gwoody Thread Starter

    Joined:
    Jun 8, 2012
    Messages:
    22
    OBP - it is only returning 1 file which does not make any sense.
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Have you tried my version?
    If it works try importing your table in to my version and testing it there.
     
  15. gwoody

    gwoody Thread Starter

    Joined:
    Jun 8, 2012
    Messages:
    22
    OBP -

    It is strange, when I leave msgbox FileCount in the code, the process works. As soon as I remove it it states no record found.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1056316