Variable in a Select TOP query

[MKH2001]

I have the query:

SELECT TOP 10 PERCENT Rnd(tbl_InspectionAudit1_1.COMPKEY) AS Expr1, Now() AS Expr2, fOSUserName() AS Expr3, tbl_InspectionAudit1_1.COMPKEY, tbl_InspectionAudit1_1.COMPBY, tbl_InspectionAudit1_1.AGENT, tbl_InspectionAudit1_1.REGIME, tbl_InspectionAudit1_1.INITIATION, tbl_InspectionAudit1_1.UNITID, tbl_InspectionAudit1_1.ACTCODE, tbl_InspectionAudit1_1.ACTDESC, tbl_InspectionAudit1_1.ACTKEY, tbl_InspectionAudit1_1.FirstOfSTARTDTTM, tbl_InspectionAudit1_1.TOWN, tbl_InspectionAudit1_1.ROADNAME, tbl_InspectionAudit1_1.DISTRICT, tbl_InspectionAudit1_1.OWNER, tbl_InspectionAudit1_1.DESCRIPT INTO tbl_InspectionAudit1_2
FROM tbl_InspectionAudit1_1
ORDER BY Rnd(tbl_InspectionAudit1_1.COMPKEY);

which correctly select my random sample of 10percent of the required dataset.

However what I am wanting to do is replace 10 with a variable from a form.
Have tried the following & '([Forms]![frm_InspectionAudit]![Percentage])' & in place of the 10 but receive and error.

Is there anyway anyone knows of where a variable can be set inside a select query?

 

[dhookom]

I would use code to update the SQL property of the querydef.

Duane
Hook'D on Access
MS Access MVP

 

[MKH2001]

Havent had opportunity to revisit this in some time due to other priorities.

How do I look at updating SQL property of a querydef?

 

[dhookom]

A generic function for changing the SQL property of a saved query is:

Public Function ChangeSQL(stQryName as String, stSQL as String) as String
   'stQryName is the query name to change
   'stSQL is the SQL statement
   Dim db as DAO.Database
   Dim qd as DAO.QueryDef
   Set db = Currentdb
   Set qd = db.QueryDefs(stQryName)
   ChangeSQL = qd.SQL 'return current SQL
   qd.SQL = stSQL
   Set qd = Nothing
   Set db = Nothing
End

Duane
Hook'D on Access
MS Access MVP