We have an Access Database were we have all our contacts. The Database creates an excel report for mailing publications.

The problem is that the address field may contain 2 lines. The query created for the report splits the address field in two separate columns. However, if there is a second line, the first line drops the last letter/number which causes mailing issues.

Here is the code using to split the Address field. I did not create the database but I'm trying to fix the report. Any help will be appreciated.

Address1: Str(1,Nz([Address]),Chr(13))=0,[address],Mid([address],1,InStr(1,nz([address]),Chr(13))-2))

Address2: SplitAddress(nz([address]))

THanks

Address1: Iif(Instr([Address], vbCrLf)=0, [Address], Left([Address],Instr([Address], vbCrLf)-1))

Address2: Iif(Instr([Address], vbCrLf)=0, Null, Mid([Address],Instr([Address], vbCrLf)+1))

The MID function normally takes two arguments:

Mid(String, Start, Number of letters)

But if you leave out number of letters it assumes the rest of the length of the string. The long way to write it would be

Address2: Iif(Instr([Address], vbCrLf)=0, Null, Mid([Address],Instr([Address], vbCrLf)+1), Len([Address])-Instr([Address], vbCrLf)+1))


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Mr. Serrano,

THanks for the information, however, when I put your string in the Access query it give me a error message and when you run the query all fields say error.

Its asking for a vaule for the paramater vbCrLf.