Query fields (columns) do not apear in the right order.

[ser01]

I have a query whose SQL is generated from VBA. There are 3 different tables involved in the query. My problem is that the query's fields do not appear in the correct order and by that I mean they in a different order than listed in the SQL. Here is the SQL:

SELECT tblOrders_ORD.[Item Code], tblOrders_ORD.Item, tblItems_ORD.Layer, tblWeeklySalesData_ORD.[6/1/2010], tblWeeklySalesData_ORD.[6/8/2010], tblItems_ORD.[In Stock], Raw(3,tblItems_ORD![In Stock],tblWeeklySalesData_ORD![6/1/2010],tblWeeklySalesData_ORD![6/8/2010]) AS Raw, tblOrders_ORD.Quantity
FROM (tblItems_ORD RIGHT JOIN tblOrders_ORD ON tblItems_ORD.[Item Code] = tblOrders_ORD.[Item Code]) LEFT JOIN tblWeeklySalesData_ORD ON tblOrders_ORD.[Item Code] = tblWeeklySalesData_ORD.[Item Code]
WHERE (((tblOrders_ORD.Vendor)="JACK & JILL") AND ((tblOrders_ORD.Completed)=0))
ORDER BY tblOrders_ORD.[Item Code];

And bellow is a pic with the order the fields show up in. As you can see from the image, the fields with the dates appear at the end (rightmost) as opposed to where they are supposed to be based on the SQL. If I rearrange the fields manually and close/save the query then open it again, they show up correctly. However once the VBA code is run, the query once again is generated with the fields being in the wrong order. I tried creating a datasheet form based on the query, and the fields were in the right order right away. the problem is I can not use a form for this, because the fields used are dynamic. Any suggestions would be greatly appreciated.

[ser01]

Any suggestions of what might be wrong at all? Please let me know if more info is needed to further clarify the problem.

[ajetrumpet]

they are in the right order. these are being omitted:

tblWeeklySalesData_ORD.[6/1/2010], tblWeeklySalesData_ORD.[6/8/2010]

what is the purpose of doing that?? I have not seen it done before. I don't even know what it does.

[kennejd]

It looks like you've duplicated the problem fields in your SQL. These fields are being selected twice:

, tblWeeklySalesData_ORD.[6/1/2010]
, tblWeeklySalesData_ORD.[6/8/2010]

I don't know if fixing this will fix your problem, but give it a try and see. I know Access doesn't like having fields named the same thing and might act a little strange if you do.

[ser01]

The fields are not duplicated, nor are they being omitted. The fields that you guys are referring to, are used in a calculated field. I have created a function in VBA to do the calculation since it was to complicated to just do in SQL. I named the function "Raw" and the fields you guys are referring to are some of the arguments for the function... This is driving me crazy now, since i just don't see what is wrong. If I copy the SQL and just paste it in a blank new query it displays the fields in the right order. Its just after running my VBA where things get messed up...

[kennejd]

The problem must be in your vba?

Can you send your vba code?

[ser01]

The problem must be in your vba?

Can you send your vba code?

That's what I'm thinking. Bellow is the code. I am not a programmer, so my VBA may be incredibly goofy , I'm learning this stuff on my own, and not having any programming background is making it very difficult fore me

Private Sub Command0_Click()
    Dim strSQL As String
    Dim db As DAO.Database
    Dim qdf1 As DAO.QueryDef
    Dim qdf2 As DAO.QueryDef

    On Error GoTo Command15_Click_Error

    Set db = Application.CurrentDb
    Set qdf2 = db.QueryDefs("qryPurchaseOrder_ORD")
    Set qdf1 = db.QueryDefs("qryExportSalesHistory_ORD")

    Dim wk1 As Date
    Dim wk2 As Date
    Dim wk3 As Date
    Dim wk4 As Date
    Dim wk5 As Date
    Dim wk6 As Date
    Dim delivery_date As Date
    Dim vendor As String
    Dim weeks As String
    Dim weeks2 As String

    delivery_date = Text16.Value
    vendor = Combo0.Value

    'Determine and set the right dates for the query that will export the sales history.
    wk1 = Text2.Value
    wk2 = Text4.Value
    weeks = "((tblSales_ORD.Dates) = #" & wk1 & "# Or (tblSales_ORD.Dates) = #" & wk2 & "#)"

    If Text12.Value <> "" Then
        wk6 = Text12.Value
        wk5 = Text10.Value
        wk4 = Text8.Value
        wk3 = Text6.Value
        weeks = "((tblSales_ORD.Dates) = #" & wk1 & "# Or (tblSales_ORD.Dates) = #" & wk2 & "# Or (tblSales_ORD.Dates) = #" & wk3 & "# Or (tblSales_ORD.Dates) = #" & wk4 _
                & "# Or (tblSales_ORD.Dates) = #" & wk5 & "# Or (tblSales_ORD.Dates) = #" & wk6 & "#)"
    ElseIf Text10.Value <> "" Then
        wk5 = Text10.Value
        wk4 = Text8.Value
        wk3 = Text6.Value
        weeks = "((tblSales_ORD.Dates) = #" & wk1 & "# Or (tblSales_ORD.Dates) = #" & wk2 & "# Or (tblSales_ORD.Dates) = #" & wk3 & "# Or (tblSales_ORD.Dates) = #" & wk4 _
                & "# Or (tblSales_ORD.Dates) = #" & wk5 & "#)"
    ElseIf Text8.Value <> "" Then
        wk4 = Text8.Value
        wk3 = Text6.Value
        weeks = "((tblSales_ORD.Dates) = #" & wk1 & "# Or (tblSales_ORD.Dates) = #" & wk2 & "# Or (tblSales_ORD.Dates) = #" & wk3 & "# Or (tblSales_ORD.Dates) = #" & wk4 & "#)"
    ElseIf Text6.Value <> "" Then
        wk3 = Text6.Value
        weeks = "((tblSales_ORD.Dates) = #" & wk1 & "# Or (tblSales_ORD.Dates) = #" & wk2 & "# Or (tblSales_ORD.Dates) = #" & wk3 & "#)"
    End If

    If DLookup("[Order Date]", "tblOrders_ORD", "[Completed]=0 And [Vendor]=" & "'" & vendor & "'") <> "" Then
        msgChoice = MsgBox("It seems that there is an open order for " & vendor & " already. The delivery date for the order is: " & DLookup("[Order Date]", "tblOrders_ORD", "[Completed]=0 And [Vendor]=" & "'" & vendor & "'") & ". If this order has already been delivered and the cost and quantity recieved has been checked, then " _
                           & "click YES bellow to mark the order as completed and a new order will be created. Otherwise click NO to edit the existing order. Click CANCEL to exit without any changes", _
                           vbYesNoCancel, "Read Carefully!!!")
        If msgChoice = vbYes Then
            DoCmd.RunSQL "UPDATE tblOrders_ORD SET tblOrders_ORD.Completed = 1 " _
                         & "WHERE (((tblOrders_ORD.Vendor)=""" & vendor & """) AND ((tblOrders_ORD.Completed)=0));"
           ElseIf msgChoice = vbCancel Then
                Exit Sub
        End If
    End If




    'Insert the order into the [tblOrders_ORD] to be populated with the Quantity later;
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblOrders_ORD ( [Item Code], Item, Vendor, [Order Date] ) " _
                 & "SELECT tblItems_ORD.[Item Code], tblItems_ORD.Item, tblItems_ORD.Vendor.Value,#" & delivery_date & "#AS Expr1 " _
                 & "FROM tblItems_ORD " _
                 & "WHERE (((tblItems_ORD.Vendor.Value)=""" & vendor & """));"
    DoCmd.SetWarnings True

    'Generate Pivot Table query for the selected weeks to be exported to EXCEL
    strSQL = "TRANSFORM Sum(tblSales_ORD.Quantity) AS SumOfQuantity " _
             & "SELECT tblItems_ORD.[Item Code], tblItems_ORD.Item " _
             & "FROM tblItems_ORD LEFT JOIN tblSales_ORD ON tblItems_ORD.[Item Code] = tblSales_ORD.[Item Code] " _
             & "WHERE (" & weeks & ") " _
             & "GROUP BY tblItems_ORD.[Item Code], tblItems_ORD.Item " _
             & "PIVOT tblSales_ORD.Dates;"

    qdf1.SQL = strSQL

    DoCmd.DeleteObject acTable, "tblWeeklySalesData_ORD"
1:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportSalesHistory_ORD", "C:\qryExportSalesHistory_ORD.xls", True
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblWeeklySalesData_ORD", "C:\qryExportSalesHistory_ORD.xls", True

    Set db = CurrentDb()
    Set tbl = db.TableDefs("tblWeeklySalesData_ORD")
    Set idx = tbl.CreateIndex("PrimaryKey")
    Set fld = idx.CreateField("Item Code")

    idx.Fields.Append fld
    idx.Primary = True
    tbl.Indexes.Append idx




    'Generate the SQL for the Purchase Order query
    weeks2 = "SELECT tblOrders_ORD.[Item Code], tblOrders_ORD.Item, tblItems_ORD.Layer, tblWeeklySalesData_ORD.[" & wk1 & "], tblWeeklySalesData_ORD.[" & wk2 & "], " _
             & "tblItems_ORD.[In Stock], Raw(3,[tblItems_ORD]![In Stock],[tblWeeklySalesData_ORD]![" & wk1 & "], [tblWeeklySalesData_ORD]![" & wk2 & "]) "

    strSQL = weeks2 _
             & "AS Raw, tblOrders_ORD.Quantity " _
             & "FROM (tblItems_ORD RIGHT JOIN tblOrders_ORD ON tblItems_ORD.[Item Code] = tblOrders_ORD.[Item Code]) LEFT JOIN tblWeeklySalesData_ORD ON " _
             & "tblOrders_ORD.[Item Code] = tblWeeklySalesData_ORD.[Item Code] " _
             & "WHERE (((tblOrders_ORD.Vendor)=""" & vendor & """) AND ((tblOrders_ORD.Completed)=0)) " _
             & "ORDER BY tblOrders_ORD.[Item Code];"
    qdf2.SQL = strSQL
    DoCmd.OpenQuery ("qryPurchaseOrder_ORD")
    Forms(ft).RecordSource = strSQL
    DoCmd.OpenForm "ft"
    
CleanUp:
    Set fld = Nothing
    Set idx = Nothing
    Set tbl = Nothing
    Set db = Nothing

    MsgBox "Weekly sales data generated succesfuly. Click OK to close this dialog", vbOKOnly, "Success!!!"

    On Error GoTo 0
    Exit Sub
Command15_Click_Error:
    If Err.Number = 94 Then
        MsgBox "Please make sure that you have a VENDOR, DELIVERY DATE and at least 2 WEEKS OF SALES selected and try again.", vbOKOnly, "NO VENDOR SELECTED!"
    End If
    If Err.Number = 7874 Then GoTo 1
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command15_Click of VBA Document Form_frmMain"

End Sub