Results 1 to 9 of 9
  1. #1
    Paulyd2018 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    5

    Print monthly report for one of our clients on my database

    Hi all, I hope someone can help with this

    I am creating an access database to our staff to all use at work for raising jobs for the clients we have, I have set up a table for adding clients & one for raising jobs with a selection of forms & sub forms.

    99% of our clients will receive an invoice after each job we do for them which I have set up & created a report to use as an invoice which is working perfectly, with the data from the clients table & data from the job raising table all going onto the report (invoice) like it needs to.

    One of our clients has to be invoiced differently, they need a single invoice for all the jobs we have carried out for them from the first of the month to the last day of the month, this used to be created in our office using data from an excel spreadsheet but I am looking to replace that on the access database I am making.

    Here is what I need to add

    Fields from the job raising table
    Client name (already set up on the form as a drop down box) - added at the top of the report
    Job number
    Site address


    Job details
    Start date
    Finish date
    Order number
    Cost
    VAT (already set up as a calculative field)
    Cost + VAT (already set up as a calculative field)

    Fields from the client table - All added at the top of the report
    Address
    Town
    County
    Postcode
    For the attention of

    I would like us to be able to select a client on the form I have created then select a start date & a finish date then press a button to generate the monthly report showing all the jobs we have done for them for that month selected.

    Could someone help me with this please.

    Thx in advance

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    A 'report' form,...cboBoxes ,and text boxes for dates.
    pick the report,
    pick the client,
    pick the date range.

    Docmd.openReport acPreview me.cboRpt

    the query would pull the client in that date range.

  3. #3
    Paulyd2018 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    5
    Quote Originally Posted by ranman256 View Post
    A 'report' form,...cboBoxes ,and text boxes for dates.
    pick the report,
    pick the client,
    pick the date range.

    Docmd.openReport acPreview me.cboRpt

    the query would pull the client in that date range.
    Thank you ranman256

    I've given that a try & I presumed I had to add the report name like I did on the code for the rest of the buttons I added for printing reports so the code looks like this

    Docmd.openReport "MonthlyInvoice",acPreview me.cboRpt

    When I added some jobs to test & selected the two dates & pressed the button I added I got a compile error: syntax error

    Any idea's where it went wrong?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd probably have the date criteria in the report's source query (referring to form controls) and the customer here:

    http://www.baldyweb.com/wherecondition.htm

    I'm not sure what that Me.cboRpt is meant to accomplish. From context I think it wasn't meant to select a report, in which case it would replace the report name argument. It is out of place where it is.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Paulyd2018 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    5
    Quote Originally Posted by pbaldy View Post
    I'd probably have the date criteria in the report's source query (referring to form controls) and the customer here:

    http://www.baldyweb.com/wherecondition.htm

    I'm not sure what that Me.cboRpt is meant to accomplish. From context I think it wasn't meant to select a report, in which case it would replace the report name argument. It is out of place where it is.
    Thanks for your input pbaldy

    Sorry for my noobness but I'm still confused on what to add in the code.

    The main part of my form is the "JobNumber," do I need to add that as either the field name or control name & use "Client" for the other? or should one of them be the date field?

    DoCmd.OpenReport "MonthlyInvoice", , , "FieldName = #" & Me.ControlName & "#"

    Please can someone explain further & write the code out for me

    Thank you

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Example of search form and code to build filter criteria: http://allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just a few comments:

    Why is the client name in the job raising table? Should be in the client table.

    Shouldn't use punctuation or special characters in object names. (It looks like you have a field name "Cost + VAT".)

    Shouldn't use spaces in object names. (includes field, table, query, form and report names)
    If you MUST separate words use the underscore: "Client name" (bad) would be "Client_Name" (better) or "ClientName" (best)

    Should never use calculated fields in tables. The calculations should be in a query. See http://www.allenbrowne.com/casu-14.html



    Quote Originally Posted by Paulyd2018 View Post
    I would like us to be able to select a client on the form I have created then select a start date & a finish date
    I wouldn't think you would want to select jobs based on start date. If a job started in one month and finished in the next month, it seem to me that that job would never be invoiced.

    If you only invioce finished jobs, maybe use just the "FinishDate".
    Something like
    Code:
    DoCmd.OpenReport "MonthlyInvoice", , , "ClientName = '" & Me.cboClientName & "' AND FinishDate Between #" & BOM & "# AND #" & EOM & "#;"
    (BOM = beginning of the month (ie the first)/ EOM = end of the month)


    The open report command syntax (as suggested by Paul) is
    DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

  8. #8
    Paulyd2018 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    5
    Quote Originally Posted by ssanfu View Post
    Just a few comments:

    Why is the client name in the job raising table? Should be in the client table.

    Shouldn't use punctuation or special characters in object names. (It looks like you have a field name "Cost + VAT".)

    Shouldn't use spaces in object names. (includes field, table, query, form and report names)
    If you MUST separate words use the underscore: "Client name" (bad) would be "Client_Name" (better) or "ClientName" (best)

    Should never use calculated fields in tables. The calculations should be in a query. See http://www.allenbrowne.com/casu-14.html




    I wouldn't think you would want to select jobs based on start date. If a job started in one month and finished in the next month, it seem to me that that job would never be invoiced.

    If you only invioce finished jobs, maybe use just the "FinishDate".
    Something like
    Code:
    DoCmd.OpenReport "MonthlyInvoice", , , "ClientName = '" & Me.cboClientName & "' AND FinishDate Between #" & BOM & "# AND #" & EOM & "#;"
    (BOM = beginning of the month (ie the first)/ EOM = end of the month)


    The open report command syntax (as suggested by Paul) is
    DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)
    Thank you for your detailed reply ssanfu

    I haven't used punctuation, special characters or spaces in my field names, I just wrote it loke that in the post, my bad, I should have wrote it how they are in my tables, I have Client & Totalcost for the field names.

    I wasn't aware of not using calculative fields though, I will have to look at changing them the queries.

    Thinking about it I think you are 100% correct about just having a finishdate & not start & finish dates, I will do that

    I will try the cost you shared when I am on my pc in a bit, thank you for that.

    Question
    Does access know BOM is the start of the month & EOM is the beginning of the month or does that need to be set up somewhere & will I also need to set up something to select which month we need to invoice by using drop down boxes like what was suggested in an earlier post?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    BOM and EOM are variables you have to populate. Yes, you need method for user to select month. Code could just use current month but what if you run report in first week and you really need last month? Combobox can have DefaultValue so it is pre-selected for current month but user can change the selection.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-06-2015, 03:26 PM
  2. Replies: 1
    Last Post: 02-21-2015, 11:35 PM
  3. Database Login Using Windows ID - Citrix and Thin Clients
    By IncidentalProgrammer in forum Programming
    Replies: 11
    Last Post: 01-09-2015, 08:55 AM
  4. Replies: 2
    Last Post: 03-19-2014, 08:03 PM
  5. Replies: 9
    Last Post: 02-12-2013, 03:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums