Visual Basic :: Query For MS-Access: SQL Differences In Access Query Window Vs. Using ADO In Code

SQL Select Works With ADO But Not In Access Query Window
I have an sql select that works fine when I run it with ado from my VB6 program. However, when I try to run the same select statement in the Access DB that is called by the ADO in the VB6 program, I get a circular reference error.

Does that make any sense?

Here is the sql statement:

SELECT Format(SFTimingFrom,'MM/DD/YY') & ' - ' & Format(SFTimingTo,'MM/DD/YY') as SFTimingFrom ,Right(SFTxt,(Len(SFTxt)-LenFileExtName)) as SFTxt,SFImpression ,SFElement, SFDetail FROM SponsorCatAndFileInName WHERE SFTxt <>'' AND CatID = 22 AND NameID = 13 AND SponsorID = 56 ORDER BY SFTxt ASC

Here is the ADO that calls it and works just fine:

Set myRS = New adodb.Recordset
Set myRS.ActiveConnection = adoconn
If (pagesize > 0) Then
myRS.pagesize = pagesize
End If
myRS.CursorLocation = adUseClient
myRS.Open sqlTxt

Here is the error I get when I run it in the Access query window:

"Circular reference caused by alias 'SFTimingFrom' in query definitions select list"

Access VBA Code In SQL Query
Is it possible to open a query from MS Access using ADO where the query contains vba code from the Access macro?

Code:
SELECT Null AS PanelDetailID, Null AS PanelID, "" AS PanelNo,
tblComponents.Width, tblComponents.MinLength,
tblComponents.MaxLength, "N" AS Required, tblComponents.Value, 9999 AS
ProductionOrder, ZoneByLength(tblComponents.TypeID,0) AS [Zone],
"" AS PrintString, IIf(tblComponents.FingerJoint=True,"Y","N") AS FingerJoint
, IIf(tblComponents.Remnant=True,"Y","N") AS Remnant
ZoneByLength is a function within the VBA code.

Create New SQL Code For A Query In Access?
I am generating a report based on a query. The query prompts the user for a product type. Here is the current sql for that query:

Code:
SELECT Contacts.[Company Name], Contacts.[First Name], Contacts.[Last Name], Contacts.[Phone Number],
Contacts.Extension, Contacts.FAX, Contacts.[Product Type], Parts_For_RFQ.[Part Number],
Parts_For_RFQ.[Ann Vol], Parts_For_RFQ.[Model Year], Parts_For_RFQ.Platform, Parts_For_RFQ.Quote,
Parts_For_RFQ.Misc, Parts_For_RFQ.[Due Date], [First Name] & " " & [Last Name] AS WholeName
FROM Contacts, Parts_For_RFQ
WHERE (((Contacts.[Product Type]) Like [Enter Product Type for Filter (leave blank for entire preferred list)]) AND ((Contacts.Preferred)=True))
ORDER BY Contacts.[Company Name];
Note the section where is has the prompt:

Code:
WHERE (((Contacts.[Product Type]) Like [Enter Product Type for Filter (leave blank for entire preferred list)]
Now, in a userform, I would like the ability to select the product type and have that query use the product type from my combobox. How can I (through code) modify the query to reflect the new SQL? Something like:

Code:
SELECT Contacts.[Company Name], Contacts.[First Name], Contacts.[Last Name], Contacts.[Phone Number],
Contacts.Extension, Contacts.FAX, Contacts.[Product Type], Parts_For_RFQ.[Part Number],
Parts_For_RFQ.[Ann Vol], Parts_For_RFQ.[Model Year], Parts_For_RFQ.Platform, Parts_For_RFQ.Quote,
Parts_For_RFQ.Misc, Parts_For_RFQ.[Due Date], [First Name] & " " & [Last Name] AS WholeName
FROM Contacts, Parts_For_RFQ
WHERE (((Contacts.[Product Type]) Like "Clamps") AND ((Contacts.Preferred)=True))
ORDER BY Contacts.[Company Name];

Can't Call Access Query W. VBA Code From VB?
I have an Access 2K query that call a VBA function stored in a module in Access. The query works fine when I execute it within Access. When I try to execute it from a VB program, I get an 'Undefined function FunctionName in expression'. How can I make FunctionName visible to my VB program?

Editing A Query In Access By VB6 Code
in order to get word report from access in my vb6 program, i create a template query in access's queries tab. vb6 code uses this template query. and i distributed this access file lots of users.
problem is here: i have to add some fields into this query. how can i edit this template query in access database by using vb6? (i must do this process silently. users musn't be known about what changes is being made)

Add A Field To Query Via Code (vb6 && Access)
Hello
I need to make a recovery program to add a field to a query in users databasae.
This query made from 2 tabels.
Can I do it with code?
for example a madule that check query for this field and if there wasn't , add it to query automaticaly?
Thanks

Vb 6.0 MS Access Query Code For Saving
how can i write a code for saving the contents of Ms Acess query that involves multiple table database?

please assist.

Thanks

Dunco

Editing Access Query Using Vb Code Module Script
I'm developing a little access program thats uses a form to input a date range, which I then need to use to query and export a table. I have the form made and I have the export portion finished. All I need to figure out is whether I can edit an existing query's criteria using vba. If not, is it possible to create a new query that will persist after the code has been run or replace an existing query with one incorporating the new date range. Any help would be greatly appreciated.

Thanks,
Chris

How Can I Make My VB Code Run An Access Query When VB Opens The File
Hi
I'm trying to make a query i have in Access run when I open the file through VB. The query will run if I open the Access file using Access but when my VB code opens the file it won't run. Anyone know how I can force the query to run? Thanks

Query Data From Access By Writing A Code In Excel
Here is what I am doing - create a code in Excel to bring data from Access Database in the same folder. Two problems on the following code: 1) when I used double quote for DatePart , the quoted part (like "yyyy") is highlighted as compile error. 2) it also shows the problem in the line of " .Open, , 3, 3 "

I got this code from a friend and just make minor change and expect it to work propertly. It turned out i met these two problems. Please give me a hint how to fix it. Many thanks.

Code:

Sub Import2()

Dim cn As Object, rs As Object, myCallYear As String, myCallMonth As String
Dim MySql As String, dbfullname As String, myCnt As Long

dbfullname = "C:Documents and SettingsxyzDesktopFFR.mdb"
myCallYear = Sheets("Reference").Range("E13").Value 'Pass Year
myCallMonth = Sheets("Reference").Range("E14").Value 'Pass Month

MySql = "SELECT DatePart(''yyyy'',[TransactionTime]),DatePart('m',[TransactionTime]),[Category],[Subcategory],Sum([DollarSpend]),[TransactionTime]" & _
    "FROM tblExpense GROUP BY DatePart('yyyy',[TransactionTime]),DatePart('m',[TransactionTime]),Category,Subcategory" & _
    "Having DatePart("yyyy",[TransactionTime]) ='" & myCallYear & "'" & "AND DatePart("m",[TransactionTime]) ='" & myCallMonth & "'Order By DatePart("yyyy",[TransactionTime]), DatePart("m",[TransactionTime]);"

myCallYear = Empty
myCallMonth = Empty

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
       & dbfullname & ";" 'Create DB connection

Set rs = CreateObject("ADODB.Recordset")
With rs
    Set .ActiveConnection = cn
    .Source = MySql 'Pass your SQL
    .Open , , 3, 3 '.Open , , adOpenStatic, adLockOptimistic
    myCnt = .RecordCount
    If myCnt > 0 Then
        .MoveLast: .MoveFirst
         'Pull data to first sheet, cells a1:RecordestCountRow & column 3 _
            3 fields in the sql pass
        Sheets("Data").Range(Cells(2, 14), Cells(myCnt + 1, 21)).CopyFromRecordset rs
    End If
    .Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing

End Sub

Update Table In M.Access,code Field,query
Hi
I have made an application to connect Microsoft Access with Visual Basic 6 to save some records!!!! My table in Microsoft Access has these fields:

code ->>>>>>>>>integer
Name ->>>>>>>>>>char
LastName ->>>>>>>>>>>>>char
Age ->>>>>>>>>>>>>>>>integer

Now i save the records correct to my table!!!!In code field count my records!!!
for examle: 1 Jim Dean 29
2 Leite Koontz 40
3 Melina Barker 20
4 Elina Smith 50

Now when i delete a record for example 3 row( Melina Barker) and save new record in code field will take number 5 but in table i have 4 records saved!!! How i can correct that????? With query??? But how??? Any code ideas????

(SOLVED)how Make A Query Totally From VB Code For Ms Access Table?
Hi all,please help me to understand this..
I have two tables (table_connection and table_operator)
with following field :
table_connection
1. ID_connection
2. Date_connection
3. Cost
4. ID_oprator
Table operator
1. ID_operator
2. Name
i want to form the query to show on my datareport :
each record is ID_operator, name, sum of cost where Date_connection is between Calendar1.value and calendar2.value (group by ID_operator,group by name,but not group by Date_connection)

if tried to build the query from msaccess side,
but i cannot avoid to have group by date_connection if i use sum for field cost.
i connect to that query this way
Code:
With rsQcost
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open "Query_cost", conAVB, , , adCmdTable
End With

but now i want to do all (including querying) in vb code
idont know how to do that
i only have this code in mind
Code:
Select ID_operator, table_operator.name, sum of cost from table_connectio, table_operator
where Date_connection >= Calendar1.value and Date_connection <= calendar2.value
group by ID_operator

please correct me
thanks

Edited by - kjoephi on 8/8/2004 12:14:33 AM

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

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

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

Access 97 DB Query Works In Access 2000 But Not In Access 97
I have a MS Access 97 DB that worked but when I came to my new company the DB has problems with a particular query on my new PC. The new PC(Win98) has office 97, therefore Access 97. When I try the same DB on a co-workers PC (also Win98) which has Office 2000 installed I do not encounter this problem. The following is the error message that I get: Function isn't available in expressions in query expression '||f(IsNull([Close Date]),Date(),[Close Date])'

Does anyone know what the problem is?

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

From In Access Make Query To The Access Tables
i build a form in access
and i want when i press on a button there i will make a query on a table called tbl1 .
what code do i need to use to get the data from tha tables?
(i know how to write query but no how to make a query oon the access tables,how to connect open recordset and so on)

thnaks in advance
peleg

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

How Can You Access An Access Query From A VB6 Program
Hi
I was wondering if anyone knows how I can access an Access query from a VB6 program. In Acccess you would use a DCmd like DoCmd.OpenQuery " qryFindTotalaAmt". But naturally you cannot do this in VB, So I was woundering if there is a way to do this in VB.
Thank You

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