SQL Help, Data Type Mismatch

[thspimpolds]

I posted earlier about trying to find missing numberin series and I got
overwhelming help, thank you. However I am having trouble implementing
the ideas. I am using two SQL statements, the frist of which works
perfectly. The first statement selects all the records which have the
same category as the form has selected. The second statement should go
through the selected records and find the first numeric value missing
the series. I would really appreciate any help. When this is run I
get a "type mismatch" error. Does it have to do with the modified RS i
am using?


Code:

' Selects the values which have the same category
strSql = "select * from tbl_Item_Info where Category = '" &
Me.Category & "'"

Set rs = CurrentDb.OpenRecordset(strSql)
Set dbo = CurrentDb

' Should select the null values and output to query

strSQL2 = "SELECT rs.TCR_Number from tbl_Item_Info WHERE
rs.TCR_Number IS Null"
Set qdf = dbo.CreateQueryDef("qry_Next_TCR", strSQL2)

rs.Close
dbo.Close

 

[Allen Browne]

That's not going to work.

You are opening a recordset (which closes at the end of the procedure), and
then trying to use that recordset in a saved query that runs after the
recordset is closed?


A SQL statement like this should do the job:

strSql = "SELECT tblI_Item_Info.* FROM tbl_Item_Info WHERE (Category = """ &
Me.Category & """) AND (tbl_Item_Info.TRC_Number Is Null);"

 

[thspimpolds]

thank you much, you are my hero