> What does the input box say? Does it refer to a field name that has been
> changed? Right click in the query design (top half, not a field) and check
> the properties to see if there is anything being referenced in the [order by]
> or filter properties.
>
>
>
> ryan.fitzpatri...[ at ]safeway.com wrote:
> >I have renamed query field headings with my own headings this way when
> >users export to excel the orginal database headings, which are
> >confusing, will not be displayed. but when i rename them and click the
> >export to excel button a input box appears, why would this be?
>
> >Here's query SQL that renames fields
>
> >SELECT tblAdagePaidDebits.gl_cmp_key AS Company,
> >tblAdagePaidDebits.so_brnch_key AS Branch,
> >tblAdagePaidDebits.en_vend_key AS [Vendor ID],
> >tblAdagePaidDebits.en_vend_name AS [Vendor Name],
> >tblAdagePaidDebits.in_type_key AS [Ingred/Pack],
> >tblAdagePaidDebits.in_item_key AS [Item Number],
> >tblAdagePaidDebits.in_desc AS [Item Desc],
> >tblAdagePaidDebits.po_dtl_uom AS UOM, tblAdagePaidDebits.in_comcd_key
> >AS Commodity, tblAdagePaidDebits.[Rec Date] AS [Year],
> >tblAdagePaidDebits.[SumOfActual Inv Quant] AS Quantity,
> >tblAdagePaidDebits.[SumOfAct Dom $ Spend] AS Spend,
> >tblAdagePaidDebits.en_phfmt_key AS Buyer, Date() AS [Date Downloaded]
> >FROM tblAdagePaidDebits;
>
> >Here is export to excel code
>
> >Private Sub Export_Click()
> >On Error GoTo Err_Export_Click
>
> >Dim dbCurr As DAO.Database
> >Dim qdfTemp As DAO.QueryDef
> >Dim lngOrderBy As Long
> >Dim strQueryName As String
> >Dim strSQL As String
>
> >' You only need to go to this effort if there's a filter
> > If Len(Me.Filter) > 0 Then
> > Set dbCurr = CurrentDb
>
> >' Get the SQL for the existing query
> > strSQL = dbCurr.QueryDefs("3QryAdageVolumeSpendsum").SQL
>
> >' Check whether there's an ORDER BY clause in the SQL.
> >' If there is, we need to put the WHERE clause in front of it.
> > lngOrderBy = InStr(strSQL, "ORDER BY")
> > If lngOrderBy > 0 Then
> > strSQL = Left(strSQL, lngOrderBy - 1) & _
> > " WHERE " & Me.Filter & " " & _
> > Mid(strSQL, lngOrderBy)
>
> > Else
> >' There's no ORDER BY in the SQL.
> >' Remove the semi-colon from the end, then append the WHERE clause
> > strSQL = Left(strSQL, InStr(strSQL, ";") - 1) & _
> > " WHERE " & Me.Filter
> >End If
>
> >' By using the current date and time, hopefully that means
> >' a query by that name won't already exist
> > strQueryName = "qryTemp" & Format(Now, "yyyymmddhhnnss")
>
> >' Create the temporary query
> > Set qdfTemp = dbCurr.CreateQueryDef(strQueryName, strSQL)
>
> >' Export the temporary query
> > DoCmd.TransferSpreadsheet transfertype:=acExport, _
> > spreadsheettype:=acSpreadsheetTypeExcel9, _
> > tableName:=strQueryName, FileName:= _
> > "C:\Documents and Settings\All Users\Desktop\Adage Downloaded
> >On" & Format(Now, "mm" & "-" & "dd" & "-" & "yyyy" & "[ at ]" & "hh" &
> >"nn") & ".xls", _
> > hasfieldnames:=True
>
> >' Delete the temporary query
> > dbCurr.QueryDefs.Delete strQueryName
>
> > Else
>
> > DoCmd.TransferSpreadsheet transfertype:=acExport, _
> > spreadsheettype:=acSpreadsheetTypeExcel9, _
> > tableName:=strQueryName, FileName:= _
> > "C:\Documents and Settings\All Users\Desktop\Adage Downloaded
> >On" & Format(Now, "mm" & "-" & "dd" & "-" & "yyyy" & "[ at ]" & "hh" &
> >"nn") & ".xls", _
> > hasfieldnames:=True
>
> > End If
>
> >Exit_Export_Click:
> > Set dbCurr = Nothing
> > Exit Sub
>
> >Err_Export_Click:
> > MsgBox Err.Description
> > Resume Exit_Export_Click
>
> >End Sub
>
> >It's weird it used to work and now I get this input box. Any
> >suggestions? Thanks.
>
> >Ryan
>
> --
> Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1