Access Calculation in query issue

Jennet Knuckey 45 Reputation points
2026-04-17T20:45:18.06+00:00

In Query 'Carpet Purchases Date Calculations' Field: Final Due Date

I want the calculation to pick up the answer from field 'Invoice Date Used for Calc Actual or Est' +

Supplier Terms.

Supplier Terms are found in the Table 'Supplier Information'. Field 'Payment Terms'.

The system won't let me attach an access file. Rather than go back and forth this is the easiest way. Is there a way I can get the file to you?

Microsoft 365 and Office | Access | For home | Windows
Answer accepted by question author

AI answer

2026-04-17T20:45:28.64+00:00

Access Q&A cannot receive or open database files.

To calculate Final Due Date in the query using an invoice date plus supplier terms stored in another table, use a calculated field expression in the query that joins to Supplier Information.

  1. Ensure the query includes both:
    • The table (or query) that has [Invoice Date Used for Calc Actual or Est]
    • The table Supplier Information, joined on the appropriate key (for example SupplierID).
  2. In Query Design View, add a new column in the Field row and enter an expression like:
Final Due Date: [Invoice Date Used for Calc Actual or Est] + [Supplier Information].[Payment Terms]

This assumes:

  • [Invoice Date Used for Calc Actual or Est] is a Date/Time field.
  • [Payment Terms] is a Number field representing the number of days to add.

Access will then calculate Final Due Date for each row when the query runs.

For more complex date logic (for example, using functions like DateDiff or DateAdd), a similar calculated field can be created in the query’s Field row.

References:

AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

2 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Jennet Knuckey 45 Reputation points
    2026-04-17T22:56:49.5266667+00:00

    I get an error message Data type mismatch in criteria expression

    The error is in Final Due Date: [Invoice Date Used for Calc Actual or Est] which as in the Property Sheet Format: Short Date

    It is picking up from calculated field in the same [Invoice Date Used for Calc Actual or Est] which is also Format: Short Date

    and this works fine. This original field is Invoice Date Used for Calc Actual or Est: IIf(IsNull([Carpet Purchase Order Data ex Cin7]![Invoiced Date]),[Est Invoice Date],[Carpet Purchase Order Data ex Cin7]![Invoiced Date])

    Was this answer helpful?

  2. Jennet Knuckey 45 Reputation points
    2026-04-18T20:53:08.45+00:00

    Final Answer from Duane

    Date Used for Calc Actual or Est: CDate(Nz([Carpet Purchase Order Data ex Cin7]![Invoiced Date],[Est Invoice Date]))

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.