Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Access - Random Sample


(!)

gwoody's Avatar
gwoody gwoody is offline
Member with 22 posts.
THREAD STARTER
 
Join Date: Jun 2012
29-Jun-2012, 10:53 AM #16
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
Quote:
sql = "SELECT Orders_YTD_1.* " & _
"FROM Orders_YTD_1 " & _
"WHERE [Parsed License Number] = '" & rs.[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:
Quote:
sql = "SELECT Orders_YTD_1.* " & _
"FROM Orders_YTD_1" & _
"WHERE [Parsed License Number] = '" & rs.[Parsed License Number] & "' AND [State] = '" & rs.[State] & "' "

Last edited by gwoody; 29-Jun-2012 at 02:21 PM..
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,666 posts.
 
Join Date: Mar 2005
Location: UK
29-Jun-2012, 11:34 AM #17
You have to be careful with the syntax, for instance the State may not need the extra quotes.
Have you tried it without them?
gwoody's Avatar
gwoody gwoody is offline
Member with 22 posts.
THREAD STARTER
 
Join Date: Jun 2012
29-Jun-2012, 11:48 AM #18
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
gwoody's Avatar
gwoody gwoody is offline
Member with 22 posts.
THREAD STARTER
 
Join Date: Jun 2012
29-Jun-2012, 12:59 PM #19
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:
Quote:
"WHERE [Parsed License Number] = '" & rs.[Parsed License Number] & "' AND [State] = '" & rs.[State] & "';"
Without the single quotes:
Quote:
"WHERE [Parsed License Number] = '" & rs.[Parsed License Number] & "' AND [State] = "& rs.[State]"
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,666 posts.
 
Join Date: Mar 2005
Location: UK
29-Jun-2012, 01:13 PM #20
I will post you a working version tomorrow as I am out of time tonight.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,666 posts.
 
Join Date: Mar 2005
Location: UK
29-Jun-2012, 01:16 PM #21
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.
__________________
OBP
I do not give up easily
gwoody's Avatar
gwoody gwoody is offline
Member with 22 posts.
THREAD STARTER
 
Join Date: Jun 2012
29-Jun-2012, 02:27 PM #22
OBP - this is what I tried:
Quote:
"WHERE [Parsed License Number] = '" & rs.[Parsed License Number] & "' " & _
"AND [State] = ' " & rs.[State] & "' "
It says there are no matching records
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,666 posts.
 
Join Date: Mar 2005
Location: UK
30-Jun-2012, 04:33 AM #23
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
File Type: zip Audits_Demo.zip (63.9 KB, 8 views)
gwoody's Avatar
gwoody gwoody is offline
Member with 22 posts.
THREAD STARTER
 
Join Date: Jun 2012
02-Jul-2012, 11:32 AM #24
OBP

I get the pop up for the state, but I am still getting a message that states no current record.
gwoody's Avatar
gwoody gwoody is offline
Member with 22 posts.
THREAD STARTER
 
Join Date: Jun 2012
02-Jul-2012, 11:59 AM #25
Could it be impacted by the size of my population?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,666 posts.
 
Join Date: Mar 2005
Location: UK
02-Jul-2012, 12:13 PM #26
It shouldn't make any difference, although the randomise sample could.
I will take a look.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,666 posts.
 
Join Date: Mar 2005
Location: UK
02-Jul-2012, 12:39 PM #27
Try adding
msgbox FileCount
between the 2 lines shown below to see how many records you get returned.

FileCount = rst.RecordCount

Erase answers()
gwoody's Avatar
gwoody gwoody is offline
Member with 22 posts.
THREAD STARTER
 
Join Date: Jun 2012
05-Jul-2012, 11:45 AM #28
OBP - it is only returning 1 file which does not make any sense.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,666 posts.
 
Join Date: Mar 2005
Location: UK
06-Jul-2012, 11:42 AM #29
Have you tried my version?
If it works try importing your table in to my version and testing it there.
gwoody's Avatar
gwoody gwoody is offline
Member with 22 posts.
THREAD STARTER
 
Join Date: Jun 2012
09-Jul-2012, 10:56 AM #30
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

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
access 07

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2