Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Select Count Statement


i have this select count statement

Dim strSQL
"SELECT count(ctrl_no) As [tally] From logsheet where date_prep ='" &mebDat.Text & "' and ctrl_no= '" & txtConNo.Text & "'"
MsgBox count

this must show the total number of records with the condition:date_prep=mebDat.Text and ctrl_no=txtConNo.Text, how come it doesntcount any number of records?

lewell

The content of this email when sent is as full as practical. Due to thesettling of the words the contents might not appear as full as it wasbut the weight remains the same. The contents is being sent by weightand not by volume.




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Select Multiple Count In 1 Sql Statement?
Can you do a select count to return multiple count values into 1 recordset?

for example..

lets say i have a field called status... and there are 2 possible values..

P or A for pending or approved..

now i know i can do
"select count(*) as CountOfPending where status = 'P'"
"select count(*) as CountOfApproved where status = 'A'"

but can i do both in 1 SQL statement so i don't have to execute it twice?

Select * Faster Than Select Count(*)?
Hi,
I have a simple test, i compare the elapse time for sql "select * from mytable" with "select count(*) from mytable". The result
is as the following:

select * = 0.8515625
select count(*) = 1.171875

why select * is faster here? I know that COUNT(*) should be faster!!! why?

Private Sub Command1_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim aaa As Long

Set db = OpenDatabase("f:iblio.mdb") 'c on myserver

aaa = Timer

For i = 1 To 100
Set rs = db.OpenRecordset("select count(*) from authors", dbOpenSnapshot)
rs.Close
Next i
Debug.Print Timer - aaa
End Sub

Any idea?

Count Statement Help
hi,please help.i want to count the otrate from overtime table display at the data report.

for example:
user1 otrate:1.0 othours:4
user1 otrate:1.0 othours:4
user1 otrate:2.0 othours:5
user1 otrate:2.0 othours:5

result:
user1 otrate:1.0 othours=8
user1 otrate:2.0 othours=10

Code:Private Sub Command1_Click()
Dim totalrate As Double
Dim total As Double
Dim strSQL As String
Dim conDataConnection As Connection
Dim empName As String
Dim enddate As String
Dim startdate As String
Dim Department As String
Dim empno As String
Dim designation As String


'totalrate = Text1.Text
empOTsummary.Orientation = rptOrientLandscape
empOTsummary.Show
empName = cboempName.Text


start = Format(DTPicker1.Value, "dd MMM yyyy")
enddate = Format(DTPicker2.Value, "dd MMM yyyy")


 Set conDataConnection = New Connection
      conDataConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & _
                "Overtime.mdb;Persist Security Info=False"

Dim rs As New ADODB.Recordset
strSQL = "SELECT * FROM Overtime where OvertimeDate BETWEEN #" & start & "# and #" & enddate & "# and EmployeeName='" & empName & "'"
rs.Open strSQL, conDataConnection, adOpenStatic, adLockOptimistic

While Not rs.EOF
        If IsNull(rs("OtHours").Value) Then
            total = total + 0
        Else
            total = total + rs("OtHours").Value
        End If
     If IsNull(rs("OtRate").Value) Then
           totalrate = total + 0
        Else
           totalrate = totalrate + rs("OtRate").Value
        End If
        Department = rs!Department
        empno = rs!EmployeeNo
        designation = rs!designation
        rs.MoveNext
    Wend
  
   
  'display total at report footer
  empOTsummary.Sections("Section5").Controls.Item("Label15").Caption = Format(total, "#,##0.00")
  empOTsummary.Sections("Section5").Controls.Item("Label24").Caption = Format(totalrate, "#,##0.00")
  empOTsummary.Sections("Section4").Controls.Item("Label17").Caption = Department
  empOTsummary.Sections("Section4").Controls.Item("Label4").Caption = empName
  empOTsummary.Sections("Section4").Controls.Item("Label5").Caption = empno
  empOTsummary.Sections("Section4").Controls.Item("Label23").Caption = designation
  empOTsummary.Sections("Section4").Controls.Item("Label19").Caption = start
  empOTsummary.Sections("Section4").Controls.Item("Label21").Caption = enddate
'set the database source in rs
    Set empOTsummary.DataSource = rs
    empOTsummary.Sections("section1").Controls.Item("Text6").DataField = "OvertimeDate"
    empOTsummary.Sections("section1").Controls.Item("Text7").DataField = "TimeFrom"
    empOTsummary.Sections("section1").Controls.Item("Text8").DataField = "TimeTo"
    empOTsummary.Sections("section1").Controls.Item("Text10").DataField = "OtRate"
    empOTsummary.Sections("section1").Controls.Item("Text11").DataField = "CategoryDay"
    empOTsummary.Sections("section1").Controls.Item("Text12").DataField = "WorkDescript"
    empOTsummary.Sections("section1").Controls.Item("Text13").DataField = "Othours"
End Sub


thanks for help!




Edited by - monchichi on 12/18/2007 12:27:37 AM

SQL : Using SELECT Statement With An UPDATE Statement
Hi all

I'm sure this is very easy but I'm ill and I just can't work it out!!!

I need to update a table using data from a second table, so how do I use a SELCT statement in conjunction with an UPDATE statement.  

NB:  I'm using an access database

Please help, once this is sorted I can go home!!!!

Thanks

Jon

Select Count Help
Good Afternoon

Shopping for some SQL help today...

I have a query that I am using to select certain number of fields from a table. Is there a way to limit the resultset to a certain count? For reference, I need only the first 15 out the 400+ that is outputted.

Thanks for the help,
EJD

SELECT
REG_SYS_ID AS CUSTOMER_ID,
REG_CUST_NAME AS NAME,
REG_CUST_ADDR1 AS ADDRESS,
REG_CUST_CITY AS CITY,
REG_CUST_STATE AS STATE,
REG_CUST_ZIP AS ZIPCODE,
REG_CUST_PHONE AS PHONE,
REG_SOLD_DT AS PURCHASE_DATE,
REG_MODEL_NO AS MODEL_NUMBER,
REG_SERIAL_NO AS SERIAL_NUMBER,
REG_ENTRY_DT AS PURCHASEY,
REG_DEALER_NO AS PURCHASED_FROM
FROM
ECD.STORE_REG_DETAIL@ECM_TEST

SELECT COUNT(*)
What is the best way to return a Count of Records that match criteria in a DB?

The only way I know of is to Open the Recordset, MoveLast, and use the RecordCount Property...

Is there a way that I can do a Conn.Execute(SQLStatement) that just returns a Count? i.e. SELECT COUNT(*) FROM Table WHERE Active = 1, etc....

Thanks,
Kevin

Select...count
hi, i try to use this way.
Where am I wrong?

Dim temp as Integer

CN.Open "select count(*) as 'temp' from table1 where id='12345'"
msgbox ("Your number is " & temp)

Thanks'

SQL Statement Please Count Of Columns
Hello,

can anybody please help me with a SQL-Statement:

select count(*) gives my the number of the records.

but I'm looking for a statement, which gives me the number of the columns(fields)

Can anybody help me please?

Thanks in advance

Firlefanz

SELECT COUNT(*) Gives An Error
Dear All,

I am opening an ado recordset using MSDAORA provider.
rs.open "SELECT COUNT(*) FROM CUSTOMER", OBJCONNECTION
The Oracle database has 120000 records in CUSTOMER table. But when I try to get the value from rs, it thows an error

Error Code: 3021
Error Description: Either BOF or EOF is True or the current record has been deleted. Requested operation requires a current record

Here is the code

rs.open "SELECT COUNT(*) FROM CUSTOMER", OBJCONNECTION
MSGBOX "Opened"
msgbox rs.fields(0).value --Here is gives an error


Can anybody help me put?

Thanks,


__________________
Regards,
Jigar Bhavsar

Sql Problem , Select Count(*) From.......
Hi
Can anyone tell me what I am doing wrong with this sql

Set rsttemp = xlsdatabase.OpenRecordset("select count(*) as num from table1")
rsttemp.MoveLast
rsttemp.MoveFirst
MsgBox ("num = " & num)

I dont get anything returned ! I just want to be able to count the number of returned records in a recordset. Am I going about this in the wrong way ?

cheers, Mark

ADO RecordCount In Select Count(*) From...
Using ADO in Delphi...

bData := False;
Conn:=CoConnection.Create;
Conn.Open(DSN, USERID, PWD, -1);
Command:=CoCommand.Create;
Command.Set_ActiveConnection(Conn);
Command.CommandText:= 'SELECT Count(*) FROM
DB005';
Command.CommandType:=adCmdText;
RS:= Command.Execute(oRecordsReturned,
EmptyParam, -1);
if oRecordsReturned > 0 then
bData := True;

oRecordsReturned always = -1

How would I find out how many rows in the query?

If I change to:
RS:= Command.Execute(oRecordsReturned,
EmptyParam, -1);
i := RS.RecordCount;
if oRecordsReturned > 0 then
bData := True;

i always = -1 also. I know there are records there!

SQL Statement Count Total Quantity
Anyone has any idea on how to calculate the transaction date by model and then count out the total quantity for that transaction date for that day?
For example:-
I want to set a target 2th March 2005 starting to count for the transaction date and time.Then,it will pick out only the transaction date(trans_date) and time which i set in the database for me by following the certain model.Anyone can tell me how to write for the coding?Thank you.

How To Use COUNT Function In (SELECT * FROM) Sql Query...?
How to use COUNT function in (SELECT * FROM) sql query...?

Is SELECT COUNT The Best Way To See If Record Exists?
Hi, I need to find out if a record exists in SQL Server. I was going to do a SELECT COUNT with a recordset. Is that the best way, or is there a quicker way that doesn't make me create and read a recordset?

-Uncle Cake


I Need Help In Using The SELECT COUNT And Assigning Its Value To A Variable.
Hi! Can anyone help me I want to use

Docmd.runsql("SELECT COUNT(*) FROM TABLE 1")

and store its value into a variable, but I dont know if INTO statement can work if I use variable names.

 

How To Store The Result Of A Count() Sql Statement As A Variable
Good morning/afternoon

I have a table named NEW and a field named part. (the part field contains records of computer parts) I want to use the sql statment count() to get the total records of the PART field in my table named NEW. In sql launguage I can get the value, but in VB I need to store this as a variable so that I may use this to find out the percentage of let's say "hard drives". So to do this I would need to count the total records of the field "PART" and also need the total count of all fields that are "Hard drive" I would then devide the 2 to get a percentage. I would use this sql statment:

SELECT count(part)
FROM new
where part = new.part

New is my table, part is the field. this statement would give me the total records in the field "PART"


this statment would give me the total number of "hard drives" in the field "PART"

SELECT count(part)
FROM new
where part = 'hard drive'

Now how do I execute this in VB and store these values as integers so That I may divide them and get a percentage?

when I do this it gives me a syntax error at the FROM clause


Dim a As Integer 'This would be the int for the total records in the "PART" field

a = Adodc1.RecordSource = "SELECT count(part) FROM new " & "where part = new.part'" 'adodc1 is my ado control that contains the database

then I would display the value of A in a message box to see the value

msgbox a


So how would I in VB store this as an integer?


Any help would be greatly appriciated


Thanks,
Have a good day

.Open "select Use Combobox And Date, To Count ?
HI
are somone hu can help me with this

I use this ant are traying to select stod from combobox and use date from and to
.Open "select stod, count(*) As CodeNameCount from verk group by stod ", cnn, adOpenForwardOnly, adLockReadOnly, adCmdText

I am traing to select stod from combobox and date from and to
so I can get count from I select in combobox


 Trim(stod.Text) & "' AND Dagsetning Between #" & Month(dtStart) & "/" & Day(dtStart) & "/" & Year(dtStart) & "# AND #" & Month(dtEnd) & "/" & Day(dtEnd) & "/" & Year(dtEnd) & "#")

Returning All Columns Including Those With Count = 0 From CASE In SELECT
Thanks again to all, got it to work great. Bronco

Hi all, thank you for your help on thes scenarios. I now need to include all bbrackets including those with count of ZERO. My attempt is shown below, Should have all brackets other 0 and 0-49 0

Code:
SELECT
         CASE
              WHEN income BETWEEN 0 AND 49 THEN '0-49'
              WHEN income BETWEEN 50 AND 99 THEN '50-99'
              WHEN income BETWEEN 100 AND 499 THEN '100-499'
              WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
              ELSE 'other'
         END as bracket,
        (ISNULL(COUNT(id_person),0)) AS [number]
FROM #persons
GROUP BY
         CASE
              WHEN income BETWEEN 0 AND 49 THEN '0-49'
              WHEN income BETWEEN 50 AND 99 THEN '50-99'
              WHEN income BETWEEN 100 AND 499 THEN '100-499'
              WHEN income BETWEEN 500 AND 1000 THEN '500-1000'
              ELSE 'other'
         END;



Here is the code for the temp table being used:
Code:
if object_id('tempdb..#persons') is not null drop table #persons
create table #persons
(id_person int not null,
firstname varchar(30) not null default(''),
lastname varchar(30) not null default(''),
dob datetime not null,
income numeric(18,2) not null,
category varchar(30) not null default(''))

insert #persons (id_person,firstname,lastname,dob,income,category)
values (1,'Mickey','Mouse','7/18/1925','100','Cartoon')

insert #persons (id_person,firstname,lastname,dob,income,category)
values (2,'Minnie','Mouse','6/12/1927','120','Cartoon')

insert #persons (id_person,firstname,lastname,dob,income,category)
values (3,'Clark','Kent','6/12/1957','52','Superhero')

insert #persons (id_person,firstname,lastname,dob,income,category)
values (4,'Peter','Parker','1/12/1977','55','Superhero')

insert #persons (id_person,firstname,lastname,dob,income,category)
values (5,'James','Bond','1/15/1949','250','Superagent')

insert #persons (id_person,firstname,lastname,dob,income,category)
values (6,'Austin','Powers','1/15/1949','350','Superagent')

insert #persons (id_person,firstname,lastname,dob,income,category)
values (7,'Bill','Clinton','1/15/1956','500','Politician')

insert #persons (id_person,firstname,lastname,dob,income,category)
values (8,'Hillary','Clinton','4/15/1958','700','Politician')

insert #persons (id_person,firstname,lastname,dob,income,category)
values (9,'Chelsea','Clinton','6/15/1978','52','Daughter')

insert #persons (id_person,firstname,lastname,dob,income,category)
values (10,'Buddy','Clinton','6/15/1978','51','Dog')

go


Thanks again for your assistance, these are practice scenarios to get more comfortable with sql Bronco



Edited by - bronco on 8/30/2006 7:55:28 AM

Getting Count Of A Field In SQL Server With And Wihout Qualifier In One SELECT?
Hi, I am trying to get a Count(id_person) and a Count(id_person) WHERE income is > 100 in one SELECT, is this possible. Here is an example of the results I am trying to get: the numbers shoild be in two columns single digit, first row 2 in count, 1 > 100 etc.

category number number_gt_100
------------------------------ ----------- -------------
Cartoon 2 1
Daughter 1 0
Dog 1 0
Politician 2 2
Superagent 2 2
Superhero 2 0

I have tried the following:

SELECT category, COUNT(id_person) AS [number],(SELECT COUNT(id_person) AS [number_gt_100]
FROM #persons
WHERE income > 100
GROUP BY category)
FROM #persons
GROUP BY category;

It is easy to get each count seperately, but I am trying to get both counts at the same time and one has a qualifier that the other does not. Any help would be appreciated. Bronco



Edited by - bronco on 8/29/2006 7:17:33 AM

Excel - Calculating An End Date ( Option To Select Which Days To Count )
I've been playing around with a calendar based Date Calculator in Excel.
The problem I'am having is Calculating the End date based off information selected
and which days have been chosen to be counted.
I've attatched the spreadsheet. It's easier to look at than explain.
any ideas would be greatly appreciated!

If Statement And Select Statement
if i did this same thing with both statements.

Code:
If Val(Text1.Text) > 4000 Then
MsgBox "Greater"
End If

Select Case Val(Text1.Text)
Case Is > 4000
MsgBox "Greater"
End Select
both will show the messagebox as its true but if i took away the Val function

Code:
If Text1.Text > 4000 Then
MsgBox "Greater"
End If

Select Case Text1.Text
Case Is > 4000
MsgBox "Greater"
End Select
only the If statement will show the messagebox.

anybody know why ?

EDIT: sorry, forgot to mention that in the textbox is 35000

Select Into Statement
I'm trying to create an SQL statement in VB that copies the records found in an existing table and places them into another table that I have created according to the criteria.


Code:
Dim sSQL As String

If Forms![Main].PACK = True Then

'Select all records in the Sheet1 table and copy WHERE Sheet1.PACK <= 1 into a new table, SheetPack

sSQL = sSQL & "SELECT Sheet1.[ASC ITEM CODE], Sheet1.DESCRIPTION, Sheet1.PACK, Sheet1.[CHAIN COST], Sheet1.[SUGGESTED RETAIL], Sheet1.[GROSS PROFIT], Sheet1.[ORDER QTY]INTO"
sSQL = sSQL & "[SheetPack] From Sheet1"
sSQL = sSQL & "WHERE Sheet1.PACK <= 1;"

DoCmd.RunSQL (sSQL)

I keep getting a syntax error in the FROM Clause.

I'm sure there are errors I just can't figure out what I need to change.

SQL Select Statement
I have a form with a textbox control that I enter search criteria into. I currently am using a SQL Select statement to search my database. My problem is that it searches on 1 field yet my database has 7 fields in which I want to search. I have been unsuccessful in concatenating my Select statement. Here is my statement as follows.

rsDATA.Open "Select * from tblUser where LName = '" & Text1.Text & "'"

I would appreciate it if someone could help me add FName, EmployeeNo etc...

Amateur_VBGUY

Select Top SQL Statement
Hi,
1. I use "select top 10 from table" to return the top 10 records. Now, how do I loop through the next 10 records till the end.

2. If I use "select top 10 rom table where orderId > lastRecordOrderId", how do I get the lastRecordOrderId from my previous query?

3. If lastRecordOrderId is not a key, what should I do?

Thanks and appreciated!

Select Statement
Currently I need to extract data from a Microsoft Access Table to do calculation. Im using SQL select statement. Where should I place the select statement? In the main program or somewhere else? Thanks for all the help!

Weisi

Help With Select Statement
Hi,

I getting a "missing operator" in my Select statement. I need another pair of eyes to look at it.

Here goes:


Code:
SELECT distinct centers.center_id, centers.center_path,
states.state_path, centers.center_name, states.state_name,
states.region_id, states.state_id FROM centers INNER JOIN
states ON centers.state_id = states.state_id INNER JOIN
v_tenants ON centers.center_id = v_tenants.center_id where (
v_tenants.tenant like "%'" or v_tenants.search_name like "'%")
center_flag=1 state_name + center_name order by state_name,
center_name

Edit: VB tags take a forward slash /

Select Statement PLEASE HELP
I'm trying to use this to search the database.

"Select * from contacts where cboSearch.text = '" & txtSearchFor.Text & "'"

I can not get the combo box text to come over. I want to use the cbo box text for the feild to search.

Please help i've been trying and can't figure this out.

Select Statement
The following SELECT statement generates an application?? error in WindowsXP.

strSQL = "SELECT Invoices.*, Customers.CustomerName FROM Invoices LEFT JOIN Customers ON " & _
"Invoices.CustomerID = Customers.CustomersID WHERE Invoices.JobNum = " & cInvoice.JobNum

rstTemp.Open strSQL, adoConnection1, adOpenDynamic


Whenever rstTemp.Open executes, I get the error "Visual Basic has encountered a problem and needs to close" and XP wants to send the error report to MS.

I have tried rebooting, running the code from a new application, changing Invoices.* to listing all fields and nothing helps. If I don't execute that statement, the rest of my code runs fine - opening other recordset, etc.

More Info:
ADO 2.7; Invoices Table contains - InvNum, JobNum, CustomerID,... Customers Table contains CustomerID, CustomerName....;

Any Ideas?

I've sent the reports to Microsoft. Do they ever respond to these?

I'm connecting to an Access database

Select Statement
Hi friends,
I am new in your group and this is my first question.
I am trying to run this macro in visual basic editor in MS access but I don't know why it doesn't return any thing when I hit the run botton.

Any help is appreciated.
Elahe


Public Sub DoSQL()

Dim SQL As String

SQL = "SELECT name As Name " _
& " FROM Table1 " _

End Sub

Select Statement
I have attached a word document describing my predicament

The Table:
Developing a golf tournament program.
Table name = scores
Fields: lastname (primary index, only 2 tables, the other table is reference data only)
Flightnumber
Handicap
Hole1,hole2 …..hole18 (not a control array)
Gross
Net

The Problem:
The select query sort order should be : flightnumber (primary), gross
Groups are required where gross scores are tied.
Where scores are tied within each group a winner has to be established.
This is done by a process called “countback”.
Starting at the 18th hole within each group the highest score moves up in the order of things. (This is because, at the point where a tie occurred, the player with the
Highest score was leading). If players are still tied at the 18th hole then the same procedure must be executed at the 17th hole etc.until each group has been rearranged with the winner and those following determined by the score at each hole. Depending on the number of players in a group it is possible to require several iterations before an order has been established.


Can I do this whole operation with a single select statement or do I require VB coding to do the rearranging.

Select (*) Statement
Hello Everyone,

Ive been having some problems with the select * statement. I have two tables in Access 2000 as follows:

Table 1 = CompanyInfo
Table 2 = ContactInfo
The two tables have a relationship via CompanyID. CompanyID (In Table1) = CompanyName1 (In Table2) I used the Lookup wizard in Access to create this relationship.

I have no problems connecting to this database and returning recordsets using different types of complex SQL statements. However I am having a problem trying to select all the records that match the CompanyID value that is already in a label (Label9) on my form.

Here is the code I currently have:


Code:
Dim sCustPath As String
Dim sCustUpdateSQL As String
Dim connCustUpdate As New ADODB.Connection
Dim rsCustUpdate As New ADODB.Recordset
sCustPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"C:Customers.mdb;" & _
"Persist Security Info=False"

'*************** BEGIN EXTRACTING CUSTOMER INFORMATION **********************
sCustUpdateSQL = "SELECT * "
sCustUpdateSQL = sCustUpdateSQL & "FROM CompanyInfo "
'DO I NEED AN INNER JOIN STATEMENT IF BOTH TABLES ARE LINKED BY COMPANY ID?
sCustUpdateSQL = sCustUpdateSQL & "INNER JOIN ContactInfo On CompanyInfo.CompanyID = ContactInfo.CompanyName1 "
sCustUpdateSQL = sCustUpdateSQL & "WHERE CompanyID = '" & Label9.Caption & "' ORDER BY CompanyID "
rsCustUpdate.Open sCustUpdateSQL, sCustPath, adOpenKeyset, adLockOptimistic

With rsCustUpdate
Debug.Print rsCustUpdate!CompanyName
End With

Any Suggestions?
Thankyou in advance

Select Statement
I want to be able to continue the statement on different lines, but it won't let me. Please let me know what I am doing wrong.

SQL = "SELECT Comm_Name As CommName FROM Commodity & _
WHERE Commodity.Comm_Num = '" & txtCommNumber & _
(i - 1).Text & "'"

SELECT Statement - Is This Possible
Is this possible??

I have a table called "Jobs" with the following fields:

JobNum(key); Desc;Amount; JobType; PrimaryJob

JobType is either a 1 or 2; 1 indicates a primary contract job, 2 indicates a "change order" to an existing contract job. If the job is a primary contract, then primaryjob is the same as jobnum. If the job is a change order, then primary job is the jobnum of the primary contract

I would like to create a SELECT statement that gives me the following:

JobNum; Desc; Sum(amount) Where JobType = 1

but I want Sum(amount) to sum where PrimaryJob is the same.

Example: the following table would return:


JobNum; Desc; Amount; JobType; PrimaryJob
1 Bank 25000 1 1
2 Bakery 35000 1 2
3 change1 1000 2 1
4 change2 2000 2 1
5 change1 500 2 2


result:
1 Bank 28000
2 Bakery 35500

Select Statement
I have an Access database with columns Name, Price and Date. There are many Dates for each name. I need a Select statement to retrieve Name, Price and only the most recent Date. Obviously "Select * from Table Order by Date desc" gets everything. I only want to see the most recent Date for each Name. Thanks.

Select Into From Where Statement
Hi

I was just wonder that is it possible to use the Select.. into statement in MS access

I am trying to run this code.. but its giving me an error :

db.Execute ("select * into Temp from Details where Invoice='alpha1' and itemnum > 3 and itemnum < 15)")

Any help would be appreciated

Mus

Trying To Get Max From A Select Statement?
Hey everyone, I'm running an "insert" statement into a database table (tblJob). Once I run that, I want to immediately run another query to get the last jobID inserted (which should be the one that I just inserted). So I figured I'd use the Max property in a select statement but I keep getting an error. Here's the code I'm using (including the code that inserts the record):


Code:
Set MyRecSet = New ADODB.Recordset
Dim sqlNewJob As String
sqlNewJob = "Insert Into tblJob " & _
"(jobDate, jobTime, jobAssignTime, jobPickUpTime, jobDropOffTime, " & _
"custID, drvID, puID, destID, jobStatus, jobNotes) " & _
"Values " & _
"('" & txtCallDate.Text & "', " & _
"'" & txtCallTime.Text & "', " & _
"'" & Format(Now, "hh:mm AM/PM") & "', " & _
"'', " & _
"'', " & _
"'" & strCustID & "', " & _
"'" & 1 & "', " & _
"'" & 3 & "', " & _
"'" & 1 & "', " & _
"'', " & _
"'')"
MyConn.Execute (sqlNewJob)
Set MyRecSet = New ADODB.Recordset
Dim sqlGetJobID As String
sqlGetJobID = "Select Max(jobID) as max_JobID " & _
"from tblJob"
MyConn.Execute (sqlGetJobID)
txtJobNumber.Text = MyRecSet.Fields("jobID").Value

This is the error that I get:

"Run-time error '3265':

Item cannot be found in the collection corresponding to the requested name or ordinal".

I go into the database and the tblJob table has been updated (with the empty fields that you see in the initial "insert" statement) but I can return the Max field for jobID. What am I doing wrong?

Thanks in advance for any help you can give.

DTFan

Select Statement
y we have to use "select p.col1 from table1; for our sql statement
y use the p.col1?

y dun we just use select col1 from table1;
??

Select Statement
U]confirmer [/u] status statusCount showed
JOHN pending* 44 Y
Steve confirmed 29 N
Karen NULL 09 Y
Bob pending* 11 N


I'm trying to have a select statement that retrieves all records from a certain date as long as status is not NULL and when status equals pending* showed can not be N.

Here is what i have so far.


Code:
SELECT DISTINCT confermer, status, COUNT(status) as statusCount, showed
FROM TS_DATA
WHERE LEFT(CONVERT(varchar, _lastcall, 120), 10) = @date AND Status <> 'none' and Status <> 'never call' and (status = 'pending* and showed <> 'N')
GROUP BY confermer, status, showed
I know its wrong cause its still retrieves records with status = to pending* while showed is 'N'.
Please any help would be appreciated.

Select Statement
I'm trying to use a select statement to retrieve fields from the database and use Where to search for fields in the database. I don't know if I'm using this right because the statement is not finding anything in the database.


Code:
Set filterConnection = New ADODB.Connection
Set adoFilter = New ADODB.Recordset
filterConnection.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=K:Master DBEngineering.mdb"
filterConnection.Open
SQLQuery = "Select DESC1 from [STD PARTS] Where DESC1 = 'Screw'"
adoFilter.Open SQLQuery, filterConnection, adOpenKeyset, adLockOptimistic

Set txtKW1.DataSource = adoFilter
txtKW1.DataField = "DESC1"

Debug.Print adoFilter.EOF & " " & adoFilter.BOF

In this line...

Code:
SQLQuery = "Select DESC1 from [STD PARTS] Where DESC1 = 'Screw'"

DESC1 is a field. [STD PARTS] is the table. DESC1 has thousands of screw's in it but this statement is not finding anything. Am I using this wrong or something?

Need Help With Select Statement
what's wrong with this?
adobau.RecordSource = "SELECT * FROM tblbau WHERE year(Bau_datum) => " & Jahr_beg & "AND year(Bau_datum)<= " & (Jahr_beg + intyears)

Select Between Statement
i dont know what is my problem with regards to this code


Code:
Private Sub Command1_Click()
Set myConn = New ADODB.Connection
myConn.Open (strConn)

Set myRecSet = New ADODB.Recordset
Set myRecSet = myConn.Execute("SELECT * FROM REPO WHERE CMDATE BETWEEN '1/2/2003' AND '6/29/2004'")
If myRecSet.EOF = False Then
MsgBox ("this field is not empty")
Else
MsgBox ("no record")
End If


End Sub
this is the error that has been displayed...

Data type mismatch in criteria expression

what is wrong with my code? i use the between statements... i double check it and its ok...

Select Statement
hi
i m working in VB6 with a database created in MS Access

Set MyRecSetClient = MyConn.Execute("SELECT * FROM client WHERE clientID = '" & cID & "'")

cID is integer, clientID in the DB is 'number'

i m getting type mismatch, i know it gives me that error because it is treating cID as string, but i don't know how to fix it. how can i make it so it treats cID as integer??

Help With Select Statement
I have two tables: Invoices and Payments. I want to select the invoices that are unpaid as of a cutoff date(EC1Date) and the sum of payments made toward those invoices.
The following Select works in that it selects the invoices that are unpaid as of the cutoff date and it sums the payments made toward the invoices. The problem is that it includes all payments made toward the invoices, even those after the cutoff date. Is there a way to limit the payments to those received before the cutoff date? There is a date recvd field in the Payments table. I tried adding "AND Payments.Date <= #" & str(EC1Date) just before the "GROUP BY" but it didn't work. It only returned invoices where partial payments had been received (if no payment had been received then no record was returned)


strShape2 = "SELECT Invoices.InvNum, Invoices.CustomerID, Invoices.InvDate, Invoices.Total, SUM(Payments.Amount) AS [Paid], Invoices.Total - [Paid] AS [Balance] FROM Invoices LEFT JOIN Payments ON Invoices.InvNum = Payments.InvNum WHERE InvDate <= #" & str(EC1Date) & "# AND PaidDate > #" & str(EC1Date) & "# GROUP BY Invoices.CustomerID, Invoices.InvNum, Invoices.Status, Invoices.InvDate, Invoices.Total"


Any help would be greatly appreciated. This statement is actually the APPEND portion of a SHAPE Select statement so if I would like to avoid using SHAPE on this part.

SELECT Statement
I have the following code

If conn.Execute("SELECT * FROM [sheet1$] WHERE EmployeeNo = " & parsed("EmpNum") & " And DepartmentNo = " & parsed("Dept") & "").RecordCount > 0 Then

...code

basically what I want is, if the recordcount from the select statement is greater than 0 then that means the record exists then peform some other operation. But I get the error "Data type mismatch in criteria expression"

Any ideas to why?

SQL Select Statement
I have an Access table with the following fields:
fldID(autonumber),fldLotNo, fldLength, fldDiameter, fldFrom, fldTo

This table has multiple records with the same fldLotNo value. The table is tracking the movements of the fldLotNo's from one warehouse to another. I'm trying to create a query that will select distinct fldLotNo records based on it's highest fldID value, thus giving me each fldLotNo's current warehouse location.

Help!

Thanks,

Jeff Sizemore

Select Statement
hello!

HTML Code:

time labelcode status ufmc
7/18/2007 10:30:08 AMF1594703PP1 UFG1
7/18/2007 10:30:43 AMF1594703OC2 UFG1
7/18/2007 10:31:13 AMF1594703PW2 UFG1
7/18/2007 10:31:48 AMF1594703PX2 UFG1
7/18/2007 10:32:53 AMF1310704UB1 UFG1
7/18/2007 10:31:48 AMF1594703PX2 UFh1
7/18/2007 10:32:53 AMF1310704UB1 UFh1


i want to select all status with the same labelcode and the same ufmc
between time and date with count of 3 consecutives status of 2

how can i get this output?

labelcode =F15947 it has 3 consecutives 2 status count
ufmc = UFG1

pls. help...

Select From Statement Help
Hey all,

I'm having a problem with a select statement. I need to select a set of records that are older than a certain number of days old. I'm using a sql statement to get the records, here's what i have:


VB Code:
Dim daylien, findit As Stringdaylien = DateAdd("d", -30, Now) findit = Format(daylien, "mm/dd/yyyy")   Text1.text = findit    Count = 0    List1.Clear Set Table = frmlein.db.OpenRecordset("SELECT * FROM wrecker WHERE toweddate <= '" & findit & "'")....


needless to say it's not working like i'd like it to. Any ideas?

SQL Select Statement
Hello,
Here is the code that I have:

VB Code:
With cnTeamCMI        .ConnectionString = sConnString        .ConnectionTimeout = 0        .CursorLocation = adUseClient        .Open strConnString    End With        sSQl = "SELECT [وقت الدخول] FROM [توقيتات الصيانة] WHERE (DateDiff(""h"", [وقت الدخول],[وقت الخروج]) = 4 OR DateDiff(""h"", [وقت الخروج],[وقت الدخول]) = 20) AND [تاريخ الدخول] = #" & Format(Now, "mm/dd/yyyy") & "# AND [اسم العامل] = '" & lstSiyana.List(lstSiyana.ListIndex) & "' AND [حضور] IS NULL AND [وقت الدخول] <> NULL AND [وقت الخروج] <> NULL"        Debug.Print sSQl        rsTempRecordSet.Open sSQl, cnTeamCMI, adOpenStatic, adLockReadOnly    With rsTempRecordSet       If Not (.EOF) Then                If Time < .Fields("وقت الخروج") Then            cmdSYPunchin.Enabled = False            cmdSYPunchout.Enabled = True            cmdSYAbsent.Enabled = False            cmdSYHoliday.Enabled = False                Else            cmdSYPunchin.Enabled = True            cmdSYPunchout.Enabled = False            cmdSYAbsent.Enabled = True                End If       Else            cmdSYPunchin.Enabled = True            cmdSYPunchout.Enabled = False            cmdSYAbsent.Enabled = True       End If    End WithrsTempRecordSet.CloseSet rsTempRecordSet = NothingSet cnTeamCMI = Nothing


Basically I am having problems in this part:
If Time < .Fields("وقت الخروج") Then
cmdSYPunchin.Enabled = False
cmdSYPunchout.Enabled = True
cmdSYAbsent.Enabled = False
cmdSYHoliday.Enabled = False
Else
cmdSYPunchin.Enabled = True
cmdSYPunchout.Enabled = False
cmdSYAbsent.Enabled = True
End If
As you may notice is a datetime field in a microsoft access database. What I want is to have the first action done when the real time is less than the time inputted in the field [وقت الخروج]. Else it would do the second action. The error I get when I try to execute this code is cannot find object. Any ideas?

SELECT Statement ?
Hi everyone
I have a problem using SQL statement
As I want to pass an avariable to the statement SELECT
( Select * from Table Where A > avariable) but it doesn't work well. Here the code that i wrote

Private Sub Form_Load()
Dim n3 As Integer
n3 = Txtnumber
MSFlexGrid1.FormatString = "Num1 |" & " Num2 |" & " Num3 |" & " Amount"
Data3.DatabaseName = App.Path & "dbbacon.mdb"

Data3.RecordSource = "select * from Sobacon where Amount > n3 order by Num1"

End Sub

Copyright © 2005-08 www.BigResource.com, All rights reserved