Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

SQL query returning EOF

Copper Contributor

Editing a state data field.  Hitting a table called "States".  If i get a hit then data entry is good otherwise invalid entry.  So only needs to return one record.  When i hardcode the state ""KS"" it works fine.  Use the string variable, EOF.   Print the variable in immediate window value is KS.   Stop execution and point at the variable value is, "KS" with the quotes, and throws EOF.  

The query runs executes perfectly in query designer.   RecCount = -1.  Very simple query.   Very confused.  Please help!!

 

Set objRecordset = New ADODB.Recordset
objRecordset.ActiveConnection = CurrentProject.Connection
'objRecordset.Open SQLval
objRecordset.Open "SELECT States.[State Abbreviation], States.State FROM States WHERE (((States.[State Abbreviation]) = [State]));"
RecCount = objRecordset.RecordCount
If objRecordset.EOF = True Then

2 Replies

d@SilverDevil 

 

you can use DAO.Recordset also.

your current query will not include Null (empty) for [State Abbreviation] Or [State].

always test for both .BOF And .EOF.

 

@arnel_gp   Thanks for your response!    I will convert this to DAO and see if it handles the query better.  Will also check for both EOF and BOF.   I hope this helps.  I will be a couple days before i can get back to this.   I will respond again.   Again, thanks.