formatting a text box to show all data

[mejia.j88]

Hi AccessForum,

I have a report with a field called D_Lots_used.

the text stored in this field has the following format: DAA0001. Sometimes the field includes up to 5 of these DAA numbers, something like this: DAA0001, DAA0002, DAA0005, etc.

in the case of 5 of these D numbers (actually they are lot numbers) the report wants to display them as one long stright, with no space after the commas.



Is there a way to easily format this to where it displays something like:

DAA0001, DAA0002
DAA0003, DAA0005 ?

any hints, advice or nudges in the right direction would be very appreciated!

thanks,
J

[VariableZ]

I assume you mean that the report has a control that pulls from a table that contains a field with the string "DAA0001, DAA0002, ..." and not that the control pulls the "DAA..." strings separately and concatenates them. If that is not correct, please clarify. Based on that, make sure that the table field is set to 'text' or 'memo' type.

[mejia.j88]

yes, it pulls from a table that contains a field iwth the string " DAA0001, DAA0002, ..."
it is already set to text.

[VariableZ]

Questions:

1) Does the report's control have a formula in it, or is it just set to the table's field?
2) Under the FORMAT section of the properties of the report's control, is there anything listed, or is it blank? (it should be blank)

If neither of these leads in the right direction, a sample would be helpful. I haven't been able to recreate the problem from the information provided.

[mejia.j88]

1 just set to teh tables field,
2 it is blank

a sample of what? There really isn't a problem per say, rather, I'm wondering how to break up a string

instead of DAA0001, DAA0002, DAA0003, DAA0004

have it display in a stacked form like below
DAA0001, DAA0002
DAA0003, DAA0004

in my report

[VariableZ]

Oh, I see. You initially said that the spaces were missing.

You have a few options. You can shorten the field and increase it's height to accommodate two lines and it will automatically go to the next line when it runs out of space on the first line. Or, you can write a formula that breaks the field into multiple sections with breaks in between the string at predetermined intervals (you have to be sure that each lot number will be the same length). Something like =Left([D_Lots_used],7) & ", " & Mid([D_Lots_used],10,7) & Chr$(13) & Chr$(10) & Mid([D_Lots_used],19,7) &​ Mid([D_Lots_used],29,7) Does that help?

[mejia.j88]

oh, the spaces missing i fixed, that was just part of my code. i had re-edited the message and took that out since it had only been up for a few mins.

yes, that is what i was looking for!
Two questions: what exactly does Chr$(13) do,
and unrelated to this post:
i have a field on my form that can be null, when the record is saved, i want that field to have 0s instead of null values.
does this involved the Nz function or IIf, where would the function be placed (e.g. control source? format? etc)

[VariableZ]

Chr$(13) is a "Carriage Return" while Chr$(10) is a "Line Feed". I have no idea why you need both. I just know that you have to do "Chr$(13) & Chr$(10)" in that order to make it work.

You can go simple and set the default value to 0 - either in the table or in the form - but it won't prevent people from deleting the value and setting your field back to null again unless you set the table field to Required = Yes. If you do set Required = Yes and it's a text field, you might end up with a nothing instead of a null. Or you can use VBA. Something along the lines of:

Private Sub whateveryourcontroliscalled_AfterUpdate()
If IsNull(Me.whateveryourcontroliscalled) = True Then
Me.whateveryourcontroliscalled= 0
End If
End Sub

Or Me.whateveryourcontroliscalled = Nz(Me.whateveryourcontroliscalled, 0) instead of the if statement.

I can't think offhand of a way to do it with just a formula in the control since the formula will write to a contol, not to a table field.

[mejia.j88]

thanks variableZ,
very helpful replies!