Access to Excel Question

[Amileaux]

I currently have a excel file that I update from an access query. However, since I have made a code change in access, this is now returning erroneous information. Here are the particulars:

I have a query in Access that utilizes an if statement (actually IIf in the query)and uses the "Left" function to pull the word "new" from a field, with the default being "old" if the first 3 characters did not read "new". It is a file that I get every month. This month, the programmer decided to change the wording so that for month 11, the word "new" appears at the end of the field. Sooo - I changed my query to read:
Iif([obj type]like "*new*","new","old") to get around this and still have the query work on all the past months. It worked. However, when I opened up Excel and hit the refresh key, everything changes to "old". I even tried starting all over and pulled in the data (data/get external data/ create new query) thinking changes in formulas don't save over. It still brings in all "old", even though when I run the query in access it is correct with a mixture of "old" and "new".

Any ideas? This worked fine when I used:
IIf(left([obj type],3)="new","new","old")

Thank you. Marie

 

[Russell Hauf]

How are you updating the Excel sheet? Is it a linked table? Are you exporting the data? Are you dynamically entering the data into the sheet via VBA?

 

[Amileaux]

I am updating a pivot table in Excel that is tied to the query in Access that has the "problem" function. I set this up in Excel by going to Data/Get External Data/Creat New Query. Once that is done, I normally just click refresh on the pivot table and everything comes across just fine..Does that answer your question? Marie

 

[Russell Hauf]

That does sound weird. Maybe try running a make-table query and basing the pivot table off of the new table. I know that this won't really suit your needs going forward, but maybe it will give some clues as to what's going on.

-rh

 

[Amileaux]

Thank you - it will work with a table because the calculation will already be done. I think the problem must be that excel does not know what to do with the word "like", which would suggest that the processing must happen in excel? That doesn't seem correct - Anyway - thanks! Marie

 

[Russell Hauf]

Also take a look at the following post - for another way of doing this:

http://mrexcel.com/board/viewtopic.php?topic=32011&forum=2&1

 

[Bariloche]

Marie,

How about creating a query that uses the "problem" query and just "passes" everything through? Pull the data through that query and see if it helps. I can't think of any reason why it would, but if I were you I'd try it just to see.

hth

 

[Amileaux]

I did try writing another query that referred to the problem query. Same problem when I try and pull it into excel. Thanks for the thought though! Marie

 

[BrentA]

Hi. Try using the InStr function instead of the Like. Not sure if it'll work, but it will get around the issue if "Like" is the problem.

The function would be:

IIF(InStr([obj type],"new")>0,"new","old")

Brent

 

[Amileaux]

InStr worked!! Thank you. Marie