work with two recordsests

[ben1988]

i'm so desperate.. i tried a lot of ways to avoid it... but i have. :(

i have a recordset with range of dates and more other columns
i want to create temporary table for calculation with same structure which take any range from the first recordset and create records as the number of days in the range

for (1) 14/08/2016 - 15/08/2016 in the first recorset i'll get 2 records in the second recorsetd:
(1) 14/08/2016
(2) 15/08/2016

but because their same structure i dont success to write it:

dim d as date

do until EOF

for d= [rst1]![startshift] to [rst1]![endshift]


  With rst2
    rst2.AddNew
 
    .Fields("startshift") = d
    .Fields("endshift") = d
 
 .Update

next
  
End With

loop

i would like to accept your help


note: The goal of splitting dates is to enable run 3 functions i worte which check fulfillment of some labor and rest laws
I am convinced that it is impossible to avoid this split

[PhilOfWalton]

This seems a follow on from a question you asked a few days ago, and when we asked for more information to check our suggestions - Nothing.

What you are asking for here seems pointless as you can use a query to get the information from the date range in your original table.

Phil

[ben1988]

hey!!!!! i check every suggestion!!!
but it's not same case!
last time i wanted to union! now i split
but i dont ask about the splitting.. i ask about the syntax who to work with two recordsets with same structure

[PhilOfWalton]

If you look at the function that I posted in https://bytes.com/topic/access/answe...tes-min-groups, you get a date range which I output as a string.

Instead of that, play around with the DateDiff function and the 2 dates on order to get days or hours difference.

Without confirmation that the function is providing the correct results, I can go no further.

Phil

[ben1988]

Phill ure really sweet. Thanks u
But the qeustion was about syntax how to reperence to recordset when i work with two recordsets with same structure (same fields)
But i tried it now
It was so simple.. i understood it was difficult for me cause i was exposed to .with method so i thought i need do somehow simultaneous with

Im rookie in access programming ;)

[zmbd]

Typically one opens a record set thus:

(I am typing the following by hand, there may be typos!)

'(...Air Code...)
DIM zDB As DAO.Database
DIM zRS1 As DAO.RecordSet
DIM zRS2 As DAO.RecodeSet
DIM zSQL as String
Dim zCalc as Long
'
'(...)
'
Set zDB = CurrentDB
'
zSQL = "SELECT [Field1], [Field2]" & _
   "FROM [Table1]" & _
   "WHERE([Table1].[FIELD1] Between " & _
      "#12/01/2016# And #12/31/2016#"
Set zRS1 = zDB.OpenRecordset(Name:=zSQL, Type:=dbOpenDynaset)
'
zSQL = "SELECT [Field1], [Field2]" & _
   "FROM [Table_TWO]" & _
   "WHERE([Table_TWO].[FIELD1] Between " & _
      "#12/01/2016# And #12/31/2016#"
Set zRS2 = zDB.OpenRecordset(Name:=zSQL, Type:=dbOpenDynaset)
'
'(code here to check for records in both RS)
'
'Using the current records in the recordsets
zCalc = zRS1![Field1] - zRS2[Field2]
'
'(...)
'
'Clean up
On error resume next
If not zRS1 is nothing then
   zRS1.close
   Set zRS1 = Nothing
End IF
'
If not zRS2 is nothing then
   zRS2.close
   Set zRS2 = Nothing
End IF
'
If not zDB is nothing then Set zDB = Nothing

- Lines 16 and 22 open the record sets. Notice that except for the table named in the SQL string, the record sets would be identical. You don't have to use a string, you can also open a recordset on a stored query or directly against a table.
- Line 27 shows one use against the two record sets
- Line 31: It is very importaint to clean up your record sets. If you OPEN it CLOSE it, if you SET IT, FREE IT.
>> Take note, Line 43, I didn't open the database in code. I used the CurrentDB Function to set a pointer, Line10, to the current database in the workspace. If you close this, all sorts of spooky things can happen!


+ You've told us nothing about how RST1 and RST2 are related nor have you shown us in your code how these two record sets were opened.

+ It's generally best to avoid temporary tables in ACC as they tend to bloat the file size and quite often are not needed; however, you've told us nothing about your table(s). There may very well be a way to do this with a stored query as Phil has suggested (There's a good reason he has the "Expert" tag under his avatar).

+ Keep in mind, when using dates in VBA they MUST usually be in the English-USA format #MM/DD/YYYY hh:nn:ss#, especially when creating dates in SQL strings as I have show in the above example and in VBA functions such a DateDiff()! (Lines 15 and 21 in the code block)

++ Ben, you really must go thru the tutorial I PM'd you some days ago. There is a basic/core skill set that you simply must master in order to get the most out of Access and our help. Access may not be a heavy-weight such as MYSQL or ORACLE; however, just like them, Access isn't something you can easily learn to use effectively "as you go." There really is a steep learning curve for anything beyond the basics.