Update Query

[buckwheat]

I have an Excel table imported into Access. There is an Email field. Some records have emails .. . some do not. I need a query please that will put a "Yes" or "1" in all records that have an "@" in the string. I've tried the following SQL code, but the results tell me I am about to up "0" records when I know there are many without email addresses.

UPDATE [Copy of Survey - DB Link2]
SET Email = 'Yes'
WHERE Email = '@';

[JoeM]

The way you code is written, it will only work if the value in your email field is EXACTLY equal to '@', not if it contains '@'.

[JoeM]

Also note that your Update Query will overwrite your email addresses. Is that really what you want to do?
If you are just checking the email address, I would recommend using a calculated field in a SELECT QUERY instead.

That calculated field would look something like:

Email_Check: IIf(InStr([Copy of Survey - DB Link2]![Email],"@")>0,"Yes","No")

(assuming looking for the "@" anywhere in your string)

[buckwheat]

Thanks, I did get this to work. Only problem now that I face is that it will only update the excel spreadsheet if I import it and will not update if I link it.

[JoeM]

Only problem now that I face is that it will only update the excel spreadsheet if I import it and will not update if I link it.

Yes, that is a known limitation. Starting with Office 2003, you cannot update linked Excel tables from Access directly like that (Microsoft lost a lawsuit on the technology they were using to do that).

I believe you can update Excel workbooks from Access via VBA, but that becomes a bit more complex.

If your goal is to update Excel, why not just do it directly in Excel first?

[buckwheat]

Hi Joe,

I currently am using the method of updating in Excel first, but process is going to be published to other users, so I need to automate as much as possible. Because of the limitation for links, I created a macro to import the spreadsheet that I will assign to a button for users, but I could use VBA code that will replace the existing data in the imported table when the button is clicked. Can you help with this please?

[JoeM]

Its not something that I have really done much of, but Google searches return many links on "using Access VBA to update Excel spreadsheets".
Here is one such link: http://www.vbforums.com/showthread.p...ave-excel-file

Note: Another option, which may be a little cleaner, is to create an Excel macro that does what you want to the Excel spreadsheet, and just call/run that Excel Macro from Access VBA. It is a little cleaner, especially if you get lost in all the VBA code, but involves incorporating an Excel Macro into your project too. So I don't know if that is something you want to add.

[buckwheat]

Thanks Joe, I appreciate your suggestion, but is a little beyond my expertise, plus I already have the import, update query and export processes in Access. I'd hate to have to start a different approach. I will post another thread that perhaps can pull all my processes together. Thanks for your help.