Qdef to Excel export with manipulation

[jordanh]

Hi,

I have a sub that exports a set of records to an excel spreadsheet, however some of the records contain just "-" as their value. In all of these cases I want to replace the "-" with a Null value, NOT in the actual database, but just the values exported to excel.

Does anyone know how to do this? The sub I've written is below:

Thanks in advance,

Jordan

Private Sub export_Click()
On Error GoTo Err_export_form_Click

Dim dbs As Database, qdf As QueryDef
    Dim strsql As String

    Set dbs = CurrentDb

    ' Refresh QueryDefs collection.
    dbs.QueryDefs.Refresh
    ' If toexport query exists, delete it.
    For Each qdf In dbs.QueryDefs
        If qdf.Name = "toexport" Then
            dbs.QueryDefs.Delete qdf.Name
        End If
    Next qdf
    ' Create SQL string to select contacts to invite where values are as specified in form.
    strsql = "SELECT DISTINCTROW " _
        & "[invite_management_query].[cont_title], " _
        & "[invite_management_query].[cont_initial], " _
        & "[invite_management_query].[cont_surname], " _
        & "[invite_management_query].[cont_jobtitle], " _
        & "[invite_management_query].[cont_company], " _
        & "[invite_management_query].[cont_group], " _
        & "[invite_management_query].[cont_department], " _
        & "[invite_management_query].[cont_address_num], " _
        & "[invite_management_query].[cont_address_street], " _
        & "[invite_management_query].[cont_address_town], " _
        & "[invite_management_query].[cont_address_county], " _
        & "[invite_management_query].[cont_address_post], " _
        & "[invite_management_query].[cont_address_country], " _
        & "[invite_management_query].[cont_tel], " _
        & "[invite_management_query].[cont_email] " _
        & "FROM [invite_management_query] " _
        & "WHERE ([invite_management_query].[events_id] = " & [Forms]![invite_management_add]![event_list] & ") " _
        & "AND ([invite_management_query].[invite_sent_date] = null) " _
        & "ORDER BY [invite_management_query].[cont_surname], [invite_management_query].[cont_id];"
    ' Create new QueryDef object.
    Set qdf = dbs.CreateQueryDef("toexport", strsql)

    DoCmd.OutputTo acOutputQuery, qdf.Name, acFormatXLS, , 0

Set dbs = Nothing

Exit_export_form_Click:
    Exit Sub

Err_export_form_Click:
    MsgBox Err.Description
    Resume Exit_export_form_Click
    
End Sub

 

[lupins46]

This would be easier done using a global find and replace in XL.
In Access you will have to test every field individually:

& iif([invite_management_query].[cont_title]="-","",[invite_management_query].[cont_title])], " _

 

[jordanh]

lupins46:

Thanks for the reply. I agree that a global F&R in Excel would be the best thing to do. I suspect my client will moan about the extra level of interaction though.

How can I incorporate the If statement into the SQL query? Can it just go in as you've posted it?

Thanks again,

Jordan

 

[lupins46]

Yes; you would have to use an iif for each field.

 

[jordanh]

Thanks a lot. It's actually only a couple of fields that contain the offending hyphens, so it may be a solution.

It seems to work fine, brilliant.

Thanks again.

Jordan