Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copying specific records to a new table...

    Hi everyone... This is probably a silly question with a very simple answer, but I'm hoping someone can help me here...

    How do I copy records from a query to a table of it's own...inside a module?

    'rsq' is the query recordset with 280 records... (defined earlier)... I know that I should end up with 16 records after I do this test... but I want these full records (all fields) to go to their own table (tblDvdAccruals)... I don't want to change the original query....

    Here's the simple loop I need:

    rsq.MoveFirst
    Do Until rsq.EOF
    If CheckRDate = rsq.Fields("Newdvdex").Value Then

    '**This is where I'd like to say something along the lines 'of "copy record into 'tblDvdAccruals' and go on to check 'the next record"...Can anyone tell me how to write this?

    Else 'ignore this record and move on
    rsq.MoveNext
    End If
    Loop

    I am going to add a message to the user if there are no records when the test is done...

    Thanks...[img]/w3timages/icons/smile.gif[/img]

  2. #2
    carl g.
    Guest

    Re: copying specific records to a new table...

    Hi Alexya,

    Assuming that you are doing this in MS Access...

    You can use SQL within code (i.e. a action query within code) here is an example that fits your problem:

    Sub InsertRequired(CheckRDate as Date)

    Dim sSQL as String

    sSQL = "INSERT INTO tblTwo ( Fld1, Fld2 ) "
    sSQL = sSQL & "SELECT tblOne.Fld1,"
    sSQL = sSQL & "tblOne.Fld2 "
    sSQL = sSQL & "FROM tblOne "
    sSQL = sSQL & "WHERE (((tblOne.Fld1)=" & CheckRDate & ")); "

    Debug.Print sSQL

    DoCmd.RunSQL(sSQL)

    End sub

    You test it by cutting and pasting the SQL string from the immediate (debug) window (created by debug.print) into the back end of a query GUI.

    Regards,

    Carl

  3. #3
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying specific records to a new table...

    Thanks Carl... The idea was great... I knew I could run SQL statements in modules...I just didn't know whether it would right for this purpose... I haven't been able to get it to work yet though... I need help with one more thing....

    When I alter the code accordingly and compile and run it...
    I get a "division by zero" error... I am assuming that this is because the CheckRDate value is 12/12/00 (??) since this is the only calculation in the statement...
    WHERE (((Required_Records.Newdvdex)=" & CheckRDate & "));

    won't the system read this as:
    WHERE Required_Records.Newdvdex=12/12/00

    (actually... I just realized that the Newdvdex values will all be like that... 12/23/00, etc...)
    Any ideas on how to fix this?

    Thanks again... [img]/w3timages/icons/smile.gif[/img]

  4. #4
    carl g.
    Guest

    Re: copying specific records to a new table...

    Alexya,

    I got this to work after fiddling a bit with the date formats - passing them as a string first, but them recognising them as a date in the query (the "#" characters)

    Option Compare Database
    Option Explicit

    Sub PassTheDate()
    Dim CheckRDate As String

    CheckRDate = "21/01/2001"

    Call DateExtract(CheckRDate)

    End Sub


    Sub DateExtract(ByVal CheckRDate As String)

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim sSQL As String

    sSQL = "INSERT INTO tblTwo ( TheInfo, TheDate ) "
    sSQL = sSQL & "SELECT tblOne.TheInfo, "
    sSQL = sSQL & "tblOne.TheDate "
    sSQL = sSQL & "FROM tblOne "
    sSQL = sSQL & "WHERE (((tblOne.TheDate) "
    sSQL = sSQL & "= #" & CheckRDate & "#)); "

    Debug.Print sSQL
    Set db = CurrentDb
    Set qd = db.CreateQueryDef("qryDateExtract", sSQL)

    qd.Execute

    MsgBox qd.RecordsAffected & " Records added"

    Set db = Nothing
    Set qd = Nothing

    End Sub

    This version is the non lazy one, and will create a Query in the database window for you to examine - it will produce an error on the second run though as a query of that name will already exist.

    I'm pretty sure that the prob with the old version was that it was dividing 12 by 12 by 00 (12/12 then the result of that /00) and so coming up with your zero error message.

    Regards,

    Carl

  5. #5
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying specific records to a new table...

    Thank you so much Carl!!! ...As soon as I put the #'s around the CheckRDate bit, it worked perfectly... I will add an SQL statement at the beginning of the daily procedures that will delete the previous day's records from the file... and this should be wonderful...

    I can't thank you enough!! Have a terrific day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •