Results 1 to 12 of 12
  1. #1
    lgrange is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2021
    Posts
    6

    Question Show selected cashiers and date range in report header

    Hi all
    I'm working on a report that shows transaction by various cashiers within a user-specified date range.


    The cashier name ([Cashier]), starting date ([StartDate]), and ending date ([EndDate]) are all selected by a user on a form ([Frm_Search]), which then runs a query ([Qry_Main]) to show matching records.
    Inputing a starting and ending date is required, but if a cashier is not selected then results for all cashiers are shown for the date range.

    I'd like a text box in the report header to read "Report for [Cashier] from [StartDate] to [EndDate]." And if no cashier is selected on the form, then the message will read "Report for all cashiers from [StartDate] to [EndDate]."
    In my text box I currently have the following expression:

    =IIf(IsNull([Forms]![Frm_Search]![Cashier]),"Report for all cashiers from " & [Forms]![Frm_Search]![StartDate] & " to " & [Forms]![Frm_Search]![EndDate],"Report for " & [Forms]![Frm_Search]![Cashier] & " from " & [Forms]![Frm_Search]![StartDate] & " to " & [Forms]![Frm_Search]![EndDate])

    This successfully shows the date range that's specified by the user on the form, but no matter what I do it always reads "all cashiers" instead of specific [Cashier] names when they are selected. What am I doing wrong?
    Thanks!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What's the SQL of the query? I'm wondering if there's a difference in what is being referenced for cashier.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If cashier on the form is a combobox, try this, specifying the column in the combo.

    =IIf(IsNull([Forms]![Frm_Search]![Cashier].column(1)),"Report for all cashiers from " & [Forms]![Frm_Search]![StartDate] & " to " & [Forms]![Frm_Search]![EndDate],"Report for " & [Forms]![Frm_Search]![Cashier].column(1) & " from " & [Forms]![Frm_Search]![StartDate] & " to " & [Forms]![Frm_Search]![EndDate])

  4. #4
    lgrange is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2021
    Posts
    6

    Thumbs up

    You called it pbaldy, I checked the SQL and some of the references were [CashierID] instead of [Cashier]. I'm pretty new to Access, SQL, etc., so thank you for helping me find where I went astray!

    For anyone curious as to what expression I'm using now to get the desired result in my report:
    =IIf(IsNull([Forms]![Frm_Search]![CashierID]),"Report for all cashiers from " & [Forms]![Frm_Search]![StartDate] & " to " & [Forms]![Frm_Search]![EndDate],"Report for " & [Forms]![Frm_Search]![CashierID] & " from " & [Forms]![Frm_Search]![StartDate] & " to " & [Forms]![Frm_Search]![EndDate])

    And the SQL for my Qry_Main:
    SELECT Tbl_Main.Cashier, Tbl_Main.Register, Tbl_Main.ErrorDate, Tbl_Main.IssueCategory, Tbl_Main.OSForm, Tbl_Main.Correction, Tbl_Main.Over, Tbl_Main.Short, Tbl_Main.SumDiscrepancy, Tbl_Main.Comments, Tbl_Main.RecordID
    FROM Tbl_Main
    WHERE (((Tbl_Main.Cashier)=[Forms]![Frm_Search]![CashierID]) AND ((Tbl_Main.ErrorDate)>=[Forms]![Frm_Search]![StartDate] And (Tbl_Main.ErrorDate)<=[Forms]![Frm_Search]![EndDate]) AND (([Forms]![Frm_Search]![RecordID]) Is Null)) OR (((Tbl_Main.ErrorDate)>=[Forms]![Frm_Search]![StartDate] And (Tbl_Main.ErrorDate)<=[Forms]![Frm_Search]![EndDate]) AND (([Forms]![Frm_Search]![RecordID]) Is Null) AND (([Forms]![Frm_Search]![CashierID]) Is Null));

  5. #5
    lgrange is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2021
    Posts
    6
    You called it pbaldy, I checked the SQL and some of the references were [CashierID] instead of [Cashier]. I'm pretty new to Access, SQL, etc., so thank you for helping me find where I went astray!

    For anyone curious as to what expression I'm using now to get the desired result in my report:
    =IIf(IsNull([Forms]![Frm_Search]![CashierID]),"Report for all cashiers from " & [Forms]![Frm_Search]![StartDate] & " to " & [Forms]![Frm_Search]![EndDate],"Report for " & [Forms]![Frm_Search]![CashierID] & " from " & [Forms]![Frm_Search]![StartDate] & " to " & [Forms]![Frm_Search]![EndDate])

    And the SQL for my Qry_Main:
    SELECT Tbl_Main.Cashier, Tbl_Main.Register, Tbl_Main.ErrorDate, Tbl_Main.IssueCategory, Tbl_Main.OSForm, Tbl_Main.Correction, Tbl_Main.Over, Tbl_Main.Short, Tbl_Main.SumDiscrepancy, Tbl_Main.Comments, Tbl_Main.RecordID
    FROM Tbl_Main
    WHERE (((Tbl_Main.Cashier)=[Forms]![Frm_Search]![CashierID]) AND ((Tbl_Main.ErrorDate)>=[Forms]![Frm_Search]![StartDate] And (Tbl_Main.ErrorDate)<=[Forms]![Frm_Search]![EndDate]) AND (([Forms]![Frm_Search]![RecordID]) Is Null)) OR (((Tbl_Main.ErrorDate)>=[Forms]![Frm_Search]![StartDate] And (Tbl_Main.ErrorDate)<=[Forms]![Frm_Search]![EndDate]) AND (([Forms]![Frm_Search]![RecordID]) Is Null) AND (([Forms]![Frm_Search]![CashierID]) Is Null));

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    lgrange is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2021
    Posts
    6
    A new issue has emerged. The text box is working perfectly when I view the report in Print Preview, but when I view it in Report View I get a #Name? error. Why might this happen? Thanks again, everyone.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is the form remaining open after the report is opened? It needs to be.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    lgrange is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2021
    Posts
    6
    Okay yup, keeping the form open fixed the issue. Is there a principle behind that to help me better understand why it needs to stay open? Is it because the text box is referencing the form, whereas all the data in the report is from a query? My query references the form as well, but I'm guessing the data is able to carry over to the report after the form closes because of having run the query.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, and you'd find that with the form closed, you'll get an error trying to print the report. It reruns the query at that point.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    lgrange is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2021
    Posts
    6
    Great thanks again! Consider this case closed.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Include date range criteria in Report Header?
    By templeowls in forum Reports
    Replies: 2
    Last Post: 10-07-2020, 08:36 AM
  2. Replies: 3
    Last Post: 01-21-2015, 02:40 PM
  3. Replies: 3
    Last Post: 10-01-2014, 04:09 PM
  4. Find Min and Max in a selected date range
    By rkalapura in forum Queries
    Replies: 9
    Last Post: 04-01-2013, 09:24 AM
  5. Getting a date range to show on a report
    By recon2011 in forum Reports
    Replies: 3
    Last Post: 01-10-2012, 01:27 PM

Tags for this Thread

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