Thanks for your response. It turns out the problem was in qryoldStores -- I
didn't provide the code for that before. Here it is:
SELECT DISTINCT PCTRENT.OCT, PCTRENT.DUE_DATE, PCTRENT.STORE
FROM PCTRENT
WHERE ((Not (PCTRENT.OCT) Is Null) AND ((PCTRENT.Opened) Is Null)) OR
(((PCTRENT.Opened)<(Year(Date())-1)));
The problem is the DISTINCT, if I remove that my query (almost) works as
desired. I don't know why this makes a difference, but it works when I
remove it.
The DISTINCT is a remnant from when I had a join to the payments table in
this query. The payments table can have more than one record per store. I
removed the join but forgot to remove the distinct.
The one remaining problem is that with the sample data I am using, 50
records should be updated. Here is my full procedure:
Set db = CurrentDb
db.Execute "update pctrent set pctrent.audit = false"
Set rst = db.OpenRecordset("sysdata", dbOpenDynaset)
strMon = MonthName(Month(rst("eomdate").Value), True)
rst.Close
Set rst = db.OpenRecordset("Select pctrent.* from pctrent left join
payments on " & _
"pctrent.store = payments.store where (((payments.amount) is
null) AND " & _
"(Not (pctrent." & strMon & ") is null) AND ((Payments.Amount)
Is Null) AND ((PCTRENT.Opened) Is Null)) or " & _
"(((PCTRENT.Opened)<(Year(Date())-1)))", dbOpenDynaset)
rst.MoveLast
intCount = rst.RecordCount
intCount = Round(intCount * 0.1, 0)
strSQL = "UPDATE PCTRENT set pctrent.audit = true where pctrent.[store]
in " & _
"(select top " & intCount & " qryoldStores.[store] from
qryoldStores " & _
"order by Rnd(Len([Store] & """")))"
'"order by randomnumber(val(store)))"
When I step through this code, inCount is 50 as it should be, but I
variously get 50, 51, or 52 records with audit set to true every time I run
the code. Examining further, I see that while there SHOULD be just one
record per store in the PCT rent table, there are some duplicates in there.
I bet if I remove the duplicates, I'll get only 50 records each time.
I think I've kind of worked things out for myself just writing this message.
Thanks for your help!
dg
John Spencer said:
I don't know. I tested it with the following and it worked well for me.
UPDATE FAQ
SET FAQ.fPriority = 1
WHERE FAQ.fID In
(SELECT TOP 20 FID
FROM FAQ
ORDER BY Rnd(FID))
Have you tried doing a debug.Print StrSQL and copying the SQL statement to a
new query and seeing if it will run?
strSQL = "UPDATE PCTRENT " & _
" set pctrent.audit = true " & _
" where pctrent.[store] in " & _
"(select top " & intCount & " [store]" & _
" from qryoldStores " & _
" order by rnd(val([store])))"
Try using
Rnd(Len(Store & ""))
that should handle any case where Store is null or contains a value that VAL
can't handle.
Other than that, do you have a huge number of records?
Do you have indexes on the Store field?
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
In my application I need to select a random 10% of a subset of records, and
then update a field of the table so that I can identify which of the records
are in the random sample.
I am trying to do this by the following update query:
strSQL = "UPDATE PCTRENT set pctrent.audit = true where pctrent.store in " & _
"(select top " & intCount & " qryoldStores.store from
qryoldStores " & _
"order by rnd(val(store)))"
db.Execute strSQL
intCount is calculated in previous code and by stepping through the code I
have verified that it is working as expected. I have also run just the
subquery (beginning with "select top..."), and that works as desired.
However, when I run the code above, Access just hangs on the db.Execute
line. Cursor changes to an hourglass and remains so. Access doesn't respond
to any keystrokes, the only way out is to close Access.
Any ideas on what I'm doing wrong here? Is there another way to accomplish
what I'm trying to do?
Thanks for any help.
dg
