Recordset Question

[ckeener]

I have a question about DAO recordsets that is sort of baffling me. If anyone has run into this or it is a common problem, here is the problem.

strSQL = "SELECT * FROM tblLogSongs WHERE LogRef = " & TempRef & _
    "AND SongPosition = " & Position
Set rst = db.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then

When I run this code the rst.recordcount is 26, which is correct only if I am using the first part of the strSQL -

"WHERE LogRef = " & TempRef

When I add the "AND" part, the recordcount should only = 1

However when I use this Code -

strSQL = "SELECT * FROM tblLogSongs WHERE LogRef = " & TempRef & _
    " AND SongRef = " & TempRef2
Set rst = db.OpenRecordset(strSQL)

- the recordcount = 1 just like it is supposed to.

I have racked my brain, I have run the queries with the strSQL statements but to no reward.

I am confused as to why my first set of code returns the wrong recordcount and the second set of code returns the correct recordcount.

Caleb

 

[Blorf]

I am just making a guess, and please forgive me if this is a typo, but....

There is no space before the And in

strSQL = "SELECT * FROM tblLogSongs WHERE LogRef = " & TempRef & _
"AND SongPosition = " & Position
Set rst = db.OpenRecordset(strSQL)

So, if Tempref = "Hello" and Position = "World", then your record set would be:

SELECT * FROM tblLogSongs WHERE LogRef = HelloAND SongPosition = World

No spece between hello and "And" giving you HelloAnd

ChaZ

Ascii dumb question, get a dumb Ansi

 

[ckeener]

Thanks ChaZ,

That was a typo on my part as I put in the post...Sorry. There should be a space before the AND.

strSQL = "SELECT * FROM tblLogSongs WHERE LogRef = " & TempRef & _
    " AND SongPosition = " & Position
Set rst = db.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then

Good eyes though! I thought I double checked for typos. Oh well.

Caleb

 

[Blorf]

Then one last thought. Are LogRef and SongPosition text fields?

If so, Try changing it to

'" & TempRef & _
"' AND SongPosition = '" & Position & "'"

Just a thought.


Ascii dumb question, get a dumb Ansi

 

[mkasson]

Caleb,

When I have an issue with a built up string like you have, I MsgBox it out. If Blorf turns out to be correct that quotes are neccesary, it might have jumped out at you if you saw this:

SELECT * FROM tblLogSongs WHERE LogRef = 9431 AND SongPosition = This Pos Here

Similarly, if there had been no space before the AND, it would also have jumped out.

Good luck.

- MSK

 

[Blorf]

Good point. Often I do the same, or I use a watch. Either way, sometimes see the problem in big neon letters that way.




Ascii dumb question, get a dumb Ansi

 

[ckeener]

Thanks guys,

I guess the consensus here is that rst should do properly what I am trying to do and that the problem lies in my SQL or my code somewhere. Thanks for you insight, I will look harder to see what I can. Thanks for the debugging tip mkasson, I will try it.

Caleb