asked on

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

Scott McDaniel (EE MVE )

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?

bill201

ASKER

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

Scott McDaniel (EE MVE )

Will there be other data in Table1? I assume you'd have something more than just a Date and an AutoId field.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bill201

ASKER

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?

Gustav Brock

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

bill201

ASKER

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 )

Scott McDaniel (EE MVE )

I'm objecting because your comment https://www.experts-exchange.com/questions/28667813/problem-with-copy-date-from-one-table-to-another-with-s-record-set-and-a-sql-query-on-access-2013.html?anchorAnswerId=40759968#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:

https://www.experts-exchange.com/questions/28667813/problem-with-copy-date-from-one-table-to-another-with-s-record-set-and-a-sql-query-on-access-2013.html?anchorAnswerId=40759946#a40759946

or

https://www.experts-exchange.com/questions/28667813/problem-with-copy-date-from-one-table-to-another-with-s-record-set-and-a-sql-query-on-access-2013.html?anchorAnswerId=40759976#a40759976

bill201

ASKER

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

bill201

ASKER

Thanks a lot