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
bill201Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.