I am creating an invoice in a report. I pretty much have everything resolved except a problem that I am having with the address, ship to address. Some addresses are simple, no country and only one address line, however I have other addresses which may be international requiring a country and multiple address lines.
In Microsoft Word there is an address function that will automatically format the address even if certain fields are empty and display blank lines or spaces.
How can I achieve this in an Access report?
Full Version: Address Lines - Blanks
The most effective method, IMO, is to concatenate the address lines into a single string so that it can be placed into a single text box control on the report. Using a combination of IIf's and Chr() strings you can format it pretty much the way you want.
Use the Chr() function to place line breaks between elements in the address like so:
[AddressLineOne] & Iif([AddressLineTwo] Is Null, "", Chr(13) & Chr(10) and [AddressLineTwo])
The control's Can Grow and Can Shrink Properties can control its hieght.
George
Use the Chr() function to place line breaks between elements in the address like so:
[AddressLineOne] & Iif([AddressLineTwo] Is Null, "", Chr(13) & Chr(10) and [AddressLineTwo])
The control's Can Grow and Can Shrink Properties can control its hieght.
George
ok great! ok so let's say I have the following fields and they would appear in the following order in the address block:
firstname
lastname
department
company
address1
address2
city
state
country
zip
so would it be like this:
firstname&" "&lastname,& Iif([firstname] Is Null, "", Chr(13) & Chr(10) and [firstname])Chr,& Iif([lastname] Is Null, "", Chr(13) & Chr(10) and [lastname]) department,& Iif([department] Is Null, "", Chr(13) & Chr(10) and [department])chr,company,chr,address1,chr,address2,& Iif([address2] Is Null, "", Chr(13) & Chr(10) and [address2])chr,city&", "&state&" "&zip,chr, country
So the idea would be that
FirstName LastName (optional)
Department (optional)
Company (required)
Address1 (required)
Address2 (optional)
City, State zip
Country (optional)
firstname
lastname
department
company
address1
address2
city
state
country
zip
so would it be like this:
firstname&" "&lastname,& Iif([firstname] Is Null, "", Chr(13) & Chr(10) and [firstname])Chr,& Iif([lastname] Is Null, "", Chr(13) & Chr(10) and [lastname]) department,& Iif([department] Is Null, "", Chr(13) & Chr(10) and [department])chr,company,chr,address1,chr,address2,& Iif([address2] Is Null, "", Chr(13) & Chr(10) and [address2])chr,city&", "&state&" "&zip,chr, country
So the idea would be that
FirstName LastName (optional)
Department (optional)
Company (required)
Address1 (required)
Address2 (optional)
City, State zip
Country (optional)
The best way to find out if it will work in your scenario is to try.
Make sure you leave spaces where appropriate :
firstname&" "&lastname will not work. It needs to be firstname & " " & lastname
chr by itself is not valid. It needs to be Chr(13) & Chr(10) to insert a line break.
Make sure you leave spaces where appropriate :
firstname&" "&lastname will not work. It needs to be firstname & " " & lastname
chr by itself is not valid. It needs to be Chr(13) & Chr(10) to insert a line break.
so would i put the string into a query or ??? somewhere in the text box properties and would the text box be unbound? or?
two small problems so far:
1) the number assigned to the company name is appearing and not the company name
2) i am not getting a line break, carriage return
below is my string so far:
expr1: [fnom] & " " & [lnom] & IIf([fnom] And [lnom] Is Null,"",Chr(13) & Chr(10) And [fnom] And [lnom]) & [dept] & IIf([dept] Is Null,"",Chr(10) & Chr(10) And [dept]) & [luorg]
What did I do wrong? and how can I fix it so the actual name appears and not the number number?
Anyone Plz
Edited by: ssaucedo on Thu Jul 30 13:20:54 EDT 2009.
1) the number assigned to the company name is appearing and not the company name
2) i am not getting a line break, carriage return
below is my string so far:
expr1: [fnom] & " " & [lnom] & IIf([fnom] And [lnom] Is Null,"",Chr(13) & Chr(10) And [fnom] And [lnom]) & [dept] & IIf([dept] Is Null,"",Chr(10) & Chr(10) And [dept]) & [luorg]
What did I do wrong? and how can I fix it so the actual name appears and not the number number?
Anyone Plz
Edited by: ssaucedo on Thu Jul 30 13:20:54 EDT 2009.
It would be fairly common to assume Nulls and allow propagation of Null to form your expression.
However, as you're depending on more than one field to determine value, to ammend your current expression you need to concatenate rather than logical compare ("&" instead of "And").
Equally - it's hard to know about the "company name" not appearing - without any familiarity of your fields.
expr1: [fnom] & " " & [lnom] & IIf([fnom] & [lnom] Is Null, Null,Chr(13) & Chr(10)) & [dept] & IIf([dept] Is Null, Null,Chr(10) & Chr(10)) & [luorg]
or to allow propagation - something vaguely like:
expr1: (([fnom] & (" " + [lnom])) + (Chr(13) & Chr(10))) & ([dept] + (Chr(10) & Chr(10))) & [luorg]
Also consider the control based method - as ably demonstrated by Mr Niesz here (I believe).
Cheers.
However, as you're depending on more than one field to determine value, to ammend your current expression you need to concatenate rather than logical compare ("&" instead of "And").
Equally - it's hard to know about the "company name" not appearing - without any familiarity of your fields.
expr1: [fnom] & " " & [lnom] & IIf([fnom] & [lnom] Is Null, Null,Chr(13) & Chr(10)) & [dept] & IIf([dept] Is Null, Null,Chr(10) & Chr(10)) & [luorg]
or to allow propagation - something vaguely like:
expr1: (([fnom] & (" " + [lnom])) + (Chr(13) & Chr(10))) & ([dept] + (Chr(10) & Chr(10))) & [luorg]
Also consider the control based method - as ably demonstrated by Mr Niesz here (I believe).
Cheers.
Just an idea to simplify things a bit...
In order to concatenate a "line break", you could use the constant vbCrLf instead of hard-coding Chr(13) and Chr(10) - it's easier to read and less likely to introduce typos - like using Chr(10) twice.
Plus, if there's a chance that one of your fields is NULL (not an empty string, which is different), you can use the Nz() function:
Nz([dept],"")
This would convert a null value in [dept] to an empty string without the need for IF testing.
Hope this helps.
Dennis
In order to concatenate a "line break", you could use the constant vbCrLf instead of hard-coding Chr(13) and Chr(10) - it's easier to read and less likely to introduce typos - like using Chr(10) twice.
Plus, if there's a chance that one of your fields is NULL (not an empty string, which is different), you can use the Nz() function:
Nz([dept],"")
This would convert a null value in [dept] to an empty string without the need for IF testing.
Hope this helps.
Dennis
Hi Dennis
Although Nz would obviously be fine regardless, vbCrLf would only be available as a constant if this address concatenation were being performed in code. As part of an expression (in a query or report control source) the full characters are required.
Of course a UDF could be created, passed the value to use and determine Null from it and optionally append a carriage return - to make the expression more readable...
Cheers.
Although Nz would obviously be fine regardless, vbCrLf would only be available as a constant if this address concatenation were being performed in code. As part of an expression (in a query or report control source) the full characters are required.
Of course a UDF could be created, passed the value to use and determine Null from it and optionally append a carriage return - to make the expression more readable...
Cheers.
Leigh,
Whoops. I forgot about that - that'll teach me to type up a solution without actually trying it out first.
I rather like the idea of a UDF to create the address text. Gives you more control and would be easier to read/maintain.
Dennis
Whoops. I forgot about that - that'll teach me to type up a solution without actually trying it out first.
I rather like the idea of a UDF to create the address text. Gives you more control and would be easier to read/maintain.
Dennis
thanks for all the input :-) ok hit a road block note what I have so far:
expr1: [fnom] & " " & [lnom] & Nz([fnom] & [lnom],"") & vbCrLf([dept]) & Nz([dept],"") & vbCrLf([luorg])
I get the error message;
undefined function vbCrLf in expression
what did i do wrong?
expr1: [fnom] & " " & [lnom] & Nz([fnom] & [lnom],"") & vbCrLf([dept]) & Nz([dept],"") & vbCrLf([luorg])
I get the error message;
undefined function vbCrLf in expression
what did i do wrong?
ok this is going a little over my head what is a UDF? please translate?
A UDF is a User Defined Function - in other words, a bit of VBA code that can return a value for you after much mucking about with logic and stuff.
Something like this:
Public Function FormattedAddress(firstname As String, lastname As String, _
department As String, company As String, address1 As String, _
address2 As String, city As String, State As String, _
country As String, zip As String) As String
[color="green"]' Add optional first name[/color]
FormattedAddress = Nz(firstname, "")
[color="green"]' If first name or last name are filled in, add a space between them[/color]
If Len(Nz(firstname, "") & Nz(lastname, "")) > 0 Then
FormattedAddress = FormattedAddress & " "
End If
[color="green"]' Add the optional last name[/color]
FormattedAddress = FormattedAddress & Nz(lastname, "")
[color="green"]' If there's ANYTHING in the first name/last name line, add a line break[/color]
If Len(Nz(FormattedAddress, "")) > 0 Then
FormattedAddress = FormattedAddress & vbCrLf
End If
[color="green"]' Add the optional department[/color]
If Len(Nz(department, "")) > 0 Then
FormattedAddress = FormattedAddress & department & vbCrLf
End If
[color="green"]' Add the required company[/color]
FormattedAddress = FormattedAddress & company & vbCrLf
[color="green"]' Add the required address 1[/color]
FormattedAddress = FormattedAddress & address1 & vbCrLf
[color="green"]' Add the optional address 2[/color]
If Len(Nz(address2, "")) > 0 Then
FormattedAddress = FormattedAddress & address2 & vbCrLf
End If
[color="green"]' Add the required city/state/zip[/color]
FormattedAddress = FormattedAddress & city & ", " & State & " " & zip
[color="green"]' Add the optional country (with a leading line break, if necessary)[/color]
If Len(Nz(country, "")) > 0 Then
FormattedAddress = FormattedAddress & vbCrLf & country
End If
End Function
In your query, it would be something like:
strAddress: FormattedAddress([firstname], [lastname], [department], [company], [address1], [address2], [city], [State], [country], [zip])
Hope this helps,
Dennis
Edited by: doctor9 on Thu Jul 30 14:40:00 EDT 2009.
Something like this:
CODE
Public Function FormattedAddress(firstname As String, lastname As String, _
department As String, company As String, address1 As String, _
address2 As String, city As String, State As String, _
country As String, zip As String) As String
[color="green"]' Add optional first name[/color]
FormattedAddress = Nz(firstname, "")
[color="green"]' If first name or last name are filled in, add a space between them[/color]
If Len(Nz(firstname, "") & Nz(lastname, "")) > 0 Then
FormattedAddress = FormattedAddress & " "
End If
[color="green"]' Add the optional last name[/color]
FormattedAddress = FormattedAddress & Nz(lastname, "")
[color="green"]' If there's ANYTHING in the first name/last name line, add a line break[/color]
If Len(Nz(FormattedAddress, "")) > 0 Then
FormattedAddress = FormattedAddress & vbCrLf
End If
[color="green"]' Add the optional department[/color]
If Len(Nz(department, "")) > 0 Then
FormattedAddress = FormattedAddress & department & vbCrLf
End If
[color="green"]' Add the required company[/color]
FormattedAddress = FormattedAddress & company & vbCrLf
[color="green"]' Add the required address 1[/color]
FormattedAddress = FormattedAddress & address1 & vbCrLf
[color="green"]' Add the optional address 2[/color]
If Len(Nz(address2, "")) > 0 Then
FormattedAddress = FormattedAddress & address2 & vbCrLf
End If
[color="green"]' Add the required city/state/zip[/color]
FormattedAddress = FormattedAddress & city & ", " & State & " " & zip
[color="green"]' Add the optional country (with a leading line break, if necessary)[/color]
If Len(Nz(country, "")) > 0 Then
FormattedAddress = FormattedAddress & vbCrLf & country
End If
End Function
In your query, it would be something like:
strAddress: FormattedAddress([firstname], [lastname], [department], [company], [address1], [address2], [city], [State], [country], [zip])
Hope this helps,
Dennis
Edited by: doctor9 on Thu Jul 30 14:40:00 EDT 2009.
i am getting an error message about FormattedAddress should it be formataddress or something else? i am putting this in a query/
Anyone?!
Edited by: ssaucedo on Thu Jul 30 14:53:30 EDT 2009.
Edited by: ssaucedo on Thu Jul 30 14:54:29 EDT 2009.
Anyone?!
Edited by: ssaucedo on Thu Jul 30 14:53:30 EDT 2009.
Edited by: ssaucedo on Thu Jul 30 14:54:29 EDT 2009.
well i have a table that has all the company names, so when a user creates a record they choose from a drop down field. now i want to create an invoice and i need the company name in the bill to address. but access is putting the number associated with the company name in the address not the actual name.
ssaucedo,
First, there's no point in "bumping" the thread - in fact, it can be counter-productive.
Second, if the function is called "FormattedAddress", you should use "FormattedAddress" in the query. Did you create a code module and copy/paste the function code into it? What is your error message, exactly?
If so, you can test the spelling quickly enough. Go into your VBA immediate window, and type:
? FormattedAddress(
Once you type that last parenthesis, you should see the pop-up text letting you know which value it wants next.
As far as company NUMBER versus company NAME goes, this is why so many developers use a naming scheme that identifies what sort of data is stored in the field. For example, the field that stores the number should be something like "intCompanyID" (as in long INTEGER) and the field that stores the actual name in the Companies table should be something like "strCompanyName" (as in STRING). This sort of strategy is commonly referred to as Hungarian Notation.
This will help you to use the correct field name in your query.
Hope this helps,
Dennis
First, there's no point in "bumping" the thread - in fact, it can be counter-productive.
Second, if the function is called "FormattedAddress", you should use "FormattedAddress" in the query. Did you create a code module and copy/paste the function code into it? What is your error message, exactly?
If so, you can test the spelling quickly enough. Go into your VBA immediate window, and type:
? FormattedAddress(
Once you type that last parenthesis, you should see the pop-up text letting you know which value it wants next.
As far as company NUMBER versus company NAME goes, this is why so many developers use a naming scheme that identifies what sort of data is stored in the field. For example, the field that stores the number should be something like "intCompanyID" (as in long INTEGER) and the field that stores the actual name in the Companies table should be something like "strCompanyName" (as in STRING). This sort of strategy is commonly referred to as Hungarian Notation.
This will help you to use the correct field name in your query.
Hope this helps,
Dennis
not sure what is meant by "bumping" the thread???
I have this setup to be an expression in a query, not a module and am not familiar with modules. Is it possible, to get some guidance for my expression in my query?
I looked up an alpha list of functions and could not find the "FormattedAddress" so maybe this does work in a module but for some reason does not appear to work in my query.
When I export the data to excel the company name displays not the number, so it must be possible to get the company name to display instead of the number.
Please anyone if you can help me sort this all out.
I have this setup to be an expression in a query, not a module and am not familiar with modules. Is it possible, to get some guidance for my expression in my query?
I looked up an alpha list of functions and could not find the "FormattedAddress" so maybe this does work in a module but for some reason does not appear to work in my query.
When I export the data to excel the company name displays not the number, so it must be possible to get the company name to display instead of the number.
Please anyone if you can help me sort this all out.
ssaucedo,
"Bumping" a thread is editing your original message to add "anyone?" a few minutes later. We're all volunteers here, and we donate our time as much as we can, but sometimes we can't dedicate 100% of our time to your problem. What I'm getting at is: Just be patient. There are already several people who have contributed to this thread, so there's no need to say "please anyone help me" any longer.
Now, let's start over: The User Defined Function that I posted earlier is called "FormattedAddress". Since it's user-defined, by definition you won't find it in the alpha list of functions. It's a custom function that YOU add to the database.
Here's how:
1. Go to the Modules tab of your database window (as in Tables, Queries, Forms, Reports, Pages, Macros, Modules).
2. Click the NEW button
3. Copy/Paste the code I posted into the editor that appears, below any text that appears there by default (probably "Option Compare Database").
4. Save the module with a name (different from the function name) like "modCustomAddressMaker".
5. Close the editor. The function is now available to your forms/queries/reports.
6. In your query, create a query field as I suggested after my posted VBA code.
Hope this helps,
Dennis
"Bumping" a thread is editing your original message to add "anyone?" a few minutes later. We're all volunteers here, and we donate our time as much as we can, but sometimes we can't dedicate 100% of our time to your problem. What I'm getting at is: Just be patient. There are already several people who have contributed to this thread, so there's no need to say "please anyone help me" any longer.
Now, let's start over: The User Defined Function that I posted earlier is called "FormattedAddress". Since it's user-defined, by definition you won't find it in the alpha list of functions. It's a custom function that YOU add to the database.
Here's how:
1. Go to the Modules tab of your database window (as in Tables, Queries, Forms, Reports, Pages, Macros, Modules).
2. Click the NEW button
3. Copy/Paste the code I posted into the editor that appears, below any text that appears there by default (probably "Option Compare Database").
4. Save the module with a name (different from the function name) like "modCustomAddressMaker".
5. Close the editor. The function is now available to your forms/queries/reports.
6. In your query, create a query field as I suggested after my posted VBA code.
Hope this helps,
Dennis
I can see how that might have been interpreted as inpatient and i must admit that I am eager to resolve this issue.
But that is not the reason, that I posted "anyone".
I guess I just wanted help working out the expression for a query. I tried 2 of your suggestions and wasn't getting anywhere. It seems that you wanted to take me in another direction that I was not prepared for, so that is why I said "anyone". Didn't mean to step on anyone's toes.
but here is my latest and greatest:
[fnom] & " " & [lnom] & IIf([fnom] & [lnom] Is Null,Null,Chr(13) & Chr(10)) & [dept] & IIf([dept] Is Null,Null,Chr(13) & Chr(10)) & ([luorg]+(Chr(10) & Chr(13))) & ([add1]+(Chr(10) & Chr(13))) & [add2] & IIf([add2] Is Null,Chr(13) & Chr(10)) & ([city] & ", " & [lustate] & " " & [zip] + (Chr(10) & Chr (13))) & [ctry]
previous versions did work but when i started to add more fields I got the following:
data type mismatch in criteria or expression
not sure what is wrong with the above expression
But that is not the reason, that I posted "anyone".
I guess I just wanted help working out the expression for a query. I tried 2 of your suggestions and wasn't getting anywhere. It seems that you wanted to take me in another direction that I was not prepared for, so that is why I said "anyone". Didn't mean to step on anyone's toes.
but here is my latest and greatest:
[fnom] & " " & [lnom] & IIf([fnom] & [lnom] Is Null,Null,Chr(13) & Chr(10)) & [dept] & IIf([dept] Is Null,Null,Chr(13) & Chr(10)) & ([luorg]+(Chr(10) & Chr(13))) & ([add1]+(Chr(10) & Chr(13))) & [add2] & IIf([add2] Is Null,Chr(13) & Chr(10)) & ([city] & ", " & [lustate] & " " & [zip] + (Chr(10) & Chr (13))) & [ctry]
previous versions did work but when i started to add more fields I got the following:
data type mismatch in criteria or expression
not sure what is wrong with the above expression
ok finally got the address block to show with the appropriate line breaks/carriage returns with the following and no error messages:
expr7: [fnom] & " " & [lnom] & (Chr(13)+Chr(10)) & [dept] & (Chr(13)+Chr(10)) & [luorg] & (Chr(13)+Chr(10)) & [add1] & (Chr(13)+Chr(10)) & [add2] & (Chr(13)+Chr(10)) & [city] & ", " & [lustate] & " " & [zip] & (Chr(13)+Chr(10)) & [ctry]
now all I have left to resolve is:
1) if the field is null that the address block adjust and not show blank lines
2) and the numbers displaying instead of the name
expr7: [fnom] & " " & [lnom] & (Chr(13)+Chr(10)) & [dept] & (Chr(13)+Chr(10)) & [luorg] & (Chr(13)+Chr(10)) & [add1] & (Chr(13)+Chr(10)) & [add2] & (Chr(13)+Chr(10)) & [city] & ", " & [lustate] & " " & [zip] & (Chr(13)+Chr(10)) & [ctry]
now all I have left to resolve is:
1) if the field is null that the address block adjust and not show blank lines
2) and the numbers displaying instead of the name
Just a guess... I'd move the parentheses so they encompass the text AND the Carriage Return/Line Feed characters inside:
expr7: ([fnom] & " " & [lnom] & Chr(13)+Chr(10)) & ([dept] & Chr(13)+Chr(10)) & ([luorg] & Chr(13)+Chr(10)) & ([add1] & Chr(13)+Chr(10)) & ([add2] & Chr(13)+Chr(10)) & ([city] & ", " & [lustate] & " " & [zip] & Chr(13)+Chr(10)) & [ctry]
Hope this helps,
Dennis
expr7: ([fnom] & " " & [lnom] & Chr(13)+Chr(10)) & ([dept] & Chr(13)+Chr(10)) & ([luorg] & Chr(13)+Chr(10)) & ([add1] & Chr(13)+Chr(10)) & ([add2] & Chr(13)+Chr(10)) & ([city] & ", " & [lustate] & " " & [zip] & Chr(13)+Chr(10)) & [ctry]
Hope this helps,
Dennis
QUOTE
The most effective method, IMO, is to concatenate the address lines into a single string so that it can be placed into a single text box control on the report. Using a combination of IIf's and Chr() strings you can format it pretty much the way you want.
Use the Chr() function to place line breaks between elements in the address like so:
[AddressLineOne] & Iif([AddressLineTwo] Is Null, "", Chr(13) & Chr(10) and [AddressLineTwo])
The control's Can Grow and Can Shrink Properties can control its hieght.
George
Use the Chr() function to place line breaks between elements in the address like so:
[AddressLineOne] & Iif([AddressLineTwo] Is Null, "", Chr(13) & Chr(10) and [AddressLineTwo])
The control's Can Grow and Can Shrink Properties can control its hieght.
George
Ok here is where I am at:
expr15: [fnom] & " " & [lnom] & (Chr(13)+Chr(10)) & Iif([dept] Is Null, "", Chr(13) & Chr(10) and [dept]) & [luorg] & (Chr(13)+Chr(10)) & [add1] & (Chr(13)+Chr(10)) & Iif([add2] is null, "", Chr(13) & chr(10) and [add2]) & [city] & ", " & [lustate] & " " & [zip] & (Chr(13)+Chr(10)) & [ctry]
The above does indeed give me what i want, however on small problem for the "add2" the line does not break and if the value is null a "-1" is displayed in print preview.
why does is work for the "dept" field and not for the "add2" field?
ssaucedo,
You are using the word "and" where you probably mean to be using an ampersand before [add2].
Dennis
You are using the word "and" where you probably mean to be using an ampersand before [add2].
Dennis
using "&" instead of and results in a blank line even if there is a value
Hi
Not wanting to seem blunt but... I have very limited time, this has been dragging on for some time now, you did PM for help (as I'm sure you did several others) and ultimately it's not a difficult requirement - so it should have been sorted by now.
Part of the problem is clarity. (Several methods and attempts crossing over that you're trying).
I offered some thoughts on doing this in an earlier post.
I see you've been trying to move forward with some other lines of thought - but are there any efforts on the expressions I suggested?
(It's important to reply to the exact post you're replying to - so it's clear what method you're trying to implement).
If the fields are actually Null (and are not storing zero length strings) then you can, generally, use propagation of Null to let an expression build as required.
Your other issue of wanting to display full names instead of ID numbers then I'm afraid that's all up to you.
You know you fields - we don't. You need you employ the appropriate fields that store text and not IDs. (If they are foreign key values then you need the report based on a query joined to the other tables and return the full text fields from there).
So, related to knowing your own fields (taking possession of the process) is building a function as Dennis offered.
Personally I'd not make it specific.
For example in a report I might have something like:
Obviously you'd have to choose your own appropriate fields.
(And in a report it's important to have you controls added to the report for the bound fields you're refering to).
txtAddress is just an unbound control you use to fill with the full address as required.
However that's not to say that the expression propagating Null and the control growing method (as demonstrated by Walter in the linked thread) aren't equally viable (and a bit simpler to implement) options.
But they need to be at least attempted.
See how you go with your various attempts.
Cheers.
Not wanting to seem blunt but... I have very limited time, this has been dragging on for some time now, you did PM for help (as I'm sure you did several others) and ultimately it's not a difficult requirement - so it should have been sorted by now.
Part of the problem is clarity. (Several methods and attempts crossing over that you're trying).
I offered some thoughts on doing this in an earlier post.
I see you've been trying to move forward with some other lines of thought - but are there any efforts on the expressions I suggested?
(It's important to reply to the exact post you're replying to - so it's clear what method you're trying to implement).
If the fields are actually Null (and are not storing zero length strings) then you can, generally, use propagation of Null to let an expression build as required.
Your other issue of wanting to display full names instead of ID numbers then I'm afraid that's all up to you.
You know you fields - we don't. You need you employ the appropriate fields that store text and not IDs. (If they are foreign key values then you need the report based on a query joined to the other tables and return the full text fields from there).
So, related to knowing your own fields (taking possession of the process) is building a function as Dennis offered.
Personally I'd not make it specific.
For example in a report I might have something like:
CODE
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim varAddress
varAddress = fAddLine(varAddress, Me.Address1)
varAddress = fAddLine(varAddress, Me.Address2)
varAddress = fAddLine(varAddress, Me.Town)
varAddress = fAddLine(varAddress, Me.County)
varAddress = fAddLine(varAddress, Me.Postcode)
Me.txtAddress = varAddress
End Sub
Function fAddLine(ByVal varText, ByVal varNew)
If IsEmpty(varText) Then
varText = Null
End If
If IsNull(varNew) Then
fAddLine = varText
Else
If Not IsNull(varText) Then
varText = varText & vbCrLf
End If
fAddLine = varText & varNew
End If
End Function
Dim varAddress
varAddress = fAddLine(varAddress, Me.Address1)
varAddress = fAddLine(varAddress, Me.Address2)
varAddress = fAddLine(varAddress, Me.Town)
varAddress = fAddLine(varAddress, Me.County)
varAddress = fAddLine(varAddress, Me.Postcode)
Me.txtAddress = varAddress
End Sub
Function fAddLine(ByVal varText, ByVal varNew)
If IsEmpty(varText) Then
varText = Null
End If
If IsNull(varNew) Then
fAddLine = varText
Else
If Not IsNull(varText) Then
varText = varText & vbCrLf
End If
fAddLine = varText & varNew
End If
End Function
Obviously you'd have to choose your own appropriate fields.
(And in a report it's important to have you controls added to the report for the bound fields you're refering to).
txtAddress is just an unbound control you use to fill with the full address as required.
However that's not to say that the expression propagating Null and the control growing method (as demonstrated by Walter in the linked thread) aren't equally viable (and a bit simpler to implement) options.
But they need to be at least attempted.
See how you go with your various attempts.
Cheers.
Almost Final Solution - No more address line blanks.
Ok below is the the expression that finally worked (beautifully
) in my query/report:
Expr1: IIf(IsNull([fnom]),"",[fnom] & " " & [lnom] & Chr(13)+Chr(10)) & IIf(IsNull([dept]),"",[dept] & Chr(13)+Chr(10)) & IIf(IsNull([luorg]),"",[luorg] & Chr(13)+Chr(10)) & IIf(IsNull([add1]),"",[add1] & Chr(13)+Chr(10)) & IIf(IsNull([add2]),"",[add2] & Chr(13)+Chr(10)) & [city] & ", " & [lustate] & " " & [zip] & Chr(13)+Chr(10) & [ctry]
The above expression assumes that if there is not a first name there will not be a last name. I will save this forever.
Now I just have to resolve the number display for the company name.
Ok below is the the expression that finally worked (beautifully
) in my query/report:Expr1: IIf(IsNull([fnom]),"",[fnom] & " " & [lnom] & Chr(13)+Chr(10)) & IIf(IsNull([dept]),"",[dept] & Chr(13)+Chr(10)) & IIf(IsNull([luorg]),"",[luorg] & Chr(13)+Chr(10)) & IIf(IsNull([add1]),"",[add1] & Chr(13)+Chr(10)) & IIf(IsNull([add2]),"",[add2] & Chr(13)+Chr(10)) & [city] & ", " & [lustate] & " " & [zip] & Chr(13)+Chr(10) & [ctry]
The above expression assumes that if there is not a first name there will not be a last name. I will save this forever.
Now I just have to resolve the number display for the company name.
OK
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.