Dynamic Query

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

---

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.

---

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.

---

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.

---

Passing Dynamic Query From VB To Crystal Reports
I want to know how to pass a SQL query from a VB form to crystal reports so that the same report will change depending on the query given from the vb form. Please help.

---

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

---

How To Create Dynamic Form By Clicking A Dynamic Control Placed In Dynamic Form?
Hi all,

I have generated a dynamic form at run time inside which i have placed a dynamic command button. I need to open another dynamic form and place controls in the second form on clicking that command button. Can anyone please help me to come out with the solution?



Thanks in advance,
Devi

---

Dynamic Forms With Dynamic Controls
I have an mdi form with a couple of child forms on it. these children forms are pre loaded with controls with an index of 0. i create an instance of these children forms and load some controls at run time. in some cases (like a label control) the controls are not visible. and in another it gives run time error 340 (control array element 1 does not exist).

Hope someone can tell me what gives this kind of error?

TIA

ferdz

---

Dimensionalized Dynamic Array Copied To A New Array Is No Longer Dynamic
I have a user-defined object DS, with a child used-defined object l() which is a dynamic array.

At some stage, I ReDim DS.l(x) where x is any positive integer and use the array.

I later do Let tempDS = DS and try to use the tempDS object and its property .l() in the same way. I'm trying to subsequently ReDim tempDS.l(y) ... but it doesn't work because the tempDS.l object was created as tempDS.l(x), not tempDS.l()

How can I make tempDS.l be created as l() and not l(x) when created by copying DS.l(x) ?

---

Script Control, Dynamic Control Creation, And Dynamic Control Events
Hey everyone.

I have been messing around with the Scripting Control and Dynamic Control Creation.. I think it's amazingly powerful to have a program that can be extended by an end user by using VB Scripting commands.  I'm actually developing a system that will allow scripts to be written, saved, and run on start-up of my program to create any additional forms or controls that end users have created so it is as if they have been there the whole time.

My problem is this:

I am creating controls as follows:

I Created a class ScriptingFunctions and added some functions to it, instantiate the class, and add the object to the ScriptControl so that i can have full control of the functions.

I run a function called AddObjectToForm(ByVal sFormName as string, byVal sObjectType as string, ByVal sObjectName as String).

It loops through vb.forms, finds the form with the name you sent, selects it, and does the following:


   frmForm.Controls.Add sObjectType, sObjectName


I then call, from script, a function already in the form called "GiveAccessTo" which adds the object with the form name as a prefix to the ScriptControl.. so if I just created a "Vb.CommandButton" with name "cmdNew", I can access it through the scripting control now using "frmFormcmdNew.Caption = "NewCaption""


My problem is, however....

How can I add code .. through the scripting control, for the events on the new control I just created?

I could see creating a new windowproc in the scripting control and doing a SetWindowLong w/ GWL_WNDPROC to subclass the window procedure, but I'm not sure what I would have to do with the function?  Should I have a MsScriptControlCTL.Module and add the function to that, and reference that function via AddressOf to get the pointer to that function?

Don't forget, I know when the program ends, all of this will go away, but Like I said, I plan to have the script saved so it will execute on startup.

I'd really appreciate any help anyone could give me on this.

Thanks a lot in advance! :)
--NipsMG

---

Executing And Running A Sql Statement To Create A Query And Save Query In DataBase
I cannot seem to find the syntax needed however to execute the sql or create a query based on the SQL string that I created that can be saved. Can you please take a look at the block of code below and let me know what you think?


Sub Collect_HealthPlan()

Dim Healthplan_Count As Integer 'counts the number of items in Healthplan listbox
Dim CompanySize_Count As Integer 'counts the number of items in CompanySize listbox
Dim CurrentRow As Integer 'Stores index value of current row
Dim ctrlHPlstbox As Control 'Control variable for list box
Dim ctrlNBchkbox As Control 'Control variable for checkbox
Dim ctrlNBEAchkbox As Control 'Control variable for checkbox
Dim ctrlCompSizelstbox As Control 'Control variable for list box
Dim i As Integer
Dim j As Integer
Dim HealthPlan_Selected As Integer
'Dim ConnectDB As New ADODB.Connection
'Dim rsData As ADODB.Recordset
'Dim ADOCommand As ADODB.Command

'Set ConnectDB = Nothing
'Set ConnectDB = New ADODB.Connection

'the block of code below assigns the basic SQL upon which the form will be used to build a query
SQLString = "SELECT [DBP 04 Pipeline Total].[CountOfCompany Name], [DBP 04 Pipeline Total].[Company Name], [DBP 04 Pipeline Total].[Effective Year], [DBP 04 Pipeline Total].[Health Plan], [DBP 04 Pipeline Total].[CountOfDead No Finalist], [DBP 04 Pipeline Total].[Dead No Finalist], [DBP 04 Pipeline Total].[CountOfDead Finalist1], [DBP 04 Pipeline Total].[Dead Finalist], [DBP 04 Pipeline Total].CountOfDTQ, [DBP 04 Pipeline Total].DTQ, [DBP 04 Pipeline Total].CountOfSold, [DBP 04 Pipeline Total].Sold, [DBP 04 Pipeline Total].[Opportunity Type], [DBP 04 Pipeline Total].[Quoted MBRS], [DBP 04 Pipeline Total].[Quoted Subs], [DBP 04 Pipeline Total].[Company Size]"
SQLString = SQLString & " " & "FROM [DBP 04 Pipeline Total] GROUP BY [DBP 04 Pipeline Total].[CountOfCompany Name], [DBP 04 Pipeline Total].[Company Name], [DBP 04 Pipeline Total].[Effective Year], [DBP 04 Pipeline Total].[Health Plan], [DBP 04 Pipeline Total].[CountOfDead No Finalist], [DBP 04 Pipeline Total].[Dead No Finalist], [DBP 04 Pipeline Total].[CountOfDead Finalist1], [DBP 04 Pipeline Total].[Dead Finalist], [DBP 04 Pipeline Total].CountOfDTQ, [DBP 04 Pipeline Total].DTQ, [DBP 04 Pipeline Total].CountOfSold, [DBP 04 Pipeline Total].Sold, [DBP 04 Pipeline Total].[Opportunity Type], [DBP 04 Pipeline Total].[Quoted MBRS], [DBP 04 Pipeline Total].[Quoted Subs], [DBP 04 Pipeline Total].[Company Size]"

'Set dbsNorthwind = OpenDatabase("Strategic Marketing Dashboard.mdb")

'The following block of code initializes the control variables
Set ctrlHPlstbox = lstHealthPlan
Set ctrlNBchkbox = chkNB
Set ctrlNBEAchkbox = chkNBEA
Set ctrlCompSizelstbox = lstCompanySize

'The following block of code assigns the count values to variables for the list boxes (looping purposes)
Healthplan_Count = ctrlHPlstbox.ListCount
CompanySize_Count = ctrlCompSizelstbox.ListCount

i = 1

'The following block of code loops through the Healthplna list box to gather the names of all of the selected health plans
For CurrentRow = 0 To Healthplan_Count - 1
    If ctrlHPlstbox.Selected(CurrentRow) Then
        HealthPlan(i) = ctrlHPlstbox.ItemData(CurrentRow)
        'MsgBox "current item is " & HealthPlan(i), vbOKOnly, "test"
        HealthPlan_Selected = i
        i = i + 1
    End If
Next CurrentRow

i = 1

'the following block of code takes the values for all of the healthplans selected and begins building the query
'from the basic SQL Statement that was assigned to the SQLString variable. The SQL Statement in Querybuilder
'is updated dynamically based on the selections on the form
For i = 1 To HealthPlan_Selected
    QueryBuilder(i) = SQLString
    QueryBuilder(i) = QueryBuilder(i) & " HAVING ((([DBP 04 Pipeline Total].[Health Plan])=" & HealthPlan(i) & ")"
    
    If ctrlNBchkbox.Value = True Then
        j = 1
        QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Opportunity Type])=" & "New Business" & ")"
        
        For CurrentRow = 0 To CompanySize_Count - 1
            If ctrlCompSizelstbox.Selected(CurrentRow) Then
                CompanySize(j) = ctrlCompSizelstbox.ItemData(CurrentRow)
                QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Company Size])=" & CompanySize(i) & "));"
                j = j + 1
                
            End If
            
        Next CurrentRow
        
    End If
    
    If ctrlNBEAchkbox.Value = True Then
        j = 1
        QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Opportunity Type])=" & "NBEA" & ")"
        For CurrentRow = 0 To CompanySize_Count - 1
            If ctrlCompSizelstbox.Selected(CurrentRow) Then
                CompanySize(j) = ctrlCompSizelstbox.ItemData(CurrentRow)
                QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Company Size])=" & CompanySize(i) & "));"
                j = j + 1
            End If
            
        Next CurrentRow
        
    
    End If
    
Next i
    
End Sub

Let me know if you have a better way to do this..Thanks for all your help!!!

Regards,
Eric




Edited by - bahamaej on 3/3/2005 4:50:53 AM

---

Count Rows Returned By Query In The Query Strings
i have some problem guys about grid. the prob is like this. im using DBGrid n when i queried it, it return the values ryt ? but i dono how to count the data from the returned query string processing that i put in DBGrid. pls tell me the solution about this pls ? wil u help me guys ? n thx in advanced 4 ur
 help. GOD BLESS U ALL !!!

---

Redim A Dynamic Array As A Dynamic Array
How can I redim a dynamic array as a dynamic array??



dim h()

some loop function

h() = split(string, chr(0))

redim h()

next

---

Loading Dynamic Menus Within Dynamic Menus
We have a situation where some of our menus have become extremely long (upwards of 50 items) and are becoming unusable as a result. I've been asked to checnge our menu program so that, if there are more than 15 items it will group them into sub menus. Sound reasonably easy but I just can't quite seem to do it. I've been able to group the first 15 together by using the following algorithm:-


VB Code:
while moreRowsToAdd 'this is actually a while not eof statement against a recordset   fnCreateMenu(rcsTmp("description")) 'this dynamically loads a menu item and returns it   Let intMenuCount = intMenuCount + 1   If intMenuCount = mconMaxToolbarMenuOptions Then      'we need to add a sub menu and move all of the last 15 items onto it      'or we need to add a new menu at this level and continue to load into that one      For lp2 = 1 To mconMaxToolbarMenuOptions         'get the new sub option menu         Set mnuTmp2 = fnGetSubMenuItem(lp2) 'this routine loads a new sub menu item dynamically and returns it.         'get the existing menu         Set mnuTmp = fnGetMenuItem(lp2)          'copy the details across         Let mnuTmp2.Caption = mnuTmp.Caption         'unload the existing (and now unwanted) menu         Unload mnuTmp      Next lp2      Set mnuTmpHeader = fnGetMenuItem(intXpos, intYpos, 0, False)      Let mnuTmpHeader.Caption = "Page 1"    End Ifwend


So my menu structure at design time is to have an indexed menu item with an indexed sub menu item below it. Under normal circumstances it just dynamically loads as many menus as it needs. It always leaves menuItem(0) as blank and then hides it later in the program.

However, if it reaches 15 menu items it then starts loading sub menu items and copying the details of the current 15 menu items across. It then unloads the menu items as it no longer need them and renames menuItem(0), which now contains the 15 sub menu items, to "Page 1". This is fine and working so far.

The problem is that the next 15 items need to be loaded into Page 2, then Page 3 and so on. I can't seem to achieve this because Page 2 would presumably equate to menuItem(1), page 3 to menuItem(2) and so on. But at design time subMenuItem(0) can only exist in one place, i.e. under menuItem(0). So if I just keep loading subMenuItems they all appear under Page 1 which defeats the point of doing this in the first place.

I've tried creating menuItem(0) to menuItem(9) at design time and then creating subMenuItem0(0) to subMenuItem9(0) under them respectively but, since I need to do this with several menus, I eventually reach the point where VB reports tha I can't create any more controls on the form.

Does anyone have another suggestions?

---

How To Add Parameters To A Query Created In Microsoft Query
I define the parameter I want (which is data placed in the excel file itself) but when I refresh the cell the sql loosses the parameter.

---

SSTab Query && Data Repeater Query
I have set up a SStab to make data from a system easier to organise. The system retrives the data correctly but does not update it. I am using the propertychanged method on the datarepeater.

Private Sub txtclosedate_Change()
PropertyChanged "closedate"
End Sub

Public Property Get closedate() As Date
closedate = txtclosedate
End Property

Public Property Let closedate(ByVal newclosedate As Date)
txtclosedate = newclosedate
End Property

Is the Tab affecting it if so where can I find a guide to sort this out- the KB is not that helpful.

Thanks

Frank

---

Query With SHAPE Command In Query Analyzer ???? Please Help!
Hi,

I am writing a complicated SQL that deals with SHAPE command.
I am using MSSQL 2000. Ok.

The problem is that Query Analyzer dont recognizes SHAPE sintaxe (gives me a "[Microsoft][ODBC SQL Server Driver] Sintaxe error or access violation" message), then is a pain-in-the-*** to keep testing the query directly in VB.

Can anyone tell me if there is some SQL environment where I can test queries with SHAPE command, or if there is any way to trick Query Analyzer to force it to understand this command?

Thanks!

---

Executing Query Analyzer Query File
Is it possible to execute a Query Analyzer Query File from within VB6? If so, can you show me how? Can the Query Analyzer Query File be converted into a SP?


Thanks in advance!!

---

Set Query Parameter For Query That Is Report Source
I am trying to assing a value to a parameter defined in one of my queries in an Access Database. The ultimate purpose is to run a report named "Summary" that has this query as the source. The problem is that I've tried different ways to assing the parameters and they don't seem to be working. How would I go about making this code work?

CODE:

Dim db As DAO.Database
Set db = CurrentDb
Dim parQ As QueryDef
Set parQ = db.QueryDefs("Summary")
Dim strGrp As String

Select Case Selection.Value 'Form value entered
Case 0
strGrp= ""
Case 1
strGrp = "One"

Case 2
strGrp = "Two"
Case 3
strGrp= "Three"
Case 4
strGrp = "Four"
End Select

parQ.Parameters("prmGrp") = strGrp
parQ.Parameters("prmSel") = lstSelect.Value 'Assign value specified in Form

DoCmd.OpenReport "Summary", acViewPreview

'This didn't work as it still prompted me to define the parameters.
'So I tried the following Code:

Dim db As DAO.Database
Set db = CurrentDb
Dim parQ As QueryDef
Set parQ = db.QueryDefs("Summary")
Dim strGrp As String
Dim rstQuery As Recordset

Select Case Selection.Value 'Form value entered
Case 0
strGrp= ""
Case 1
strGrp = "One"

Case 2
strGrp = "Two"
Case 3
strGrp= "Three"
Case 4
strGrp = "Four"
End Select

parQ.Parameters("prmGrp") = strGrp
parQ.Parameters("prmSel") = lstSelect.Value

Set rstQuery = parQ.OpenRecordset()
DoCmd.OpenReport "Summary", acViewPreview

'This doesnt work either. I have to other parameters in the query, two dates read off directly as entered in the form, that are not explicitly defined as parameters. This code gives me an error that it expected more parameters. I assume it is the dates. But the dates do have values in the form that are apparently not being read.

Any suggestions? All is appreciated.

---

Time Elapsed In SQL Query In VB Vrs SQL Query In Access
Hi guys,

Should it be normal for a SQL query in VB to take longer than in Access. Both queries are out to a SQL server and it is taking 30 seconds for MS Access and almost 4 minutes for VB. Is there anything I am missing or are there different engines in each of the SQL queries?

Thanks a lot,

- George

---

Help Needed To Create Such An SQL Query, A Looong Query?
I am trying to create one long Sql query based on users selection, but keep getting confused, need some guidance. I have created a sample access database and here is my explanation, hope i make sense!

I have three tables with the following data:
Table one: tblAssessment

UserID companyName SportName CountryName
   1 2        6         
   2 4        8    
   3 3        7
 
Table two: tblDropDown

DropDownID ParentID Name
1 0 CompanyName (as CompanyName has DropDownID=1, all the companynames will have parentId 1
2 1 Nike
3 1 Reebox
4 1 Puma
5 0 SportName (as SportName has DropDownID=5, all the Sportnames will have parentId 5)
6     5 Football
7     5 Baseball
8 5 Rugby

Table three: tblSummary

CompanyName SportName CountryName
Nike     Football England
Reebok     Baseball USA
Puma     Rugby NewZealand


Below is my brief explanation of the tables and fields

tblAssessment have 4 Fields:
UserID (PrimaryKey)
CompanyName(this stores an ID that refers to the name in the dropdown table)
SportName(This stores an ID that refers to the sportsname in the dropdown table)
CountryName

tblDropDown has three fields:
DropDownID (Primary Key)
ParentID
Name

The way tblDropDown is designed is that if the Field "Name" has data "CompanyName" and its DropDownID is "1", then
all the companyNames will have ParentID "1", so fields Nike, Reebok and Puma will have parentID "1"

tblSummary has three fields:
CompanyName
SportName
CountryName
This table has pure text data for all fields.

I need to create an sql statement that takes all the selection of a User and update the CountryName field of tblAssessment.

So for example if UserID "1", has CompanyName 2 (which is Nike, from DropDown table) and SportName 6 (which is Football from DropDown table) then lookup
for them two selection from table tblSummary and get the CountryName and update tblAssessment's CountryName Field. So in this case it will be "England" as the tblSummary
has CountryName "England" where SportName is "Football" and CompanyName is "Nike"

Could someone plz guide me how to create such a query?

I have attached a sample access database that mirrors the same above format.

Thanks,

---

Access Query Vs VBA Query Question
I have some code (Listing 1 below) that produces the following:

SELECT tblECOMain.JobNo, tblECOMain.ECORefNum, tblECOList.SubAssy, tblECOList.PartNo, tblECOList.PartDescription, tblECOList.ManufacturedBy, tblECOList.RevisionLevel, tblECOList.SubChg, [NewQty]-[OldQty] AS Expr1, tblECOList.ECOBy, tblECOList.ECODate, tblECOList.ECOReturnProc, tblECOList.ECOReturnNo, tblECOList.ECOReturnNoReason
FROM tblECOMain INNER JOIN tblECOList ON tblECOMain.ECORefNum = tblECOList.ECORefNum
WHERE (((tblECOMain.JobNo)=99999) AND ((tblECOList.ManufacturedBy)<>'METRO MACHINE') AND ((tblECOList.SubChg)=False) AND (([NewQty]-[OldQty])<0) AND ((tblECOList.ECOReturnProc)=True));

The query runs great, however when run from the command button in VBA for Access it produces the rs.BOF or rs.EOF condition which goes to a “no records to process” condition. The purpose of the code is to simple check for records! I select records from a form via the "((tblECOList.ECOReturnProc)=True));" condition. If any are selected, then lets perform on those records later.

The initial form (which allows me to check my values) comes directly from a query, not code. The code basically reproduces that same query, but with the "((tblECOList.ECOReturnProc)=True));" set to true, meaning that these are the specific records I want to deal with. When I check the box, the control source is the field ECOReturnProc, for my check box control chkECOReturnProc.

I also tried referring to the control in code instead, so that the last line read “(Me.chkECOReturnProc = True));” – but this produced a different error. A runtime error of:

-2147217904(80040e10) – No value given for one or more required parameters.

I’d prefer to do this on read only, forward only – since I’m doing nothing to the data in this segment – but have changed to open keyset, lock optimistic because I’ve had similar problems before. There is no difference.

I’d have to say my confusion level is on the rise. I’ve done this exact thing elsewhere, in several places – with no problems. I believe the code is representatively correct because the debug.print produces valid syntax. Sad to say I spent the whole day on this yesterday, and it really is something that should take 30 minutes.

Any help would be greatly appreciated!


Listing 1:
sql = "SELECT tblECOMain.JobNo, tblECOMain.ECORefNum, tblECOList.SubAssy, tblECOList.PartNo, tblECOList.PartDescription, " & _
"tblECOList.ManufacturedBy, tblECOList.RevisionLevel, tblECOList.SubChg, [NewQty]-[OldQty] AS Expr1, tblECOList.ECOBy, " & _
"tblECOList.ECODate, tblECOList.ECOReturnProc, tblECOList.ECOReturnNo, tblECOList.ECOReturnNoReason " & _
"FROM tblECOMain INNER JOIN tblECOList ON tblECOMain.ECORefNum = tblECOList.ECORefNum " & _
"WHERE (((tblECOMain.JobNo)= " & [Forms]![frmReportsListing]![cboJN] & ") AND" & _
"((tblECOList.ManufacturedBy) <> " & Chr(39) & "METRO MACHINE" & Chr(39) & ") AND " & _
"((tblECOList.SubChg)=False) AND " & _
"(([NewQty]-[OldQty])<0) AND " & _
"((tblECOList.ECOReturnProc)=True));"

Debug.Print "SQLCHK: "; sql
rs.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.BOF Or rs.EOF Then GoTo Skip1

---

SQL Query From Access Query Vs Table
Hello!

Will a simple SQL query from an access query be much slower that one from a table:

SELECT * FROM qryTest (qryTest = "SELECT Name, Adress from tblTest")

vs

SELECT Name, Adresss FROM tblTest

noccy

---

Multitable Query In A Single Query....
Hi guys!

Long time since last time I ask for your help but now I have hit a wall... and I need your advice again...

I have 2 tables:
- Table A: Has Date field and Qty field [DateIn,Qty].
- Table B: Has Date field, Week field (1-52), Month field (1-12) [DayIn, WeekIn,MonthIn].

What I need is to retrieve the sum of Qty and show the information Daily, Weekly or Monthly.

For the daily part I have no problem:

Query = "Select DateIn, SUM(Qty) FROM Table A GROUP BY DateIn"

My problem is when I tried to do it Weekly or monthly, I don't know how to relate sum qty and GROUP by WeekIn or MonthIn in a single query.

I know I can do multiple querys using the WHERE clause "WHERE DateIn BETWEEN DayIna AND DayInB" And DayIna = First Day of week or month and DayInb = Last Day of Week or month.

Also I can Include the Week and Month fields in table A, but I don't want to have that extra information in every single row of table A.

Thanks in advance...

regards!

---

Sub-query, Nested Query Or A JOIN?
This should prolly go to Database forum, but there's virtually no one there at this time.

I need to pull rec's from multiple tables based on a single ID and put it all in one rec set. I can generate that query, but here's my problem...

Once I have that rec set, I need to pull another record from one of the same tables already used based on a value from that initial query and have it all in one rec set. Is that clear as mud?

I have company records coming from say 3 tables. One of the tables is an addressbook. Once I find all the related company info I need the address info for a contact name within the company info. Therefore I am trying to pull the company address and the contact's address from the addressbook table and put all the data in one rst.

Can this be done???

---

MySQL Query Vs Access Query
Ok so I made the switch finally after some headache but am finding query speeds are so much faster than Access. I am running into a problem with one of my Queries.

Access = #DateValue#
mySQL = 'DateValue'

Is that correct? Doug I believe you posted the wrapper for Access using the number sign, do I need to switch it back now or am I going about this wrong?

---

Why MSSQL Query Is Different With MYSQL Query?
somebody please help me, i have a trouble with this, i used connection to database with this

Code:


rs_xsr.Open "SELECT DISTINCT tblxsr.Mark,tblxsr.Rev FROM tblxsr where tblxsr.Transmittal='" & cmbtrans.Text & "'" & _
" and tblxsr.mark !='E' order by tblxsr.Mark,tblxsr.Number", dbcon, adOpenDynamic, adLockPessimistic


then error show this
Run Time ERRor '-2147217900 (80040e14)
[Microsoft][ODBC SQL Server Driver][SQL SErver] ORDER BY items must appears in the select list if select distinct is specified

and when i dont use "DISTINCT" that error is not show but my data is not showing, how to fix it
somebody know what the different between MYSQL query with MSSQL Query?
if u have tutorial , please send me email
harry@dbsd.co.id

---

Query Works In Access Query But Not Through ADO??
Hi there,
my sql string send through ADO from an asp page doesn't return any results but when I put the query directly into Access query it works fine. Got any ideas what I might be doing wrong, I'm at a loss. Below is my the query which searches between dates and the code used to connect to the db(it connects ok just doesn´t return anything). Any help would be great.

Cheers in advance
Williery

SELECT Noticias.Noticias_Fecha, Noticias.Noticias_Titulo, Noticias.Noticias_Imagene FROM Noticias WHERE (((Noticias.Noticias_Fecha) Between #10/01/2001# And #20/01/2001#) AND ((Noticias.Noticias_Titulo) Like '*test*')) AND (((Noticias.Noticias_Typo)=1));



Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath("BaseDeDatosGolfNavarra.mdb")            
            
Set rsNoticias = Server.CreateObject("ADODB.Recordset")
                rsNoticias.open strSql,objConn

---

How To Print The Query Resukts In The Grid && Excel Row Transfer From VB Grid Query
Good afternoon. I use VB6 and have no Crytals Report. I want to print with formatting (headers plus the query) the query result which is displayed on a grid.

Also should the user want to transfer it to Excel, there is a button when you click it whatever you row you have selected onthe query results in the grid is transferred to Excel. the user may use ctrl to choose individual rows int he query grid or the use shift to select a group of rows to be transferred to Excel.

How do you use the import (to restore back up .mdb files) and export (to backup .mdb files) in real time?

Suggestions are welcomed.

God bless,
Alvin

---

Query For MS-Access: SQL Differences In Access Query Window Vs. Using ADO In Code
OK, many of us have come across the "Like" wildcard issue. When doing an Access query in Access itself, the "*" is used, whereas doing the query thru ADO in code, the "%" is used.

What other situations are there like this? I have a query that uses IIF, INSTR, and UCASE. When I test it in the Access query window, it works fine, but when I produce the same SQL dynamically in my VB program and try to execute it (using ADO), it chokes. Obviously, "standard" SQL doesn't like these VBA functions in the query. Does anyone know (or know where I find documentation on) the specific differences when doing queries for Access in the Access IDE vs. ADO? Thanks.

---

UI And Dynamic UI
hey,

i'm a newbie in VB but i've already some questions:

1. i've made a user interface in photoshop, how can I use this in visual basic??

2. How can I make this userface dynamic such as what you can do in flash (fade in/out effects and some other dynamical stuff)?

---

Dynamic IP
o can i use a dynamic IP for a program am creating, i have set up the account and for privacy purposes lets say it squidge.no-ip.info (its not really)so do i just set the remotehost = sqidge.no-ip.info ?

if not how do i go about it?

---

Dynamic Var V2
Hi together...

Think i've found a way to describe my problem.


Code:
Dim xxx as String
xxx="Action=3"
Ok. thats all. But how can i EXECUTE this String IN a Variable in Runtime?

Something like:

Code:
execute(xxx)
MsgBox(Action) 'Should return 3

Response.Write(xxx) ' Doesnt work. Tried so many Things. I'm lost
xxx is DYNAMIC. Its in a do while function... every time he reads this in do while, he haves to execute it.

Please help :'(

---

Dynamic SQL
I have a little problem....


I have 3 options on a frame and on the basis of which option is chosen I want to load a combo box.But I want to write only one SQL statement for them.

can i do something like this

if option1.value then
a=option1.caption
elseif option2.value then
a=option2.caption
elseif option3.value then
a=option3.caption
end if

sql.open "Select a from table1"

In this way i want to write only one sql query whatever the option is chosen.

PLease help.

---