Excel not recognizing Access Expression

[nazzoli]

I am running a pivot table in Excel off of an Access Query. The pivot table
is not recognizing the like expression I have built in Access. Any reason
why?

 

[FSt1]

hi
more info please.
if the access query has prompts for parameters on the access side, that may
be your problem.
post the access espression.

Regards
FSt1

 

[nazzoli]

NORY: IIf([ATTY ID]=3042,"NAN",IIf([ATTY ID] Between 80 And 1500 Or [atty
id]<80 And [list date with attorney] Is Not Null Or [forward dte] Is Not Null
Or [status] Like "dis*" Or [status] Like "atty *" Or [status] Like "req*" Or
[status] Like "ban*" Or [STATUS] Like "DIM*" Or [STATUS] Like "JUD*" Or
[STATUS] Like "AT*" Or [status] Like "CL*" Or [status] Like "demand *" Or
[status] Like "served" Or [status] Like "wtg *","YGC"))

 

[nazzoli]

How would I tell if there were paramaters?

hi
more info please.
if the access query has prompts for parameters on the access side, that may
be your problem.
post the access espression.

Regards
FSt1

 

[FSt1]

hi
does the query run in access? what errors are you getting?

Regards
FSt1

NORY: IIf([ATTY ID]=3042,"NAN",IIf([ATTY ID] Between 80 And 1500 Or [atty
id]<80 And [list date with attorney] Is Not Null Or [forward dte] Is Not Null
Or [status] Like "dis*" Or [status] Like "atty *" Or [status] Like "req*" Or
[status] Like "ban*" Or [STATUS] Like "DIM*" Or [STATUS] Like "JUD*" Or
[STATUS] Like "AT*" Or [status] Like "CL*" Or [status] Like "demand *" Or
[status] Like "served" Or [status] Like "wtg *","YGC"))

hi
more info please.
if the access query has prompts for parameters on the access side, that may
be your problem.
post the access espression.

Regards
FSt1

 

[nazzoli]

The query does run in access. I do not receive any errors in Access. The
query runs fine in Access.
When I use a pivot table in Excel the expression does not translate over to
give me the same totals.

hi
does the query run in access? what errors are you getting?

Regards
FSt1

NORY: IIf([ATTY ID]=3042,"NAN",IIf([ATTY ID] Between 80 And 1500 Or [atty
id]<80 And [list date with attorney] Is Not Null Or [forward dte] Is Not Null
Or [status] Like "dis*" Or [status] Like "atty *" Or [status] Like "req*" Or
[status] Like "ban*" Or [STATUS] Like "DIM*" Or [STATUS] Like "JUD*" Or
[STATUS] Like "AT*" Or [status] Like "CL*" Or [status] Like "demand *" Or
[status] Like "served" Or [status] Like "wtg *","YGC"))

hi
more info please.
if the access query has prompts for parameters on the access side, that may
be your problem.
post the access espression.

Regards
FSt1

:

I am running a pivot table in Excel off of an Access Query. The pivot table
is not recognizing the like expression I have built in Access. Any reason
why?

 

[Kevin B]

A parameter query in access lets the user supply one or more criteria
elements each time the query is run by displaying a dialog box and requesting
specific input from the user. If your query is running without displaying
dialog boxes then you do not have a parameter query on the Access side.

 

[FSt1]

hi
now i am confused. access interpets access expressions not excel. I have
never had any problems bringing in data from access unless i have prompt for
parameters on the access side. admittily, i have also never brought data
directily in for a pivit table so i can only guess that may be the problem.
admitily also i have limited experience with excel pivott table but not pivot
table. i usually have to build my own with vb becasue my boss wanted data in
date buckets.

I would try to bring the data into excel outside of the pivot wizard ie MSQ,
to see if that was the problem but beyond that, i am currently at a loss.

regards
FS

The query does run in access. I do not receive any errors in Access. The
query runs fine in Access.
When I use a pivot table in Excel the expression does not translate over to
give me the same totals.

hi
does the query run in access? what errors are you getting?

Regards
FSt1

NORY: IIf([ATTY ID]=3042,"NAN",IIf([ATTY ID] Between 80 And 1500 Or [atty
id]<80 And [list date with attorney] Is Not Null Or [forward dte] Is Not Null
Or [status] Like "dis*" Or [status] Like "atty *" Or [status] Like "req*" Or
[status] Like "ban*" Or [STATUS] Like "DIM*" Or [STATUS] Like "JUD*" Or
[STATUS] Like "AT*" Or [status] Like "CL*" Or [status] Like "demand *" Or
[status] Like "served" Or [status] Like "wtg *","YGC"))

:

hi
more info please.
if the access query has prompts for parameters on the access side, that may
be your problem.
post the access espression.

Regards
FSt1

:

I am running a pivot table in Excel off of an Access Query. The pivot table
is not recognizing the like expression I have built in Access. Any reason
why?

 

[FSt1]

hi,
parameter (or criteria as some call it) are restrictions to data.
ie

8/1/08

like "smith"
<> "smith"
ect
prompt for parameter is when you ask the user to supply the
pamameter(criteria)
ie
[Enter a date]
[enter a name]

a access query can have parameter and still take the data to access but from
excel the driver is not good enough to go to access, realize that there are
prompts for paramters, collect the parameters, come back and display them in
excel. the driver can take prompts for parameter to access provide that they
are on the excel side.

regards
FSt1