Unusual formatting problem

[medic133]

I have a subreport which is based on a query. This query has a "calculated" field which is as follows:

Readdress: [Street]&" "&[City]&", "&[State]&" "&[Zip]

The Street, City, State, and Zip fields are within the same query. This combines the above fields to give me a format such as "101 W. 2nd Ave. Aimes, Iowa 12345". This field appears in a subreport which is based on the above query. If all of the above fields have values in the table, the formatting on the report is fine. If, for instance, no street address is in the table, then on the report I have two "spaces" followed by the rest of the query. Similarly, if none of the fields in the table have values, I only have a comma in the field on the subreport. My question is how do I eliminate preceding "spaces" or eliminate the comma to leave a blank field in the subreport. I don't want to make the fields required in the data entry form for the table because this information is often not available. I have tried variations of the following in code for the subreport as well as the main report, but I get an error that I can't assign a value to this field:

If Left(me.readdress,2)=" " then
me.readdress=right(me.readdress,(len(readdress)-2))
end if

I know this would only solve my problem of two preceding "spaces", but I figured if this worked, I could work out the rest. I also don't want to eliminate null values for the above fields in the query because I think that would eliminate records in the query (such as a student's name and exam score) that I need to include in the report (subreport). Any suggestions on this? Is it coding I need in the report or something in the query? Thanks in advance!

 

[KenReay]

Hi

You could write a User Function to concatenate the columns, ignoring any with zero length or null values, and include that user function in your query

do you need more information on how to do this ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JaneC]

Hi,

Think this will work as your calculated field. A separate user defined function would be more elegant though.

readdress : IIf(IsNull([street]),"",[street] & " &quot & IIf(IsNull([city]),IIf(Len([street])>0,", ","&quot,[city] & IIf(Len([state] & [zip])>0,", ","&quot) & IIf(IsNull([state]),[zip],[state] & " " & [zip])


J

 

[medic133]

Ken and J,

Thanks for the response. J, I'll try your idea, but in the meantime, Ken, if you wouldn't mind, I'd like more information on how to perform your suggestion. I'd like to see an example of the user defined function, not only just to learn more, but I've got repetitive components of my code throughout several forms (it simply capitalizes components of fields following spaces or hyphens or preceding periods) which I would like to turn into one of these functions. I just haven't figured out how to pass variables and what not. Your example might very well be helpful in that case if not this one.

Thank you both for your responses!

 

[JaneC]

Hi Medic,

create a new module and paste the following. Note the field names in brackets need not be the same as your table field names. Ie could just as well be a.b.c and d



Function SetAddr(Street, city, state, zip)

SetAddr = IIf(IsNull([Street]), "", [Street] & " &quot & IIf(IsNull([city]), IIf(Len([Street]) > 0, ", ", "&quot, [city] & IIf(Len([state] & [zip]) > 0, ", ", "&quot) & IIf(IsNull([state]), [zip], [state] & " " & [zip])

End Function

Then on your report for the calculated field :

readdress : setaddr([street],[city],[state],[zip])

This will then call the function you have just written, passing the appropriate parameters to it.

HTH

Jane