Hi Steve,

aah, Aries ... I thought WI might be Working Interest ...
guess it is! I have pulled data from Aries before.

I do not know if the fieldname is causing the problem --
just kinda odd to call something that name

lets break apart your equation:

Val(
(
Left([expression],
Len([expression])-
(
Len([expression])-InStr(1,[expression]," ")
+1)
))/100)


what if [expression] is null?
-- Len([expression]) will be an error
-- InStr(1,[expression]," ") will be an error
-- Left([expression],#) will be an error

what if it doesn't have a space in it?

definitely, if there is no record to even get the field
from, there will be a problem...

Why not make a UDF (User-Defined function) to calculate WI?

What is an example if what [Expression] looks like? Also,
what is the name of a field in the table that will always be
filled out (so we can test if there is a record)? I will
help you with a UDF.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Steve wrote:
Yes, its the fieldname for a column in a table for a commercial product
called Aries. Are you thinking that's my problem?
Thanks Steve

"strive4peace" "strive4peace2006 at yaho" wrote:


Hi Steve,

What does [Expression] represent?

surely this is not a fieldname...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Steve wrote:

Thanks so much for you help. I'm having a tough time getting into this
website and am not getting notifications of replies. I tried to implement
your suggestion and I'm still getting the #error in the second query. Anyway
I'm including the sql for the queries I'm having the issues with.

First query:
SELECT dbo_AC_ECONOMIC.PROPNUM,
(Val((Left(nz([Expression]),Len(nz([Expression]))-(Len(nz([Expression]))-InStr(1,nz([Expression])," ")+1)))/100)) AS [Initial WI]
FROM dbo_AC_ECONOMIC
WHERE (((dbo_AC_ECONOMIC.KEYWORD)="net") AND ((dbo_AC_ECONOMIC.SECTION)=7)
AND ((dbo_AC_ECONOMIC.QUALIFIER)=[forms]![frmsaverun].[txtqual]));
Second query:
SELECT dbo_AC_PROPERTY.PROPNUM, dbo_AC_PROPERTY.ENTITY, [WC Initial
WI].[Initial WI], dbo_AC_PROPERTY.RUN
FROM dbo_AC_PROPERTY LEFT JOIN [WC Initial WI] ON dbo_AC_PROPERTY.PROPNUM =
[WC Initial WI].PROPNUM
WHERE (((dbo_AC_PROPERTY.RUN)="y"));