Access Query Vs VBA Query Question

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.

---

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

---

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

---

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?

---

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

---

Query On An Access Query !
I have a table with data as follows:

ProductNo, Customer
1, John
1, Simon
1, Andrew
2, Andrew
2, Dave
3, Simon
4, Pete
4, John
4, Dave

I want to display the data in a query as follows:

Product Number, Customers Bought
1, "John, Simon, Andrew"
2, "Andrew, Dave"
3, "Simon"
4, "Pete, John, Dave"


Any ideas how??????

---

Access Query -> ADO Query Using DSN
Hello,

I have an Access database which links to tables on an MS SQL Server. It has a query which pulls together information from three different tables. Two of the tables are in one database; the third is in another. The query is supposed to grab the first full set of information it finds for a particular production unit (reel). The query is as follows, and works as expected from Access:

SELECT TOP 1 [dbo_reel].[reel_key], [dbo_Roll_Summary].[cust_key_end_user], [dbo_reel].[dt_turnup], [dbo_reel].[avg_speed_fpm], [dbo_reel].[birth_bwt_act], [dbo_reel].[birth_moisture], [dbo_Quality_Data].[Opacity_(6S20)], [dbo_Quality_Data].[TEAR_md_(2M80)], [dbo_Quality_Data].[TEAR_cd_(2C80)], [dbo_Quality_Data].[TENS_cd_(2C10)], [dbo_Quality_Data].[BRT_t_(6T10)], [dbo_Quality_Data].[SMOOTH_t_(4T40)], [dbo_Quality_Data].[SMOOTH_b_(4B40)], [dbo_Quality_Data].[POROSITY_(5S30)], [dbo_Quality_Data].[TEA_md_(2M60)]

FROM dbo_Quality_Data INNER JOIN (dbo_reel INNER JOIN dbo_Roll_Summary ON [dbo_reel].[reel_key]=[dbo_Roll_Summary].[roll_reel]) ON [dbo_Quality_Data].[sample_key]=[dbo_reel].[reel_key]

WHERE ((([dbo_reel].[reel_key])=[Reel Key]) And (([dbo_Quality_Data].[Opacity_(6S20)]) Is Not Null));

Now, I need to convert this query to a form I can use with ADO. I created a DSN for the SQL Server and pass its name to the ADODB.Connection object. I realized that the table names in Access are not those on SQL Server. Through the SQL Server database list and small test SELECT queries I found the database & table names I need and that ADO (or SQL Server?) does not like square brackets in the statement. My "converted" query is:

SELECT TOP 1 rep_snnb.dbo.reel.reel_key, Technical.dbo.Roll_Summary.cust_key_end_user, rep_snnb.dbo.reel.dt_turnup, rep_snnb.dbo.reel.avg_speed_fpm, rep_snnb.dbo.reel.birth_bwt_act, rep_snnb.dbo.reel.birth_moisture, Technical.dbo.Quality_Data.Opacity_(6S20), Technical.dbo.Quality_Data.TEAR_md_(2M80), Technical.dbo.Quality_Data.TEAR_cd_(2C80), Technical.dbo.Quality_Data.TENS_cd_(2C10), Technical.dbo.Quality_Data.BRT_t_(6T10), Technical.dbo.Quality_Data.SMOOTH_t_(4T40), Technical.dbo.Quality_Data.SMOOTH_b_(4B40), Technical.dbo.Quality_Data.POROSITY_(5S30) , Technical.dbo.Quality_Data.TEA_md_(2M60)

FROM Technical.dbo.Quality_Data INNER JOIN (rep_snnb.dbo.reel INNER JOIN Technical.dbo.Roll_Summary ON rep_snnb.dbo.reel.reel_key=Technical.dbo.Roll_Summary.roll_reel) ON Technical.dbo.Quality_Data.sample_key = rep_snnb.dbo.reel.reel_key

WHERE (((rep_snnb.dbo.reel.reel_key)='" & reelKey & "') And ((Technical.dbo.Quality_Data.Opacity_(6S20)) Is Not Null));

So, [dbo_reel] = rep_snnb.dbo.reel; [dbo_Roll_Summary] = Technical.dbo.Roll_Summary; and [dbo_Quality_Data] = Technical.dbo.Quality_Data.

Unfortunately, the converted query doesn't work. When I run the following code, I get a "Run-time Automation" error at the last line:


   Dim rs As ADODB.recordSet
   Dim conn As ADODB.connection
   Dim sql As String

   Set rs = New ADODB.recordSet
   Set conn = New ADODB.connection
' SPNEWBERG14 = ODBC data source name
   conn.Open "SPNEWBERG14"
   sql = (query)
   rs.Open sql, conn, adOpenDynamic, adLockOptimistic


I've also tried simple JOINing queries such as this one, but still get the error.

SELECT TOP 1 rep_snnb.dbo.reel.reel_key, Technical.dbo.Roll_Summary.cust_key_end_user FROM rep_snnb.reel INNER JOIN Technical.Roll_Summary ON rep_snnb.dbo.reel.reel_key = Technical.dbo.Roll_Summary.roll_reel WHERE rep_snnb.dbo.reel.reel_key = '5H0101';

Only the most simple SELECT queries work:

SELECT rep_snnb.dbo.reel.reel_key FROM rep_snnb.dbo.reel WHERE rep_snnb.dbo.reel.reel_key='5H0101';

Perhaps it has something to do with my JOIN statements, but they were essentially (except for the name conversion and removal of square brackets) created by Access and should work--or am I wrong in assuming that?

Thank you for any light you can shed.

- Ben

---

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

---

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

---

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 !!!

---

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.

---

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,

---

Print Access Reports With Values Passed To A Access Query From A Textbox On A Form.
Hello,


DB = access 2000
Method = DAO

This is my first post on this forum, I'm new to VB.

I am trying to print a report from access using VB, but what I would like to do is pass values to the query and then print the report based on the the results of the query that had values passed to it. I'm able to pass values to the query that works well.
also I'm able to print the report but I have to pass values to it using the dialog boxes that Access 2000 provides.

Is there a way to print the report that uses the query with the values that have been passed to it through the form?

***Code for printing reports***

Private Sub cmdReport_Click()

  Set MSAccess = New Access.Application

  MSAccess.OpenCurrentDatabase ("C:Documents and SettingsfiqbalDesktopdb1.mdb")
  MSAccess.DoCmd.OpenReport "Report based on Para Passing", acViewNormal
  MSAccess.CloseCurrentDatabase
  Set MSAccess = Nothing
    
End Sub

***End of Code****

***Code for Passing values from a textbox on a form***

sqlstr = "select * from Table1 where [name] ='" & Text1.Text & "' and [phone] ='" & Text2.Text & "'"

*** End of Code****

How can I get the report to use the above SQL without the access dialog boxes and the values just being passed from the text boxes.

would appreciate all the help

thanx in advance.

Kinara

---

Access Query From Vb
i'm writting a program that is going to update a table in an access data base. this is all automated from visual basic. the table that is going to be updated is a history file (its Called AdamWeeklySpentHistory). this is going to be updated by having other tables put in to it eg AdamWeeklySpentWeek1. Nothing is shown in vb.

i think i need to aisgn an sql statement to a variable then pass it to a query in access. i don't know how to pass the variable to the access query. can anyone help.

i've never programmed access to work with vb. all i've got so far is the access database opening. and an exit button. this is the code:


Option Explicit
'access variables
Dim myACApp As New Access.Application
Dim myDatabase As Database

Private Sub cmdCreateHistoryFile_Click()
Set myACApp = New Access.Application
myACApp.OpenCurrentDatabase ("H:/History Project/dbtest.mdb")
Set myDatabase = myACApp.CurrentDb


End Sub

Private Sub cmdExit_Click()
myACApp.Quit
End
End Sub


any help would be great.

thanks a lot

---

Access Query And VBA
I am using the following code to return a sum of all sales (this is actually a Select query with totals). The only this this query returns is the sum. The field is TotalSales. The problem is as follows:
----------------------------------
Compile Error:

Method or Data member not found.
-----------------------------------

The debuger highlights the TotalSales in the next to last line.
----------------------------------------------------------------

Dim qryLoc1Total As QueryDef
Dim strTotLoc1 As String


Set qryLoc1Total = CurrentDb.QueryDefs("qry_SalesTotalCurMonth_TotalsLoc1")

qryLoc1Total("strTotLoc1") = Me.TotalSales

MsgBox strTotLoc1


The gist is I am using a query that returns a sum of sales. That is the only value that is returned (in the TotalSales field).


Thanks again.....

---

Access Query
Hey

Im trying to create a query in Access to show the currency in a field. The field is called Monthly Rental Cost and the currencies are £300, £400 and £500. When I try to define the criteria to one of these amounts it doe'nt work.

any info on how to solve this will be appericated

Thanks

---

Access Query
Code:
DoCmd.RunSQL ("INSERT INTO tblfinalquery ( FIRST, LAST ) " & _
"SELECT tblMain.FIRST, tblMain.LAST FROM tblMain WHERE (((tblMain.pcn) Like '*'))")


Is there some way to place a variable AND or several AND's towards the end of this statement? I have tried several different ways unsuccesfully.

---

Query Access From VB
I have a form with 2 text boxes (Date and shift)and a Command button. I want to make it so when I enter a date in the date box and a shift (1,2,3) in the shift box and click the command button it sets the criteria in a access query.

Is this possibe and if it is, Could anyone please explain to me how to do it. I'm just learning VB so this may take awhile.

Thanks

---

Access Query
I have a text box called 'LearnerID' on a form and i would like to simply add the contents of whatever is inside to a 'Learner' table on a press of a button. Can this be done all in vba and NOT as an append/update query? If so roughly how would it be written? Thanks

---

Access Query
I want to make a program this way: I click a button and the input box comes.. i write the sql select command and it adds a query named "vb" in the file "test.mdb".
How to add a query from vb?? Help me! I use visual basic 6 with service pack 6.. any ideas?? anyone?

---

Access DB Query
How can I run a query through a Access Database? I have the data control on my form, and it's all fine and dandy, but now instead of moving from one to next I want to move straight to the entry where the 'email' field is set to, say, "me@notyou.com".

---

Access Query
this is a query in access:access gives me weird errors....please help

strSql = ((((SELECT a.book as Book, a.bucket as Bucket, a.Book_Attr6 as [Book Attribute], a.trade_type1 as [Trade Type],
a.trade_date as [Trade Date], a.group1 as [Group], a.tnum as TNUM, a.cpty_short as [Counterparty],
a.load_shape as [Load Shape], a.comp1 as [Component], a.buy_sell as [Buy Sell], a.putcall as [Put Call],
a.premium_rt as [Premium Rate],
b.delta_quantity as [Delta Quantity],a.qty as [Quantity], a.prc_strk_addr as [Price Strike Adder],
a.market1 as [Market], a.rec_date as [RecDate],
b.days_to_expiration as [Days to Expiration], b.interest_rate as [Interest Rate], b.option_price as [Option Price],
b.market_value as [Market Value], b.mark_to_market_disc as [MTM Disc], b.mark_to_market_nondisc as [MTM Non Disc],
b.contract_value as [Contract Value], b.fwd_price as [Forward Price],
v.Price as [Palo Verde On Peak Trade Date],
x.Price as [Palo Verde Off Peak Trade Date],
y.Price as [Palo Verde Flat Trade Date]
FROM dbo_u_master_pl a
INNER JOIN dbo_u_MTM_Output b ON a.rec_date = b.rec_date AND a.tnum = b.tnum AND a.block_num = b.block_num AND a.bucket = b.bucket )
LEFT JOIN dbo_vPrices v ON a.bucket = v.MatureDate AND a.trade_date = v.EntryDate AND v.Zainet_Mkt = 'POWER' AND v.Zainet_Comp = 'PL_ON')
LEFT JOIN dbo_vPrices x ON a.bucket = x.MatureDate AND a.trade_date = x.EntryDate AND x.Zainet_Mkt = 'POWER' AND x.Zainet_Comp = 'PL_OF')
LEFT JOIN dbo_vPrices y ON a.bucket = y.MatureDate AND a.trade_date = y.EntryDate AND y.Zainet_Mkt = 'POWER' AND y.Zainet_Comp = 'PL_FL' )
WHERE a.BUCKET BETWEEN (SELECT MIN(bucket) FROM dbo_u_master_pl WHERE rec_date = #" & dtmDate & "# ) AND (SELECT MAX(bucket) FROM dbo_u_master_pl)" & _
AND a.book IN ('SE10','SE06')
AND a.rec_date = #" & dtmDate & "#
ORDER BY a.book, a.bucket, a.trade_date, a.tnum

---

Access Query Help
Guys/Gals,

I am in need of assistance with some Access Queries.

The problem I'm facing is with a query I wrote that queries two other queries and 1 table to total sale amounts for salesmen. The salesmen have three different "concrete" categories for their sales which we call Market Units. Salesmen get commission based on their involvement in the project. This means that one salesman may get a % of another salesman's sale.

The data transmission I receive has the following data on each line:

CustomerName, Item Desc, TotalSaleAmt, OrderNo, Market Segment, SalesmanName1, Salesman1Commission, SalesmanName2, Salesman2Commission, TotalCommission, WeightedSaleAmt1, WeightedSaleAmt2


With the data above, I've been tasked with summarizing the total amount of money each salesman has brought into the company and a breakdown by Market Unit. The Weighted amounts were calculated by taking ((salesman's commission/total commission) * TotalSale Amount). This shows how much of the Total Sale Amount that should be placed in the salesman's totals.

Since Salesman1 and Salesman2 have monies on each row, I created two crosstabs for the salesman that show SalesmanName, Total Dollars, Market Unit.

Example:

SName1 --- Total --- Market1 --- Market2 --- Market3
J Bowen --- 150.00 --- 50.00 --- 100.00
C Adams --- 200.00 --- 0.00 --- 200.00


SName2 --- Total --- Market1 --- Market2 --- Market3
J Bowen --- 50.00 --- 20.00 --- 30.00
D Davis --- 90.00 --- 70.00 --- 20.00


My final query queries a Salesman Table(has all of their names, ids, etc), Crosstab1, and Crosstab2 using outerjoins to the Salesman table.

Query: SELECT RepTable.RepName, Sum(Crosstab1.Market1+Crosstab2.Market1) as Total-Market1, Sum(Crosstab1.Market2+Crosstab2.Market2) as Total-Market2, Sum(Crosstab1.Market3+Crosstab2.Market3) as Total-Market3
(Crosstab1.Total+Crosstab2.Total) As Total_Sales
FROM Crosstab2 Right Join Crosstab1 Right Join RepTable On Crosstab1.Sname1 = RepTable.SName On Crosstab2.Sname2 = RepTable.Sname
GROUP BY RepTable.Sname, Crosstab1.Market1, Crosstab1.Market2, Crosstab1.Market3, Crosstab2.Market1, Crosstab2.Market2, Crosstab2.Market3, Crosstab1.Total, Crosstab2.Total

=========================================
Picture of the join->

[Crosstab1]<----------[RepTable]---------->[Crosstab2]
Sname RepName Sname

=========================================


PROBLEM: Sometimes, there isn't any data for a Market Unit, (Market 3 in our example; usually happens at the first of the year when there aren't sales in some of the markets) and the Jet Engine gives me problems because of it. I tried using the NZ function but I'm still having issues. This query is used by other users and they need the query to take these events into consideration.


Can anyone help?

Thanks in advance...

---

VB/Access SQL Query Help!!!
Can anyone please help. I can't see where I am going wrong.
I have an access database with 2 tables , order and supplier.
Supplierno is a number field and appears in both tables where as, orderno is a text field and date is date/time hboth of which appear only appear in the order table.

I have a form with a command button called find order with 3 fields, orderno, supplierno and date. I also have 2 data objects pointing to the supplier and order table. However when I run the form and type 1 in the order no field I encounter an error message stating " Syntax error in date in expression"
Here is the code for the find button.

I have not included the SQL statement to search for info from the related table ( trying to get one part working first)

Help!!!!!!

Private Sub Command1_Click()
Dim SQL As String
Dim Supplier As String
Dim Orderdate As String
Dim SupplierNo As Integer
Dim OrderNo As String

SQL = "ordate = #" & Orderdate & "# And Orderno = '" & OrderCd & "'"

Data1.Recordset.FindFirst SQL

If Data1.Recordset.NoMatch Then
MsgBox "no records"

Else: SuppplierNo = Data1.Recordset("Supplierno")
End If


End Sub

---

Query Help In MS Access
I have created a query that is filtering out records that are <100 in a certain field.


How can I make it so that I can specify what the filter cutoff point is (instead of 100)?

I want to do this in a form or popup box somehow -- without having to manually open the query design up every time and specify the number.

---

Access Query
hi

i have a report in access, it take data from a query.

but the query will return all the data. i want to query

data between two days. but those days will not be the

same always. so how can i edit the query at runtime, or

can i pass parameters to the query and get the required data

please send me the syntax of executing a query from Vb by

passing parameters

---

How To Use Query In Access?
How to make Query with parameter in Access?
And how to call it by passing parameters in VB code?
for SELECT, INSERT, UPDATE, DELETE queries?

Please give me code example...
THank you.

---

How Do I Set Up This Query In Access?
these are the results of my query:

CeilingMaterials Materials (Direct)TravelFunds
$33,449.81$0.00$0.00$0.00GSA
$4,022.41$0.00$0.00$0.00OM&N
$42,005.25$0.00$0.00$0.00OM&N
$510,294.47$0.00$0.00$0.00OM&N
$21,458.87$0.00$0.00$0.00OPN
$106,680.00$0.00$0.00$0.00RDT&E
$114,978.41$0.00$0.00$318.00RDT&E
$20,500.21$0.00$0.00$0.00RDT&E

now i need a different query to sum up the ceiling amounts for each type of funds

my result should look like:
CeilingMaterials Materials (Direct)TravelFunds
$33,449.81$0.00$0.00$0.00GSA
$546,294.47$0.00$0.00$0.00OM&N
$21,458.87$0.00$0.00$0.00OPN
$242,680.00$0.00$0.00$0.00RDT&E

any ideas?

---

Access / ADO Query
hi all,
i'm stuck to low with a strange issue... the following query works fine in Access and return 1 record :

SELECT *
FROM sysp
WHERE rtrim(sp1)="REG";

whreas the SAME QUERY do not return any records in VB :


Set AdoLogin(2) = dbAccBoot.Execute("select * from sysp where rtrim(sp1)='REG'")


ADODB declaration:
-------------------
Set AdoLogin(2) = New ADODB.Recordset
AdoLogin(2).Open "select * from sysp ", dbAccBoot, adOpenStatic, adLockOptimistic

dbAccBoot declaration:
----------------------
Set dbAccBoot = New ADODB.Connection
dbAccBoot.CursorLocation = adUseServer
dbAccBoot.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & " est.mdb"

any clue please ..?
thanks !

---

MS Access Query Help
With the following data below I am trying to query the schools that have both a Math program and a Science program. I posted something similar a few weeks ago....and someone answered the thread with the following:

SELECT s.School
FROM [Select School,
Program
FROM Table1
WHERE Program In ("Math", "Science") ]. AS s
GROUP BY s.School
HAVING (((Count(*))>1));

I thought it worked but it doesn't. The above SQL selects the following schools: COX, NISK, RCS, UNI. It seems to be selecting schools that have either a Math Program or a Science program.

I need an SQL to select schools that have both a Math program and a Science program which would be: RCS, NISK

Any help would really be appreciated.

SchoolProgramLengthType
RCSMath10Each
RCSScience10Each
COXScience12Each
RCSHistory12Each
NISKMath10Each
NISKScience12Each
NISKAlgebra14Each
COXHistory14One
CATSHistory10One
RCSScience11One
COXScience10One
UNIMath12Each
UNIHistory12One
UNIMath14One

---

MS Access Query
I have the following fields and data in tblProgram:

This is the way the data is imported from a text file.
I am having trouble manipulating this data in a query to get the results I want. See below.

School Program Length

RCS Science 10
Lishakill Math 12
Catskill Science 8
Catskill Math 10
RCS History 12
RCS Math 8

I am looking to answer the following question:

What schools have both a Math program and a Science program? Answer: Catskill

What would the SQL be? I can set it up using Math OR Science but not using AND.
Thank You.

---

Access Query Sql To VB Sql
Hi everyone

Im struggling to transfer from access to VB
is there a tool that can take the sql from an access query
and return sql VB ready

Cheers Ston

---

Access Query
Hi Im very very new to this,

I have been using access for a while and have decided in my wisdom to try and put a VB frontend to a database I have been using.

I have read loads of books but dont seem to be looking in the right place
so any help would be good.

I want to connect to the database and run the querys that I have already done in access, I dont need to see the results just run them.

Thanks in Advance

Ston

---

Access Query
hi, i need to run a complicated query in access. the database was made in access etc. the query needs to select records from 1 table but based on the data in another table (but in a diff way to last time i asked)

basicly, i need all the records from table 1 where NO records in table 2 are linked to the record in table 1. if any of the records in table 2 are linked to the record in table 1 then that record IS NOT shown.

table 1 has "itemID" (primary key) and table 2 has "Item" these are the fields that are linked.

can anyone help please, ive tried everything i can think of and an hour later still no luck

---

Access SQL Query
I have a query that looks similar to the below:

ID Activity Date
01 Counselling visit 11/11/2003
01 Counselling visit 27/01/2004
02 Training Seminar 03/04/2004
02 Training Seminar 27/10/2004
02 Counselling visit 12/12/2004
02 Networking Event 03/01/2005
03 Counselling visit 07/06/2002
04 Training Seminar 03/04/2004
04 Networking Event 17/09/2004

Where ID is the ID of a person (linked to a particular company).

As you can see a person can complete more than 1 Activity.

What I would like to do is for each ID, only display the first activity and the last activity. The result of this would be a list but there would be a maximum of 2 rows for each ID.

Any ideas??

---

Access Query From VB
I don't know if the title is the best one, but my problem is that I have a query in MS Access. The query works fine, so I want put the results from these Access Query to a VB's grid.

the query name in Access is: qry_test

so I make this to connect the VB to the query:

str_sql = "SELECT * FROM qry_test"
db_rs.open(str_sql, db_conn, adOpenStatic, adLockOptimistic)

db_conn is the DB connection. The last sentence don't generate error, but returns 0 records (db_rs.RecordCount) but when I paste the same sql sentence (str_sql) to access it gives me the correct number of records.

What am I doing wrong?

---

SQL Query In Access
I'm doing an overhaul on a co-workers Access database and I need to take her pre-built query and turn it in to my own SQL query. Building the "SELECT FROM" is a piece of cake, but how do I select the table? I'm used to setting the connection via VB 6.0 and have no idea how to reference a table in the Access database I'm already in. Any help is greatly appreciated!

---

VB6 And Access Query
Is it possible to execute a query defined in an MDB, from VB code?

---

How To Run Query In Access In Vb6 ?
Is it like this ?......I get an error when i try to run.....

Set querQue = pdbQue.OpenRecordset("Quest Query", "there should be something here")

My question is : 1) What should be in "there should be something here" ?
2) Is it ok to have two recordsets ? meaning like this....


Code:
Set pdbQue = OpenDatabase(App.Path & "Test Editor.mdb")
Set mrstQue = pdbQue.OpenRecordset("SELECT * FROM Quest ORDER BY QuestionId")
Set querQue = pdbQue.OpenRecordset("Quest Query")

---

Access Query Using 'In()'
I have a form with a textbox. Into the textbox, I load a comma-delimited list of id numbers.
I've also got a query based on the table containing the id numbers. If I set the criteria for the id field as
Code:
SELECT ST_AccountsSub.SAccountID
FROM ST_AccountsSub
WHERE ST_AccountsSub.SAccountID In ([Forms]![frmSplitAmounts]![txtSelection])
I get nothing back unless only 1 id is in the textbox.

If I type the list of id's into the query criteria - "In(1,2,3)" - it functions as intended. Any ideas out there?

---

VB Access Query
Is there any way i can create a query from vb and make a new query in access.
The reason why i want to to this is because i have distrubuted an application that uses access as its database and i now need to alter a database query or delete IT and create a new one.

Is there any way i can achieve this?

Dave

---

MS CRM Query Through MS Access
Hey everyone:

I usually write into this forum in regards to VB, but lately I've been doing a lot of reporting through Access and I'm at an impass.

BAckground:

We recently released MS CRM for our sales team and I gotta say, it's a great System, but reporting on it has been difficult. Since our company uses Active reports we went and hosed all of the crystal reporting features and we run our own queries through the back end (in Access). While we are still customizing the CRM Application, I have a ton of reports that ar quite dicey.

This is the story of one of them.

We have telemarketers that make phone calls to Hot Accounts (we have yet to implement the "Leads" module, but that a story for another time). These phone calls go into a db table called dbo_ActivityBase. I'm joining on GUIDs from the main Accounts and Opportunities Tables (dbo_AccountBase and dbo_OpportunityBase) to produce a query table that shows how their Phone Call Activity results in "Qualifying" these Accounts (we use the CRM "Picklist" -- a drop-down control--CustomerTypeCode to track the Sales Progress at the Account Level).

Based on this field, I return Account records that have "Qualified Lead" in this Picklist and the Phone Calls linked to that Account.

My problem is that the query returns a record for every Phone call made, even if it is for the same Account, thus, the Account appears 2,3, maybe even 4 times (because it took them 4 phone calls to Change CustomerTypeCode to "Qualified Lead").

For each of these records I have essential data that gets summed in the report. when the Same Account is returned for multiple Phone Call Activities, this summed is erroneously inflated.

So, what I've been trying to do (and this has worked for other queries with similar criteria) is to construct a Select Query that will only show the Account Record and it's corresponding Phone Call with the latest "Close Date" (in CRM, this data field is called "ActualEnd"). This Date field is handy because the Phone call with the latest "ActualEnd" is the Date on which the Telemarketer changed the Value in CustomerTypeCode to "Qualified Lead" (the integer Value for this picklist item is "13")

So I tried using the Sigma(?) button to get "Total" criteria to work for me (MAX for the Date Values I want to weed out of the mix), but it doesn't work.

I suppose I can give you the SQL if it helps any:


Code:
SELECT dbo_AccountBase.Name AS Account, dbo_CustomerAddressBase.StateOrProvince AS State, dbo_AccountBase.TerritoryId, [dbo_SystemUserBase.FirstName] & " " & [dbo_SystemUserBase.LastName] AS Telemarketer, [dbo_ContactBase.FirstName] & " " & [dbo_ContactBase.LastName] AS Contact, dbo_ContactBase.JobTitle, Max(Format([dbo_ActivityBase.ScheduledStart],"m-d-yy")) AS [Due Date], Max(Format([dbo_ActivityBase.ActualEnd],"m-d-yy")) AS Closed, dbo_AccountBase.CFPCurrent_Provider, dbo_AccountBase.CFSLastResults AS [Last Results], dbo_ActivityBase.Subject, dbo_AccountBase.CFIADC_TOTAL AS [ADC Total]
FROM ((((dbo_ActivityBase INNER JOIN dbo_SystemUserBase ON dbo_ActivityBase.OwningUser = dbo_SystemUserBase.SystemUserId) INNER JOIN dbo_ActivityPartyBase ON dbo_ActivityBase.ActivityId = dbo_ActivityPartyBase.ActivityId) INNER JOIN dbo_ContactBase ON dbo_ActivityPartyBase.PartyId = dbo_ContactBase.ContactId) LEFT JOIN dbo_AccountBase ON dbo_ActivityBase.ObjectId = dbo_AccountBase.AccountId) LEFT JOIN dbo_CustomerAddressBase ON dbo_AccountBase.AccountId = dbo_CustomerAddressBase.ParentId
GROUP BY dbo_AccountBase.Name, dbo_CustomerAddressBase.StateOrProvince, dbo_AccountBase.TerritoryId, [dbo_SystemUserBase.FirstName] & " " & [dbo_SystemUserBase.LastName], [dbo_ContactBase.FirstName] & " " & [dbo_ContactBase.LastName], dbo_ContactBase.JobTitle, dbo_AccountBase.CFPCurrent_Provider, dbo_AccountBase.CFSLastResults, dbo_ActivityBase.Subject, dbo_AccountBase.CFIADC_TOTAL, dbo_AccountBase.CustomerTypeCode, dbo_ActivityBase.ActivityTypeCode, dbo_SystemUserBase.LastName
HAVING (((dbo_CustomerAddressBase.StateOrProvince) Is Not Null) AND ((dbo_AccountBase.CustomerTypeCode)=13) AND ((dbo_ActivityBase.ActivityTypeCode)=137) AND ((dbo_SystemUserBase.LastName)="Smith" Or (dbo_SystemUserBase.LastName)="Yeretzian") AND ((Max(dbo_ActivityBase.ActualEnd)) Between Now()-20 And Now()-1))
ORDER BY Max(dbo_ActivityBase.ActualEnd) DESC;
This method has worked on other queries, and I don't understand why this doesn't work now...

Anyone have any Ideas?

Let me know if more info is needed here...

--Dodd

---

Access: Some Help With A Query?
I have a query that I want to base a report on.

I am generating this SQL statement in the detail view of the Access query generator.
I need to pull records based from one table based on a range of dates from another table. I got that part working with the SQL statement:

Between [datetable].[firstdate] And [datetable].[seconddate]

Next I need to display only the oldest record per machine task completion date.

Next I need to calculate the average based on 1's and 0's stored in a field called [ontime] in the table. The calculation of the average has to be for all records that fall in the date range that I mentioned before.

So the field [ontime] will not be displayed but the average of all of the records between those dates will be.

When I limit the records to just display the oldest then it also just calcs the avg of the oldest record. This is brain racking. I am not familiar of a way to calc the sum then use that value for the avg field that is loaded.

And I think that's about all.

The problem is that i can get each of these to work seperately but not all in the same SQL statement. Since all three of these need to be on the same report, I do not know how to call several SQL statements into one report?

So if anyone has any ideas as how to syntax this problem, I would greatly appreciate it.

Thanks,
Richard

---

Need Help With Access Query
I have a query that computes the finish order of a race based on cumulative times, but the finsh orders are coming out incorrectly depending on the data set. Here's the query:


Code:
SELECT (Select Count(*) from qryStandings Where qryStandings.FinishTime < rst1.FinishTime And qryStandings.Class = 'Bear' And qryStandings.Round = 1)+1 AS FinishPlace,
Format(qryStandings.FinishTime,'0.0000') AS FinishTime
FROM qryStandings
INNER JOIN qryStandings AS rst1 ON (qryStandings.Round = rst1.Round)
AND qryStandings.Class = rst1.Class
AND qryStandings.CarNumber = rst1.CarNumber
WHERE qryStandings.Class='Bear' AND qryStandings.Round=1
ORDER BY qryStandings.FinishTime;
This is the result:
Place Time
1 15.7657
2 15.8917
3 15.9953
5 16.1179
5 16.2359
6 16.3215

If I change the part of the subquery that is comparing the times to this:
Format(qryStandings.FinishTime, '0.0000') < Format(rst1.FinishTime, '0.0000') then it will work with that data set, however with another data set, I then get results like this:

Place Time
18 9.9501
2 10.0130
3 10.1110
.
.
17 13.0601

How can I compare these times and get this to come out correctly?

Thanks,

Randy

---

Access Query
I have an app that runs an access query every seven seconds. It seems to be drawing quite a bit of processing power from my workstation. Does anyone have any suggestions for ways to minimize the amount of overhead used by this query? It is query a table with anywhere from 1000 - 10000 records...

Any help would be much appreciated!

MJ

---