Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2014
    Posts
    16

    Unanswered: Address into a Report

    Hi there.

    I have an access database which is designed to produce invoices.
    The raw data is taken from our Accounts system and I have built a front end which then manipulates various bits of data to produce the invoice.
    The problem I am having is that the address is entered as a text string with commas in (e.g (10 London Road, London, SW1 4WS) and I need this to come out as
    10 London Road
    London
    SW1 4WS
    when it is outputted in the report. The query which produces the report references a number of tables and queries to produce all the info so if I can avoid adding another query I would like to and I am sure there has to be a way of doing this in the report itself. The field I have is called [ClientAddress]
    Can anyone help with either a simple entry I can put into a field on the report or how I manage to embed code in the report to make this work as all of the pages I have read on Split Functions do not seem to work when I put similar code in to the computer?
    Any assistance would be much appreciated

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What DBMS are you using?
    What reporting software are you using?

    What about simply replacing the commas with a carriage return?

    Something like
    Code:
    SELECT ClientAddress
         , Replace(ClientAddress, ',', Char(13)) As ClientAddressBlock
    ...
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2014
    Posts
    16

    Re:

    Quote Originally Posted by gvee View Post
    What DBMS are you using?
    What reporting software are you using?

    What about simply replacing the commas with a carriage return?

    Something like
    Code:
    SELECT ClientAddress
         , Replace(ClientAddress, ',', Char(13)) As ClientAddressBlock
    ...
    Sorry, Should have put that in the beginning. I am using Microsoft Access 2010.
    I am self taught and relatively new to this so not necessarily sure where to put it. I looked at putting some code attached to the Text Box that currently is just a link to the column [ClientAddress] from the query but none of them activate when the report is opened and when I tried to put some code in to the report as a whole it tried to effect all cells and it caused the report to crash.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Moving to MS Access topic
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2014
    Posts
    16
    Ok. Thanks. I did try and look at the Topic headers but there are so many I must have missed this one. If anyone can assist that would be appreciated. I can put a screen grab of the report up if that will assist?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Set the rowsource for the address control to be
    Code:
    = replace(clientaddress, ",", vbcrlf)
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2014
    Posts
    16
    Quote Originally Posted by healdem View Post
    Set the rowsource for the address control to be
    Code:
    = replace(clientaddress, ",", vbcrlf)
    Thanks for this.
    I just tried this and replaced the control source line in the properties with this code and when the report is run it asks be to enter the parameter for vbcrlf as as soon as I have typed it it puts square brackets around vbcrlf? Any idea what I am doing wrong?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    vbcrlf is an intrinsic vba constant..., Im surprised its not finding it.
    thats trying to embed the ASCII characters for Carriage Return (13) and Line Feed (10)
    so what you could try instead is as GVEE alluded to:-

    Code:
    = replace(clientaddress, ",", chr(13) & chr(10))
    or see if the vbcrlf constant is available if you invoke the wizard to build an expression
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Oct 2014
    Posts
    16
    Quote Originally Posted by healdem View Post
    vbcrlf is an intrinsic vba constant..., Im surprised its not finding it.
    thats trying to embed the ASCII characters for Carriage Return (13) and Line Feed (10)
    so what you could try instead is as GVEE alluded to:-

    Code:
    = replace(clientaddress, ",", chr(13) & chr(10))
    or see if the vbcrlf constant is available if you invoke the wizard to build an expression
    I gave that a try and all it gives me is #Type! in the output box of the report rather than an address. I am not fully cognizant with VBA and most of what I have picked up is through Forums and reading other people's code knowing what I it is supposed to do and working back from there through the code. It does this with the setting on both Plain and Rich text as I found this page http://www.access-programmers.co.uk/...d.php?t=197968 which indicated that could be an issue.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what error message do you get when you run the form
    what do you thunk that means

    I can replicate this behaviour in A2010 where the name of the control you are setting is the same as the name of column you are trying to modify. the error message tells a you that it has a circular reference or something similar. the resolution is to change the name of the control to something else but keep the name of the column as is. So:-
    rename the control to something else, say ctlClientAddress.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Oct 2014
    Posts
    16
    Quote Originally Posted by healdem View Post
    what error message do you get when you run the form
    what do you thunk that means

    I can replicate this behaviour in A2010 where the name of the control you are setting is the same as the name of column you are trying to modify. the error message tells a you that it has a circular reference or something similar. the resolution is to change the name of the control to something else but keep the name of the column as is. So:-
    rename the control to something else, say ctlClientAddress.
    It doesn't give an error. It runs fine but instead of any form of the address it just puts up "#Type!".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •