Dynamic Crosstab Query In A Form
Does anyone know how to update the form as the crosstab column headings
change as the data does.Help!!
The columns headings are week number and must be dynamic.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Crosstab With Dynamic Columns. Possible? (Oracle SQL)
Hi, Gurus!
Wanted to create some crosstab in Oracle, which wouldn't have preset (in query) columns, is that possible?
Let's say I have filed some home assets, for example, TV-set and wardrobe.
That two items have various parameters, kept in separate table.
The data would be like this:
Assets:
Quote:AssetID AssetName AssetManufacturer AssetModel AssetSN
000001 TV Set Toshiba T4561 TTV234781
000002 Wardrobe Paris Woodcraft Classic -
AssetSpecs:
Quote:AssetID AssetAttrID Value
000001 Height 50
000001 Width 80
000001 MaxChannels 100
000001 PowerUsage 250
000001 Color pink
000002 Height 200
000002 Width 150
000002 Capacity 100
000002 Color black
and etc...
(All of this stuff is fake )
Is it possible to create a SELECT statement, which would return the following?
Quote:AssetID AssetName AssetManufacturer AssetModel AssetSN Height Width Color MaxChannels PowerUsage Capacity
000001 TV Set Toshiba T4561 TTV234781 50 80 pink 100 250
000002 Wardrobe Paris Woodcraft Classic - 200 150 black 100
I've seen the crosstab queries with preset columns, in this case I would have to 'define' all those parameter columns, but, if, like in my real case, there are a lot of them (around 800) and that list is always a subject to change, what would be the best to do?
Thanks
-----------------------------------------------------------------
Making mistakes is one of the ways of improvement.
GL and HF
Crosstab Query
hi~
i have a cross tab query that shows employee productivity within a certain date range (week). i can feed it a week date range and it will pull corresponding data if it is in the database and show an empty recordset if there isn't any data except for in one instance. i have data in the weeks of 3/15/04 and 3/22/04. When i feed it the week range of 3/1/04-3/5/04, it gives me all the data in the table!??!? can anyone see if i'm doing something wrong here?
Code:
sql:
TRANSFORM Sum(depTBL.DepAmt) AS SumOfDepAmt
SELECT depTBL.Emp AS Employee, depTBL.DepType, Sum(depTBL.DepAmt) AS Total
FROM depTBL
WHERE (((Format([DateCmp],"Short Date")) Between #3/1/2004# And #3/5/2004#))
GROUP BY depTBL.Emp, depTBL.DepType
PIVOT Format([DateCmp],"Short Date");
CrossTab Query
Hi:
I'm using a "CrossTab" Query to retrieve data. When I get it back, I want
to print the column Headers along the first row of the grid. How to I get
the names of the column Headers? The names of the columns will be dynamic;
i.e., I am listing facilities and getting total transports provided for each
facility. I am returning the Name of the Facility (or it's designator) as
Column headers and want that in the first row...with the totals in the
second row. Is there a way to use visual basic and get the names of the
columns in the Crosstab...and then put each one in a field in the first row?
Your advice would be appreicated.
JP
Crosstab Query
I have some cross tab queries in Access that I would like to replicate in SQL Server 7 stored procedures.
Does SQL Server have an equivalent command to the Access crosstab? I can manually do it using temp tables where the range is predefined, but gets messy where the range isn't known.
Thanks in advance
Dave Seddon.
Crosstab Query For A Graph
Well here is my latest quandary. I am working for the first time with Graphs in VB. I have an Access 2k database and I wish to create a Graph for some of the data.
My table, qryErrorData, has several columns but the ones that I am concerned with are “Cottage” and “Description”. There are 12 different cottages that will be in the “Cottage” Column and 8 different Error Descriptions in the “Description” Column. What I would like is to have a Graph that shows the 12 cottages along the bottom and in each cottage Row the count of each of the Error Descriptions.
In Access I am able to generate a Graph using the Wizard and there is a “TRANSFORM_PIVOT Statement in the properties page that reads as follows.
Code:
TRANSFORM Count(*) AS [Count] SELECT [Cottage] FROM [qryErrorData] GROUP BY [Cottage] PIVOT [Description];
I have tried using this in my code and have even altered it some but I just cannot get the graph to display the data properly. The only thing that seems right is the legend. It lists all the descriptions.
Here is the complete code to load the graph.
Code:
Private Sub Command1_Click()
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Set cnn = New ADODB.Connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
cnn.Mode = adModeReadWrite
cnn.Properties("Jet OLEDB:Database Password") = "!@mandk@!"
cnn.Open "Data Source = MedErrors.mdb"
strSQL = "TRANSFORM Count(*) AS [Count] SELECT [Cottage], [Description] FROM [qryErrorData] GROUP BY [Cottage] PIVOT [Description]"
'
rst.Open strSQL, cnn, adOpenStatic
With MSChart1
Set .DataSource = rst
.ShowLegend = True
.chartType = VtChChartType2dBar
.SeriesType = VtChSeriesType2dBar
End With
End Sub
I don't know if there is something missing in the SQL statement or in the settings of the MSChart.
I thank you in advance for any answers that lead me to the resolution of this simple question.
Michael
Crosstab Query Order By
I can't seem to get how to do the ORDER BY in an Access 2000 Crosstab Query:
Code:
TRANSFORM Count(CorrectProductsMaster.Answer) AS CntOfAns
SELECT ProductMaster.ProductName
FROM (((StudyData INNER JOIN CorrectProductsMaster ON
(StudyData.QuestionNumber = CorrectProductsMaster.CondNum)
AND (StudyData.Answer1 = CorrectProductsMaster.Answer))
INNER JOIN ConditionMaster ON
CorrectProductsMaster.CondNum = ConditionMaster.CondNum)
INNER JOIN ProductMaster
ON CorrectProductsMaster.Answer = ProductMaster.ProdNum)
INNER JOIN StudyDemo ON
(StudyData.Study = StudyDemo.Study)
AND (StudyData.MeNumber = StudyDemo.MENumber)
AND (StudyData.Study = StudyDemo.Study)
AND (StudyData.MeNumber = StudyDemo.MENumber)
WHERE StudyData.Study = "TEST"
AND StudyData.QuestionType = "C"
AND CorrectProductsMaster.[Y/N] = Yes
AND StudyDemo.AlphaSpecialty = "P"
GROUP BY ProductMaster.ProductName
PIVOT ConditionMaster.ConditionName;
In the Query Designer, if I select the field CntOfAns to Sort Descending, then there is an error stating:
"Cannot have aggregate function in Order By Clause (Count(CorrectProductsMaster.Answer)).
I am allowed to switch to the SQL View in the designer, where it shows the incorrect SQL code regarding the above error:
Code:
TRANSFORM Count(CorrectProductsMaster.Answer) AS CntOfAns
SELECT ProductMaster.ProductName
FROM (((StudyData INNER JOIN CorrectProductsMaster ON
(StudyData.QuestionNumber = CorrectProductsMaster.CondNum)
AND (StudyData.Answer1 = CorrectProductsMaster.Answer))
INNER JOIN ConditionMaster ON
CorrectProductsMaster.CondNum = ConditionMaster.CondNum)
INNER JOIN ProductMaster
ON CorrectProductsMaster.Answer = ProductMaster.ProdNum)
INNER JOIN StudyDemo ON
(StudyData.Study = StudyDemo.Study)
AND (StudyData.MeNumber = StudyDemo.MENumber)
AND (StudyData.Study = StudyDemo.Study)
AND (StudyData.MeNumber = StudyDemo.MENumber)
WHERE StudyData.Study = "TEST"
AND StudyData.QuestionType = "C"
AND CorrectProductsMaster.[Y/N] = Yes
AND StudyDemo.AlphaSpecialty = "P"
GROUP BY ProductMaster.ProductName
ORDER BY Count(CorrectProductsMaster.Answer) DESC
PIVOT ConditionMaster.ConditionName;
I am at a loss on how to order the records.
CrossTab Query To Excel
i have a recordset that run crosstab query in Access via command object..
now i want to write the result to excel..
1. can someone guide me how to do it?
2. is there something like this set excel.datasource = rs?? (it help me alot to show it at datagrid )
thanks
Crosstab Query In SQl Server
Hi Guys-
I have a crosstab query in my access database. Then I tried to create crosstab query in SQL server but it always gives me error. So if anybody have any idea how to create a cross tab query in SQL server, Please let me know it.
Thanks in advance-
Sohel
Using ‘Like’ In A Crosstab Query
I have a Crosstab query in Access 97 where I wish the user to enter the products the query runs on. In a standard query I would just type ‘Like [Enter Product Type]’ in the criteria field in design mode. When the query is run a dialog box appears requesting the user to enter the product type, the query then runs according to the user input.
But in a crosstab query it comes up with an error ‘The Microsoft jet engine does not recognize ‘[Enter Product Type]’ as a valid field or expression. How do I get around this issue? Oh yes the crosstab query is run on another query not from tables.
Any help would be appreciated.
Use VBA To Apply Filter To Crosstab Query
I was hoping someone can point me in the proper direction. I have a user form with several combo boxes. These combo boxes would allow the user to select various items to build out a filter. I would like to use this filter and have it applied to an existing crosstab query and output only the columns with data. This is in Access 2003
It is a slight modification to what I have done in the past where I used the crosstab query as the record source for a form and applied the filter to the form. The problem I ran into with that approach was that in the form I needed to lock in the column headings which ended up making the report cumbersome due to the large number of blank columns that would be output. It ended up defeating what I was trying to do.
I hope I have explained this well enough.
Is there any way to combine the nice feature of the crosstab query only outputing columns with data and applying a multi-field filter in VBA.
Any direction would be appreciated.
Thank you.
Newbie Problem {crosstab Query}
Hi everybody
I'm desperately trying to write a Macro to transform some raw statistical data of a survey into a crosstab.
The first macro I'm writing is to count the different answers which are cathegorized. Her s the code I ve written till know, obviously it looks horrible and even more obviously nothing works til now:-)
Code:
Sub crosstab()
Dim i As Integer
Dim j As Integer
Dim data As Sheet2
Dim crosstab As Sheet1
Dim count1 As Integer
Dim count2 As Integer
Dim count3 As Integer
Dim count4 As Integer
Dim count5 As Integer
Dim count6 As Integer
Dim count7 As Integer
Dim count8 As Integer
Dim count9 As Integer
Dim count10 As Integer
Dim missing As Integer
count1 = 0
count2 = 0
count3 = 0
count4 = 0
count5 = 0
count6 = 0
count7 = 0
count8 = 0
count9 = 0
count10 = 0
missing = 0
For j = 7 To 25 Step 1
For i = 2 To 479 Step 1
While data.Cells(i, 4) <> data.Cells(i + 1, 4)
'as the data is cathegorized,i'm trying to count rsults by cathegory, cathergories are like AA BB etc
'by the way, it gives me a bug
If data.Cells(i, j) = 1 Then
count1 = count1 + 1
ElseIf data.Cells(i, j) = 2 Then
count2 = count2 + 1
ElseIf data.Cells(i, j).Value = 3 Then
count3 = count3 + 1
ElseIf data.Cells(i, j).Value = 4 Then
count4 = count4 + 1
ElseIf data.Cells(i, j).Value = 5 Then
count5 = count5 + 1
ElseIf data.Cells(i, j).Value = 6 Then
count6 = count6 + 1
ElseIf data.Cells(i, j).Value = 7 Then
count7 = count7 + 1
ElseIf data.Cells(i, j).Value = 8 Then
count8 = count8 + 1
ElseIf data.Cells(i, j).Value = 9 Then
count9 = count9 + 1
ElseIf data.Cells(i, j).Value = 10 Then
count10 = count10 + 1
Else: missing = missing + 1
End If
Wend
k = 1
crosstab.Cells(i, k) = data.Cells(i - 1, 4)
crosstab.Cells(i + 1, k + 1).Value = count1
crosstab.Cells(i + 2, k + 1).Value = count2
crosstab.Cells(i + 3, k + 1).Value = count3
crosstab.Cells(i + 4, k + 1).Value = count4
crosstab.Cells(i + 5, k + 1).Value = count5
crosstab.Cells(i + 6, k + 1).Value = count6
crosstab.Cells(i + 7, k + 1).Value = count7
crosstab.Cells(i + 8, k + 1).Value = count8
crosstab.Cells(i + 9, k + 1).Value = count9
crosstab.Cells(i + 10, k + 1).Value = count10
Next i
k = k + 2
count1 = 0
count2 = 0
count3 = 0
count4 = 0
count5 = 0
count6 = 0
count7 = 0
count8 = 0
count9 = 0
count10 = 0
missing = 0
Next j
End Sub
So problem is, nothing works and I can really not figure out why, is there something wrong with the syntaxe???I would be gratefull for every tip or hint anyone could give me. Thanks
Reporting From Access Crosstab Query
I am pulling information from an access crosstab query. Has anyone used this before? I need to change the query at runtime using an sql statement, but so far have been unable to get it to work! This is what I have so far:
'form where department is selected contains this:
reportSql = "Select * from qryType_Crosstab where Department = '" & dacDepartment.Text & "' order by Date"
'qryType_Crosstab is the access query
'dacDepartment is the form control which allows dept to be chosen
'the following is the code attached to the command button that gets the report:
Private Sub cmdGetReport_Click()
Dim oconn As ADODB.Connection
Dim orec As ADODB.Recordset
Set oconn = New ADODB.Connection
oconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\TRAMPintegWORK ORDERSWORKORDERS.mdb;Persist Security Info=False"
oconn.Open
Set orec = New ADODB.Recordset
orec.Open reportSql, oconn, adOpenKeyset, adLockOptimistic
darMeasures2.Show
End Sub
The code I have so far just runs the whole report, it does not change the recordset based on what is chosen for reportSQL. Does anyone have a suggestion on how to get this to work?
Appreciate the help!
Crosstab Query On SQl Server 2000
Hi all,
Please show me how to make a crosstab query on SQL server 2000. I used structure "Transform ... pivot" but it only works in Asccess not for SQL Server.
Thanks a lot.
How To Display The Crosstab Query Data ?
hi
i am designing one survey applcation.in that i have a table questions & answers table. for a question we can no.of answers. means any no.of survey of people. so what i want is that when i select a question i want to get the total, and for each 4 answers how many votes are given... for this i have design one crosstab quesry..and its working fine... but unable to get the fields into the VB. How to get these fields and how to display this data in VB. check the crosstab here i am attached.
thanks in advance
thanks
---------------------------------------------
Srinivas.
Displaying Results From A Crosstab Query
Hey thanks for the help on creating a new table. it works great. My other problem is displaying results from a crosstab query. I figured out how to create a crosstab query and it works really well, but I can't figure out how to display the results, or how to store the results in variables for later usage. Any help would be appreciated. Thanks again for the help!!
Finding The Smallest Value Using Crosstab Query
HI THERE
I am trying to find the smallest Value Of a specific Item (Items Like, Valves, Gas Meters, Regulators …) in a Database of my Clients (Different Vendors of Items Like, Valves, Gas Meters, Regulators …).
Using a Crosstab query I was bale to Find the Value of the Smallest Item, but I just can not Show the Name of its Relevant (Related) Company, and Origin Name.
I think I need to use Dlookup in my Crosstab query, Or Use a New query altogether…
Attached is a Copy of my application, any Suggestion Or recommendation is greatly appreciated.
Best regards
sanan
Access Data To Excel Using CrossTab Query??
Hi ... I am trying to import Access data to excel woksheets using a crossTab query. I need to use a search criteria in excel. I have no clue to convert the crosstab query to excel VBA code. Can anyone help me with this ...
The Query looks like this :
TRANSFORM Sum(try2.Charge) AS SumOfCharge
SELECT try2.ServiceID, try2.ServiceDescription
FROM try2
GROUP BY try2.ServiceID, try2.ServiceDescription
PIVOT Format([DateOut],"yyyy");
try2 is another query:
SELECT tblService.ServiceID, tblService.ServiceDescription, tblCar_Service.DateOut, tblService.Charge
FROM (tblCar_Service INNER JOIN tblCar_Service_Details ON tblCar_Service.CarServiceID = tblCar_Service_Details.CarServiceID) INNER JOIN tblService ON tblCar_Service_Details.ServiceID = tblService.ServiceID;
Can anyone tell me how one canuse a crosstab query with excel. or can we make parameterized queries with crosstab.
Cheers, Nit
Error On MoveFirst Method On A Crosstab Query
I've been using some code for a while now to generate reports off of queries in a Microsoft Access 2002 database for some time now. I just created a crosstab query today and found my code resulted in:
Microsoft JET Database Engine error '80004005'
Operation is not supported for this type of object.
On the MoveFirst method. Any ideas why crosstab queries are treated differently from regular SELECT queries...? Any ideas on what I can do to get around this? I have to loop through my data twice for my reports to work the way I am using them. Not sure what's going on here though. Why are crosstabs so different?
Get Field Header Names From Crosstab Query
I am using VB6 DAO 3.6 and an Access 2K database. I used Access to create a Crosstab query and pasted it into my program as seen below. The Query works fine, but I need to retrieve the names of the Field Headers it creates in the query.
I tried mTmpMaterialsUsed.Fields(0) but this gives me data, as I would expect. I cannot find a mTmpMaterialsUsed.Header property. I can not seem to figure this out. Any pointers?
Wayne
Dim gDB As Database
Dim SQL As String
Dim mTmpMaterialsUsed As Recordset
SQL = "TRANSFORM Sum(MaterialsUsed.Qty) AS SumOfQty " & _
"SELECT MaterialsUsed.Description " & _
"From MaterialsUsed " & _
"GROUP BY MaterialsUsed.Description " & _
"ORDER BY MaterialsUsed.WhatMonth DESC " & _
"PIVOT MaterialsUsed.WhatMonth; "
Set mTmpMaterialsUsed = gDB.OpenRecordset(SQL)
Crosstab Query - Need Rows Sorted In Arrival Sequence
Hello,
I have a crosstab query (below) that works fine except that I need the rows sorted in the order that they appear in the subquery. MS Access is sorting them in ascending order by default, and this is not good for me.
I.E.
TestDUOM appears:
"SPC"
"Yeast"
"Mold"
in source query (query LD)
but show up as
"MOLD"
"SPC"
"YEAST"
in crosstab because it is sorting them in ascending order.
Does anyone know of a way I can prevent the crosstab from sorting the row headers???
Any help is appreciated.
Here is my crosstab query:
TRANSFORM First(LD.Results) AS FirstOfResults
SELECT LD.TestDUOM
FROM [SELECT TestDetail.TestDetailSeq, LoginDetail.ProductDescription, TestDesc & " " & UOM AS TestDUOM, TestDetail.Results
FROM Tests INNER JOIN (LoginHeader INNER JOIN (LoginDetail INNER JOIN TestDetail ON (LoginDetail.DetailSeq = TestDetail.DetailSeq) AND (LoginDetail.LogID = TestDetail.LoginID)) ON LoginHeader.LogID = LoginDetail.LogID) ON Tests.TestID = TestDetail.TestID WHERE LoginHeader.LogID ="3031-51"
ORDER BY TestDetail.TestDetailSeq]. AS LD
GROUP BY LD.TestDUOM
PIVOT LD.ProductDescription;
Thanks!
Crystal Reports & Dynamic Query (even Tables And Fields Changes In The Query At Run Time)
Hi there!
I need to link the crystal report to a query that is generated dynamically. I will be knowing about which table to be linked and which fields to be retrieved at run time only. Fields are refered using an alias in all the query output to get a common name for all. I cant use a dataset or TFX file solution as mentioned earlier as i am not aware of the tables and fiels till runtime. Else i have to hardcode all the possible combinations in different tft file and link .
i tried with a temporarytable but that too doesnt seems to work.
Can anyone guide me of any simpler methode of doing this?
Bipin
&"un-crosstab&" Query?
I have a data table with n rows and m columns.
First column of data is the key: Name
I want to create a single table with (n*m) rows and three columns. Name, column heading, data
For example:
Column headings might be {name, height, weight, age}
Names are {Bob, Carol, Xian}
Bob is 6', 200lb, 45
Carol is 5', 105lb, 22
Xian is 5'6, 150lb, 34
I want my table to have {Name, Category, Value}
Bob, age, 45
Carol, age, 22
Xian, age, 34
Bob, height, 6'
Carol, height, 5'
Xian, height, 5'6
(etc)
Thoughts?
Dynamic Query
I need to create a sql query to an access db where the query can change periodically. Is it possible to place the query patterns into a table and just change the table contents when needed...the VB query would read the table and build the sql statement needed to query another table? Where would I find information about this?
VBA Dynamic Query
Hi,
I am pretty new to programming using VB Access. I am also pretty new to my postion, where I am looking to build a query that can return records based upon a dynamic search.
I want to have the specific instance I am searching for able to be dynamically requested.
So, I will have "Select * from TableName where FieldID = User Entered Variable" . User Entered Variable will be like an input box delivered variable.
Sorry that this is so kindergarten.
Any help appreciated.
Mike
Dynamic Query..?
Hi all,
How can I retrive records in VB6 using a value from a TextBox into a SQL-query?
Something like:
select * from Table1 where Field1= TextBox1
/Kent J.
Dynamic Query In Dataenvironment
Hey!
I want to use text from a Combobox in a query and want to use sql builder. Is that possible? I am in "Command1 Properties" and want to make a search like this "SELECT * FROM Customers WHERE Customer = Combo1.Text".
/Bengt
Help With A Dynamic Query And Report.
Hi Folks,
In thread 146520 which I can’t seem to be able to reply to PLC-Punk quoted:
“I need to know if this is possible. I would like to dynamically change the parameters of a query based on user input from a form. Then this query would be used as the source data of a report.
Basically I want to create a "Search" type function on my report where the user can generate any report they want based on their entry on a form.”
I need to do this in my Access project however I am very new to VB and presently doing self-teaching. Timbo and Mkoslof were discussing a way to modify a query and I have looked at this MS Knowledge Base Q304302
http://support.microsoft.com/?kbid=304302
that looks at the controls on a form to build a SQL string. I would like to combine these two processes for a dynamic query.
I am pretty much lost at this point and need to finish the search form quickly. Could someone help me develop this code? I will greatly appreciate it.
Thanks,
Rich
Query Regarding Dynamic Array In VB
Hello Sir,
I am using dynamic array and I would like to know How can I identify that this array is empty.
Because while accessing dynamic array, before storing any value, the error msg pops up like 'subscript out of range'. I tried to use LBound and UBound but did not work.
Please me through
Thanks And Regards
Abhijit
Creating Dynamic Query
i have a purchase table
My pruchase form uses this table
i want to store the values of vat% ,vat on,vatamount in that table
since vat% is not constant
so i decided to add 3 common fields
vatpercent,vaton,vatamount
in a bill there might be 1 or 2 ,or many tax slabs
for example
bill no 1 may contains products of 4 % tax ,12.5% tax
bill no 2 may contain products of 12.5,0,4 etc
so i am storing in purchase table in these fields
purchaseinvoiceno vatpercent vaton vatamt
purinv(primary+autonumber)
bill no 1 4,12.5 50,100
2,12.5 1
bill no 2 12.5,0,4 200,100,50
25,0,2 2
it means for bill no 1
products which have taxes 4% its amount is 50 and vatamt is 2
products which have taxes 12.5 its amount is 100 and vatamt is 12.5
while reporting it want to split this slabs
if i want to see the output for the 2 bills it should look
billno vatpt4 vaton4 vatamt4 vatpt12p5 vaton12p5 vatamt12.5
vatonpt0,vaton0,vatamt0
bill no 1 4 50 2 12.5 100
12.5 0 0 0
bill no 2 4 50 2 12.5 200
25 0 100 0
note the tax slabs can be anything it is not limited
which query should i write
Dynamic Query & Report
I have a Form that I use as a criteria selector for a query. By picking a value from a list of combo-boxes the user can filter the records to show the data they want.
However, it displays the results in DATASHEET view.
I'm trying to mod it so it will update a REPORT, dynamically, on the fly as the user selects filter information.
When I select my criteria in the combo boxes, then hit my applyfilter command button, it then invokes a select query and asks me which records to find?? It's suppose to pull that info from the combo-boxes themselves, then apply it to the Report.
After checking it over and over again for days, I still can't figure out what I've done wrong, and I'm hoping someone might offer insight or advice.
(CountyName, AgencyStatus, POstatus, ReferSource..etc. are all their own tables.)
The 'cmdApplyFilter' event looks like this:
Private Sub cmdApplyFilters_Click()
Dim strCountyName As String
Dim strAgencyStatus As String
Dim strPOStatus As String
Dim strReferalSource As String
Dim strServices As String
Dim strFilter As String
If SysCmd(acSysCmdGetObjectState, acReport, "rptConsumerpurchase") <> acObjStateOpen Then
MsgBox "Please Open the Report."
Exit Sub
End If
' Build criteria string for County field
If IsNull(Me.cboCountyName.Value) Then
strCountyName = "Like '*'"
Else
strCountyName = "='" & Me.cboCountyName.Value & "'"
End If
' Build criteria string for AgencyStatus field
If IsNull(Me.cboAgencyStatus.Value) Then
strAgencyStatus = "Like '*'"
Else
strAgencyStatus = "='" & Me.cboAgencyStatus.Value & "'"
End If
' Build criteria string for POStatus field
If IsNull(Me.cboPOStatus.Value) Then
strPOStatus = "Like '*'"
Else
strPOStatus = "='" & Me.cboPOStatus.Value & "'"
End If
' Build criteria string for ReferalSource field
If IsNull(Me.cboReferalSource.Value) Then
strReferalSource = "Like '*'"
Else
strReferalSource = "='" & Me.cboReferalSource.Value & "'"
End If
' Build criteria string for Services field
If IsNull(Me.cboServices.Value) Then
strServices = "Like '*'"
Else
strServices = "='" & Me.cboServices.Value & "'"
End If
'Combine criteria strings into WHERE clause for the filter
strFilter = " [County] " & strCountyName & " AND [AgencyStatus] " & strAgencyStatus & " AND [POStatus] " & strPOStatus & " AND [ReferalSource] " & strReferalSource & " AND [Services] " & strServices
'Apply the filter and switch it on
With Reports![rptConsumerpurchase]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Dynamic SQL Query And Import To CSV
I have a table that has about 10 unique fields in it. These fields need to be queried by the user through a visual basic application/form that would have dropdowns for each field. The user would then mix and match and and select the different criteria with dropdown boxes for a dynamic query. When the user has all of their criteria selected, I need them to be able to click a button that will export the query results to a CSV file. I do not necessarily need the query results to be displayed to the user, only exported to the CSV file. Can anyone help me out with some code or advice on where to begin.
Dynamic Query & Report Not Working
I've been trying to debug a dynamic query & report I'm attempting to build for a database for days now, and finally giving in and posting it, in hopes that someone might be able to show me what I'm missing.
Essentially - I've built the code from this example:
http://www.fontstuff.com/access/acctut17.htm
I've even downloaded the 'examples' so I could compare my VB to the example.
The problem - I created a form to be used as a dynamic query, I open the named report, then open the form to select my options from the combo boxes, but after i select them it's as if I've invoked a select query, as it asks me to then type in the criteria I'm looking for again??
The criteria should be picked out of my combo boxes, not manually typed in.
Mind you all this happens - and 'still' the data isn't copied to my report either.
I've looked and looked and looked and cannot figure out what i've done wrong. Any help at all is appreciated, I am a novice VB coder.
-------------------------------------------------
Code:
Private Sub cmdApplyFilters_Click()
Dim strCountyName As String
Dim strAgencyStatus As String
Dim strPOStatus As String
Dim strReferalSource As String
Dim strServices As String
Dim strFilter As String
If SysCmd(acSysCmdGetObjectState, acReport, "rptConsumerpurchase") <> acObjStateOpen Then
MsgBox "Please open the report."
Exit Sub
End If
' Build criteria string for County field
If IsNull(Me.cboCountyName.Value) Then
strCountyName = "Like '*'"
Else
strCountyName = "='" & Me.cboCountyName.Value & "'"
End If
' Build criteria string for AgencyStatus field
If IsNull(Me.cboAgencyStatus.Value) Then
strAgencyStatus = "Like '*'"
Else
strAgencyStatus = "='" & Me.cboAgencyStatus.Value & "'"
End If
' Build criteria string for POStatus field
If IsNull(Me.cboPOStatus.Value) Then
strPOStatus = "Like '*'"
Else
strPOStatus = "='" & Me.cboPOStatus.Value & "'"
End If
' Build criteria string for ReferalSource field
If IsNull(Me.cboReferalSource.Value) Then
strReferalSource = "Like '*'"
Else
strReferalSource = "='" & Me.cboReferalSource.Value & "'"
End If
' Build criteria string for Services field
If IsNull(Me.cboServices.Value) Then
strServices = "Like '*'"
Else
strServices = "='" & Me.cboServices.Value & "'"
End If
'Combine criteria strings into WHERE clause for the filter
strFilter = " [County] " & strCountyName & " AND [AgencyStatus] " & strAgencyStatus & " AND [POStatus] " & strPOStatus & " AND [ReferalSource] " & strReferalSource & " AND [Services] " & strServices
'Apply the filter and switch it on
With Reports![rptConsumerpurchase]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Edit by loquin: Added [vb][/vb] code tags around your code for readability.
Dynamic Query In Access 2002
I'm attempting to pass several constraints to a query and so far have been unsuccessful. Perhaps what I'm trying to do can't be done or perhaps there is a better way. A user can select multiple items within a listbox. Whatever the user selects will act as the criteria for the query. (Field = Item1 OR field = Item2 OR field = Item3, ect.) I can't come up with a way to write the sql statement so that its modified depending on the number of items selected. I've attempted to write the sql statement as a variable, but the query can't tell the difference between the item and the 'OR field =' portion of the string. Am I going in the right direction to accomplish what I want to do? Is there an easier way?
Thanks in advance.
Order By In Dynamic Union Query
I have a stored procedure that executes a very large dynamically built union query. In the procedure I actually have it such that it says:
EXEC(I concatenate the dynamic query here)
When running this, it does not return my results in order by the Primary Key. I used an order by clause and union merge hint, both of which are a detriment to speed.
However, if I do this:
@localvariable = (concatenate the dynamic query here)
EXEC(@localvariable)
It does return my results in order by Primary Key without using an order by clause or union merge hint, hence saving time.
My question: Is there a logical reason why this occurs? Or is it just a fluke?
If there is logic, I would love to read about it somewhere.
Thank you...
Reports Using Dynamic Query Results...
I don't feel that I was entirely clear the first time around...here is what I
need to accomplish..
say I have an SQL statement in VB modules:
SQL1 = "Select ID FROM People WHERE ID = ' & userinput.text & ' "
how do I produce the results of this query in result from, as I do not have
Crystal Reports installed on any system that I use. Thanks in advance for
the help.
Dynamic Query Not Returning RecordCount
I am using a Stored Procedure with Dynamic SQL Statements in SQL Server. If try to retrieve records using the Stored Procedure in VB (ADO Command & Recordset combination), there are no records retrieved. However if I extract the result to a MSHFlexGrid
(Set MSHFlexGrid1.DataSource=rstTest)
I can get the results. Still the RecordCount property of the rescordset is -1. I can get the results in SQL Query Analyzer also. Any help on this?
Creating A Dynamic Database Query
My objective is to create a dynamic query of a database using inputs from a form. The problem is that there are about 30 things i want to be able to query by, each of which the use will have the option of turning on and off the query for. For example, if the person wanted to query by dates, they'll input the hi and low value, and click a check box acknowledging this criteria... they can then check other criteria and input those values.
The question is: how can this be done easily? I'm using vba in an access database so i'd like to stick to Jet SQL statements. Is there a way to contatenate a string to include each of these criteria --> after checking if the check box is checked or not?
I'm pretty lost on how to get this one started. If anyone knows how this can be done.. i'd appreciate it. Also, if anyone has some samples of code used to run queries on tables in vba i'd appreciate that as well. I'm having a really difficult time trying to get a queries to run. I'm not sure exactly what object you use to run a Jet SQL statement.
Dynamic Query In SQL Server Stored Proc
I need to control a where clause based on potentially a list of options provided in a SQL Server stored procedure. Right now I'm accomplishing it by doing something like the following:
Code:
CREATE PROCEDURE MyProc(@MyVals VARCHAR(100))
AS
DECLARE @SQL VARCHAR(1000)
-- ...
SET @SQL = 'SELECT Rec_Date, SUM(Field1), SUM(Field2)
FROM MyTable
WHERE Field3 IN (' + @MyVals + ')
GROUP BY Rec_Date'
EXEC (@SQL)
RETURN
And I call it something like this:
Code:
EXEC MyProc '''AAA'',''BBB'''
-- or
EXEC MyProc '''CCC'''
Note that the parameter must be able to handle a variable number of values.
Is there any cleaner/better way to handle this in SQL Server? Something like ParamArrays in VB, perhaps? Forgive me if it's a somewhat basic question; I'm not an expert in Transact-SQL yet.
Dynamic SQL Query Based On Textbox Entry.
Hi,
I'm trying to create a SQL query base on a textbox entry. It should update the query for every new character entered and do an auto-fill on the rest of the text that would appear in the textbox. So an entry of Mich would look through the firstname fields of a table and return "Michael" while leaving "ael" highlighted. So, basically, the same autofill effect as Internet Explorers.
I was thinking of:
dim strMyQuery as string
public sub txtFName_change()
strMyQuery = txtFname.text
adoname.recordset.execute strMyQuery
end sub
Ah, also, there are other textboxes linked to other fields which should be autofilled and updated on each character entry. Mic in txtFName should return both "Michael" in txtFName textbox and "Rodriguez" in txtLName. Would I be barking up the right tree in assuming this would be a matter of changing the cursor location in the recordset? Thanks.
Dumping The Dynamic Query Data Into Temporary XLS
Hello, people
Is there any fast way to export (or even dump) results of some query into temporary XLS object, which is located in the memory?
For example, I've some SQL string, which I'd like to have results for. I can successfully use ADO objects, query the DB, then process the results to the XLS objects row by row. The code remains dynamic all the time. But if the record count is big (1K+), the whole thing works not so fast.
Before, I used TransferSpreadsheet action, but it doesn't work with temporary objects, i.e. I have to create a query, then assign some SQL to it, then dump it to the some file, which will be created somewhere. The code becomes dependent of the static objects (queries, target file).
I'd like it to be dynamic, is that possible?
Thanks!
-----------------------------------------------------------------
Making mistakes is one of the ways of improvement.
GL and HF
Edited by - un5killed on 11/28/2006 7:17:56 PM
Refreshing Pivot Table Using Dynamic Query
Hi,
I want to create Pivot report by getting data from a table in SQl server 2000. So I recorded a macro from excel and put that code in VB. Now the problem is my query to get this data is going to change dynamically (have to provide 'from' and 'to' dates selected by user). I put this piece of logic in VB and ran it. But pivot table always runs the query which was supplied when creating the report for the first time.
Also I want to change the connection informtaion from my VB code. But again excel uses same connection information which was provided while creating report.
This is the code I use to generate pivot tables:
''This function creates/refreshes PivotTabls for the specified report
Sub CreateReport_SLAAss(strNewOrRefresh As String)
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
'Variables for Error handling
Dim strErrorParameter1 As String
Dim strErrorParameter2 As String
Dim strErrorParameter3 As String
Dim dtmFromDate As Date
Dim dtmToDate As Date
dtmFromDate = "01/02/2003" 'Hardcoded for testing,will be given by user
dtmToDate = Now 'Hardcoded for testing,will be given by user
On Error GoTo Err_CreateReport_SLAAss
Set objExcel = New Excel.Application
With objExcel
.Visible = True
'If report does not exist create a workbook
If strNewOrRefresh = m_strCREATENEW Then
Set objWorkBook = .Workbooks.Add
Else 'If report exists, open it.
Set objWorkBook = .Workbooks.Open(App.Path & "Reports" & m_strSLAAss)
End If
With objWorkBook.PivotCaches.Add(xlExternal)
' Get the connection string from Registry (in g_strCONNECTIONSTRING variable).
GetConnectString
.Connection = g_strCONNECTIONSTRING
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM viw_AHL_SLAAssessment viw_AHL_SLAAssessment where xDateReceived between '" & dtmFromDate & "' and '" & dtmToDate & "'")
'If the report is not there create it.
Set objSheet = objWorkBook.Worksheets(1)
If strNewOrRefresh = m_strCREATENEW Then
.CreatePivotTable TableDestination:=Range("A3"), TableName:="PivotTable1"
End If
End With
'If the report is already there just refresh it. This is where I think the problem is. It just does not take the new query....but does not give any error also. It just refreshes the data using the query which was supplied while creating this report in the step above.
If strNewOrRefresh = m_strREFRESH Then
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End If
End With
If strNewOrRefresh = m_strCREATENEW Then
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields("xDateReceived")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("StatusDesc")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("in4_N_AHLOpp")
.Orientation = xlDataField
.Position = 1
End With
Range("B5").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of in4_N_AHLOpp")
.Caption = "Stats"
.Function = xlCount
End With
Range("A5").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("xDateReceived").AutoSort _
xlAscending, "xDateReceived"
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
ActiveSheet.PivotTables("PivotTable1").Format xlTable9
Sheets("sheet1").Select
Range("A1").Select
Rows("4:4").Select
With Selection.Font
.Name = "Arial"
.Size = 6
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Range("B5").Select
ActiveWindow.FreezePanes = True
End If
If strNewOrRefresh = m_strREFRESH Then
objWorkBook.Save
Else
objWorkBook.SaveAs (App.Path & "Reports" & m_strSLAAss)
End If
'objWorkBook.Close
'objExcel.Quit
Exit_CreateReport_SLAAss:
Set objExcel = Nothing
Set objWorkBook = Nothing
Set objSheet = Nothing
Exit Sub
Err_CreateReport_SLAAss:
'Get rhe error message
g_strErrorProcedure = "Sub CreateReport_SLAAss"
g_strErrorModule = "basCommonFunctions"
Call getAHLError(Err.Number, strErrorParameter1, strErrorParameter2, strErrorParameter3)
'Log the error
LogError g_strApplicationCode, g_lngUniqUserID, Now, Err.Number, Err.Description, Err.Source, g_strErrorModule, g_strErrorProcedure
GoTo Exit_CreateReport_SLAAss
End Sub
Now can anyone please help me out with these two things :
1. How do I make excel take new query everytime without going through the creation of new report every time. i.e I just want to refresht the report...
2. How do I make excel take connection information from my VB code ?
Any help regarding this will be greatly appreciated.
Thanks,
Vikalp
(1) Making Vertical Scrollbar On A Vb Form (scrolling Vb Form). (2) Dynamic Text Storage.
Code:Option Explicit
Dim WithEvents ctlText As VB.TextBox
Private Sub Form_Load()
Dim i, l As Integer
For i = 1 To 4 Step 1
l = i * 700
Set ctlText = Controls.Add("VB.TextBox", "ctlText" & i, Form1)
ctlText.Move 1000, l, 1000, 500
ctlText.Visible = True
ctlText.Text = "" & l
Next
End Sub
How can i make a vertical scrollbar such that in case the textboxes are more than 4 and cannot fit on the form, the others are also visible by scrolling down the form.
How can i store data from each textbox to the database or file.
Form To Form Interaction Problem. (dynamic Forms)
Hello,
I have a form (MAIN) which is duplicated during runtime using
Dim X As Form
Set X = New MAIN
X.Show
"MAIN" contains functions, which trigger events for that form. My problem is, I have another form "TOOLS" which needs to interact with the "MAIN" from which it was launched. So far, "TOOLS" interacts only with the first "MAIN", and not with the one it was launched from. I know I could make "TOOLS" a user control and place it within "MAIN" so that it will work, but I prefer to have "TOOLS" be a single form. Any ideas on how I could solve this with ways to reference the form from which "TOOLS" was launched to where I can interact with it will be much appreciated.
Thank you ahead of time
Form As Property Of Other Form + Dynamic Menu
Hello everyone,
this is urgent, any ideas welcome.
Ok, I have a form and user can type log in there. In the log he can insert a problem description and solution from database. He searches this in a different form.
Now what do I want? I want to pass the first form (log) as a property to the searchform.
So when the user clicks "Insert" I can insert the right things in the right log (more forms can be open on the same time of the log thingy). Any ideas on this ? Thank you!
Also just a little question, how do I make my menu Dynamic, so that it changes the possible options when I activate a certain form. Without typing all the visible things.
Thank you!
Greetz
Search before you ask - if you don't know where to search, ask before you search
Dynamic Vb Form
hi i am using an activex control, in which i have to create a new form when user calls a function
note:
dim newfrm as form
set newfrm=new form1
i dont want the above coding, because to use the above a form1 has to be there, i dont want any form to be attached, i want to create it dynamically,
any suggestions, appreciated,
thanks in advance
|
TRACKER
