Help coding a nested If, then, else statement

[Krystoff]

Hello all,

I have a letter report that is based off of customer information. The customers can have either a mailing address or a phsyical address but there will always be a physical address.

I want the mailing address to take precedence over the physical address if there is a mailing address, otherwise I want the physical address to show.

Now this wouldn't be a problem if there wasn't a Street 2 field. If there is no street 2 or mailstreet 2 entered, then I want the report to put the city, state and zip (Either Mailing or Physical) on that line.

So far here is what I have on the query:

PCSZ = Physical City, State, Zip
MCSZ = Mailing City, State, Zip

Adressline2: Iif([MailingStreet2] is null,[MCSZ],iif([Street2] is null,[PCSZ],"fields are all blank"

I know that statement does not work! But I have no idea how to do an else if statement in the query. I was thinking it could be done in a module and I could call it on the Open_Report event but I have very limited coding knowledge. Am I just going about this the wrong way or have I been up to long?

Any help is appreciated.

Chris

 

[FancyPrairie]

Quick thought. You could call a function from within your query. Something like this:

SELECT Field1, GetStreetAddress([mail1],[mail2],[street1],[street2]) AS Adressline2
FROM YourTable;

The Function would look something like this:

Function GetStreetAddress(arg1 as string, arg2 as string, arg3 as string, arg4 as string) as String

do your if-then-else statement here and return the results like this:

GetStreetAddress = "YourAnswer"

End Function

 

[Krystoff]

Thanks for the reply FancyPrairie!

I wrote the function and it looks like this:

Function GetStreetAddress(Data_Cust As String) As String

If IsNull([MailingStreet2]) Then
If IsNull([MailingCity]) Then
If IsNull(Street2) Then
Addressline2 = CSZ
Else
Addressline2 = [Street2]
End If
Else
Addressline2 = [MailCSZ]
End If
Else
Addressline2 = [MailingStreet2]
End If

End Function


Data_Cust is the table where I am getting all the information from. I am trying to call this from a query called D_Cust_Qry and I can't figure out how. I'm not even sure that my function works. The name of the module I put this function in is called Test.

Can anyone show me what I am doing wrong? Besides all of it =)

 

[vb6novice]

In your description of the intended logic, it doesn't seem like you have provided enough information to determine whether the system should use MCSZ or PCSZ if both MialingStreet2 and Street2 are null. You did state that there will ALWAYS be a physical address, therefore, if there is no mailing address (determined by a null in MailingStreet2) then you should use the physical address.

My guess is that your current statement only gives you the "fields are all blank" when both MailingStreet2 and Street2 have a value, which is not what you want.

It appears from your description that:
1) PCSZ will always have value
2) MCSZ may have a value
3) MailingStreet2 is the mailing address (e.g. PO BOx 123)
4) Street2 is the physical street address (e.g. 123 Main St)
5) If MailingStreet2 is null, MCSZ will also be null

Is there an AddressLine1 which will display either MailingStreet2 or Street2? If there is, the logic I believe you want is:
If MailingStreet2 has value, you want to use the mailing address (MailingStreet2 in AddressLine1 and MCSZ in AddressLine2).
If MailingStreet2 is null and Street2 has value, you want to use the physical address (Street2 in AddressLine1 and PCSZ in AddressLine2).
If MailingStreet2 and Street2 are both null, you want to use the physical address (NOTHING in AddressLine1 and PCSZ in AddressLine2).

If this logic is correct, try:

AddressLine2: Iif([MailingStreet2] is NOT null,[MCSZ],[PCSZ)

 

[Krystoff]

You are pretty close VB6Novice!

I did leave off some information.

PhysAddress1 and MailingStreet1 and the first line under the Customer Name. IE 1234 Anywhere Street or P.O. Box 123

MailingStreet2 and Street2 are the second line IE. C/O Norman Brown
Could be different for Physical and Mailing so we make allowance for it.

PhysAddress1 and MailingStreet1 has a single line that will always have something in it so we can say:

If MS1 is null, PS1, MS1
Very easy.

The second line is hard because if there isn't any Street2 (Mailing or Physical) Then I don't want a blank line there, I want to bring the City State Zip up from line three and make it Line two.

Other than that you are correct.
1) PCSZ will always have value
2) MCSZ may have a value
3) If MailingStreet1 (Or any of the mailing fields)is null, then MCSZ is null.

So for one field on the report, I have 4 different values. MailingStreet2, Street2, MCSZ, or PCSZ

 

[FancyPrairie]

You need to step thru your procedure with debug. Type the word Stop at the beginning of your procedure (or set a breakpoint (press F9 on the line you want the breakpoint)). Press F8 to proceed thru your code one line at a time. (Press F5 to continue running code) Your can examine the values of each variable by simply placing the mouse pointer over the variable name. Or, in debug's immediate window, type (for example) ?MailingStreet2.

My hunch is that your code doesn't know what MailingStreet2, etc. is. You need to pass these arguments to your function. For example, your function should look like this (Note that all arguments must be passed to this function)


Function GetStreetAddress(MailingStreet2 as Variant, MailingCity as Variant, Street2 as Variant, CSZ as Variant, MailCSZ as Variant) As String

Stop
If IsNull(MailingStreet2) Then
If IsNull(MailingCity) Then
If IsNull(Street2) Then

GetStreetAddress

= CSZ
Else
GetStreetAddress= Street2
End If
Else
GetStreetAddress= MailCSZ
End If
Else
GetStreetAddress= MailingStreet2
End If

End Function

Your query would look like this:

SELECT *, GetStreetAddress([MailingStreet2], [MailingCity], [Street2], [CSZ], [MailCSZ]) AS Adressline2
FROM YourTable;