Results 1 to 3 of 3
  1. #1
    kolney is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2020
    Posts
    1

    Change field value in report depending upon another field value

    Hi everyone and thanks in advance for any help you are able to offer



    I have a very simple database with just 2 tables.
    Table 1 - ClientsTABLE which includes Client details and the respective "Day Rate" field and and a separate "Hourly Rate" field
    Table 2 - TimesheetTABLE which I record separate Daily tasks for each client from the ClientsTABLE.
    I have a "Unit" Field in TimesheetTABLE that has 2 options - either "Hrs" or "Day" to set the charge out to either number of hours or day rates for every day for each client I've worked for.

    My question is:-
    Is there a way I can set up a report that I run off at the end of each month.
    I want to include a single report field that will change its value to show either the Client Table "Day rate" field or "Hourly Rate" field depending upon what "Unit" has been set on the timesheetTABLE Unit field.

    Hope this makes sense?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    do it in the query, so the report only deals with 1 field.
    the fld:

    Rate: iif([RateOpt]="Day",[DayRate],[HrRate])

    it can also be done in the query by joining it to the t.Client.RateOpn table, to pull that rate type. No need for the IIF.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It would be helpful if you would post a copy of your dB with a few made up clients (Clark Kent, Fred Flintstone) an Timesheet records.
    How often do the rates change?

    I would have a field (PayRate) in table Timesheets for "Hourly Rate" or "Day rate", a field (WrkTime) for number of hours (or 1 if day rate) and a field for units (Hourly Rate/Day rate). Use a query to do the calculations (DailyAmt) and as the record source for the report.

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

Similar Threads

  1. Replies: 25
    Last Post: 04-16-2019, 06:52 PM
  2. Replies: 6
    Last Post: 03-28-2018, 11:20 PM
  3. Replies: 10
    Last Post: 10-07-2015, 05:31 PM
  4. Replies: 6
    Last Post: 02-11-2015, 12:53 PM
  5. Replies: 1
    Last Post: 02-20-2012, 01:02 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