Newbie Question for ya.

  • Thread starter Thread starter bergwax
  • Start date Start date
B

bergwax

I'm new to Access and used to the ease of Excel.. yep.. need a lil hel
to start off.

I have a couple tables already set up in my database. I then created
query calculating the values from each table (simpl
multiplication/division)... here are my problems...

1. some of the values in the tables are zero... so I get a divide b
zero error. I wanted to create an if statement like: if x = 0, the
find the largest value in the column or if x = 0, then let x = 1...jus
as long as its not zero.

2. some of the results are also very large (relative to what I'
doing... over 100,000). I wanted to create an 'if' statement tha
would do something similar to this: if x > 100000 then x = ">100000"
yep, the '>' sign included.

**if you think there would be a better way to solve this, other tha
'if' statements, please feel free to mention the process**

If anyone can help that would be great!! I know some of this might b
simple but I'm trying to crash through this stuff.. taking a little bi
longer than expected and I'm running out of time.. thanks!
 
Ain't it always the way! You try to start learning a new tool by doing
something simple and you find out it isn't so simple in the new paradigm.

First, a couple of hints: there are several Access newsgroups. Among them
is one named microsoft.public.access.gettingstarted. Another goody is
microsoft.public.access.tablesdesign. Newbies do well to lurk those groups
a lot until most of the posts are rehashes of things you already know (then
you might stick around and answer a few posts to repay the kindness of
others :-) I'm talking a time frame of at least months, not minutes. Once
you get away from the canned facilities, Access has a steep learning curve.
Another good hint is www.mvps.org/access It is the gathering of a lot of
Access lore from experts and pioneers.

The answer to both your issues below will be a single function procedure
that handles both cases and returns with the one correct value. (you Excel
folk call such code "macros". You'll find that a macro in Access is a
different beast altogether and should be avoided if possible!). You must
create a standard module and write your function procedure there.

To call your function, put the following in place of the name of the field
you're massaging::
NewFieldName: =MyNewFunction(FieldName)

Your function will be executed once for each row returned by the query. As
written above it will be passed the returned value in field FieldName. In
turn, your function will do its magic and return a value which then becomes
the value of field NewFieldName.

HTH
 
Back
Top