Re: [MSAccessProfessionals] SQL

Paul,

Thanks, I understand.  I hope to give it a try tonight.

 

Bill

 

Bill Singer

 

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Paul Baldy
Sent: Friday, April 9, 2021 10:43 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] SQL

 

The actual code would look exactly like I showed, or at least it could.  rs(0) is the same as rs!FieldName.  It's a shortcut to typing out field names, because I'm lazy.  The number is a zero-based reference to the order of fields in the SELECT statement, so if you had

 

SELECT Field1, Field2...

 

rs(0) would return Field1, rs(1) would return Field2, and so on.  

 

Paul

 

------ Original Message ------

From: "Bill Singer" <Bill.Singer@at-group.net>

To: MSAccessProfessionals@groups.io

Sent: 4/9/2021 6:22:42 AM

Subject: Re: [MSAccessProfessionals] SQL

 

Paul,

Thanks for the info.  I was close.  I will make some adjustments.  I just have one specific question.

On the line Debug.Print rs(0), rs(1), ...

 

Will the actual code say Debug.Print rs   or will I have to put the   (0), rs(1)  .   Maybe I should say that I do not understand the " (0), rs(1) " part.  My reading on the internet did not clarify it for me.  Do I need to list an option for each record or is this something that prints out to indicate what record I am on?

 

Any help you could offer to increase my understanding would be great.

 

 

Bill

Bill Singer

 

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Paul Baldy
Sent: Thursday, April 8, 2021 8:37 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] SQL

 

You can loop the recordset and either message box or debug.print the values.  This is my template code for a loop, with a debug. You can specify field names or use position as I did:

 

  Dim strSQL  As String

  Dim db      As DAO.Database

  Dim rs      As DAO.Recordset

  Set db = CurrentDb()

 

  strSQL = "SELECT...,"

  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF

    Debug.Print rs(0), rs(1), ...

    rs.MoveNext

  Loop

  rs.Close

  set rs = nothing

  set db = nothing

 

Paul

 

------ Original Message ------

From: "Bill Singer" <Bill.Singer@at-group.net>

To: MSAccessProfessionals@groups.io

Sent: 4/8/2021 6:06:15 PM

Subject: [MSAccessProfessionals] SQL

 

In a Sub of Function, If I create a record set using SQL, is there anyway to actually view the results of the SQL Statement.  For example, if I try to pull 4 records, where can I see those 4 records?

 

Before I start to try to manipulate the records I would like to be able to know I am pulling the correct date.

 

Thanks,

Bill Singer

Access 365