Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

Access 2010 Between statement returns both positive and negative values

I am using a between statement to pull 2 values from a text box in a form and return the fields that are between these values.  The field in question is a calculated field in another query and is joined to this query.  My problem:  my statement is treating the values as absolutes and is returning those in the between range that are both positive and negative.

For example, I have values that are 255, 376, 115, 543, -36, -125, -444.  If I send values to the between statement that are between 200 and 400, I get 255, 376, 543, and -444.

Please advise.
0
jwandmrsquared
Asked:
jwandmrsquared
  • 3
  • 2
1 Solution
 
Dale FyeCommented:
You need to provide the syntax you are using so that we can interpret what is going wrong.

I'm assuming this is a SQL statement?
0
 
Rey Obrero (Capricorn1)Commented:
< The field in question is a calculated field in another query and is joined to this query.>

in which field  did you place the "Between criteria" ?
   - in the calculated field in another query ?
0
 
jwandmrsquaredAuthor Commented:
SELECT qry_pplc_all_action_items_to_email.Item_Status, qry_pplc_all_action_items_to_email.PPLCREV, qry_pplc_all_action_items_to_email.[Product ID], qry_pplc_all_action_items_to_email.[Product Action Items], qry_pplc_all_action_items_to_email.Resolution, qry_pplc_all_action_items_to_email.[Resolve Date], qry_pplc_all_action_items_to_email.AI_Owner, qry_pplc_all_action_items_to_email.AI_Owner_email, qry_pplc_all_action_items_to_email.[Product Name], qry_pplc_all_action_items_to_email.[Days to Orig Due Date], qry_pplc_all_action_items_to_email.[Days to Revised Date], qry_pplc_all_action_items_to_email.[Days to last email]
FROM qry_pplc_all_action_items_to_email
WHERE (((qry_pplc_all_action_items_to_email.[Days to Orig Due Date]) Between [Forms]![frm_pplc_email_ais]![orig_due_From] And [Forms]![frm_pplc_email_ais]![orig_due_to]));


Days to Orig Due Date is calculated.

orig_due_From and orig_due_to are text boxes.

Here is the sql that calculates the date field(s):
SELECT tbl_product_PPL_Court_Followup_LIs.pplc_AI_Key, Max(tbl_product_PPL_Court_Followup_LIs.email_date) AS MaxOfemail_date, Max(tbl_product_PPL_Court_Followup_LIs.follow_up_date) AS MaxOffollow_up_date, Max(tbl_product_PPL_Court_Followup_LIs.rsvd_due_date) AS MaxOfrsvd_due_date
FROM tbl_product_PPL_Court_Followup_LIs
GROUP BY tbl_product_PPL_Court_Followup_LIs.pplc_AI_Key;



for info purposes - maxoffollow_up_date is Days to Orig Due Date on the top query.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Dale FyeCommented:
SELECT qry_pplc_all_action_items_to_email.Item_Status
           , qry_pplc_all_action_items_to_email.PPLCREV
           , qry_pplc_all_action_items_to_email.[Product ID]
           , qry_pplc_all_action_items_to_email.[Product Action Items]
           , qry_pplc_all_action_items_to_email.Resolution
           , qry_pplc_all_action_items_to_email.[Resolve Date]
           , qry_pplc_all_action_items_to_email.AI_Owner
           , qry_pplc_all_action_items_to_email.AI_Owner_email
           , qry_pplc_all_action_items_to_email.[Product Name]
           , qry_pplc_all_action_items_to_email.[Days to Orig Due Date]
           , qry_pplc_all_action_items_to_email.[Days to Revised Date]
           , qry_pplc_all_action_items_to_email.[Days to last email]
FROM qry_pplc_all_action_items_to_email
WHERE qry_pplc_all_action_items_to_email.[Days to Orig Due Date]
Between [Forms]![frm_pplc_email_ais]![orig_due_From]
And [Forms]![frm_pplc_email_ais]![orig_due_to]

Have you defined these parameters for the query?  Chances are that it may be interpreting those form control values as text, not numbers.  You can create the parameters by right clicking in the top of the query design grid and selecting parameters.parametersThen copy your parameters:

[Forms]![frm_pplc_email_ais]![orig_due_From]
[Forms]![frm_pplc_email_ais]![orig_due_to]

to two successive lines (the top two) and select the data types.  Then open your form, enter your parameters and rerun the query.
0
 
jwandmrsquaredAuthor Commented:
I also had to set the text boxes to format Fixed, but the parameters finished it off.  The minute you said it, the dust fell off my brain.
0
 
Dale FyeCommented:
Glad I could help
0
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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