Excel import from Access

[Melanie]

Hi

I have a query in Access that performs correctly but when I import
that query into Excel it does not perform in the same manner.

This is a simple query, basically...a list of parts with a UnitPrice
less than or equal to zero and does not have delete, obsolete or do
not use in the description. The Unit price part works just fine but
the description criteria does not.

Help?


Melanie

 

[bhicks11 via AccessMonster.com]

Maybe something I've missed Melanie but how did you import an Access query
into Excel?

Bonnie
http://www.dataplus-svc.com

 

[Douglas J. Steele]

Perhaps she's talking about the functionality that's available through Data
| Import External Data | New Database Query (at least that's where it is in
Excel 2003)

 

[bhicks11 via AccessMonster.com]

Thanks,

Like I said, I missed something. I don't use Excel that much (just import or
link it) but I went and looked at what you mentioned. I don't mind learning
though and it looks pretty cool and I'm going to play with it.

Can you help her with the question?

Bonnie
http://www.dataplus-svc.com

Perhaps she's talking about the functionality that's available through Data
| Import External Data | New Database Query (at least that's where it is in
Excel 2003)

Maybe something I've missed Melanie but how did you import an Access query
into Excel?

[quoted text clipped - 11 lines]

 

[Douglas J. Steele]

How exactly are you importing it into Excel? What's the SQL of the query?
What versions of Excel and Access are you using/

 

[Melanie]

Hi

I'm using Access and Excel 2007.

I'm retrieving the data using "Get External Data from Access" on the
DATA tab.

This way I can set up the query in Access but my users can run it from
Excel.


Melanie

 

[Douglas J. Steele]

What exactly do you mean by "the description criteria does not"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi

I'm using Access and Excel 2007.

I'm retrieving the data using "Get External Data from Access" on the
DATA tab.

This way I can set up the query in Access but my users can run it from
Excel.


Melanie

 

[Melanie]

My query is Access has a field called "Description" and I need to
eliminate any rows that have certain words in the "description" field.

I just don't understand why the query works in Access but when you
trigger it from Excel........it doesn't.

Melanie

 

[Douglas J. Steele]

What's the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


My query is Access has a field called "Description" and I need to
eliminate any rows that have certain words in the "description" field.

I just don't understand why the query works in Access but when you
trigger it from Excel........it doesn't.

Melanie

 

[Melanie]

The sql is:

SELECT SYSADM_PART.ID, SYSADM_PART.DESCRIPTION, SYSADM_PART.STOCK_UM,
SYSADM_PART.UNIT_PRICE

FROM SYSADM_PART

WHERE (((SYSADM_PART.ID) Not Like "CM*" And (SYSADM_PART.ID) Not Like
"CS*") AND ((SYSADM_PART.DESCRIPTION) Not Like "*DELETE*" And
(SYSADM_PART.DESCRIPTION) Not Like "*OBSOLETE*" And
(SYSADM_PART.DESCRIPTION) Not Like "*DO NOT*" And
(SYSADM_PART.DESCRIPTION) Not Like "Do Not*") AND
((SYSADM_PART.UNIT_PRICE)<=0 Or (SYSADM_PART.UNIT_PRICE) Is Null))

ORDER BY SYSADM_PART.ID;

 

[Douglas J. Steele]

Sorry, no idea. I was thinking it might have been an ADO/DAO type of issue
(in ADO, the wildcard character is %, not *), but since your Part Id
criterion is working, it can't be that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


The sql is:

SELECT SYSADM_PART.ID, SYSADM_PART.DESCRIPTION, SYSADM_PART.STOCK_UM,
SYSADM_PART.UNIT_PRICE

FROM SYSADM_PART

WHERE (((SYSADM_PART.ID) Not Like "CM*" And (SYSADM_PART.ID) Not Like
"CS*") AND ((SYSADM_PART.DESCRIPTION) Not Like "*DELETE*" And
(SYSADM_PART.DESCRIPTION) Not Like "*OBSOLETE*" And
(SYSADM_PART.DESCRIPTION) Not Like "*DO NOT*" And
(SYSADM_PART.DESCRIPTION) Not Like "Do Not*") AND
((SYSADM_PART.UNIT_PRICE)<=0 Or (SYSADM_PART.UNIT_PRICE) Is Null))

ORDER BY SYSADM_PART.ID;