Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.
View 4 RepliesNeed the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
Is there any way to use an Excel cell as a parameter for MS Query.
i.e. Select fields from table where field = "cell value"
I tried the $ ($D$5 ) got Invalid pseudocolumn error; which leads me to hope there might be a way to introduce a VALID pseudocolumn in my Query.
I have a query that has criteria based on "is one of". I would like to pass this off to a parameter selected from a list box where the user could select multiple items. Is something like this possible?
View 9 Replies View RelatedI am trying to write an SQL query using parameters in an Excel Worksheet and then return the results to the current sheet ar cell B11. I have found it imposible to make it work in SQL so am trying to run it from VBA to pass the parameters. These are named cells StartDate and EndDate in cells A2 & A3 respectively and should replace the constant dates below. I am working in Excel2003 & MS SQL 2012.
I have tested the query on SQL server with fixed values (which are still in it). It works fine & I have pasted it in.
I took an existing query I found & tried to modify it for Excel2003 but ... Whatever syntax I use, I get a Run-time error 424 when I get to a line with 'connection'
Code:
Sub DateQuery()
With ActiveWorksheet.connections("ndserver01").oledbconnection
' .Connection = "OLEDB; DSN=Excel Files; provider=SQLOLEDB; Catalog=NDM_Sage200;Data Source=ndserver01"
.Refresh BackgroundQuery:=True
.CommandType = xlCmdSql
[Code]...
What have I done wrong
The web site is http://scores.covers.com/college-foo...chups.aspx?t=0. This website defaults to a Top 25 scoreboard. I would like to access the Full scoreboard instead. I've looked inside the web site source code for the parameter names, but using the most likely candidates in web queries pull in the Top 25 scoreboard instead. The web site name does not change when scoreboards are changed.
View 9 Replies View RelatedI would like to have set of data as Parameter if possible.
Example: In Microsoft Queries I have two data sets. In first one shows let's say Item Number, and second one I should have table with components of all Item numbers listed in first table.
So first table I set one Parameter (?) and i get like 8 results. I would like that second table takes those 8 results (sometimes more or less then 8) as parameter and shows data (components) for them.
If I set sign '?' I can only select one cell as parameter, is there any way I can select multiple cells as parameter and use it like SQL function IN ?
Code:
SELECT 1.ITMNR, 1.COMNR, 1.QTYPR
FROM 1.1.1 1
WHERE 1.ITMNR IN ?
And second table should look for results in first table under 1.COMNR and select it as 2.ITMNR
Code:
SELECT 2.ITMNR, 2.COMNR, 2.QTYPR
FROM 1.1.2 2
WHERE 2.ITMNR IN ?
My question is in regards to query and setting up parameters. I have the following query (I am using an example for security purposes)
http://examplenet.test.com/aaaa/Search/SearchList.aspx?type=Number&criteria=AA21&mode=New&rfilter=****&afilter=A&exact=False" _
What I am trying to do is have a parameter for the user to change the AA21. For example the user could type AA33 and it would change the code to:
http://examplenet.test.com/aaaa/Search/SearchList.aspx?type=Number&criteria=AA33&mode=New&rfilter=****&afilter=A&exact=False" _
I am familiar with creating parameters and have been able to add things to the end of the string but not in the middle. I have been trying a very long time to figure this out.
Is it possible to use cell references to define parameters in an SQL query to an MS Access database?
I assumed it was similar to using SQL but I'm getting an error in excel.
Here is my query: .....
The error I get in excel is: "Invalid character value for cast specification" and it wont let me specify a cell.
I have a workbook that contains two sheets with two separate queries. On the first sheet I have two cells designated to accept the values for the parameters. The first sheet accepts the values and populates the table correctly. The second sheet doesn't seem to accept the values and just returns blank rows.
View 9 Replies View RelatedI would like to develop a user-friendly Excel 2010 spreadsheet that would allow the user to enter from 1 to 10 (i just picked this number at random just to have an upper limit) employee numbers in cells A1-A10 and "click" on the VBA Code button that would invoke an Access Query and in the process return all the applicable data gathered from the query back into the Excel Spreadsheet. Believe it or not, I can do all this mentioned so far. BUT what i cant do is my second option for the user which is allow them to pull ALL of the employees back into the spreadsheet using the same query.
In summary, using Excel as a frontend dashboard, I know how to get a specific number of employees' information from a query and I know how to get all employees' information from a query but I dont know how to get either/or. In other words, I could do this with two queries and two "VBA-Code" buttons but I would like to do it with one query and one button.
i need to essentially find and replace udf's and the complication is rearranging the parameters in the udf's into a new formula structure.
so my question, is there an easy way to extract a parameter (or all parameters) from a function?
for example, if you had = SUM(A1,A2,A3:A6) you would return 'A1' as the first parameter, 'A2' as the second and 'A3:A6' as the third.
Obviously it gets complicated when you nest functions such as =SUM(IF(A2=1,0,A1),A3) etc and the if() function would be parameter 1.
1) I have an invoice form, and need to create a formula that will allow me to calculate a discount IF an item number begins with "C" or "CE".
Example: Item # is in cell F12. (may or may not begin with "C" or "CE")
Item price is in cell J12.
Extended price (qty x price) is in cell K12.
In L12, I need to calculate a discount (from % in fixed cell L9) on the figure in K12 - based on whether or not the Item # in F12 begins with a C or CE.
If it does not begin with C or CE, then L12 needs to equal 100% of K12.
(The 2 parameters I referred to in the title were:
C, followed by a number
CE followed by a number )
2) The biggest part of the dilemma is that the person using this spreadsheet is totally unfamiliar with Excel formulas, spreadsheets in general, and has to send this finished product to a client each week. So I need this to be as simple as possible - which seems to me to be a formula (that can be copied to insert rows, etc. if necessary).
I have this code snippet..
Code:
Function appointment(ByVal day As String, ByVal hour As Integer) As String
' Insert code to return any appointment for the given day and time.
Return "appointment"
End Function
But I get this error. Compile error: Expected: end of statement
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
I'm trying to query a query in Access 2003, from Excel 2003.
The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]
The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]
I use the following VBA code in Excel to excecute the query:
With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
.RowNumbers = True
.Refresh BackgroundQuery:=False
iResultRowCount = .ResultRange.Rows.Count
End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.
I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.
My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..
I have been using the following formula to return a value where a % is used, is there anyway i can make the sum shorter.
=SUMIF(Q10,'Price Calculator'!$A$5,'Price Calculator'!B5)+SUMIF(Q10,'Price Calculator'!$A$6,'Price Calculator'!B6)+SUMIF(Q10,'Price Calculator'!$A$7,'Price Calculator'!B7)+SUMIF(Q10,'Price Calculator'!$A$8,'Price Calculator'!B8)+SUMIF(Q10,'Price Calculator'!$A$9,'Price Calculator'!B9)+SUMIF(Q10,'Price Calculator'!$A$10,'Price Calculator'!B10)+SUMIF(Q10,'Price Calculator'!$A$11,'Price Calculator'!B11)+SUMIF(Q10,'Price Calculator'!$A$12,'Price Calculator'!B12)+SUMIF(Q10,'Price Calculator'!$A$13,'Price Calculator'!B13)+SUMIF(Q10,'Price Calculator'!$A$14,'Price Calculator'!B14)+SUMIF(Q10,'Price Calculator'!$A$15,'Price Calculator'!B15)+SUMIF(Q10,'Price Calculator'!$A$16,'Price Calculator'!B16)+SUMIF(Q10,'Price Calculator'!$A$17,'Price Calculator'!B17)+SUMIF(Q10,'Price Calculator'!$A$18,'Price Calculator'!B18)+SUMIF(Q10,'Price Calculator'!$A$19,'Price Calculator'!B19)+SUMIF(Q10,'Price Calculator'!$A$20,'Price Calculator'!B20)+SUMIF(Q10,'Price Calculator'!$A$21,'Price Calculator'!$B$21)
Seems like a Pain to be using something this big, just cant seem to get it to work when i try to shorten it
The actual link from a list on the left side of that web page is "Printable List".
I can't work out why all other web pages work fine with;
Get External Data/Web Query, but this particular web page won't work.
Sub Test_A()
Sheets("test1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://acttab.com.au/interbet/racing?type=venues", Destination:= _
Range("A3"))
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub
I am using the attached code however when my database query does not return any data I dont want the Dim function to perform.
Sub macro3()
Worksheets("Data 1"). Range("I3:BU65536").ClearContents
'declare a variable to hold date
Dim strDate As String
strDate = Format(CDate(Range("startDate").Value), "yyyy-mm-dd") & " " & FormatDateTime(CDate(Range("startDate").Value), vbLongTime)
Sheets("Data 1").Select
Range("A1").Select
With Selection.QueryTable
.Connection = _...........................
I am querying a database in Microsoft Query and I have it set to pull in jobs that match a customers code in cell A1. I would like it so that if cell A1 is blank, it pulls in jobs for every customer.
View 3 Replies View RelatedI am trying to add a 0 if it is missing at the start of the number in columns K2:K3000 and L2:L3000, The numbers do not have fixed lengths so I cant use the custom setting but if the number starts with a 7, 8 or 3 I want to add a 0
View 6 Replies View RelatedI am trying to run a prestored query in a Access database and popuate the result into a Recordset --- through Excel. So far, I have managed to accomplish that using the code below. However, now I need to update some records in the recordset. The code below does not allow me to do so.
The following message prompted when the code tried to update the record: Run-time error '3251' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
This is written for other users who only knows how to create a query in Access. And the users prefer not to import the query results to spreadsheet.
*Someone suggested use Recordset.Open however, that seems to work only with SQL statement but not prestored Queries.
Code:
Sub RunExistingQuery()
'Execute prestored queries in Access
Dim Con As ADODB.Connection
[Code].....
Excel Question 1.jpg
I need to have a code that looks up Column 1 (Parameter 1) first, then looks up Column 2 (Parameter 2), and inputs Column 3 (Value). The code needs to be in a SINGLE cel.
I thought initially using a Lookup function within another Lookup function might work, but I can't seem to get the code to work properly.
I'm working on an attendance tracker for church. We will have folks scan a QR code and fill out a 2 question survey: Name of person being checked in and date of check in. Both are mandatory and are set in such a way as to always have the same formatting every time. That's my first sheet, it just gathers the data. My second sheet actually has the attendance tracker with the roll and dated columns. What I'm trying to create on the second sheet is a formula that will search the first sheet for the date and name and return a 1 if they were there that day.
View 7 Replies View RelatedI have a datast that looks like:
ID Week Sales
1 1 $200
1 2 $300
1 3 $200
1 4 $250
1 5 $220
1 6 $100
2 1 $500
2 2 $100
2 3 $230
2 4 $250
2 5 $210
2 6 $110
I will have 3 parameters in different cells that will identify the range of data in which i'd like to get a SUM for:o
ID,
Week,
Number of weeks.
So for example my I would identify ID =1, Week = 3, # of Weeks = 2
I should get an output of: $450 ($200+$250, rows 3+4 from above).
I've tried using IF(AND and Sumifs nested.. but it's not working out..
I am using the following formula and I need to modify it a little:
=IF('Input Sheet'!A17="AR",0,'Input Sheet'!F17)
All I need to do to the above is add "MISC" to the formula. I have tried but I keep getting errors.
Basically I want:
=IF('Input Sheet'!A17="AR" OR "MISC",0,'Input Sheet'!F17)
I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.
1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.
2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.
I need to modify the following formula,
=IF(G5=-105,-105,-G5-IF(ABS(G5)>251,40,IF(AND(ABS(G5)>221,ABS(G5)<251),30,
IF(AND(ABS(G5)>180,ABS(G5)<221),20,IF(AND(ABS(G5)>=100,ABS(G5)
[Code].....