[RESOLVED] Select By Date

[Dbee]

I got a database that has 492 records in it I am trying to extract from that database by employee Number, date. need to do this by day so I can display there time in a flexgrid.

For a set period of time like 9-22-2008 to 10-5-2008

I know there is a set method for doing this just wondering if I use recordset or another call.

Would I have to call the select data needed By employee number and date.

As it is I am scanning the entire database to get these records.



Tryed Using This

Dim Acct$, Days$
Set Record_Sets = New ADODB.Recordset
Acct = RRR!Number
Days$ = Trim(Str(D))
Record_Sets.Open "SELECT Start_Day,Start_Time,Stop_Time,Hours,Min,Overtime from Pay_Hours WHERE Account = '" & Acct & "' ORDER BY " & Days & "'", Data_Connection(1), adOpenStatic, adLockReadOnly, adCmdText

[dee-u]

You could try a query like...

SELECT * FROM Table WHERE DateField BETWEEN #date1# AND #date2#

I think you will need to enclose the dates with # when using Access? I hope that helps...

[gep13]

Hey,

Use a WHERE clause on the query that you executing to only pull back a subset of the information in the database.

Gary

[si_the_geek]

Duplicate threads merged - please post each question only once (if you want to add more info, edit your original post or add a reply)

What dee-u showed is close, but you need to be careful when putting values (especially dates) into SQL statements. For an explanation and examples of how to do it properly, see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)


By the way, it is always a good idea to build the SQL statement in a separate string first, rather than just putting it directly into the parameter (that way you can check what is wrong with the SQL statement if it fails, etc). With your original SQL statement, I would have done it like this:

Dim strSQL as String
  strSQL = "SELECT Start_Day,Start_Time,Stop_Time,Hours,Min,Overtime " _
         & "FROM Pay_Hours " _
         & "WHERE Account = '" & Acct & "' " _
         & "ORDER BY " & Days & "'"
Record_Sets.Open strSQL, Data_Connection(1), adOpenStatic, adLockReadOnly, adCmdText

Note also that using Trim(Str( )) is wasteful - rather than converting to a padded string and then trimming it, it is better to just convert to a string without the padding, ie: CStr( )

Depending on what D is, that may well be an inappropriate thing to do anyway - hopefully the article I linked to will help you correct that if needed.

[Dbee]

Trying to get the record for that Employee for a one day of the pay period

Thats what day is.

Of course I get an error trying this.

Presently I take apart the start day and stop date of the period and extracting the mont and the day of the start Day amd Stop day of each period

I use

 For s=Start_month to stop_month' To detemant the month of the period
  If S > Val(Start_Month) And Start_Period > Stop_Period Then
    Z = 1
    Xp = Stop_Period     'Day that Period Stops
   ElseIf Start_Period > Stop_Period Then
    Z = Start_Period       ' Day that period starts
    Xp = Day_Amount(S)'Total Days for that month
   Else
    Z = Start_Period
    Xp = Stop_Period
   End If 
   For D = Z To Xp            'Day fo the period
     Extractor' Recors set Call   ' I am trying to use D as the day of the period  Here
  Next
 Next

[Dbee]

I get error in string in query experssion '22'

This is the day I of the period.

I use the where clause for the employee number.

[si_the_geek]

It doesn't matter what Day$ is, as that is not actually relevant to what you are trying to achieve (only to how you were trying to achieve it).

What matters is what D is. Assuming that it is a variable with a data type of Date, the following is likely to be what you want:

  strSQL = "SELECT Start_Day,Start_Time,Stop_Time,Hours,Min,Overtime " _
         & "FROM Pay_Hours " _
         & "WHERE Account = '" & Acct & "' " _
         & "  AND Start_Day BETWEEN #" & Format(D,"yyyy-mm-dd") & "# AND #" & Format(D,"yyyy-mm-dd") & "# "

If D is a String, what you need to do depends on how you put the value into it.

[Dbee]

The D is and Integer that I convert to a string.and I only want to get Info for that Day and Month.

Each being a separate field in the database.

Month
Start_Day
year

SO would I have to use the format for each filed

[Dbee]

Found Problem Fields Were Number instead of strings

Ok I give up Thanks All

[dee-u]

Found Problem Fields Were Number instead of strings

Ok I give up Thanks All

You found the problem already and you are giving up?