Complex Formulas

[LadyDi]

There are a couple formulas that I was wondering if it is possible to create.

1. Can a formula be written to look in a text field for a word? For example in my raw data, there is a cell where the salesman types the reason for a change order (i.e. customer wants to delete equipment from the order). I would like to set up a formula that will look in that and tell me that it contains the word "delete". Is that possible?

2. Can a formula be written to change the format for which a name is entered? For example, in my raw data, the salesman types his name in first name then last name (i.e. Mickey Mouse). I would like to change that to show last name, first initial (i.e. Mouse, M.). Is that possible?

3. I was also wondering if it is possible to create an ODBC connection in an Excel Spreadsheet - like a pass through query in Access.

 

[techsupport1]

Hey LadyDi,

I can only answer the first two as I'm somewhat lacking Access skills haha. I wrote these to go into excel, not sure if you wanted VBA or what but here ya go.

1.

=IF(ISERR(FIND(D13,E13)),"not found","found")

Where D13 is the text you want to search for and E13 is the field you want to search


2.

=RIGHT(D16,LEN(D16)-FIND(" ",D16)) & ", " & LEFT(D16,1)

Where D16 is the field with the name in it. Note that the name has to have a space in it and will change it from format "Jane Doe" to "Doe, J"


Hope this helps

 

[noboffinme]

Hi LadyDi

You can create an ODBC connection to an Access database & use the tables from the database.

Is that what you wanted to do?

I'm using Office 2007, what are you using?

 

[LadyDi]

The formulas you provided were perfect. I was wondering if the first formula could be modified to look for multiple words (i.e. for a change order where parts are deleted, the salesman could use the word delete, deleted, deletion, or remove).

As for the OBDC connection, I already have that connection to Access, I would like it in Excel, if that is possible. On the spreadsheet I am creating, the salesmen provide the order number, but not the order date. I have a query set up that searches the ordering system for the order date of each order number. I would like to get this to work without having to manually copy and paste the order numbers into the query.

I am using Office 2007.

 

[techsupport1]

Aboslutely, see the modifications below. Note that instead of D14, D15 or D16 you can put "delete" (including the quotes) or something similar.

=IF(OR(NOT(ISERR(FIND(D14,E13))),NOT(ISERR(FIND(D15,E13))),NOT(ISERR(FIND(D13,E13)))),"found","not found")

 

[LadyDi]

That is perfect. Thank you very much for your help.

 

[techsupport1]

Glad to! If you don't mind, could you take a look at the issue I'm currently working on? It's VBA heavy and is slightly beyond my current ability.

http://www.access-programmers.co.uk/forums/showthread.php?p=1136274#post1136274

Any help is greatly appreciated

 

[LadyDi]

I'm sorry, I don't think I will be much help with that. I've written a lot of vb, but normally, I do it in Access.

I did look in my "Object Browser" to see what I could find. In addition to the BeforeSave event, I found a WorkbookBeforeSave event. Perhaps, if you tried that one it would work better for you. I also found something that said you set "Save Defaults" (I'm not sure if that would apply to one specific workbook, or if it applies to the application - it might be something to try too).

Sorry, I can't be more help.

 

[noboffinme]

Hi LadyDi

If you create an ODBC connection from Excel, you can connect to the Access database & bring all the results of an Access query into your Excel spreadsheet.

Open Microsofts ODBC Data Source Administrator >> go to the System DSN tab >> Add >> select the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) driver >> Finish.

Give the connection a Name & Description & browse to your Excel workbook.

Do the same with your Access database.

Go back to your Excel workbook & the 'Data' tab >> 'From Other Sources' >> 'From Microsoft query' >> Select the Access database from the list of connections & you will see all the Tables & 'Views' (Queries) that have been created in Access.

You need to have Access open & run the query, do some testing on what's available to you.

Follow the wizards instructions & have the data sent to Excel.

HTH

 

[LadyDi]

I did as you suggested, but when I click on Finish, it tells me that my connection has failed, and I don't get any results. Am I having a problem, because the query I am selecting in Access is a Pass Through query?

 

[lemo]

maybe try using non pass-through query from the same database as a test? if it works, then you know the problem is in the pass-through query, and not in the connection.

i use a few pivot tables in my excel 2007 files linked to access queries. the way i set them up was -

'insert' pivot table;
pick 'use an external data source', then click on 'choose connection...';
click on ' browse for more...';
select your database;
then you'll get the menu with all the connection options, in a few tabs. you may have to play with those. my default connection is 'ms office 12.0 access database engine ole db provider'. i have no idea what it means and how it's different from odbc as i am strictly an end user;
then, as far as i remember, you can pick your query from the list.

hope it helps.
l

 

[noboffinme]

LadyDi

Sorry I should have also added that you need to select an Access driver when you set up the Access database datasource.

Select the Access Driver that has accdb in the list of extensions.

Open Microsofts ODBC Data Source Administrator >> go to the System DSN tab >> Add >> select the Microsoft Access Driver (*.mdb, *.accdb) >> Finish.


Try to connect again.

The library that Lemo has suggested is the reference library most suitable for Access 2007 - it gives you access to the library you will need to use the Access app from Excel.

Let me know if you see your Pass Through query in your Excel spreadsheet.