Update query criteria using variables
My spreadsheet contains a query from an Access database.
I recorded a macro to edit the query's criteria, which uses 'selection.querytable'.
This works fine whilst the criteria are hard coded into the macro.
However, I want the criteria to come from variables (via inputboxes maybe).
I can't work out how to work the variable name into the criteria line of the macro without getting an error.
Can anyone help out.
Thank you.
Can you post your current code?
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
Recorded code is shown below. I want the criteria bit to be dynamic so the user chooses whether to report on 'Employee' or 'Contractor'
Regards.
Sub QueryUpdate()
Range("B4").Select
With Selection.QueryTable
.Connection = _
"ODBC;DSN=MS Access Database;DBQ=F:\TR MI.mdb;DefaultDir=F:\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
.CommandText = Array( _
"SELECT `Bookings Query for Recharge MI Report`.`Period Name`, `Bookings Query for Recharge MI Report`.SumOfQuantity, `Bookings Query for Recharge MI Report`.SGRate, `Bookings Query for Recharge MI Rep" _
, _
"ort`.Recovery, `Bookings Query for Recharge MI Report`.`Employee Cost Centre`, `Bookings Query for Recharge MI Report`.EmployeeContractor" & Chr(13) & "" & Chr(10) & "FROM `F:\TR MI`.`Bookings Query for Recharge MI Report` `Book" _
, _
"ings Query for Recharge MI Report`" & Chr(13) & "" & Chr(10) & "WHERE (`Bookings Query for Recharge MI Report`.`Cost Centre Recharge` Is Not Null) AND (`Bookings Query for Recharge MI Report`.EmployeeContractor='Contractor')" _
)
.Refresh BackgroundQuery:=False
End With
End Sub
Last edited by ot070707; 05-26-2011 at 05:29 AM. Reason: Problem now solved thank you
Problem now solved thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks