Trying to use Case

[Krystoff]

Hello all,

I am trying to use a Case statement to give me a correct value for my Address Lines in a report. The Report is based off a query and I am calling the Function I made from the query. When I run the query, it runs the function and opens the query but nothing seems to happen. I am really inexperienced at building functions and I thought I would post here to see what I am doing wrong.

Here is my function:

Option Compare Database
Option Explicit

Function AddressL2()
Dim Attn, ADL2, Dept, StreetAddress

Select Case Attn
Case Is <> Null
ADL2 = Attn
Case Is = Null
If Dept Is Not Null Then
ADL2 = Dept
End If
Case Else
ADL2 = StreetAddress

End Select

End Function


I am using a simplified vs. so that once I can get this correct, I can get everything else working. What it does is this. If Attn is filled, then I want ADL2 to be ATTN. If Dept is filled, but Attn is not, then I want ADL2 to be Dept. Other than that I want it to be Street1.

Any help is appreciated

Chris

 

[Darrylles]

Hi K,

First of all, a function returns a value, it also mostly
expects a value as a parameter).

e.g.

Function AddXY(x as integer, y as integer)

AddXY = x + y

end sub

This expects x and y as parameters.

You would call this function like this:

txtAdded = Addxy(fieldx,fieldy)

txtAdded would then show fieldx + fieldy


In your function, you must have a statement to return
the function value.....

AddressL2 = ADL2

Just before the 'End Function' statement.

You would then call this function from your calling
form like this....

txtAddL2 = Addxy(fieldx,fieldy)

Where txtAddL2 is the resultant text box, fieldx & fieldy
are textbox values.

Regards,

Darrylle
&quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot;

 

[JeremyNYC]

Chris,

There is absolutely nothing wrong with a function that doesn't take an argument and doesn't return a value.

Your select structure, though, does need a bit of tweaking. If you're writing a case for null and one for not null, there really aren't any other cases. I would rewrite it this way:


Select Case Attn
Case Is Null
'Something
Case Else
'Something else
End Select

However, it's clear that you're trying to get three cases out of this, so something's amiss. What are you trying to test for?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[Krystoff]

Darrylle,

Thanks! I knew I was missing something when I was calling my function. I couldn't figure out how to get the values of Attn, Dept, and StreetAddress into the function (They come from a table) and then I couldn't figure out how to get them out of the function once I did so.

Jeremy,

Thanks for the help again! You seem to be helping me a lot lately.

What I am trying to do is this. I have a spot for address's on my report. I need the report to collapse when a field is not filled out. The Can Grow, Can Shrink properties won't work as there is a large text field to the right of the Address lines that mess with those properties.

So, in this case I am trying to get function to give me the value of Address Line 2. (ADL2) This field will always be populated with something. It will either have the Attn: (Attention) field, the Dept (Department) field, or the Street1 field.

In my case statement above, the logic is as follows.

If Attn Is Not Null - Use Attn:
If Attn Is Null, it can be either of two values, Dept, or StreetAddress. So I tried testing for Dept with an If statement. If Dept Is Null, then StreetAddress, Else Dept.

Thanks!

Chris