Any MS Access experts here?

[richardycc]

I got tagged in for a small Access change, I've never done Access before, but to upper management, they think programmer is programmer and this would be a good "opportunity" for me to learn. anyway, here is one of the many small changes, and I am not even sure if it's possible. The current report is set up to address up to 3 names like:
name1
name2
name3,
address line1
address line2.


because most cases only has 1 person, therefore, they are seeing the 2 blank lines between the name1 and address line1, and they wanted to remove the blank space to make the reports look better.

I can simply just remove name2 and name3, but it wouldn't work for cases that do have more than one names. The correct way (I think) to do this is to add some logic somewhere, so if the case has only 1 name, it will use template 1 with one name1 line, and template 2 with name1 and name2, and template 3 with 3 name lines. Is that even possible to do? if so, how??

 

[KLin]

http://msdn.microsoft.com/en-us/library/office/aa165406(v=office.10).aspx

In the OnFormat event of the report, Evaluate if the the name2 and name3 textboxes are null. If they are, set the visible property for the name2 and name3 textboxes and labels to false.

i.e.

if IsNull(Me.name2) Then
	Me.name2.visible = false
	Me.name2Label.visible = false
End if

 

[sao123]

http://msdn.microsoft.com/en-us/library/office/aa165406(v=office.10).aspx

In the OnFormat event of the report, Evaluate if the the name2 and name3 textboxes are null. If they are, set the visible property for the name2 and name3 textboxes and labels to false.

i.e.

if IsNull(Me.name2) Then
    Me.name2.visible = false
    Me.name2Label.visible = false
End if

I think in addition to checking for NULL, you also need to check for empty string. -> ""


--------
another easy way to do this would be to just create a new field in your query, which would concatenate the 3 names into one field, then print only that new field on the report.
the query would then do all the work, rather than changing the program itself.

for example, i have a phone number database with 2 fields phone and Fax. Some records have one, the other, or both.
I just made a field which combines the 2 in a useful way and just put that new field on my report.

 

[KLin]

I think in addition to checking for NULL, you also need to check for empty string. -> ""


--------
another easy way to do this would be to just create a new field in your query, which would concatenate the 3 names into one field, then print only that new field on the report.
the query would then do all the work, rather than changing the program itself.

for example, i have a phone number database with 2 fields phone and Fax. Some records have one, the other, or both.
I just made a field which combines the 2 in a useful way and just put that new field on my report.

1. Not necessarily. If no value is in the field, then it should evaluate as being null.

Your suggestion still requires modifying the report. So you'd have to modify the query and the report. Sounds like more work to me.

 

[sao123]

1. Not necessarily. If no value is in the field, then it should evaluate as being null.

Your suggestion still requires modifying the report. So you'd have to modify the query and the report. Sounds like more work to me.

deleting 2 unused fields and replaceing a third is hardly a large undertaking in changing the report.

modifying the query can be done with using only the expression builder to mkae a new calculated oclumn.



The benefit of my solution is that the user who is not familiar with access, it can be done without any programming knowledge at all, using only those tools directly within access.

 

[richardycc]

I was able to get this done by changing the "can shrink" property in name2 and name3 to Yes, and looks like Access "shrinks" the name2 and name3 fields automatically when it is blank. But this creates a new issue. Because when the name2 and name3 are blank, Access would move the addressline1 and addressline2 up, so now I have 2 empty lines after addressline2 before the next section of text in the report. How can I fix this? Ideally, I want the name1 to move down and the addressline1 and addressline2 to not move up so it won't create empty space. Maybe can shrink isn't the correct way to do this in the first place?