expression error

[Derek Ruesch]

I have a fairly complex UNION query in my Access database
that includes several Iff() functions.

This query works fine on a Windows 2000 computer but when
I run it on a Windows NT 4.0 computer I receive the
following error message: "This expression is typed
incorrectly or it is too complex to be evaluated. For
example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by
assigning parts of the expression to a variable (Error
3071).

When I took out the UNION ALL clause and the query that
follows this clause, the query ran fine on the Windows NT
4.0 computer. However, I need this to be a UNION ALL
query.

Why does this query run fine on the Windows 2000 computer
but errors out on the Windows NT 4.0 computer? What can I
do to make sure this query runs fine on both the Windows
2000 computer and the Windows NT 4.0 computer?

I would greatly appreciate any help. Thanks.

Derek Ruesch

 

[Allen Browne]

Derek, first thing to look for is differences between the Office service
packs of the two computers.

Whatever version of Access you are using, open Help | About, and see what
service pack is installed. For example, if this is Access 2000 and you have
service pack 3 installed, you should see "Access 2000 SP-3". You can
download the serivce packs from support.microsoft.com

Next thing is to ensure that the data type returned by each field of the
SELECT statements in the UNION query matches. Since these are calculated
fields, Access is likely to misunderstand the data type unless you
explicitly typecast. For example, the calculated field:
Nz(DateAdd("d",30,[OrderDate]),Date()) As PaymentDate
is likely to be interpreted as Text. To solve that, use:
CVDate(Nz(DateAdd("d",30,[OrderDate]),Date())) As PaymentDate
Once the data type matches, the UNION query should be able to proceed.

More info on typecasting calculated fields:
http://allenbrowne.com/ser-45.html

 

[Wayne Morgan]

My first guess would be that the MDAC and/or Jet files aren't current on the
NT4 computer.

http://www.microsoft.com/downloads/...6c-8cdd-4168-a030-46f815d3f5cd&DisplayLang=en

http://www.microsoft.com/downloads/...e3-c795-4b7d-b037-185d0506396c&DisplayLang=en

 

[Derek Ruesch]

Thanks. The new MDAC and Jet files corrected the problem.

Derek

 

[zfidjili]

Derek, first thing to look for is differences between the Office service
packs of the two computers.

Whatever version of Access you are using, open Help | About, and see what
service pack is installed. For example, if this is Access 2000 and you have
service pack 3 installed, you should see "Access 2000 SP-3". You can
download the serivce packs from support.microsoft.com

Next thing is to ensure that the data type returned by each field of the
SELECT statements in the UNION query matches. Since these are calculated
fields, Access is likely to misunderstand the data type unless you
explicitly typecast. For example, the calculated field:
Nz(DateAdd("d",30,[OrderDate]),Date()) As PaymentDate
is likely to be interpreted as Text. To solve that, use:
CVDate(Nz(DateAdd("d",30,[OrderDate]),Date())) As PaymentDate
Once the data type matches, the UNION query should be able to proceed.

More info on typecasting calculated fields:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a fairly complex UNION query in my Access database
that includes several Iff() functions.

This query works fine on a Windows 2000 computer but when
I run it on a Windows NT 4.0 computer I receive the
following error message: "This expression is typed
incorrectly or it is too complex to be evaluated. For
example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by
assigning parts of the expression to a variable (Error
3071).

When I took out the UNION ALL clause and the query that
follows this clause, the query ran fine on the Windows NT
4.0 computer. However, I need this to be a UNION ALL
query.

Why does this query run fine on the Windows 2000 computer
but errors out on the Windows NT 4.0 computer? What can I
do to make sure this query runs fine on both the Windows
2000 computer and the Windows NT 4.0 computer?

I would greatly appreciate any help. Thanks.

Derek Ruesch