Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create name of append field (2000)

    I'm sure this is something I've done before, but can't remember how or where!

    I am running an append query.
    The file to be appended from contains the fields 'Week 01 forecast', 'Week 01 forecast', 'Week 02 forecast', 'Week 03 forecast', 'Week 04 forecast', 'Week 04 forecast', upto 'Week 52 forecast'.
    I want to enter a week number eg '03' and append the contents of this to a field called 'ThisWeek'.

    In the append query I entered TW: "[BaseData]!" & "[Week " & [weeknumber] & " forecast]" in the field row and 'ThisWeek' in the append to row but this gives me [BaseData]![Week 03 forecast] as the value.

    How do I make it insert the value of [BaseData]![Week 03 forecast] rather than the string?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create name of append field (2000)

    You cannot specify a field name this way, by assembling it. You could do the following instead:
    - Create an unbound form with a text box txtWeek and a command button cmdAppend.
    - Set the Format property of the text box to General Number or to 0.
    - Look at the SQL for the append query you have now.
    - Create an event procedure for the On Click event of the command button:

    Private Sub cmdAppend_Click()
    Dim strSQL As String
    If IsNull(Me.txtWeek) Then
    MsgBox "Please enter a week.", vbExclamation
    Me.txtWeek.SetFocus
    Exit Sub
    End If
    If Val(Me.txtWeek) < 1 Or Val(Me.txtWeek) > 52 Then
    MsgBox "Please enter a valid week.", vbExclamation
    Me.txtWeek.SetFocus
    Exit Sub
    End If
    strSQL = "INSERT INTO tblTarget (..., ThisWeek, ...) " & _
    " SELECT ..., [Week " & Format(Me.txtWeek, "00") & " Forecast], ... " & _
    "FROM tblSource"
    DoCmd.RunSQL strSQL
    End Sub

    Model the SQL statement on the one for your existing append query.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create name of append field (2000)

    Thanks Hans. Worked wonderfully.
    Rather than now having to wrestle with the selection of the weeks as part of the selection query I can append the data and run it just on the subset i need. It's cut the time to run the selection query from 13 minutes down to under 2 minutes as i had to include another table in the report to help calculate the current week.
    Many thanks (again)

Posting Permissions

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