Check For Multiple Blank Fields To Combine. May or may not be null

[Shaunk23]

Currently writing database for my small business ( International Freight Forwarder/ Maritime Industry) I have made great progress after getting a few tips from users on this site so here is my next problem! I have a "edit Shipper Information Form" in which user can enter the below Data

Shipper
Address line 1
address line 2
city
state
zipcode
phone numbers etc..

My question pertains to the address. Each of the address fragments have their own field in table. On the main display page I'm combining them.

I managed to write the code for IF the second line of the address is empty then omit it, don't add that to the text box for full address display.

I had written this..

'' Combine address into full shipper address including both lines of street address
If IsNull(Form_AECLeditshipper.Shipper_Street_Address_2) = False Then
Form_AECLeditshipper.Shipper_Full_Address = (Form_AECLeditshipper.Shipper_Street_Address_1 + vbCrLf + Form_AECLeditshipper.Shipper_Street_Address_2 + vbCrLf + Form_AECLeditshipper.Shipper_City + " " + Form_AECLeditshipper.Shipper_State + ", " + Form_AECLeditshipper.Shipper_ZipCode + vbCrLf + Form_AECLeditshipper.Shipper_Country)
End If

'' Combine into full address if the second line of street address is null. Don't use Space in combination text box.
If IsNull(Form_AECLeditshipper.Shipper_Street_Address_2) = True Then
Form_AECLeditshipper.Shipper_Full_Address = (Form_AECLeditshipper.Shipper_Street_Address_1 + vbCrLf + Form_AECLeditshipper.Shipper_City + " " + Form_AECLeditshipper.Shipper_State + ", " + Form_AECLeditshipper.Shipper_ZipCode + vbCrLf + Form_AECLeditshipper.Shipper_Country)
End If



This works perfect!! Now.. on occasion in my industry i have a "foreign to Foreign" move in which there IS NO state or zip code..

Is there a way to check TWO text boxes or three for being empty..?? I had tried

If IsNull(Form_AECLeditshipper.Shipper_Street_Address_2 AND Form_AECLeditshipper.shipper_Zip_Code) = True Then

blah blah... It throws error.. Is there a way to check for the two text boxes not containga value? Then i can tell it how to format the "Full Address"

 

[spikepl]

http://office.microsoft.com/en-us/a...-lines-in-labels-and-reports-HA001149472.aspx

 

[Shaunk23]

I had tried that... Using my variables i had put


Form_AECLeditshipper.Shipper_Full_Address = Trim([Form_AECLeditshipper.Shipper_Street_Address_1] & (vbCrLf + [Form_AECLeditshipper.Shipper_Street_Address_2]) & vbCrLf + Form_AECLeditshipper.Shipper_City & (" " + [Form_AECLeditshipper.Shipper_State]) & (", " + [Form_AECLeditshipper.Shipper_ZipCode]) & vbCrLf + Form_AECLeditshipper.Shipper_Country)


again there will always be a address line 1, city, & country. Address Line 2, zip code & state could be null.

 

[spikepl]

Tried "that" ? What exactly? The function CanShrinkLines listed lower down?

 

[Shaunk23]

Sorry.. Should have been more specific. I had tried using the Trim function which i had read about on forums. I have an edit shipper form... Upon clicking a command button at the bottom of that to "save" and return to main export screen, it should check for the individual fields & then store the correct formatted value in a FULL SHIPPER ADDRESS field thats on the main screen.. So i had written the below. It seems to be formatted exactly as the external link you sent me said to do it. Only adds the zip code / state & address line 2 IF they aren't empty.


Form_AECLeditshipper.Shipper_Full_Address = Trim([Form_AECLeditshipper.Shipper_Street_Address_1] & (vbCrLf + [Form_AECLeditshipper.Shipper_Street_Address_2]) & vbCrLf + Form_AECLeditshipper.Shipper_City & (" " + [Form_AECLeditshipper.Shipper_State]) & (", " + [Form_AECLeditshipper.Shipper_ZipCode]) & vbCrLf + Form_AECLeditshipper.Shipper_Country)

 

[Shaunk23]

Never mind.. I figured it out... for some reason they were using [ & changing it to ( worked. Thanks for your help!