Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2010
    Posts
    32

    Unanswered: Postage label Boolean

    Hello all,

    As it stands, our database for mail ordering at the moment is set up so a customer has their billing address and also a shipping addres. At times the shipping address comes through as a seperate address, meaning the postage label would have to be changed accordingly (the customer details in the database are saved under the billing address).

    At the moment when printing a label for an alternative address we have to individually print the address whereas the other addresses are printed off as bulk on the report.

    I was wondering, if I add an extra field at the end of my orders table containing a Boolean is there a way for the report containing the labels to display the addresses of either the billing address or the shipping address depending what is entered (ie-If true display billing address on label, if false display shipping address)?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you coudl use an IIF in the query to select the relevant address
    or change your label report, seelcxt the shippi9ng address as part of the query asawell as the billing address
    you will probably need the controls for the address to be unbound
    place some code in the label report on format event and assign the shipoing address to the unbound controls if sepcified


    eg
    if strLen(ShippingAddress1)>0 then
    tbLine1 = ShippingAddress1
    tbLine2 = ShippingAddress2
    ...
    else

    tbLine1 = BillingAddress1
    tbLine2 = BillingAddress2
    endif
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2010
    Posts
    32
    Thanks, looks simple enough. Would I have to put the IIF function in all field criteria boxes in the query? How would I link it to the 2 tables with info?

    IE - iif([M/A]="True",[Address1]=Customers.Address1, iif[M/A]="False",[Address1]=Alternative.Address1)

    (M/A meaning 'Main/Alternative')

  4. #4
    Join Date
    Jul 2010
    Posts
    32
    Bump!!!!!!!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I wouldn't recomned doing that as it measn putitng in an IIF for each column you wantr to pull aqcross. if you extract the billing ADN delivery address at the same time , then you can selecxt the correct address when you format the report with a single if in VBA code iin the forms on change event or reports on format event
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jul 2010
    Posts
    32
    Thanks, what would the IF statement look like? I'm not overly knowledgeable on VBA

    EDIT: Should really add in what tables and such I've got at the moment....

    Customers > Alternative Address (two tables linked by the customer ID)
    CurrentOrders (this table has the field at the end with the Boolean for which address to use on the postage label: True = Main Address, False = Alternative Address)
    Labels report (backed by the query which runs the label business)

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by rhopgrape View Post
    Thanks, what would the IF statement look like? I'm not overly knowledgeable on VBA

    EDIT: Should really add in what tables and such I've got at the moment....

    Customers > Alternative Address (two tables linked by the customer ID)
    CurrentOrders (this table has the field at the end with the Boolean for which address to use on the postage label: True = Main Address, False = Alternative Address)
    Labels report (backed by the query which runs the label business)
    http://www.dbforums.com/microsoft-ac...ml#post6493960
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jul 2010
    Posts
    32
    Hi again, I've not been able to find 'OnFormat' event in the report options only in the 'Detail' option so I've put the code in there and called it up. The report (which uses label code in VBA) has the following take up... Report- OnOpen: =LabelSetup(), Detail-OnPrint: =LabelLayout(!Reports[ST Labels]). The only 'OnFormat' I have found is in the 'Detail' section so that is now: =ChooseAddress()

    When I run the query it runs through the code but then gets to what I have entered and says 'The expression On Open you entered as the event property setting produced the following error: There was an error compiling this function.'

    The label worked before I started with this so I imagine it is wha I have entered.

    Here is the code:

    Dim ChooseAddress As String
    Function ChooseAddress()
    If strLen(CurrentOrders.WhichAdd) > 0 Then
    Title = altadd.Title
    Initial = addalt.Initial
    Surname = addalt.Surname
    Address1 = addalt.Address1
    Address2 = addalt.Address2
    Address3 = addalt.Address3
    Town = addalt.Town
    Country = addalt.Country
    Postcode = addalt.Postcode

    Else
    Title = customers.Title
    Initial = customers.Initial
    Surname = customers.Surname
    Address1 = customers.Address1
    Address2 = customers.Address2
    Address3 = customers.Address3
    Town = customers.Town
    Country = customers.Country
    Postcode = customers.Postcode
    End If
    End Function

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    straight off you shouldn't declare a variable wihthe same name as a function
    next, in the absence of knowing what version of Access you are using its tough to work out what the exact answer is. in Access 2007 the format event is part of the detail subset, so open up your report in design mode
    view the code (either right click | build event | code, or there must be a way of viewing the code)
    in the LHS top combo select detail
    in the RHS top combo select format


    If strLen(CurrentOrders.WhichAdd) > 0 Then

    is saying if the length of the string in CurrentOrders.WhichAdd is greater than 0 then use altadd, otherwise use customers.

    WhichAdd to me sounds like a switch
    so I'd expect it to read

    If strLen(CurrentOrders.WhichAdd) > <somevalue> Then

    if you look back a\t the original suggestion I proposed that you add the billiign AND Delivery addresses to the querry for this report

    then use a test to see which address to use, and the opne I propsoed was to test if line one of the delviery address had at least one character in it then use the delivery address data. thats why I used the strlen function (STRing LENgth)

    eg
    Code:
    if strlen(customers.Address1) > 0 then
      Title = customers.Title
      Initial = customers.Initial
      Surname = customers.Surname
      Address1 = customers.Address1
      Address2 = customers.Address2
      Address3 = customers.Address3
      Town = customers.Town
      Country = customers.Country
      Postcode = customers.Postcode
    else
      Title = altadd.Title
      Initial = addalt.Initial
      Surname = addalt.Surname
      Address1 = addalt.Address1
      Address2 = addalt.Address2
      Address3 = addalt.Address3
      Town = addalt.Town
      Country = addalt.Country
      Postcode = addalt.Postcode
    endif
    I suspect however you may well have problems if the query doesn't include these two tables, in which case you may have to use a different approach and pull the correct address using a recordset
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jul 2010
    Posts
    32
    I'm running Access 2003. 'WhichAdd' is the name of the field in the orders table where the user checks a checkbox (Boolean) whether it's the Billing Address or leaves it unchecked if it's the Delivery address.

    However, looking on the query I'm using I haven't brought in the table for the delivery addresses (which is why it probably didn't work). Do I bring in all the fields wanted on the label from both tables (Title, Initial, Surname, Address 1 etc etc etc...)?

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    up to you
    its your data
    if the deleivery address can include a different person then yes

    as siad before I don't think you need another column to say whether you are using a delivery address or not, as surely by defintion if a delivery address is specified then it must be being used, if it isn't specified then it isn't being used.

    doing it through demanding the user sets a boolean flag could leave a valid but odd oxymoron, whereh you have a delivery address sepcified but someone hasn't selected the use delivery address option

    the advanatge of testing if the delivery address is set (virtually every delivery address MUST have a first line) is that it requires no further action by the user. they inlcude some data on the first line fot he delivery address, by defintion its then used.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jul 2010
    Posts
    32
    The way we have the system, and how the custoemrs order, is that the billing address is automatically the delivery address. The customer states whether or not they have an alternative address (eg - work or c/o).

    Address1 in both the Customer table and the AltAdd table will have data in so would the strlen bring back an error or even both addresses on the label?

    (sorry for any naivety, still in learning stages))

Posting Permissions

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