ADO Recordset - Add a new field using vba

Question

Hello,

I'm currently using  VBA to query a table from a database which has fields for 'DateTime' and 'Duration' and returns a recordset.

I would like to take the query one step further and add two additional fields to the recordset. These being:

• A field to assign either 'Day' or 'Night' with corresonds to the Date/Times given in the DateTime column.
• A field to convert the duration from seconds to minutes

I have been reading up on using "Append"  to add fields to an ADO recordset, but can't seem to understand it.

This is a brief summary of how my code is structured (which works for extracting data):

This is the dewsired final outcome (the two fields to be added are highlighted in red)

Any help would be very much apprecited :)

Answer 1

Hi Bill , thanks for your reply. I ended up going about it a different - I wrote the record set to an array with two additional columns and populated the two additional columns with what I need. But thank you so very much for responding!

Answer 2

The three bits of information that would be useful are what appears after sqlstring = , where you have given us . . ., and what your definitions of Night and Day are (the data shows that night starts somewhere between 11:25 and 18:56 and ends somewhere between 22:33 and 06:26, and what strConn is (ie what type of database do we have since SQL is to some degree database dependent).

So, I will assume that the sqlstring is

"SELECT Case, DateTime, Duration As [Duration (Seconds)] FROM MyTable", that night runs from 18:00 to 06:00 and that the database is in Access

So just change the SQLString to

"SELECT [Case], [DateTime], Duration As [Duration (Seconds)], IIf([DateTime]-Int([DateTime])<0.25 Or [DateTime]-Int([DateTime])>0.75,'Night','Day'), CInt(Duration/60) As [Duration (Minutes)] FROM MyTable"

If my assumptions are wrong, please show us SQLString, strConn and define day and night.