Darryl Collins
Darryl.Collins at coles.com.au
Mon May 26 23:56:23 CDT 2008
Hey Darren Yep! :) that works. Many thanks. cheers Darryl. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Darren D Sent: Tuesday, 27 May 2008 2:42 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] RecordSet question... Hi D I think you need to do a With rs1 .MoveLast .MoveFirst to 'populate' the recordset then you should get the .recordCount reporting correctly There may be better ways but that's the one I use mostly HTH Darren ----------------- T: 1300 301 731 -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins Sent: Tuesday, 27 May 2008 2:37 PM To: Access Developers discussion and problem solving Subject: [AccessD] RecordSet question... Sorry for all the easy ones today (heh, at least I hope they are easy). ok.. When I run the following code in the SQL query window I get 11 records returned - which is what I want and expect for the given input variables. ' ----- CODE START -------- iYEAR_VAR = iYEAR1 sSQL = "" sSQL = "SELECT tbl_FY.FY_ID, tbl_FY_Period.FY_P " sSQL = sSQL & "FROM tbl_FY INNER JOIN tbl_FY_Period ON tbl_FY.FY_ID = tbl_FY_Period.FY " sSQL = sSQL & "WHERE (((tbl_FY.FY_ID)=" & iYEAR_VAR & "));" Set rs1 = db.OpenRecordset(sSQL) iRSCount = rs1.RecordCount ' ----- CODE END -------- However the rs1.RecordCount is returning 1, not 11, which doesn't seem correct to me(?). Also what I want to do is this: Find out how many records are in the RS (there should always be 12, except for FY08 where there is 11 - don't ask). Loop Thru all the records and return the data in field "FYP" as a variable and then write the value to a label in a report. The bit I am stuck on is looping thru the recordset. or is there a better way altogether. There are so many ways to make Access do stuff, sometimes I think I choose one that is a fashion of the day for me and flog it, not realising there is a much better/easier way. regards Darryl. This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses. No warranty is made that this material is free from computer virus or any other defect or error. Any loss/damage incurred by using this material is not the sender's responsibility. The sender's entire liability will be limited to resupplying the material. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses. No warranty is made that this material is free from computer virus or any other defect or error. Any loss/damage incurred by using this material is not the sender's responsibility. The sender's entire liability will be limited to resupplying the material.