• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 685
  • Last Modified:

problem with copy date from one table to another with s record set and a sql query on access 2013

hi

i have some table with date, and i have another empty table and i want with a code copy some dates that are  between two date ranges, from table1 to table 2,but the result are not correct for example if i'm looking to copy the dates on table one that are between october 2 2014 and april 4 2015 it's copy dates  from before october 2 2014.  i know that there is more easy ways but it's Annoying me not to can found out what is the problem with my code. there is my code:
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Dim Sql1 As String
Dim Sql2 As String
Sql1 = "SELECT First(Table1.EventID) AS FirstOfEventID, Table1.EventDate " & _
        "FROM Table1 " & _
        "WHERE (((Table1.EventDate) > #" & Me.StartDate & "# And (Table1.EventDate) < #" & Me.EndDate & "#)) " & _
        "GROUP BY Table1.EventDate;"
Me.Status = Sql1

Set rs = db.OpenRecordset(Sql1, dbOpenSnapshot)
rs.MoveFirst
If Not rs.EOF Then
    While Not rs.EOF
        Sql2 = "INSERT INTO Table2 ( EventDate ) " & _
                "SELECT #" & rs!eventdate & " # AS Expr1;"

        DoCmd.SetWarnings False
        DoCmd.RunSQL Sql2
        DoCmd.SetWarnings True
        rs.MoveNext
    Wend
End If

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

Open in new window



and i attached a sample of this database.

thanks  a lot sample.mdb
0
bill201
Asked:
bill201
  • 5
  • 3
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What exactly are you trying to do? If you can explain what you need, we can very likely show you a better way.

For example, are you trying to insert ONE and ONLY ONE of the records from Table1 into Table2, based on the Start and End Dates entered by the user?

Or are you trying to insert ALL DATES from Table1 to Table2 which fall between the Start and End dates entered by the user?
0
 
bill201Author Commented:
i'm trying to copy records that are  between two dates and  its there is two records on the same date on table 1 i want to copy only 1 for 1 day
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Will there be other data in Table1? I assume you'd have something more than just a Date and an AutoId field.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Gustav BrockCIOCommented:
You wouldn't call an SQL update inside a loop. DAO is much faster:
    Dim db As Database
    Dim rs As Recordset
    Dim rs2 As Recordset
    Dim Sql1 As String
    Dim Sql2 As String
    
    Set db = CurrentDb
    
    Sql1 = "SELECT First(Table1.EventID) AS FirstOfEventID, Table1.EventDate " & _
            "FROM Table1 " & _
            "WHERE (Table1.EventDate > DateValue('" & Me!StartDate & "') And Table1.EventDate < DateValue('" & Me!EndDate & "')) " & _
            "GROUP BY Table1.EventDate;"
    Me.Status = Sql1
    
    Set rs = db.OpenRecordset(Sql1, dbOpenSnapshot)
    If rs.RecordCount > 0 Then
        Sql2 = "Select Top 1 * From Table2"
        Set rs2 = db.OpenRecordset(Sql2)
        rs.MoveFirst
        While Not rs.EOF
            rs2.AddNew
                rs2!Eventdate.Value = rs!Eventdate.Value
            rs2.Update
            rs.MoveNext
        Wend
        rs2.Close
    End If
    rs.Close
    
    Set rs2 = Nothing
    Set rs = Nothing
    Set db = Nothing

Open in new window

/gustav
0
 
bill201Author Commented:
Scott McDaniel
 i have more data but it's Sensitive data and i think it's enough to show the problem.

Gustav Brock

thanks for your sulution but it's seems funny why it making problems what it's wrong with this code?
0
 
Gustav BrockCIOCommented:
There is nothing wrong with my code.
Yours need to use the correct SQL syntax:

Sql2 = "INSERT INTO Table2 ( EventDate ) " & _
                "VALUES (#" & Format(rs!eventdate.Value, "yyyy\/mm\/dd") & "#);"

But again, calling an insert SQL for each loop is slow.

/gustav
0
 
bill201Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for bill201's comment #a40759968

for the following reason:

thanks a but your another solution how to do it with a sql string don't work (there is some problem with windows that the is  dd/mm/yyyy )
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm objecting because your comment http://www.experts-exchange.com/Database/MS_Access/Q_28667813.html#a40759968 is not a valid technical link.

Also, gustav seems to have provided you with a valid solution, as well as with a solution to the issue with your code. You should accept one of those comments, it would seem:

http://www.experts-exchange.com/Database/MS_Access/Q_28667813.html#a40759946

or

http://www.experts-exchange.com/Database/MS_Access/Q_28667813.html#a40759976
0
 
bill201Author Commented:
sorry I'm  embarrassed!!!
I have no idea what happened probably pressed the wrong button. of course  i wanted to accept Gustav Brock first solution
0
 
bill201Author Commented:
Thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now