Visual Basic :: Excel(2000) To Query && Extract From Access(97)

Access 2000 MIN Query
I am trying to write a query that will provide me with the date and time a trouble ticket was first contacted in the system and I cannot figure out how to do it. I have 2 tables. A service call table which has a service call #, status and a date opened and time open fields. I have a details table which tells me a service call number, a date worked on and a start time it was worked on. The following query will display all service calls and the time it was worked on. If a service call was worked on 3 different times it will return all three of those time detail records. I want it to only show the VERY FIRST time detail. How would I go about it?

Code:
SELECT dbo_SCCalls.CallNumber, dbo_SCCalls.Date, dbo_SCCalls.Status, dbo_TimeHistory.T_DateWorked, dbo_TimeHistory.T_StartTime
FROM dbo_SCCalls INNER JOIN dbo_TimeHistory ON dbo_SCCalls.CallNumber = dbo_TimeHistory.T_SCNumber
WHERE (((dbo_SCCalls.Status)="I"));</

SQL Query - Access 2000
Hi

I need to use "IF" in an Access 2000 SQL Query. Is that possible?
I want to do something like this:

SELECT IF Customers.Type=0 THEN Customers.CustomerId1 ELSE CustomerId2 AS CustomerId FROM Customers WHERE ...

Is that possible?

Thank you

Access 2000 Query
Hi

how can i execute an Access 2000 quiery in VB6?

Thanx

~Mario Veyna
"In another 100 years will be machines or gods"

Sql Query In Vb, Db = Access 2000
Im trying to query a table in MS Access 2000, using ado my query looks
like this

strRecordset = "SELECT TOP " & txtnum90Under.Text & " *" & _
" FROM SerindexMaster INNER JOIN (OPCODES INNER JOIN CSIMaster on ((OPCODES.OPCODE <> CSIMaster.OPCODE1 " & _
"OR OPCODES.OPDATE <= ((NOW() + 28 - csiMaster.RODATE) / 30))) " & _
"AND ((OPCODES.OPCODE<> CSIMaster.OPCODE2 " & _
"OR OPCODES.OPDATE <= ((NOW() + 28 - csiMaster.RODATE) / 30))) " & _
"AND ((OPCODES.OPCODE<> CSIMaster.OPCODE3 " & _
"OR OPCODES.OPDATE <= ((NOW() + 28 - csiMaster.RODATE) / 30))) " & _
"AND ((OPCODES.OPCODE<> CSIMaster.OPCODE4 " & _
"OR OPCODES.OPDATE <= ((NOW() + 28 - csiMaster.RODATE) / 30))) " & _
"AND ((OPCODES.OPCODE<> CSIMaster.OPCODE5 " & _
"OR OPCODES.OPDATE <= ((NOW() + 28 - csiMaster.RODATE) / 30))) " & _
"AND ((OPCODES.OPCODE<> CSIMaster.OPCODE6 " & _
"OR OPCODES.OPDATE <= ((NOW() + 28 - csiMaster.RODATE) / 30))) " & _
"AND ((OPCODES.OPCODE<> CSIMaster.OPCODE7 " & _
"OR OPCODES.OPDATE <= ((NOW() + 28 - csiMaster.RODATE) / 30))) " & _
"AND ((OPCODES.OPCODE<> CSIMaster.OPCODE8 " & _
"OR OPCODES.OPDATE <= ((NOW() + 28 - csiMaster.RODATE) / 30))) " & _
"AND ((OPCODES.OPCODE<> CSIMaster.OPCODE9 " & _
"OR OPCODES.OPDATE <= ((NOW() + 28 - csiMaster.RODATE) / 30))))" & _
"on SerindexMaster.[Serindex1_SERIAL#] = CSIMaster.[SERIAL#] " & _
"where " & carSearch & " AND serindexmaster.LASTRODATE BETWEEN #" & _
Startdate & "#" & " and " & " #" & endDate & "#"

carsearch being the year of the car
i want to be between start date and end date

i am look in 9 different fields to find a specific opcode, the problem i am
running into is that if any of the opcodes 1 through 9 have no opcode value
it automatically eliminates that record because you cannot compare null values.

i guess my question is:
How do you compare null values or not count them against the query???
or
if you now of a better way to look at a number of fields for comparison

thanks

dj

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

Query Question Access 2000
Hi all,

I have a query with the following sql code.

SELECT DISTINCTROW Sum([TblFakBedrag].[Totaalbedrag]) AS [Sum Of Totaalbedrag]
FROM TblFakBedrag;

Does anyone know how I can display the query result in a textbox on a form?

Regards,

FreakyJaBa

Access 2000 Run Query For Nothing In The Field?
Is it possible to run a query and bring up all the records that have nothing in that field. I tried using this: = "" but it still doesnt bring up those records that have that field blank.

Thanks Nick

Access 2000 VB Query Headings?
I have a form and program built inside Access and when someone clicks a button it runs a query. How can I create a heading for that query so when they print it out it will show a title on the heading and maybe pull some data from the form for the heading. Cant figure out how to get a heading when a query is run.

thanks Nick

Search Query In Access 2000
How would I setup a simple search through a query? What would I put in the "CRITERIA" box under a particular field in order for a user to be promted to enter search criteria? Please help me. I am working in Access 2000.

Access 2000 Query Question
I have the following table structure from a client.

ID - DataType Number
FieldA - DataType YES/NO
FieldB - DataType YES/NO
Fieldc - DataType YES/NO
ClientName - DataType Text

The client needs a count of FieldsA, FieldB, and FieldC whose value = "YES" in the field and whose ClientName = Anonymous.

They also want the same criteria as above where ClientName <> Anonymous.

I have tried getting the results in Query design, but I keep getting the wrong results. Any help is appreciated.

Executing An Access 2000 Query
Okay, here is my question - I have an Access 2000 database and I have a saved query that inserts a new row into a certain table.

Can I access this query in VB5 using ADO, or do I have to use some sort of VB5 command that generates a query through VB?

Creating A Query In Access 2000 Using ADO
I would like to be able to create a query in an access 2000 database using ado.

Something like

Code:
strSQL = "CREATE PROCEDURE qryGetFoo Select * from tblBar where ID = [@ID];"

dim cnn as adodb.connection
cnn.connectionstring = cstAccess2kCNN 'jet 4.0 connectionstring
cnn.open
cnn.execute strSQL
cnn.close

But that doesnt seem to work.. any ideas?

I could create a "Shell" query in access that takes as the parameter the string of the query to build (and its name) but I don't know how to do that.. if anyone does could they please tell me?

something like if it existed.. I could then call that query to build other queries

[vb]
COMMANDTOCREATEQUERY [@ParamNewQueryname] [@ParamNewQueryText];
[vb]

I just really dont know how to do it.

Also I would like to know how to delete queries in an access database via ado if thats possible.

I always seem to get the strangest projects....

Access 2000 - Parameter Query
I have the following query

SELECT ProjectName, Val*1.45
FROM Project;

what i need however is to be able to pass a parameter to the view from vba code so that the query looks like the following:

SELECT ProjectName, Val*[myVariable]
FROM Project;

*where myVariable is a variable value in my vba code

Any help much appreciated. Thanks

Drop Query In Ms Access 97/2000
HI PEOPLE! I HAVE TO BUILD SOME SCRIPTS TO UPDATE SOME DATABASES (MS ACCESS 97 AND 2000) AND I WONDER HOW TO DROP QUERIES THROUGH CODE.
I KNOW I CAN DROP TABLES AND INDEX BUT I DONT KNOW HOW TO DROP STORED QUERIES AND I COULDNT FIND DDL TUTORIAL.
THANKS IN ADVANCED! AND SORRY MY ENGLISH.

Access 2000 Query Problem
Hello everyone;

Please if anyone can assist me of how can I run a Query from a Form by clicking on a command button in access 2000?

Thanks

SQL Database Query (Access 2000)
Hi Peeps,

I have a sales order header table and a sales order details table. I have two ado controls one showing the header information and the other showing the details relating to the header link. In the details ado control is a status field and I want to show all header with details of a specific status.

HEADER        DETAILS        STATUS
1        1        1
        2        1
        3        2
        4        3

The problem I am having is getting to show only one header record. I have used DISTINCT in the SQL but it seems to do it on the STATUS in the details table giving header 1 in this example three times becasue there is three different STATUS values. Here is my true SQL statement:-

SELECT DISTINCT SOHDR.*, SODTL.STATUS FROM SOHDR INNER JOIN SODTL ON SOHDR.REFNO = SODTL.SOHDRREFNO

Can someone please help, I only want to see one instance of the header records.

Cheers very much,

Jiggy!

Query Doesn't Like LIKE In Access 2000 ...
Hi all...

This one is DRIVING ME NUTS!!!!

This code:

Code:

Private Sub Command43_Click()
Dim FormsConnect As ADODB.Connection
Dim TrgRecSet As ADODB.Recordset

Set FormsConnect = New ADODB.Connection
Set TrgRecSet = New ADODB.Recordset

FormsConnect.CursorLocation = adUseClient
FormsConnect.Open "DSN=Billing System;"

TrgRecSet.CursorType = adOpenDynamic
TrgRecSet.LockType = adLockOptimistic
TrgRecSet.CursorLocation = adUseClient

SQLString = "SELECT [Project Title] FROM [Work Orders II] WHERE (([Work Orders II].[Project Title]) Like '*t*');"
TrgRecSet.Open SQLString, FormsConnect
If TrgRecSet.BOF = False Then
TrgRecSet.MoveFirst
MsgBox "found a record"
Else
MsgBox "Bomb!"
End If
TrgRecSet.Close
FormsConnect.Close
Set FormsConnect = Nothing
Set TrgRecSet = Nothing

End Sub

is operational. It fails tho ... There is no returned records. BOF returns True. I take the EXACT query part and throw it into the Access QBE and run it .. Presto! I have records ... Any thoughts on what I'm missing?

BTW, this is a Dynamic, LockOptimistic, and ClientSide recordset ...

Thanks!

- Mike

Select Query For Access 2000
Hi All,
I m using access 2000 and having a table in which there are some fields of data type double with rounded off upto 2 decimal points. But actully I want to round them upto 0 decimal point to show the client. But the select query for this doesn't work. this is my query

Select Round(Salary,0) from Employee

where datatype of salary is double.

when i run this query through query builder in access it gives correct result but when I run this query through my vb program it gives me following error:
"Undefined function Round"

this error comes whenever I use eithere built in function or user defined funtion

can anybody help me?

Access 2000 DB Query Search Textbox
I havnt programmed much in access itself but I have created a query in access and also have created a form. On the form I setup a text box with a command button. I want the command button to run my query searching the date that is put into the textbox. Any little bit of sample code for this would help.

Thanks

How Can I Create A Query To Access 2000 DB Thro VB?
Dear experts,

My aim is to run the DDL statemnets for Access 2000 thro VB.
That is I try to code something as follows: (I use ADO connection to the Database)

connectionvariabl.execute ("create table tableName ....")

Its working fine and the table is created.

Likewse I try to create a query as follows;

connectionvariabl.execute ("create view viewName as ....")

when executing , it says "invalid sql syntax".

I try with ADOX.Catalog object to acheive this.

I use something like

'msgbox connx.Views.Count'.

Its displays the no of select queries in my database.

Then i tried msgbox connx.Procedures.Count

It shows the no of action queris in the db.

But I am unable to create a query in my db.

You please tell me any method to create query in Access 2000 db thro VB

Thanks in advance
Latha

Access 2000 Update Query Not Working???
Hi,

I have a small problem when trying to run a update query in Access 2000 through VB.

Here is how it should work, I use the following vb code and capture a parameter value 'myval' as a string and then pass that value 'myval' to the parameter in a access stored proceedure.

Code:
Dim cnt As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param As New ADODB.Parameter
Dim myval As String

' Open connection
Set cnt = New ADODB.Connection
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & config.database_location & ";Persist Security Info=False"
cnt.CursorLocation = adUseClient

' Open command with one parameter
Set cmd = New ADODB.Command
cmd.CommandText = "Sys_Frm_modify_Change_Code"
cmd.CommandType = adCmdStoredProc

' Get Parameter Value and append it
myval = "xxxxx"

Set param = cmd.CreateParameter("NEW_FORMULA_CODE", adChar, adParamInput, "255", myval)
cmd.Parameters.Append param
param.Value = frmmodify.selectedrec

' Create recordset
Set cmd.ActiveConnection = cnt
Set rs = cmd.Execute

cnt.Close
The 'myval' paramater is then passed to the parameter in the Access 2000 stored proceedure 'NEW_FORMULA_CODE'.

The Access stored proceedure is as follows (which is a update query).

Code:
PARAMETERS [NEW_FORMULA_CODE] Text ( 255 );
UPDATE TB_Temp SET TB_Temp.Formula_Reference = [NEW_FORMULA_CODE];
By adding a couple of watches in the VB code I know that value in 'myval' is being passed correctly to the ADODB.Parameter, so the problem must be in the Access 2000 area. I have had no problems running any other stored proceedures (i.e Create, Drop, Select), it seems to be only when I try to run update proceedures.

To make things more difficult, I have not been getting any error messages, it is just not working (could it be waiting for a prompt?), I know the stored proceedure does work in Access.

Does Anyone ideas???

Thanks

Slim Steve

Running Sum Query..sort Of... (MS Access 2000)
have a table, Loans.

Fields:
ID (key)
Loan Number
Principal Balance.

I have another table, Transaction, that's based off the Loan Number field on the loans table.

Fields:
LoanNumber
Payment Type (2 choices, payment and advance)
Amount

I have a user form where users can specify the payment type, and amount, based on a certain loan number.

I'm attempting to do this:

User enters a transaction into the database. once the transaction is entered, the Principal Balance field for the specified loan number is updated.

If the payment type is advance, the query will subtract the amount from principalbalnce. if payment, the query wll add the amount to the principal balance.

Here's what I have so far.

The userform updates the transactions table with the information correctly.

My next step is to update the principal balance.

I'm thinking I could use some sort of update query, but I don't know where to begin.

HELP!!

If there is a better (normalized) way to accomplish this, I'm definitely open to ideas. Please just provide a basic example so I can understand.

Access 2000: Looping Through Records In A Query
Hello All~, First Time Poster Long Time Lurker
I know this may be a simple process to all, but I’m fairly new using Access. I’m an ok programmer and also good at SQL and databases, except Access. I’ve also stayed away from Access and mostly worked with MS SQL Server, Oracle, and MySQL. But got a project using Access 2000. Well guess I need to get used to Access soon or later. Ok, I’m trying to loop through the records in a query. I have searched the forum for help, but couldn’t find exactly what I needed, not using ADO anyway. If this has already been addressed somewhere else, please excuse my redundancy and point me in the right direction. I can create the query and can pull the results with no problem, but I can’t get past the first record. I’m sure there is some type of array to set up with the recordset, but not sure how to do it. Any Help would be appreciated.

*Also I will eventually try to send this to a report and a word doc file, some any pre-help on that would be appreciated too!

Here is what I have so far:
*On yea you may notice the database is written in Italian!

Code:
Private Sub SelectQuery(Query As String)

Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset

Dim cnt As Integer
Dim i As Integer

Dim recID As String
Dim recName As String
Dim recShift As String
Dim recEfficiency As String
Dim recDate As String

Set cnn = CurrentProject.Connection
'Query = "SELECT Gruppi.IDgrp, Gruppi.Nome FROM Gruppi ORDER BY Gruppi.IDgrp;"

rst.Open Query, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

cnt = rst.RecordCount

i = 1
Do Until rst.EOF

'Group ID
recID = rst!IDgrp2

'Group Name
If IsNull(rst!Nome) Then
recName = ""
Else
recName = rst!Nome
End If

'Shift
If IsNull(rst!Turno) Then
recShift = ""
Else
recShift = rst!Turno
End If

'Efficiency
If IsNull(rst!AvgOfEfficienza) Then
recEfficiency = ""
Else
recEfficiency = rst!AvgOfEfficienza
End If

'Date
If IsNull(rst!onDate) Then
recDate = ""
Else
recDate = rst!onDate
End If

i = i + 1
Loop

rst.Close

End Sub

Thanks for the help!

Access 2000 Query/Array Question
I have a form in VBA that has 15 fields. I have a control array set up to populate the fields, but I need to know what is the best way to take data entered into the fields and place it into an Add/new or Update.

Usually I would just assign each to a variable, using the variable check to see if it is already there and update, if it is or add/new if it isnt.

i.e.

mysql = " Select * from table where Id = " & ID & ";"
set db = currentdb
set rs = db.openrecordset(mysql)

if rs.eof = true then
with rs
.addnew
.("") = me.txtItem1.value

etc..

Any suggestions?

SQL Wildcard Query, Access 2000, VB6 With ADO **RESOLVED**
I can fetch a specific record using the following:

VB Code:
Load_lvData "SELECT [Account #], [Customer Id], Status, [Service Address] From Accounts WHERE [Service Address] = 'P.O. BOX 511'"

Now, if I want to fetch all records that contain 'BOX'...

VB Code:
Load_lvData "SELECT [Account #], [Customer Id], Status, [Service Address] From Accounts WHERE [Service Address] LIKE '*BOX*'"

The last SQL statement returns nothing, why?

Added green "resolved" checkmark - Hack

Multiple Table Query With VB 6.0 And Access 2000
Hello all I was wondering if any one can help me with this. I am trying to display the records of multiple tables in a MSHflexgrid. I am using an ADODC to access my database. The task I am trying to perform is when the user chooses a purchasin ID and the Company ID then press ok( command Button) it is query the database and display the results in the flexgrid. The purchasing ID is in the Purchasing table and the Company ID is in the Company ID and also the Purchasing table. I am not sure how to do a query with multiple tables. I know you need to put the table name in front of the field name for example purchasing.purchasing_ID. What I need to know is the SQL query itself. I have this so far but it is giving me a runtime error saying that there is a problem with my FROM statement.

Dim field As String
Dim field1 As String
Dim MyQuery As String
Dim op As String

op = "and "
field=purchasingIDCombo.Text
field1=CompanyIDCombo.Text

Myquery= field & " " & op " " & " " & field1 'This may not be needed
Adodc1.ConnectionString = "Driver Microsoft Access Driver (.mdb) "App.path &"/Tracking.mdb" '
Adodc1.CommandType = amdCmdText
Adodc1.RecordSource = " Select * From Company & Purchasing Where Company.Comp_ID = Field1 & Purchasing.Purchasing.Purchasing_ID= Field'
Adodc1.Refresh

Ok I know there is a problem with my query itself I painly dont know how to do it I know how to query the database using one table but when two tables or more are required I am totally confused. Also I am using Microsoft Access 2000 for the database and Visual Basic 6.0 service pack 6 to write this program

Thank you for all your help
Json

Number Rows In SQL Query - Access 2000
Is there a way to number the rows returned by a query in Access 2000, preferably in the SQL itself?
Something like:
Code:
SELECT RowNumber, fld1, fld2,.... FROM tblFoo

'results in
1 someData moreData...
2 thisData thatData...
3 otherData whoseData...

RowNumber is not a field in the table, it's a hoped for property of the result set, like you can see on the record selectors in forms.
I tried making another table and adding an autonumber field and inserting the results into that, but the autonumber starts with a value one more than the number of records in the original table, i.e., if there are 999 records in the original table, autonumber in the second table starts with 1000.
VBA code would be ok, but since another user who doesn't know VB usually maintains this db, and who just uses wizards, it would be nice to just do this in the SQL, if possible.
Thanks.

'Query Is Too Complex' Error From Access 2000?!
I'm getting a bizzare error when executing a query against an Access 2000 DB

The query is very large and complex, so much so that it's built up using a number of VB procedures.
This is because it creates a UNION of similar but different tables which each have their own relationships with various other tables...
Anyway, the thing is the query works in a report and I get the correct results.
But when I reuse the same SQL to get a SUM() of a total field I get the error:
'Query is too complex'

Now... the really weird thing is, if I use:
SUM(TotCst) AS TotalCost
I get the error, but if I use:
SUM(TotCst) AS x
It works!!?

In fact I've just tested it again and it fails once I use more than 3 letters in my ALIAS!!!
Presumably this must be some kind of limit on the length of an SQL string.
Has anyone else come across this or know of a way around it?

When I used the same query before it was in a SHAPE command that was much larger (possibly twice the size), and that works fine?

The error I'm getting now occurs when executing the query using a standard ADO Connection to Jet (i.e. Not an MSDataShape)

Mac

Access 2000 - Pass Through Query Error Handling
I have a subform, which has it's SourceObject set to a Pass Through Query. When an error occurs, Err.Description simply gives a generic message for any ODBC error that occurs - "ODBC--call failed." What I am expecting to be reported is the native Oracle error, e.g. "ORA-00942: table or view does not exist".

If I execute the query solo - double clicking on the query icon, then I get an error message that displays both error messages - "ODBC--call failed." and "ORA-00942: table or view does not exist".

Can someone explain why I do not see the native error message during code execution for the subform? Or perhaps a work-around solution?

The only work-around that I have found so far is to execute an ODBC query first, then perform the same query using the pass-through. The ODBC call returns the native error message, so if there is an error, I receive it, and the pass-through is never instantiated. The problem with this (besides the fact that it is a hack) is that the query has to be executed twice. Here is the code, minus the boring details...

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

'Set SQL in Pass-Through Query
CurrentDb.QueryDefs(strQryName).SQL = Trim(strSQL)

'Execute the SQL using an ODBC call, native description is returned
rs.Open strSQL, CONN, adOpenKeyset, adLockOptimistic, adCmdText
rs.Close

'Set the SourceObject of the subform in order to implicitly execute the Pass-Through Query and display the recordset
subPassThru.SourceObject = "Query." & strQryName

I have also tried to use just the ODBC call (without the Pass Through), however I have not been able to figure out how to display the subform in datasheet view. When I try to set the recordsource of the subform, it appears to be nested within the main form's recordset. Anyhow, suggestions are welcome.

TIA,

Andrew

Alter Table Decimal Query On Access 2000
I try to execute this query: "ALTER TABLE T_HASAR
ALTER COLUMN MHASAR_TUTARI DECIMAL(14,2)" on Access 2000 database.

but the query fails and gives a message "Syntax error in ALTER TABLE statement".

What is wrong with the query? could anyone help me?

Error On Alter Column Query In Access 2000
I try to execute this query: "ALTER TABLE T_HASAR
ALTER COLUMN MHASAR_TUTARI DECIMAL(14,2)" on Access 2000 database.

but the query fails and gives a message "Syntax error in ALTER TABLE statement".

What is wrong with the query? could anyone help me?

Access 2000 Stored Insert Query And Parameters
If I execute this code while txtCode field contains any char it works. But if txtOtherDesc is empty VB returns me this error:

run-time error '3708': Parameter object is improperly defined. Inconsistent or incomplete information was provided.

It doesn't seem accept any string that has Len = 0...

How can I insert a "NULL" value using parameters?

CODEPrivate Sub InsertData()
    ' Stores query to insert user entered data
    Dim cmd As ADODB.Command
    Dim prmID As ADODB.Parameter
    Dim prmTitheAmt As ADODB.Parameter
    Dim prmMissionsAmt As ADODB.Parameter
    Dim prmBuildingFundAmt As ADODB.Parameter
    Dim prmOtherAmt As ADODB.Parameter
    Dim prmOtherDesc As ADODB.Parameter
    Dim prmDate As ADODB.Parameter
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & PathToUse & "MZB Church Tithe Tracker.mdb"
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "sp_InsertProc"

    Set prmID = cmd.CreateParameter("@newID", adVarChar, adParamInput, Len(txtID.Text), txtID.Text)
    Set prmTitheAmt = cmd.CreateParameter("@newTitheAmt", adCurrency, adParamInput, Len(txtTitheAmt.Text), Val(txtTitheAmt.Text))
    Set prmMissionsAmt = cmd.CreateParameter("@newMissionsAmt", adCurrency, adParamInput, Len(txtMissionsAmt.Text), Val(txtMissionsAmt.Text))
    Set prmBuildingFundAmt = cmd.CreateParameter("@newBuildingFundAmt", adCurrency, adParamInput, Len(txtBuildingFundAmt.Text), Val(txtBuildingFundAmt.Text))
    Set prmOtherAmt = cmd.CreateParameter("@newOtherAmt", adCurrency, adParamInput, Len(txtOtherAmt.Text), Val(txtOtherAmt.Text))
    Set prmOtherDesc = cmd.CreateParameter("@newOtherDesc", adLongVarWChar, adParamInput, Len(txtOtherDesc.Text), txtOtherDesc.Text)
    Set prmDate = cmd.CreateParameter("@newDate", adDate, adParamInput, Len(dtDate.Value), dtDate.Value)
    With cmd
        .Parameters.Refresh
        .Parameters.Append prmID
        .Parameters.Append prmTitheAmt
        .Parameters.Append prmMissionsAmt
        .Parameters.Append prmBuildingFundAmt
        .Parameters.Append prmOtherAmt
        .Parameters.Append prmOtherDesc
        .Parameters.Append prmDate
        .Execute
    End With
    Set prmID = Nothing
    Set prmTitheAmt = Nothing
    Set prmMissionsAmt = Nothing
    Set prmBuildingFundAmt = Nothing
    Set prmOtherAmt = Nothing
    Set prmOtherDesc = Nothing
    Set prmDate = Nothing
    cmd.ActiveConnection.Close
    Set cmd = Nothing
End Sub

Access 2000-Jet/VB6 - Formulating A Complex SQL Query (HELP- SQL Gurus)
I have been assigned some work to generate particular queries and am having problems on how to JOIN the tables and structure the WHERE clause appropriately. Here Goes

The System is a School Administration System with a VB6 front-end and Access 2000 Backend. The database was already designed so I cant re-design it, I'm just trying to create the queries.

I have to come up with a query that returns Exam Papers not written by a particular Student in a particular class.
(I will attach the database file minus the data and some of its structure)
Ok here us the description of the Database structure.

There are Students, each uniquely identified by StudentID.

There is a SubjectClass table which has data on every class in the school, uniquely identified by SubjectClassID

There is a SubjectClassStudents table which is a collection of all students in classes. Each Student has a corresponding
SubjectClassID,StudentID and SubjectClassStudentsID to uniquely identify each record.

There is an Exams table as well. Each Exam has its corresponding SubjectClass. Each record therefore has
ExamID,SubjectClassID to uniquely identify it.

Each Exam can have several Papers. So the Exam table also has a NumberOfPapers field

Each Paper has its own unique PaperID,and ExamID to uniquely identify it

Here is the structure on text (table names in bold, fields seperated by "/")

Students
StudentID / StudentName

SubjectClass
SubjectClassID / SubjectClassName

SubjectClassStudents
SubjectClassStudentsID / SubjectClassID / StudentID

Exam
ExamID / SubjectClassID / ExamName / NumberOfPapers

Paper
PaperID / ExamID / PaperName

PaperMarks
PaperMarksID /PaperID / StudentID

The problem now:

Find the Papers NOT written by a Particular Student in a Particular SubjectClass

Let me place the database.

Edited by - tintinchasm on 1/16/2007 6:59:22 AM

Problem With Create Table Query In Access 2000
I am new to using Access in Vb. I am trying to create a table to run a report from(I had several problems getting the data the way I needed,so I tried this). I am using DataEnvironment and Datareport. The report will run once, but if I try to run it a second time, I get the error: "Record Deleted". The SQL from the DataReport is:

SELECT SalaryA3Query.RegHrs, SalaryA3Query.OvtHrs, SalaryA3Query.DblHrs, SalaryA3Query.SickHrs, SalaryA3Query.VacHrs, SalaryA3Query.HolHrs, SalaryA3Query.CommErn, SalaryA3Query.Oth1Ern, SalaryA3Query.Meals, SalaryA3Query.NoPay, SalaryA3Query.Tips, SalaryA3Query.Oth2Ern, SalaryA3Query.MiscHrs, SalaryA3Query.AdjToNet, SalaryA3Query.DeleteRec, SalaryA3Query.PAYCDE, SalaryA3Query.SCHR, SalaryA3Query.EXCFL, SalaryA3Query.CYCLE, SalaryA3Query.PAYEMP INTO SalaryTable
FROM SalaryA3Query
ORDER BY SalaryA3Query.PAYEMP;

And the code from my app is (QUERY1 is drawing from the created SalaryTable):

sSQL$ = "DROP TABLE SalaryTable"
Access.Execute sSQL$ ''Deleting previous table
Call DtaReports.CreateSalaryTable ''creating new table

''I get the error on the line above
SQL$ = ""
SQL$ = "SELECT "
SQL$ = SQL$ & "RegHrsb,"
SQL$ = SQL$ & "OvtHrsb,"
SQL$ = SQL$ & "dblHrsb,"
SQL$ = SQL$ & "SickHrsb,"
SQL$ = SQL$ & "VacHrsb,"
SQL$ = SQL$ & "HolHrsb,"
SQL$ = SQL$ & "CommErnb,"
SQL$ = SQL$ & "Oth1Ernb,"
SQL$ = SQL$ & "Mealsb,"
SQL$ = SQL$ & "NoPayb,"
SQL$ = SQL$ & "Tipsb,"
SQL$ = SQL$ & "MiscHrsb,"
SQL$ = SQL$ & "Oth2Ernb,"
SQL$ = SQL$ & "AdjToNetb,"
SQL$ = SQL$ & "SCHRb,"
SQL$ = SQL$ & "Val(PAYEMP) as PAYEMP FROM Query1 "
SQL$ = SQL$ & "ORDER BY PAYEMP"

DtaReports.rscomSalary.Open SQL$
RptSalary.Orientation = rptOrientLandscape
RptSalary.PrintReport
DtaReports.rscomSalary.Close

What do I need to do to run the report multiple times? Thanks in advance

Catrina

Access 2000 - Building String Of Make-table Query
I have a simple query which returns me 3 fields for given values. I then want to run a make-table query on this query to turn the results into a table. My code is below.

If you scroll down, the bit that isn't working is labelled after 'End of 1st Query / start of 2nd below'. "Run-time error '3131' syntax error in FROM clause" is what I get when you run the final line - DoCmd.RunSQL (SQL)

'sql to find list of parts for highlighted suppliers in From Country To Sell In Listbox (1st query)
SQL = "SELECT SupplierPart.SupplierName, SupplierPart.VauxhallNo, SupplierPart.Price " & _
"FROM SupplierPart " & _
"WHERE SuppplierPart.SupplierName IN ("
intindex = 0
Do While intindex < UBound(ArraySupplierToSellIn())
arrayLength = UBound(ArraySupplierToSellIn())
If intindex = arrayLength - 1 Then 'if it is last value in array don't add comma
SQL = SQL & "'" & ArraySupplierToSellIn(intindex) & "'"
Else:
SQL = SQL & "'" & ArraySupplierToSellIn(intindex) & "'" & ","
End If
intindex = intindex + 1
Loop
SQL = SQL & ")" & " GROUP BY SupplierPart.SupplierName, SupplierPart.VauxhallNo, SupplierPart.Price;"
SelectSuppliersForSelling = SQL
'MsgBox (SQL)

'''''''''''''''''''''''''''''''''''''''''End of 1st Query / start of 2nd below

'THIS IS THE BIT THAT ISNT WORKING...SEE HOW I HAVE BUILT STRING.....
'make table SuppliersHighlightedTop using select query just created
SQL = "SELECT (" & SelectSuppliersForSelling & ").* INTO SuppliersHighlightedTop FROM (" & SelectSuppliersForSelling & ");"

MsgBox (SQL)
DoCmd.RunSQL (SQL)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''

Pass Input Parameter From Query To Report In Access 2000
I am using a parameter query to sum information from a table. Part of the parameters is to specify the date range to be summed as shown below:

SELECT TOP 10 [Lost Time Codes].Reason, Sum([Master Lost Time Entries].Minutes) AS SumOfMinutes
FROM [Lost Time Codes] RIGHT JOIN [Master Lost Time Entries] ON [Lost Time Codes].code = [Master Lost Time Entries].[Downtime Code]
WHERE ((([Master Lost Time Entries].Shift)="1st") AND (([Master Lost Time Entries].Date) Between [Enter Start Date] And [Enter End Date]) AND (([Lost Time Codes].Area)="THT"))
GROUP BY [Lost Time Codes].Reason
ORDER BY Sum([Master Lost Time Entries].Minutes) DESC;

I want to pass the the [Enter Start Date] and [Enter End Date] inputs to the report containing a chart. Anyone know how to do this, or of a completely different way to accomplish the same task without using the above query?

Edit by Moderator:
Please post SQL questions, in the Database forum.

Thank you.

Access 2000 Question Error With Running Append Query
I have attached a doc file with screen shot of the error.

Some history:

I accidentally ran an append query twice. So is isolated the duplicate records and tried to run a delete query to delete the extra records. That didn't work giving me giving me an error. It read something like “Operation not able…something”

I went straight to Enterprise Manager and tried to delete the records by highlighting them and I received an error from Enterprise Manager stating something about the key and that I was trying to delete too many records at one time. So I tried to delete one record and received the same error.

So I dropped the table in Enterprise Manager and DTS it over from my test company database whish was only two days old. Once I did that the table was back in its original state so I tried to run the append query again and that’s when I received the attached error.

We have been having a lot of problems with our production sever and the admin person bounced the server to hopefully relieve some of the other problems he was encountering. I was hoping that it would also fix my problem but it didn’t.

I refreshed my links.

I'm running Access 2000 linking to SQL 2000.

Any ideas???????????

Execute A SQL Query (ACCESS 2000) From VB And Pass Arguments Required
Hi !

I have built a SQL query that asks the user to define the value of an alias, which is required to run it.
query : SELECT * FROM table WHERE field1=[prompted value]

I want to execute this statement with a VBA call, but I remain unable to pass the 'prompted value' in the code.

How can I do that ?
(Except by building an on-the-fly query like : "SELECT * FROM table WHERE field1=" & var_prompted_value

I Am Exporting An Access Query To Excel, How Can I Define The Excel Cell Size/format
I am using TransferSpreadsheet to Export a Query to Excel with a button from Access.

How can I define in Excel when I export it, the size of the cells, the type of letter (Arial, Bold), The Background color.

Private Sub Impacto_Click()
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
TableName:="Impact", _
Filename:="C:Impact.xls", _
hasfieldnames:=True
End Sub

Access 2000 To Excel
I have an access 2000 macro that exports a table to an excel spreadsheet. The macro worked fine for a while but now it just halts and gives me the message "Cannot delete spreadsheet cells"
simple code:
Private Sub cmdTransfer_Click()
On Error GoTo Err_cmdTransfer_Click

Dim stDocName As String

stDocName = "transfer"
DoCmd.RunMacro stDocName

Exit_cmdTransfer_Click:
Exit Sub

Err_cmdTransfer_Click:
MsgBox Err.Description
Resume Exit_cmdTransfer_Click

End Sub
Can anyone help?
Thanks in advance
Bill

Access 2000 To Excel
I have created an Access Database where one of the tables that I use on the Access form is a linked excel file. The problem is when two or more people try to open the table, one of them will get a message that says the file is in use. However, with some playing around, we can get them both in. I know Access allows you to set up users, but that would require the persons to each put in a password and username. they want to avoid this. We just want more thatn one person at a time to be able to get into this database. Is this an Excel Issue or an Access Issue?

VBA Automation From Access 2000 To Excel
I am creating a custom automation routine from MS Access.

I want to generate a worksheet and populate it with data from a recordset.

This part, I have done, although presently my code refers to the cells directly - I know I should refer to a range, although I am unsure as to the syntax.

MY CODE STARTS HERE
#####################################
Dim xAP As Excel.Application 'Mew instance of Excel
Dim xWB As Excel.Workbook 'New Excel workbook
Dim xWS As Excel.Worksheet 'New Excel worksheet

Dim xCell As Integer
xCell = 4

Set xAP = New Excel.Application 'Start Excel
xAP.Visible = False
xAP.Workbooks.Add 'New Excel workbook

Set xWB = xAP.ActiveWorkbook
xWB.Worksheets.Add
Set xWS = xWB.ActiveSheet 'Add new worksheet to workbook and set xws as this active document

Dim pvtrst As ADODB.Recordset
Dim pvtcmd As ADODB.Command

Set pvtrst = New ADODB.Recordset
Set pvtcmd = New ADODB.Command

pvtcmd.CommandText = "SELECT tblPivotData.CalDate, tblPivotData.VenueID, " & _
"tblPivotData.DuringBuildUp, tblPivotData.DuringShow, tblPivotData.DuringPullOut " & _
"FROM tblPivotData WITH OWNERACCESS OPTION;"

pvtcmd.ActiveConnection = CurrentProject.Connection

Set pvtrst = pvtcmd.Execute

Do Until pvtrst.EOF = True

xWS.Cells(xCell, 1).Value = "#" & pvtrst!caldate & "#"
xWS.Cells(xCell, 2).Value = pvtrst!VenueID
xWS.Cells(xCell, 3).Value = pvtrst!DuringBuildUp
xWS.Cells(xCell, 4).Value = pvtrst!DuringShow
xWS.Cells(xCell, 5).Value = pvtrst!DuringPullOut
xCell = xCell + 1
pvtrst.MoveNext

Loop

xWS.Cells(3, 1).Value = "Date:"
xWS.Cells(3, 2).Value = "Venue:"
xWS.Cells(3, 3).Value = "At Build Up:"
xWS.Cells(3, 4).Value = "During Show:"
xWS.Cells(3, 5).Value = "At Pull Out:"
xWS.Name = StrConv(PartID, vbUpperCase) & " Components"
xWS.Columns("A:F").AutoFit
xWS.Cells(1, 1).Value = StrConv("Component usage for Part:", vbUpperCase) & PartID & _
"between " & StartDate & " and " & EndDate

xWS.SaveAs "C:windowsprofiles
jdDesktoppivot" & PartID & ".xls"
xWB.Close
xAP.Application.Quit

Set xWS = Nothing
Set xWB = Nothing
Set xAP = Nothing
##############################################

This populates the spreadsheet with the data I am interested in.
(There are always 5 columns, but rows are variable.

However, I would also like to:

1 Select the range of data

2 Create a pivot table from the data
Column: CalDate
Row:VenueID
Data: DuringBuildUp, During Show, DuringPullOut

3 Go to print preview for this pivot table

4 Ideally, not show the main excel window, just the previewed report
and close excel correctly when the report is closed. (If possible)

Footnote: I know there is a wizard within MS Access for pivot tables, but i have found it very unreliable and not flexible enough for my needs.

Any comments greatfully received.