Calling a function to fill (update) field value in query/table.

[EmOxon]

Hi everyone

I wrote a simple function that picks the value based on the selection
criteria (See below)
When I try to use it by putting it with field values as arguments e.g.
GetLevel([SerialNumber]), Access gives me a compile error.

Any help would be very appreciated.
Thanks
EM

Public Function GetLevel(lngSerialNumber as Long) As String


If lngSerialNumber = 1113 Or lngSerialNumber = 1713 Then
GetLevel = "Iron"
ElseIf lngSerialNumber >= 1200 And lngSerialNumber <= 1300 Then
GetLevel = "Copper"
ElseIf lngSerialNumber >= 1000 And lngSerialNumber <= 2000 Then
GetLevel = "Gold
Else
GetLevel = Null 'Or whatever you want to return if it is
anything else
End If
End Function

 

[Granny Spitz via AccessMonster.com]

I wrote a simple function that picks the value based on the selection
criteria (See below)

You wrote it? That looks like Klatuu's handwriting to me.

GetLevel = Null 'Or whatever you want to return if it is
anything else

A function can't return a NULL value as a string. You'll need to alter this
to something like:

GetLevel = "Invalid Serial #"

When I try to use it by putting it with field values as arguments e.g.
GetLevel([SerialNumber]), Access gives me a compile error.

What exactly is the compile error message and what line is highlighted?

 

[Guest]

Am I that obvious, or are you just watching the posts?
I thought I had sent a rewrite because I noticed the Null problem after I
sent it to him. I don't see why he would be getting a compile error. God
forbid anyone should actually provide enought information

I wrote a simple function that picks the value based on the selection
criteria (See below)

You wrote it? That looks like Klatuu's handwriting to me.

GetLevel = Null 'Or whatever you want to return if it is
anything else

A function can't return a NULL value as a string. You'll need to alter this
to something like:

GetLevel = "Invalid Serial #"

When I try to use it by putting it with field values as arguments e.g.
GetLevel([SerialNumber]), Access gives me a compile error.

What exactly is the compile error message and what line is highlighted?

 

[EmOxon]

Hi guys

No need to fight though, I didn't use Null value anyway.
The compile error disappeared once I transported test table, query and
module in new DB . So the query works now, however the function does
not return any value, i.e. the field is empty.

Am I that obvious, or are you just watching the posts?
I thought I had sent a rewrite because I noticed the Null problem after I
sent it to him. I don't see why he would be getting a compile error. God
forbid anyone should actually provide enought information

I wrote a simple function that picks the value based on the selection
criteria (See below)

You wrote it? That looks like Klatuu's handwriting to me.

GetLevel = Null 'Or whatever you want to return if it is
anything else

A function can't return a NULL value as a string. You'll need to alter this
to something like:

GetLevel = "Invalid Serial #"

When I try to use it by putting it with field values as arguments e.g.
GetLevel([SerialNumber]), Access gives me a compile error.

What exactly is the compile error message and what line is highlighted?

 

[Guest]

There is no fight here.
What data type is the SerialNumber field?
Have you tried calling it in the immediate window to see what it returns?
I did that in testing before I sent it and was getting back the expected
results.
Try setting breakpoints and debugging to see what it is doing.

Hi guys

No need to fight though, I didn't use Null value anyway.
The compile error disappeared once I transported test table, query and
module in new DB . So the query works now, however the function does
not return any value, i.e. the field is empty.

Am I that obvious, or are you just watching the posts?
I thought I had sent a rewrite because I noticed the Null problem after I
sent it to him. I don't see why he would be getting a compile error. God
forbid anyone should actually provide enought information

EmOxon wrote:
I wrote a simple function that picks the value based on the selection
criteria (See below)

You wrote it? That looks like Klatuu's handwriting to me.

GetLevel = Null 'Or whatever you want to return if it is
anything else

A function can't return a NULL value as a string. You'll need to alter this
to something like:

GetLevel = "Invalid Serial #"

When I try to use it by putting it with field values as arguments e.g.
GetLevel([SerialNumber]), Access gives me a compile error.

What exactly is the compile error message and what line is highlighted?

 

[Granny Spitz via AccessMonster.com]

Am I that obvious, or are you just watching the posts?

It says right here in the Book of Answers "Klatuu wrote the code 9/28/2006"
and "A function can't return a NULL value as a string." Hon, you must be
famous if your name's printed in the Book of Answers.

I don't see why he would be getting a compile error.

If it's a compile error, I don't think it's from your code. I think it's
from elsewhere.

 

[Guest]

BOOK OF ANSWERS!!!

ratz, I didn't know there was such a book. I've been making up this stuff

 

[Granny Spitz via AccessMonster.com]

BOOK OF ANSWERS!!!

ratz, I didn't know there was such a book. I've been making up this stuff
on my own for years.

I had to wing it, too, until I became a granny. And then I found out
grannies get issued the Book of Answers. Mommies get eyes in the back of
their heads, grannies get the Book of Answers, and grandads get the Book of
Rules (how things are *supposed* to work). Oh, yeah. And dads get a choice
of the Book of How To Fix Anything or a compass to help them find their way.
Sadly, many of the dads who choose the compass break theirs and wish they'd
chosen the Book of How To Fix Anything.

You mean I can just copy stuff from this book and
create amazing applications?

If you're a granny, yes. But I'm guessing you haven't been issued the Book
of Answers. In that case, you need to get on your granny's good side,
because she has a lot of wisdom to impart. Chocolate fudge and flowers will
get you far. An all-expenses paid cruise to the Caribbean wouldn't hurt,
either.

 

[Guest]

Well, I chose the Book on How to Fix Things, but I chose the hard bound
instead of the pocket version. Problem is, the hard bound is to big to carry
around and I never have it with me when something breaks.

And being a grandfather, I have the Book on How Things are Supposes to Work,
but the grand kids tell me the information in it is out of date and no longer
applies.

 

[Granny Spitz via AccessMonster.com]

Well, I chose the Book on How to Fix Things, but I chose the hard bound
instead of the pocket version. Problem is, the hard bound is to big to carry
around and I never have it with me when something breaks.

And being a grandfather, I have the Book on How Things are Supposes to Work,
but the grand kids tell me the information in it is out of date and no longer
applies.

LOL!

 

[EmOxon]

Thanks - it's working . I just misspelled variable name.