Generate address in report using IIf IsNull

[Ramzess II]

Greetings!

Most likely it's simple one.
I have table with many different fields for parts of an address, like city, parish, street, region etc.
For each record some of those fields would be empty, others filled.

At the end what I need is to combine all those fields together into one, so making the full address, seperated with commas and if certain field is empty
nothing is displayed. So far it works in form, though it is wrong way to do it - I just added additional field to my table and in form I put in code like this:

Dim fulladdress As String
fulladdress = IIf(IsNull([street]), "", [street] & Chr(44) & Chr(32)) & IIf(IsNull([par_city]), "", [par_city])

Me.faddress.Value = fulladdress

so when I enter a new cient via form, the parts off address are combined and stored in the same table from where it goes to report.

Now I know it is wrong way to do and I decided that I need to reduce my table, so this full address line should be generated 'on the fly' in some unbound text field within report using the same way I did in form.

Somehow it does not work this time.
My form is built on table, same as the report.

Can anyone give me a solution?

Thank you in advance.

 

[Rich]

It's something like
=[FirstName] & " " & [LastName] & Chr(13) & Chr(10) & [Address1] & Chr(13) & Chr(10) & IIf(IsNull([City]),"",[City] & Chr(13) & Chr(10)) & [PostalCode]

 

[Ramzess II]

Rich, you mean that I should put something like you posted in the control source of this unbound field in the report, or I should put it in report's on open event, like I did it before with the peace of code from the form which of course didn't work out.

 

[KenHigg]

I would say your method is correct - to concatenate it on the fly.

Looks like what you are doing should work - what kind of error are you getting?

ken

 

[Rich]

Rich, you mean that I should put something like you posted in the control source of this unbound field in the report,

Yes, you don't need vba to do something this simple

 

[Ramzess II]

Well Rich, when I put such thing in field's control source I get an error re invalid syntax. well I am not that smart to find out where the syntax is corrupt, but I used the same one you gave me, just changed the names ot the fields.

 

[Ramzess II]

KenHigg: initially I put this code in report's on open event:

Private Sub Report_Open(Cancel As Integer)
Dim fulladdress As String

fulladdress = IIf(IsNull([par_street]), "", [par_street] & Chr(44) & Chr(32)) & IIf(IsNull([par_city]), "", [par_city] & Chr(44) & Chr(32)) & IIf(IsNull([par_place]), "", [par_place] & Chr(44) & Chr(32)) & IIf(IsNull([par_parish]), "", [par_parish] & " pagasts" & Chr(44) & Chr(32)) & IIf(IsNull([par_pooffice]), "", "p.n. " & Chr(34) & [par_pooffice] & Chr(34) & Chr(44) & Chr(32)) & IIf(IsNull([par_region]), "", [par_region] & " rajons" & Chr(44) & Chr(32)) & IIf(IsNull([par_zip]), "", "LV-" & [par_zip] & Chr(44) & Chr(32)) & IIf(IsNull([cnt_name]), "", [cnt_name])

Me.final.Value = fulladdress
End Sub

I attached the jpg file with an error I get

 

[KenHigg]

What Rich suggested should work. You may try re-building this one small pc at a time. That way the offending pc will surface...

???
ken

 

[Ramzess II]

KenHigg, I just tested it step by step andding peace by peace,
and the part where access gives me an error of syntax is this:

IIf(IsNull([par_city]), "", [par_city])

it is like I write in the control source of unbound field:

=[company_name] & ", " & [street] & IIf(IsNull([par_city]), "", [par_city])

and as I enter this part: IIf(IsNull([par_city]), "", [par_city]) I get syntax error. Sorry, as I said I'm not good at this.

 

[Rich]

what happens with
=IIf(IsNull([par_city]), " ", [par_city])

 

[Ramzess II]

here is the message I get in case of =IIf(IsNull([par_city]), " ", [par_city])

though I don't think the issue is in whether I write "" or " "
I think, though I'm not sure, that problem is really in syntax, like you can write it like this in VB but can't in control source. something, just like access points is wrong with syntax - commas, brackets or something, I just don't know what exactly.

 

[Rich]

Is Par City shown as par_city in the field list, or par city ?

 

[Ramzess II]

yeah, it is the same in the field list. par_city

...means partner's city anyway it doesn't matter....

maybe I can't just give a field names, maybe I have to give a full address like
[Forms]![frmPartners]![par_city] ...I'll just try it out...

 

[Rich]

do your regional settings have the , as the separator?

 

[Ramzess II]

in numbers and currency , is the decimal symbol
in dates . is the time seperator. No other seperators there.
in Latvia we don't seperate thousands...

 

[KenHigg]

Cut and paste the entire piece of code as you have it now to a post...


ken

 

[Ramzess II]

ok, lets take just this one. If we can find out what is wrong here, then I can manage further. So the syntax is wrong in this one:

=[par_id] & ", " & [par_name] & ", " IIf(IsNull([par_name]), "", [par_name])

besides, the part before IIf(IsNull([par_name]), "", [par_name]) works fine.

 

[Brianwarnock]

A missing & before the IIf

Brian

 

[KenHigg]

=[par_id] & ", " & [par_name] & ", " & IIf(IsNull([par_name]), "", [par_name])

 

[Ramzess II]

well & is missing just in this last post, normaly I have it.
But it seems I have found the problem, and as I thought it is the way you write it:

so instead of
=[par_id] & ", " & [par_name] & ", " & IIf(IsNull([par_name]), "", [par_name])

you have to write

=[par_id] & ", " & [par_name] & ", " & IIf([par_street] Is Null;"";[par_street])

Now it works fine, and I will be able to get rid of many stupid things I used before, to get the same results

Thank you all guys! Really thank you.