Member Avatar for shar164

I have a field on a form which is calculated (A minus B). It displays properly on the form with the correct answer. I can't figure out how to get it to pull into a query? My query is pulling any record with that field criteria >0 and no records come up when I run the query altough there should be several with a positive result in the field.

I am a newbie, so please bear with me . . . . thanks in advance for any help.

  • 3 Contributors
  • 2 Replies
  • 109 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by carolraydon

Dani AI

Generated

Good pointers from and — the core issue is that the value your form shows is produced at runtime in the form, while a standalone query evaluates table/query data. A few practical causes explain why a query with > 0 returns no rows even though the form shows positives: the form’s changes were not saved to the table, one or both fields contain NULLs, the fields are stored as text, the calculation uses form-only logic or VBA, or a join in the query is excluding rows.

Checklist and quick fixes:

  • Confirm fields A and B are actual table fields (not unbound controls) and that records are saved before running the query.

  • Handle NULLs: in Access an expression like [A] - [B] yields NULL if either side is NULL. Use NZ to default NULLs to zero in the query expression:

    Diff: NZ([A],0) - NZ([B],0)

    Put >0 in the Criteria row under that expression in Design view.

  • Verify data types: numeric fields should be numeric. If they are text, fix the schema or convert carefully in the expression (better to correct the field type).

  • If A or B come from a joined table, ensure the join type does not drop rows you need (use LEFT JOIN if appropriate).

SQL example (replace names):

SELECT t.ID, t.A, t.B, (NZ([A],0)-NZ([B],0)) AS Diff
FROM TableName AS t
WHERE (NZ([A],0)-NZ([B],0))>0;

Note: Access SQL generally does not allow referencing the SELECT alias in WHERE; repeat the expression or wrap it in a subquery.

Troubleshooting tips: create a query that only shows the calculated Diff (no criteria) to inspect values per row, check for stray spaces or nonnumeric characters if numbers are stored as text, and avoid relying on form-only calculations when building filters or reports.

Recommended Answers

All 2 Replies

Member Avatar for cpopham

Calculated fields in forms only exit in the form. You cannot query a calculated field from a form. You can regenerate the calculations in a query based on the same table the form is base on.

Chester

I have a field on a form which is calculated (A minus B). It displays properly on the form with the correct answer. I can't figure out how to get it to pull into a query? My query is pulling any record with that field criteria >0 and no records come up when I run the query altough there should be several with a positive result in the field.

I am a newbie, so please bear with me . . . . thanks in advance for any help.

Member Avatar for carolraydon

You need to reproduce your calculated field in a query based on the table(s) your form feeds;(same thing A minus B) and set the criteria of that field to show the range you want. Or set it as a parameter field if you need to ask for different results from time to time.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.