CODEWITHSUNDEEP
ms-access
JDT
JDT

Reputation: 107

MS Access Set Parameter Query

I am trying to run an Access query that filters a RawData table by Site.

When I run the query normally it runs fine, I have set up the query as follows

Picture of Relevant Fields from Query

I have a sub table in the database that contains the names of the Sites and am using VBA to run through the Site list in this table and do certain actions including filtering the Raw Data Table (via the Query) and copying the raw data to an excel sheet.

I am trying to incorporate my Access query into the Access VBA and it's falling down here

DoCmd.SetParameter "[Enter the Site:]", rs!DimensionSite
DoCmd.OpenQuery "Raw Data Filtered by Site"

rs!DimensionSite is the relevant field from the 'DimensionSite' column in the Site table

I am getting an error on the first line saying " the expression you entered contains invalid syntax"

I'm not sure in the setparameter text if I should be referencing (1) 'DimensionSite' (i.e. the column name within the table) or (2) 'Enter the Site:', i.e. the text box criteria I am using in the query.

I would appreciate any help with what is going wrong

Thanks

J

Upvotes: 2

Views: 1101

Answers (1)

user6788933
user6788933

Reputation: 287

After hitting the same problem. I have discovered the example provided by Microsoft throws you in the wrong direction.

You need to surround in " when you are using string parameters.

For your example use:

DoCmd.SetParameter "[Enter the Site:]", """" & rs!DimensionSite & """"

Upvotes: 2

Related Questions