UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Recordset Does Not Return Eof Or Bof = True, Access 2003    
 
   
TomS
post Nov 30 2017, 08:01 PM
Post#1



Posts: 71
Joined: 29-January 03



I am creating a recordset to find the last log number of a year. If there are no records found then I want to create a log number starting at "0001". I have one recordset that works and another that doesn't. Can someone explain why the SQL for the second recordset does not work:

First recordset - returns correct result of EOF or BOF = true:

Set rst = New ADODB.Recordset
Set cnn = CurrentProject.Connection

str_year = Format(Me.txt_start_date, "yyyy")

str_sql = "SELECT Max(CInt(Mid([log_number],1,4))) AS Expr2
" FROM tbl_inspection" & _
" HAVING (((Max(CInt(Mid([log_number],1,4))))=2018));"

rst.Open str_sql, cnn, adOpenDynamic, adLockOptimistic

If rst.BOF And rst.EOF Then


Second recordset - returns incorrect result of EOF or BOF = False

Set rst = New ADODB.Recordset
Set cnn = CurrentProject.Connection

str_year = Format(Me.txt_start_date, "yyyy")

str_sql = "SELECT Max(Right([log_number],4)) AS max_log_number " & _
"FROM tbl_inspection " & _
"WHERE (((Mid([log_number],1,4))= " & str_year & "));"

rst.Open str_sql, cnn, adOpenDynamic, adLockOptimistic

If rst.BOF And rst.EOF Then
Go to the top of the page
 
Jeff B.
post Nov 30 2017, 08:36 PM
Post#2


UtterAccess VIP
Posts: 9,880
Joined: 30-April 10
From: Pacific NorthWet


What happens when you use the SQL expression in an Access query?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
GroverParkGeorge
post Nov 30 2017, 08:58 PM
Post#3


UA Admin
Posts: 31,190
Joined: 20-June 02
From: Newcastle, WA


Why are you converting the year to a string?

--------------------
Go to the top of the page
 
TomS
post Nov 30 2017, 09:59 PM
Post#4



Posts: 71
Joined: 29-January 03



When I use the SQL in an Access query, it returns zero records. Year is converted to string because the log number format is: YYYY-0000. So each year would start with a log number starting with 0001. So first log number in 2018 would be: 2018-0001.
Go to the top of the page
 
Jeff B.
post Dec 1 2017, 09:11 AM
Post#5


UtterAccess VIP
Posts: 9,880
Joined: 30-April 10
From: Pacific NorthWet


If the query returns no records, that's happening whether you use the SQL in a query or in code. Try modifying your query until it does what you want, then use the SQL from THAT.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
HairyBob
post Dec 1 2017, 09:20 AM
Post#6



Posts: 872
Joined: 26-March 08
From: London


When I tried this:

CODE
SELECT Max(Right([log_number], 4)) AS max_log_number FROM tbl_inspection HAVING Max(CInt(Mid([log_number], 1, 4))) = 2018


No rows were returned; however:

CODE
SELECT Max(Right([log_number], 4)) AS max_log_number FROM tbl_inspection WHERE Mid(log_number, 1, 4) = 2018


Does in fact return a row with null in the column - that's why BOF and EOF are both False (see attachment).

HTH...

Hairy.
Attached File(s)
Attached File  Demo.zip ( 51.98K )Number of downloads: 1
 

--------------------
If it was easy, It wouldn't be fun!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    10th December 2017 - 07:05 PM