Excel Import from Access missing field

[herbc0704]

I created a query in access. One of my fields is a mulitple IIf expression. When I run the query this field populates properly. However when I try to import the query using excel as a table the field heading shows up but the results of the expression are blank, I have no clue on what is happening. Below is the access expression.


Desc2: IIf([PROD_732_D_ODBC_ITEM]![ITEM_NUM] Like "*WM*","Women's Medium",IIf([PROD_732_D_ODBC_ITEM]![ITEM_NUM] Like "*WW*","Women's Wide",IIf([PROD_732_D_ODBC_ITEM]![ITEM_NUM] Like "*WX*","Women's X-Wide",IIf([PROD_732_D_ODBC_ITEM]![ITEM_NUM] Like "*MM*","Men’s Medium",IIf([PROD_732_D_ODBC_ITEM]![ITEM_NUM] Like "*MW*","Men’s Wide",IIf([PROD_732_D_ODBC_ITEM]![ITEM_NUM] Like "*MX*","Men’s X-Wide"))))))

 

[alansidman]

How are you importing to Excel? Have you considered doing an export from Access if you are running the query in Access?

Alan

 

[herbc0704]

Alan I thought of that as it works however, this is going to be used by other people who are less access savvy than excel. I figured if I created the import from excel it would cause less questions.

 

[alansidman]

What I have done in the past for those that are Access Challenged and need the results of a query imported into Excel is to build a form unbound to any table. Put command buttons on it. One will run the query and export it directly to an excel spreadsheet. The second one will close the Access Database. No need for the User to see or know anything else. Use the spreadsheet transfer function in your VBA.

Here is some sample code:

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim reportName As String
Dim theFilePath As String

Select Case Me.Frame6.Value
    Case 1
    reportName = "MonthlyActivity"
    End Select
       
theFilePath = Me.txtfilepath.Value
theFilePath = theFilePath & reportName & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
      
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, reportName, theFilePath, True

MsgBox "Look on your desktop for the report."

   

Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click
    
End Sub

Alan

 

[RichardMGreen]

Are you using ADO to do the import?
If so, you'll need to change each * to a %.

I fell foul of this a while ago. Access will quite happily use * as a wildcard, but ADO needs "proper" SQL where % is a wildcard.