append query based On other Query

[Falcon88]

all dears

i have a table contains the dates of a year.
in Q1 .
if the day is friday i want to append as in [DayValue], 42 records .
elese
append 86 records .
where
starts with [From] , and ends with [TO] (Serially) append to OrderID
and
BillDate append to OrderDate
in tblOrders

 

[Uncle Gizmo]

Have a look at this Example HERE:-

Generate in between numbers

 

[Falcon88]

when i try to open ur example db its give an error :

Unrecognized database format <filename>. (Error 3343)
Possible causes:

• The specified file name is not a Microsoft Access database engine database.
• The specified file name is a device name, for example, a printer or a console.
• The database file has invalid header information or an unknown sort order.
• A commit is pending from another user but the lock file cannot be found.
• During a commit, you are attempting to write a Long value larger than the 2K maximum page size.
• The database is damaged. Compact the database and then try opening it again.

please give me another example>

 

[sneuberg]

when i try to open ur example db its give an error :

Unrecognized database format <filename>. (Error 3343)
Possible causes:

I looked at that database and it's working fine. This is an accdb format database. Is your version of Access earlier than 2007?

 

[Falcon88]

I looked at that database and it's working fine. This is an accdb format database. Is your version of Access earlier than 2007?

no ,my ms access version is 2007

 

[Falcon88]

Have a look at this Example HERE:-

Generate in between numbers

Can you doing the amendments to my example attached above.

 

[arnelgp]

here run Q1ToOrder sub:

 

[Falcon88]

Very thanks .

 

[Falcon88]

i have noticed some wrong results in the Sequence aranging of the OrderID after i use your code. for example: after append recocords of day 01/january/2015 in orderID=86 , it was supposed to start in appending records of 02/january/2015 in OrderID=87, but it began to append records of 01/febaruary/2015 in OrderID=87. note: my region default settings in Windows? if my default settings : dd/MM/yyyy

 

[Falcon88]

what is about the following code :

Public Sub Q1ToOrder()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim bd As Variant
    Dim i As Integer
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

    Set db = CurrentDb
    Set rs = db.QueryDefs("Q1").OpenRecordset(dbOpenDynaset)
    
    With rs
        If Not .EOF And Not .BOF Then
            .MoveFirst
            While Not .EOF
                bd = Format(![BillDate], conJetDate)
                For i = Val(![From]) To Val(![TO])
                    db.Execute "Insert Into [tblOrders] ([OrderID], [OrderDate]) " & _
                            "SELECT " & i & ", " & bd & ";"
                    DoEvents
                Next
                .MoveNext
            Wend
        End If
    End With
    
    ' close recordset and database
    Set rs = Nothing
    Set db = Nothing
End Sub