SQL problems.......

[fenris]

I have an access 2000 database with a few tables in it. The I wrote the sql statement below to access two tables. I am not sure if the statement is correct. I know it doesn't work. I have a table of hours with a dataID field that relates to a table called dates which also has a field called dateID, it also has a field called Date which contains the actual dates (ie MM/DD/YYYY). What I want to do is create an sql statement that will return all the records in the Hours table that fall between certain dates in other words all the dates including the starting and ending points.

Note: the sql statement below is formated for VB6 (I am not sure if that makes a difference?)
================================
sql = "SELECT Hours.*,Dates.* FROM Hours,Dates WHERE ((hours.DateID = Dates.DateID) AND (Dates.Date) Between #" & CDate(startTime) & "# And #" & CDate(endTime) & "#)"
=================================
The statement generates no errors but it selects the first record in the database (outside of the range I would like).

Any help would be appreciated....


Troy Williams B.Eng.
fenris@hotmail.com

 

[swilliams]

1. It sounds like you should join the two tables together if the dateID field in the Hours table is a foreign key to the dateID field in the Dates table.

sql = "SELECT Hours.*, Dates.* FROM Hours INNER JOIN Dates ON Hours.dateID = Dates.dateID WHERE ..."

2. What do the variables startTime and endTime actually hold??
Do they hold just times, or dates and times??

3. What does the field Date (in Dates table) hold??
Does it hold (i) just dates, (ii) dates and times or (iii) just times??

Answers to these will lead me to the where clause.

Simon

 

[TRYP]

sql = "SELECT Hours.*, Dates.* FROM Hours,Dates
WHERE
(
(hours.DateID = Dates.DateID)
AND
(
((Dates.Date) > #" & CDate(startTime) & "# )
And
((Dates.Date)< #&quot; & CDate(endTime) & &quot;#)
)
)&quot;

try this
-tryp

 

[fenris]

Thank you for the suggestions. They both appear to work but they only return 1 record, that is the first record within the time period..

I tried using both suggestions, but they do the same thing. If you notice there is a temp variable that is used to inspect the number of records that are selected by setting a breakpoint. I am not sure what is going on.

The startTime and endTime both contain only dates of the format MM/DD/YYYY. Any help is appreciated.

'====================================

Public Property Get getRecordSet(startTime As Date, endTime As Date) As Recordset
Dim sql As String
Dim rst As Recordset

'the sql statement below works...
sql = &quot;SELECT Dates.*,Hours.* FROM Dates INNER JOIN Hours ON Dates.DateID = Hours.DateID WHERE (((Date) BETWEEN #&quot; & startTime & &quot;# AND #&quot; & endTime & &quot;# ))&quot; ' ORDER BY Dates DESC&quot;


'The sequel statement below works
'sql = &quot;SELECT Hours.* FROM Hours,Dates WHERE ((Hours.DateID = Dates.DateID) ) AND ((Dates.Date > #&quot; & startTime & &quot;# ) AND (Dates.Date < #&quot; & endTime & &quot;#)))&quot;
Set rst = dbDatabase.OpenRecordset(sql)

'The code below is used to allow me to see how many records were found...
Dim temp As Long
temp = rst.RecordCount
Set getRecordSet = rst

End Property
'====================================



Troy Williams B.Eng.
fenris@hotmail.com

 

[swilliams]

The RecordCount of the Recordset will not be accurate until you have moved to the last record. You can only use the recordcount property to see if anything was returned (no records or some records in this case) - BUT IT NOT A GOOD IDEA TO DO THIS; YOU SHOULD USE rs.EOF INSTEAD, but this is an aside to your problem.

If you only want the number of records returned, and are not interested in the actual records, you could (and I recommend) change the sql statement to &quot;SELECT Count(field_name) AS NoOfRecords ....&quot; and then use temp = rs(&quot;NoOfRecords&quot instead of using the RecordCount property.

However, if you are interested in the records as well, you will have to do rs.MoveLast and rs.MoveFirst before you do temp = rs.RecordCount

This is not a very good way of doing things due to the excessive overheads involved.

Simon

 

[fenris]

Simon,
I managed to stumble accross the answer on my own. Thank you very much for your answer.

The idea behind the property, was not to get the number of records, but to actually get the records.

This snippet of code:

Dim temp As Long
temp = rst.RecordCount
Set getRecordSet = rst

Was simply used to test and see if anything was being retrieved. In my revised edition, I removed the temp variable and the temp = rst.recordcount.


<Quote>
However, if you are interested in the records as well, you will have to do rs.MoveLast and rs.MoveFirst before you do temp = rs.RecordCount

This is not a very good way of doing things due to the excessive overheads involved.
</Quote>

I am interested in hearing about how I would go about making things work a little better..


The property above is part of a class that is used to encapsulate the database functions. That particular property is used to return a specific set of records based on a starting and ending date.




Troy Williams B.Eng.
fenris@hotmail.com

 

[swilliams]

I have not done any testing of this, but it is my gut feeling that doing a count of the records (ie SELECT Count(*) FROM ...) will be quicker than doing a MoveLast &amp; MoveFirst.

I feel this way, as the SELECT Count(*) ... will be done in the native database and the result returned to your application, whereas a MoveLast &amp; Movefirst will be done by your code in the recordset object. (If I haven't got things horribly wrong!!)

Simon