• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

Subtracting one records field from another. Mar06-Feb06 (variance)

I have two fields with data in them.  I want to create a query, add the two fields, and then subtract one from the other.  I don't know how to do it.
Becky Edwards
Becky Edwards
  • 2
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are the fields in the same "row"? If so, you can create a calculated field. In a blank column of your query grid, in the Name row:

AddFields: (DateField1 + DateField2)

Same for subtracting them:

SubtractFields: DateField1 - DateField2

This would result in your query having two new fields - one named AddFields and one named SubtractFields.

If either of these could contain Null values, you'd want to use the Nz function:

AddFields: (Nz(DateField1,0) + Nz(DateField2, 0))

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry ... I see that you mention "records" in your question, which would indicate that these are not in the same row. If that's the case then no, you really cannot do this. You could build a subselect that did this, but you'd have to have some sort of Ordering field that would correctly sort your values. If you provided a sample of the data, perhaps we could help further.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now