Excel SQL Query Parameter

xLogan108

New Member
Joined
Jun 1, 2015
Messages
23
I have an OBDC Connection to an external server, and created a parameter on sheet 1 to filter data on sheet 2. Below is my SQL.

Code:
SELECT JCCM.JCCo, JCCM.Contract, JCCM.Description, JCCM.Department, JCCM.ContractStatus, JCCM.OriginalDays, JCCM.CurrentDays, JCCM.StartMonth, JCCM.MonthClosed, JCCM.ProjCloseDate, JCCM.ActualCloseDate, JCCM.CustGroup, JCCM.Customer, JCCM.PayTerms, JCCM.TaxInterface, JCCM.TaxGroup, JCCM.TaxCode, JCCM.RetainagePCT, JCCM.DefaultBillType, JCCM.OrigContractAmt, JCCM.ContractAmt, JCCM.BilledAmt, JCCM.ReceivedAmt, JCCM.CurrentRetainAmt, JCCM.InBatchMth, JCCM.InUseBatchId, JCCM.Notes, JCCM.SIRegion, JCCM.SIMetric, JCCM.ProcessGroup, JCCM.BillAddress, JCCM.BillAddress2, JCCM.BillCity, JCCM.BillState, JCCM.BillZip, JCCM.BillNotes, JCCM.BillOnCompletionYN, JCCM.CustomerReference, JCCM.CompleteYN, JCCM.RoundOpt, JCCM.ReportRetgItemYN, JCCM.ProgressFormat, JCCM.TMFormat, JCCM.BillGroup, JCCM.BillDayOfMth, JCCM.ArchitectName, JCCM.ArchitectProject, JCCM.ContractForDesc, JCCM.StartDate, JCCM.JBTemplate, JCCM.JBFlatBillingAmt, JCCM.JBLimitOpt, JCCM.UniqueAttchID, JCCM.RecType, JCCM.OverProjNotes, JCCM.ClosePurgeFlag, JCCM.MiscDistCode, JCCM.SecurityGroup, JCCM.UpdateJCCI, JCCM.KeyID, JCCM.BillCountry, JCCM.PotentialProject, JCCM.MaxRetgOpt, JCCM.MaxRetgPct, JCCM.MaxRetgAmt, JCCM.InclACOinMaxYN, JCCM.MaxRetgDistStyleFROM Viewpoint.dbo.JCCM JCCM
WHERE (JCCM.Contract = ?)


Is there a wildcard of some sort, so when there is no value in the cell-linked parameter, all data will show? Then when you enter a "JCCM.Contract" number, it would filter to that item entered?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try something like this:
Code:
[B]WHERE JCCM.Contract Like[/B] [I]ParameterValue [B]+[/B][/I][B] '%'[/B]
The % is the SQL wildcard. So if you have a blank as your parameter value, it will return everything.
Otherwise, it will return everything that starts with your parameter value.

Here is some more information on SQL, Like, and Wildcards: SQL LIKE Operator
 
Upvote 0
Try something like this:
Code:
[B]WHERE JCCM.Contract Like[/B] [I]ParameterValue [B]+[/B][/I][B] '%'[/B]
The % is the SQL wildcard. So if you have a blank as your parameter value, it will return everything.
Otherwise, it will return everything that starts with your parameter value.

Here is some more information on SQL, Like, and Wildcards: SQL LIKE Operator

That works to show all of my data, but it isn't even recognizing any input in the cell that the parameter is looking at/linked.
 
Upvote 0
Try something like this:
Code:
[B]WHERE JCCM.Contract Like[/B] [I]ParameterValue [B]+[/B][/I][B] '%'[/B]
The % is the SQL wildcard. So if you have a blank as your parameter value, it will return everything.
Otherwise, it will return everything that starts with your parameter value.

Here is some more information on SQL, Like, and Wildcards: SQL LIKE Operator
Nevermind. Just put a ? in for parametervalue, and now can filter by a cell on another sheet to make it easier for our project managers to track contracts by contract number. Thanks so much for your help!
 
Upvote 0

Forum statistics

Threads
1,203,071
Messages
6,053,369
Members
444,658
Latest member
lhollingsworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top