SQL Question

[Daveyk01]

Why does the following report (correctly) 2 records:
strSQL = "SELECT * FROM SERVICERECORDCALEQUIP WHERE RANum like " & TempRA & " ORDER BY RANum;"

and this statement on finds one record:
strSQL = "SELECT * FROM SERVICERECORDCALEQUIP WHERE RANum = " & TempRA & " ORDER BY RANum;"

Code Chunk:
strSQL = "SELECT * FROM SERVICERECORDCALEQUIP WHERE RANum like " & TempRA & " ORDER BY RANum;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then

[RANum] is an indexed number field, LONG, in the SERVICERECORDCALEQUIP table. It is also a primary key with duplicates allowed. For the particular RANUM two record do exist.

...little things like this drive me nuts...

 

[LPurvis]

You say that two rows is the correct return count. OK...
What text is being passed in the TempRA variable?

What really confuses me is the statement:
"[RANum] is an indexed number field, LONG, in the SERVICERECORDCALEQUIP table. It is also a primary key with duplicates allowed"

>> "a primary key with duplicates allowed"
Is a physical impossibility. Do you mean a Foreign key?

I presume you're fully populating your recordset before attempting the count?
Have you verified the results using SQL statements executed in the query window?

 

[Kenln]

is it possible the in the tow different records that TempRA or the RANum are not identical but rather begin with the same digits. Using the 'Like' might then choose records that begin similar and not using it required an exact match.

I am not sure but it's a guess.

 

[Daveyk01]

Interesting, this table had two "KEYS" in it. One to the left of "RANum" and one to the left of "SerialNO". That shouldn't be possible should it? I wish I could paste a screen shot in to the message.

You say that two rows is the correct return count. OK...
What text is being passed in the TempRA variable?

What really confuses me is the statement:
"[RANum] is an indexed number field, LONG, in the SERVICERECORDCALEQUIP table. It is also a primary key with duplicates allowed"

>> "a primary key with duplicates allowed"
Is a physical impossibility. Do you mean a Foreign key?

I presume you're fully populating your recordset before attempting the count?
Have you verified the results using SQL statements executed in the query window?

 

[Daveyk01]

TempRA is dimensioned as LONG

The field I am searching is a Number, Long Integer.

It's strange that it even finds 1 record via the first method.

is it possible the in the tow different records that TempRA or the RANum are not identical but rather begin with the same digits. Using the 'Like' might then choose records that begin similar and not using it required an exact match.

I am not sure but it's a guess.

 

[LPurvis]

Hi.
No it has one Primary Key - but it consists of more than one field (entirely possible - though IME not a convenient practice).

Shouldn't be relevant at this point. What about the other points I mention?

 

[Daveyk01]

Hi.
No it has one Primary Key - but it consists of more than one field (entirely possible - though IME not a convenient practice).

Shouldn't be relevant at this point. What about the other points I mention?

See attachment.

Wierd looking table, eh? Don't know how that happened (before my time <grin>). I will try correcting the primary key and see what that does.

Not sure how my first method even saw one record.

 

[LPurvis]

>> "Wierd looking table, eh"

No - that's what I was saying. Entirely acceptable.
But the answers to the other questions I mention are relevant.
Attaching more than a screenshot of the table design might just be quicker.

 

[Daveyk01]

[What text is being passed in the TempRA variable?]

TempRA is dimensioned as LONG (that is why I don't understand why the "like" statement worked. I thought that was for text. I just tries it on a whim.

[Is a physical impossibility. Do you mean a Foreign key?]

Don't know what a Foreign key is. You've seen the jpg.

[I presume you're fully populating your recordset before attempting the count?]

Yes.


[Have you verified the results using SQL statements executed in the query window?]

Yes. Also the second example picked up correctly the two records with the RANum that was requested. That table has 130166 records in it currently.

Dave

 

[LPurvis]

Are you actually saying now that the second example correctly found the two rows?
(i.e. the equals operator and the like only one?)

Since your data type is numeric - Like is requiring an implicit type conversion (the sort of thing Access, VBA and Jet does a lot without users realising.
Equals would be the more appropriate.
Obviously I can't see all 130,000 records. If you can attach a cut down (and sensitive data removed) version I'll have a peek.

 

[Daveyk01]

"LIKE" FOUND 2

"=" FOUND 1

That is what I am saying makes no sence. I have to wait to kick everyone out of the backend and modify that strange KEY setup and then try it again.

Dave

Are you actually saying now that the second example correctly found the two rows?
(i.e. the equals operator and the like only one?)

Since your data type is numeric - Like is requiring an implicit type conversion (the sort of thing Access, VBA and Jet does a lot without users realising.
Equals would be the more appropriate.
Obviously I can't see all 130,000 records. If you can attach a cut down (and sensitive data removed) version I'll have a peek.

 

[LPurvis]

I must reiterate that there isn't necessarily anything "strange" about that PK setup.
It's very likely been made that way for a reason - the two fields combined must comprise a unique identifier for your table. Attempting to coerce it onto the one field would then fail.

A much better use of getting a copy of the back end would be to strip it down as suggested (except for the two fields expected) and post that table in an compacted MDB.

 

[Daveyk01]

No, there is no need even to have a promary key in that table. I will of course be carefull about that and back-up, backup, as I always do, now.

That table is a sub-table to a main work table. It collects a list of equipment used to perform work referenced in the main table. It could contain 1 record for a given RANum, or 20 records with the same RANum. The only important link is the [RANum]. I have no idea why that [SerialNo] field would have been combined with the RANum field for one primary key. There are no RELATIONSHIPS setup between tables. That is all controlled via code and queries.

The backend has crapped out several times and been repaired; once I had to send it to an outfit in the UK which got it back up and running for us in no time. Maybe somewhere along the several crap-outs <grin> that key got messed up. I don't know. I will find out later if that is the fix when I get everybody booted.

Dave

I must reiterate that there isn't necessarily anything "strange" about that PK setup.
It's very likely been made that way for a reason - the two fields combined must comprise a unique identifier for your table. Attempting to coerce it onto the one field would then fail.

A much better use of getting a copy of the back end would be to strip it down as suggested (except for the two fields expected) and post that table in an compacted MDB.

 

[LPurvis]

*Every* table should have a Primary Key.
If you want my advise... even when you create a table that is for a quick one off - you have no plans for it to do anything, become anything, go anywhere... Give it a Primary Key! A table without a PK is naked and vulnerable.
A single Autonumber (/Identity) field is always good enough for me.

Generally - recovery services can sometimes result in losing primary keys (and indexes in general) in your tables, but not adding them. (It would require some thought to add one - which is extra time they needn't bother with ;-)

Relationships are also worth providing in a database.
When I get a db to look at without relationships - it just makes it so much harder to analyze. Using code and queries to give user friendly warning before violations are raised is all well and good - but is no substitute for implementing rules at the data level too.

Finally to mention again - a look at the data might prove useful here.