Need salutations removed from Excel workbook on import to Access

[templeowls]

Not sure if this is exactly a programming question but here's my issue. I have a database that prompts the user to import a customer list from excel and after import, a bunch of pre-set queries analyze the data against some tables that are already in the database. It works great, except the customer list is pulled from our CRM and our CRM by default puts salutations in the 'first name field' on export. So "John, Mr" or "Nancy, Mrs". This screws up the queries that are meant to match first names.



Now I could easily deal with this in excel prior to import if I was the only one using the database, but the plan is for almost a dozen people using it and I want to design it with ease of use in mind. Any idea on how to get Access to remove those salutations on import or in a query after import? Below is my import code if it helps. Thanks

Public Sub bttnProcessIt_Click()

Dim wdShell As Object


' On Error GoTo ImportIt_Err


    MsgBox "Remember to export the report in CRM as a  a .XLS file", vbOKOnly
    


    ' Prompt user for file path for the Raw CRM spreadsheet
    Application.FileDialog(msoFileDialogOpen).Title = "Please select the CRM file for processing"
    Application.FileDialog(msoFileDialogOpen).InitialFileName = "F:\CRM"
    Application.FileDialog(msoFileDialogOpen).Filters.Add "Excel Spreadsheets", "*.xlsx", 1
    Application.FileDialog(msoFileDialogOpen).FilterIndex = 1
    Application.FileDialog(msoFileDialogOpen).Show
    strFile_Path = Application.FileDialog(msoFileDialogOpen).SelectedItems.Item(1)
            
    DoCmd.SetWarnings (WarningsOff)
    DoCmd.OpenQuery "qryClear_RawAudit"
    DoCmd.RunSavedImportExport "Import-CRM"
    DoCmd.RunSavedImportExport "Export-Last Name Match Report"
    DoCmd.RunSavedImportExport "Export-First and Last Name Match Report"
    DoCmd.RunSavedImportExport "Export-Address Match Report"
    DoCmd.RunSavedImportExport "Export-Phone Number Match Report"
    DoCmd.RunSavedImportExport "Export-High Profile Staff Match Report"
    DoCmd.RunSavedImportExport "Export-No Action Views Report"
    DoCmd.RunSavedImportExport "Export-Summary Report"
    DoCmd.Close acForm, Me.Name


    StrResponse = MsgBox("The CRM review has been successfully imported and the exported files are located in the CRM folder!")


ImportIt_Exit:
    Exit Sub


ImportIt_Err:
    MsgBox Error$
    Resume ImportIt_Exit


End Sub

[ranman256]

remove all the "RunSavedImportExport" lines
import to a local temp table
after the import open the data in a continuous form and let the user add the salutaions.
when dont run an append query to add the temp tbl to the main data.

[templeowls]

My issue is that the folks using this database have little to no access training. If you notice how the code is written, outside of importing the file, the user does nothing more within Access. A summary report and multiple excel files are exported to various folders. That's why I was hoping there could be an automated way (through queries or such) that could remove the salutations. Maybe not; but it's worth checking. Tbh if there isn't, I think just removing the salutations via a 'find and replace' method in excel prior to import is the easiest way for folks who don't use access

[Welshgasman]

Run a cleanup query to remove everything from the , onwards.?
Perhaps put into a temp table and update your table from that, doing the cleanup at the same time?

[templeowls]

Run a cleanup query to remove everything from the , onwards.?
Perhaps put into a temp table and update your table from that, doing the cleanup at the same time?

That would be perfect. Like an update query? Do you know the criteria used to remove both the comma and the salutation afterwards?

[Welshgasman]

Use a Combination of Instr() to find the position of the , and Left() to get everything to the left of that position.

[templeowls]

Use a Combination of Instr() to find the position of the , and Left() to get everything to the left of that position.

So like this in a query field?

Expr1: Left([InStr(1,[CustomerFirstName],",")])

[Welshgasman]

So like this in a query field?

Expr1: Left([InStr(1,[CustomerFirstName],",")])

Not quite more like

Expr1: Left(CustomerFirstName,InStr(1,[CustomerFirstName],",")-1)

[templeowls]

That worked! One question though...say someone imported a report that didn't have the salutations. This query wouldn't be screwy because of that, would it?

[Welshgasman]

That worked! One question though...say someone imported a report that didn't have the salutations. This query wouldn't be screwy because of that, would it?

Yes, as Instr() would then return 0 ?
Test with IIF() and Instr() in that case. If Instr > 0 then use the above, else just use the incoming field.

See if you can construct that.

That is why sometimes you need a temp table/DB as you have a lot of tidying up to do before you can put the data into your live tables.

At present this is a simple IIF()

HTH

[templeowls]

Okay I'll try that. Also, very dumb question....but what's the proper way to link up a temp table and a live one? I created the query from the temp table, now how do I feed that query info into the live table?

[Welshgasman]

How did you put the data into the live table in the first place?
It would be by the same method, except now the source is the temp table not the external source.?

You could do this on the fly as well, as you do your import.? That decision is up to you.?

Perhaps safer to a temp table first?

[templeowls]

The data was imported via the VBA code above. But now its importing to the temp table rather than the live, and then cleaned up in the query. I'm not exactly sure how to get the query data into the live table

[Welshgasman]

The data was imported via the VBA code above. But now its importing to the temp table rather than the live, and then cleaned up in the query. I'm not exactly sure how to get the query data into the live table

Look to see what this import spec "Import-CRM" does?

If you were just Appending then append. If updating then Update with the correct corresponding fields.

The fact that you run "qryClear_RawAudit" first, leads me to believe you would be appending.?

[templeowls]

Yes its appending. I appended the query to the live table and it worked....but how do I get it to append to that table every time someone does a import? Build it into the VBA of the import form?