Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2008
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Report with blanks

    I have a specific format for a letter that I need to mail out. I want to be able to type in a customer ID or select a customer ID and have it fill in the information on a report. i.e. Suffix, Name, Address, and have it put them in the correct spots. How would I do this.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I don't understand the connection between the title (Report with blanks) and the question. Where do blanks come in?

    Build a query that contains all the right fields, build a report on the query, then open the report with a Where clause, limiting the displayed report to the correct Customer ID.

    Code:
    Dim strReportname as string
    Dim strCriteria as string
    if not isnull(me.txtCustomerID) then
        strCriteria ="[Customer ID]=" & me.txtCustomerID
       strReportName= "rptyourReportname"
       docmd.OpenReport strReportName,acPreview,,strCriteria
    else
       msgbox "Select a Customer.",vbInformation
    end if
    Chnage the code to use the correct names for things. My code assumes Customer ID is a number.
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Dec 2008
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The blanks come in because I was talking about filling in the blanks on the report. I understand creating a query and using the prompt box to select which customer to use. I just don't understand how to format the report to get it to display like a letter. Like below.

    Letter Head up top.


    [Suffix][Name]
    [Address]
    [City],[State][Zip]

    Dear [Name]:

    This is in reference to your recent application in [App Date].

    You applied for the following billet:
    Title Location Open Date Start Date
    [Billet] [Location] [O Dt] [St Dt]

    That's just a basic outline of what I'm looking for. The items in brackets will be information pulled from my table. The rest of the text will already be on the report. That is the part I'm confused about.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If you actually want to generate a letter with fields from the database interspersed with other text, a Mail Merge may be the way to go.
    Otherwise you need to create calculated fields withing the query, and use them for the report:

    e.g. Line1: "This is in reference to your recent application in " & [App Date].
    Salutation: "Dear " & [name]

    (although Name is a reserved word and should not be used as a field name in Access)
    Regards
    John



Posting Permissions

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