Recordsets - not my strong suit.

[pdldavis]

Hi, what I am trying to do is this:

I have a table which lists out business months with a business month start and business month end field.

I have a table which lists out quality incidents and the dates the incidents were found.

I would like to cycle through the dates in the quality incidents table one at a time and compare them to the business month date ranges in the business month table.

If a quality incident date does not fall in any of the business month date ranges, then debug.print that date and then test the next quality date, and so on.

What I have prints all quality dates which don't match the business month date range of the first record, and then it moves on.

Any help would be appreciated:

Dim dbs As Database
Dim rst As DAO.Recordset
Dim rstP As DAO.Recordset
Dim StSql As String
Dim stPoints As String
Dim pDate As Date
Dim dCounter As Integer
Dim pCounter As Integer
Dim x As Integer
Dim z As Integer

dCounter = DCount("[monStart]", "tblQirMonths") 'Get a count
pCounter = DCount("[qir]", "qir_table", "[qir] ='yes' AND [error_found_dt] Is Not Null AND [qi_points] >=0.5") 'Get a count

Set dbs = CurrentDb

StSql = "SELECT tblQirMonths.monStart, tblQirMonths.monEnd FROM tblQirMonths;" 'Business Month Dates

stPoints = "SELECT qir_table.qir, qir_table.error_found_dt, qir_table.qi_points FROM qir_table " & _
"WHERE qir_table.qir ='yes' AND qir_table.error_found_dt Is Not Null AND qir_table.qi_points>=0.5;" 'Quality Incidents

Set rst = dbs.OpenRecordset(StSql, dbOpenDynaset) 'Business Months
Set rstP = dbs.OpenRecordset(stPoints, dbOpenDynaset) 'Quality Points

rstP.MoveFirst 'Select first date found in the QI Table

For z = 1 To pCounter 'The number of records where Qir = Yes, the date is not null and has points assinged.

pDate = rstP("[error_found_dt]")

rst.MoveFirst 'Cycle through the table for business months.

For x = 1 To dCounter 'The number of entries in tblQir months

If Not (pDate >= rst("[monStart]") And pDate <= rst("[monEnd]")) Then

Debug.Print pDate

End If

rst.MoveNext

Next

rstP.MoveNext

Next

rst.Close

Set dbs = Nothing

Thanks, Dan

 

[PWise]

you dont need a recordset for this
you can do this in a query

SELECT qir_table.qir, qir_table.error_found_dt, qir_table.qi_points ,tblQirMonths.monStart 
FROM qir_table 
left join (SELECT tblQirMonths.monStart, tblQirMonths.monEnd FROM tblQirMonths )Months
on qir_table.error_found_dt Between tblQirMonths.monStart and tblQirMonths.monEnd 
and qir_table.qir ='yes' 
AND qir_table.error_found_dt Is Not Null 
AND qir_table.qi_points>=0.5

if tblQirMonths.monStart is null then it is not in a month

 

[pdldavis]

Hi, thanks for the response. I am not having any luck with this - getting an error Between operator without And in qury expression 'qir_table.error_found_dt Between Months.monStart'

It looked like a couple fields needed to be aliased so I changed it to this:

SELECT qir_table.qir, qir_table.error_found_dt, qir_table.qi_points ,tblQirMonths.monStart
FROM qir_table
left join (SELECT Months.monStart, Months.monEnd FROM tblQirMonths AS Months)
on qir_table.error_found_dt Between Months.monStart and Months.monEnd
and qir_table.qir ='yes'
AND qir_table.error_found_dt Is Not Null
AND qir_table.qi_points>=0.5

I am also looking for dates in the qir_table which do not fall within the business month date ranges instead of those that do.

Thanks, Dan

 

[traingamer]

[rhetorical question]
How is it that you can have dates that don't fall within your defined business months?
[/rhetorical question]

 

[pdldavis]

Actually it is not a rhetorical question as it applies to this problem.

The dates in the quality table are generated by an outside program. This program throws bogus dates and years, making quality something of an issue....

The people in charge of that department have as of yet been unable to fix their end.

So, what we are trying to do in the interim is capture those bogus dates which then have to be flagged and manually fixed.

Dan

 

[PWise]

you have to move the Alias after the brackets

SELECT qir_table.qir, qir_table.error_found_dt, qir_table.qi_points ,tblQirMonths.monStart 
FROM qir_table 
left join (SELECT Months.monStart, Months.monEnd FROM tblQirMonths )[COLOR=red] As months [/color]
on qir_table.error_found_dt Between Months.monStart and Months.monEnd 
and qir_table.qir ='yes' 
AND qir_table.error_found_dt Is Not Null 
AND qir_table.qi_points>=0.5