[RESOLVED] recorset

[Dbee]

What I am doing is extracting data on an employees sign in time and amount of hours worked extracting data by month and day..

Ok I have this a s recordset

Which works like a charm

Dim Acct, Days$, StrSql As String
Set Record_Sets = New ADODB.Recordset
Acct = Val(RRR!Number)
Days = Trim(Str(D))
 StrSql = "SELECT  Month,Start_day,Year,Start_time,Stop_Time,Hours,Min,Overtime " _
         & "FROM Pay_Hours " _
         & "WHERE Number = '" & Acct & "' And Start_Day ='" & Trim(Str(D)) & "' _
             And Month ='" & Trim(Str(S)) & "' "
 Record_Sets.Open StrSql, Data_Connection(1), adOpenStatic, adLockReadOnly, adCmdText
 Extract
 Record_Sets.Close

I would like to extract from this recordset a different way than this if possible.

Sub Extractor()
 First_time = 0
 Do While Not Record_Sets.EOF
  col = 0
  For Colx = 0 To Record_Sets.Fields.Count - 1
   If First_time > 0 Then Exit For
   If Val(Record_Sets.Fields(0).Value & "") = S _
    And Val(Record_Sets.Fields(1).Value & "") = D Then
    Found = 1
    Select Case Colx
     Case 2
      Sdata(Rows(1), 2) = Record_Sets.Fields(3).Value & ""
     Case 3
      Sdata(Rows(1), 3) = Record_Sets.Fields(4).Value & ""
     Case 4
      Sdata(Rows(1), 5) = Record_Sets.Fields(5).Value & ""
    Case 5
      Sdata(Rows(1), 6) = Record_Sets.Fields(6).Value & ""
    Case 7
      Sdata(Rows(1), 7) = Record_Sets.Fields(7).Value & ""
    End Select
   End If
  Next
  Record_Sets.MoveNext
  First_time = 1 
End Sub

The Sdata is for print outs
The reason for the First_Time Integer is the extractor would loop twice on me.

One more question does all of the data in a database have to be a variable in order for the recordset to work in the SQl select statement.

[Hack]

One more question does all of the data in a database have to be a variable in order for the recordset to work in the SQl select statement.

Naw, you can easily hard code values if you need to.

[Dbee]

Well Hack when I tryed to use the recordset as shown. I got errors in the recordset until I went into the database with Access and changed the fields to variables then it worked.

Must be a special call to return integer's

[si_the_geek]

I presume you meant that you were getting an error along the lines of "Data type mismatch", and to get past it you changed the fields from Numeric to Text - which wasn't actually needed.

The problem was simply that you put the values into the SQL statement in a way that says they are Text values rather than Numeric values. That was because they had ' marks around them (as explained in How do I use values (numbers, strings, dates) in SQL statements?).

For numeric values you don't use ' around them, eg:

         & "WHERE Number = " & Acct & " And Start_Day =" & CStr(D) & _
             "And Month = " & Trim(Str(S)) & " "

(note the change from Trim(Str( )) to CStr( ) - which is the proper equivalent as I explained in another thread).

Note that it isn't a good idea to have a field called Month (or Year or Day or Min), as those are reserved words, and can therefore using those names cause lots of bugs/errors.

[Dbee]

The Area I get the error in is the first part of the select

"SELECT  Month,Start_day,Year,Start_time,Stop_Time,Hours,Min,Overtime

It nay be from the reserve words I will change the design and let you know the results.

Now for the second Part is there a better way of getting the field data for the recordset.

[Dbee]

I tryed this record set and received "DATA Type mismatch in criteria expression"

This is the code for the record set and Month_num = Integer in database.

This is what I am talking about all criteria calls have to be Variables. Is that correct?

Dim Acct, StrSql As String
 Acct = Val(RRR!Number)
 StrSql = "SELECT  Month_Num,Year_Num,Start,Ends " _ 'Fields were my data is
        & "FROM Months " _
        & "WHERE Employee = '" & Acct & "' And Month_Num ='" & CStr(Month) & "' "
 Record_Sets.Open StrSql, Data_Connection(2), adOpenStatic, adLockReadOnly, adCmdText 
 Record_Sets.Close

[si_the_geek]

The Area I get the error in is the first part of the select

To be getting an error on the Select statement it is not a problem with the SQL itself, but simply building the string.

As you have used line continuations, you cannot tell which of the lines the error relates to (as far as VB is concerned all of those lines are one line - the separation is just to help us read it).

It looks to me as if the problem was something that I accidentally fixed in my previous post - you didn't have a " at the start of the final line, and were also missing one at the end of the previous line.

I tryed this record set and received "DATA Type mismatch in criteria expression"

That is what I was referring to in my previous post - see the changes I made, and the link I posted.

This is what I am talking about all criteria calls have to be Variables. Is that correct?

I'm not sure what you mean by that.. your criteria are all variables, the problem is with the data type - in your SQL statement you specified that they are Text/String data, when in fact the field is Numeric.

Now for the second Part is there a better way of getting the field data for the recordset.

Your Extractor routine can be simplified to this equivalent (I have no idea what you were trying to achieve with the For loop):

Sub Extractor()

If Not Record_Sets.EOF Then
  'col = 0
  If Val(Record_Sets.Fields(0).Value & "") = S _
  And Val(Record_Sets.Fields(1).Value & "") = D Then
    'Found = 1
    Sdata(Rows(1), 2) = Record_Sets.Fields(3).Value & ""
    Sdata(Rows(1), 3) = Record_Sets.Fields(4).Value & ""
    Sdata(Rows(1), 5) = Record_Sets.Fields(5).Value & ""
    Sdata(Rows(1), 6) = Record_Sets.Fields(6).Value & ""
    Sdata(Rows(1), 7) = Record_Sets.Fields(7).Value & ""
  End If
  'Record_Sets.MoveLast
  'First_time = 1
End If

End Sub

..the commented parts are probably not needed, but could have an effect elsewhere (such as if you use the variable First_time somewhere else).

The inner IF statement (checking against S and D) is something that should be covered by your SQL statement (the Where clause), so all you should need is the Sdata lines and the outer IF statement.

[Dbee]

Ok Thanks

Now I have tryed the * in selecting fields from a table and found that all fields must be variables not Integers.

I get the criteria error if I do not have them set to variables.

I am talking about this part of the "SELECT M onth_Num,Year_Num,Start,Ends "
Sql.

I have look the net over trying to find any reference to this.

As For the LOOPs that was for multi Records

At present I Use For /Next to find my data thought this might be a better way for me to use see that you could eliminate alot of what if"s

Again thanks.

[si_the_geek]

You are mis-using the word Variable, and I don't actually know what you actually mean by it.

Variables are not used in the database at all, they are what you use in your code (such as Found and First_time) which can be one of many different data types (Integer/String/Date/....).


Using "Select *" or not should be irrelevant, and if that change by itself gets rid of the Mismatch error, the problem is one of those field names being a reserved word.

If changing that part does not affect it, you need to make the changes I showed back in post #4.

As For the LOOPs that was for multi Records

The loops were actually useless I'm afraid.. the For loop did nothing except waste time (the If S/D and Sdata lines I showed get exactly the same result), and due to your use of First_time the Do loop would never actually loop (thus the If .EOF is equivalent).

[Dbee]

In the design view in access the Month_Num,Year_Num are Long Integers and required is set to NO.

I was using the routine to obtain all of the entry's for the month and day of the year they were put into the database first time was for the month of September and when it went though for Oct it loop twice for some reason. I put the First_time in the loop to stop that.

I thought that recordset count might have added to its self but found it did not.
But it still looped on me twice. I ran debug on this.

Let me add this I am putting all the data in a MSHFlexgrid By cell and row. as it comes from the data base using the SQl call. Found it is a time saver.

You got to remember I am self taught as a programmer still in the learning stage at 65 use to the old ways of programming in DOS.

I still use the random method of file data. Don't grasp the database concept any other way as of now.

[Dbee]

The integers I have can only be used as Pointers in the Select String. Took a rock falling on my head but it did dawn on me what you were saying.

[si_the_geek]

You got to remember I am self taught as a programmer still in the learning stage at 65 use to the old ways of programming in DOS.

I still use the random method of file data. Don't grasp the database concept any other way as of now.

I remember that from previous threads, and am being careful to not push you too hard/fast. The power of databases is immense, but it will be a while until you are ready to use it all.

For now it is probably best to just do the kind of things in this thread already - using an SQL statement to get just the fields and rows you want from a single table, like this one which gets the 4 fields for all rows which have the apt values of Employee and Month_Num:

 StrSql = "SELECT Month_Num, Year_Num, Start, Ends " _ 
        & "FROM Months " _
        & "WHERE Employee = " & Acct & " And Month_Num = " & CStr(Month) & " "

With a change to the first line, you can use the Count function to return a single value (just one column and one row), which says how many records there are for that Employee and Month_Num:

 StrSql = "SELECT Count(*) " _

..or use the Max function to find out the highest value for Ends for that Employee and Month_Num:

 StrSql = "SELECT Max(Ends) " _

Each time the work is done by the database system - you just need to give it the right instructions to get what you want.



I'd recommend a few articles from the SQL section of our FAQs (not just for now, but to keep as a reference), some of which you have seen already:

• http://sqlcourse.com/select.html - creating normal Select statements
• http://sqlcourse2.com/agg_functions.html - using functions like Count and Max
• http://sqlcourse2.com/orderby.html - using Order By to sort the results
• How do I use values (numbers, strings, dates) in SQL statements?
• How can I put the value of a variable/control into my SQL statement?
• How can I find out why my SQL statement isn't working?

The ones at SQLCourse include some exercises you can do (based on example tables they give you), which you can type into the page and run.

[Dbee]

Thanks again Si for your patience and shearing your knowledge with me.