Using Between And in a query

Posted on 2013-06-26
Last Modified: 2013-06-29
I know this may look complicated and I have asked several questions on this code. I asked one about using "Between And" in a IIF stament and I used one of the experts help(thank you)

 I thought it was going to work. When I tried it and didnt get a error message I thought it was working, But it doesnt.

when I use the "between And" statement It all defaults to the false answer.  I dont get a error message. The production fields data type is numbers.
I have check marks that equal a certain percentage when you check it. Based on the production number the check mark is worth certain percentages.

IF THERE is a better way to do this PLEASE let me know. I know you can have a little over 1000 characters. and Im getting close to that.

IIf([Production]Between 343 AND 346 Or [Production] Between 347 And 374 Or [Production] Between 379 And 380 Or [Production] Between 391 And 403 Or [Production]Between 405 And  407 Or [Production] Between 409 And 410 Or [Production] Between 412 And 422 Or [Production] Between 424 AND 428 Or [Production]Between 432 AND464 Or [Production]=467 Or [Production]Between 471 And 507 Or [Production]Between 524 AND 536, Format(Abs(([F]![Induction]*6+[F]![TearDown]*10+[F]![Provision]*10+[F]![AssemblyA]*10+[F]![AsemblyB]*10+[F]![AssemblyC]*10+[F]![AssemblyD]*10+[F]![AssemblyE]*10+[F]![RoadTest]*4+[F]![QC]*10+[F]![OutDuction]*10)/100),"00.0%"),Format(Abs(([F]![Induction]*5+[F]![TearDown]*10+[F]![Provision]*0+[F]![AssemblyA]*20+[F]![AsemblyB]*0+[F]![AssemblyC]*0+[F]![AssemblyD]*20+[F]![AssemblyE]*25+[F]![RoadTest]*0+[F]![QC]*10+[F]![OutDuction]*10)/100),"00.0%"),Format(Abs(([F]![Induction]*6+[F]![TearDown]*10+[F]![Provision]*10+[F]![AssemblyA]*10+[F]![AsemblyB]*10+[F]![AssemblyC]*10+[F]![AssemblyD]*10+[F]![AssemblyE]*10+[F]![RoadTest]*4+[F]![QC]*10+[F]![OutDuction]*10)/100),"00.0%"),

Format(Abs(([F]![Induction]*5+[F]![TearDown]*10+[F]![Provision]*0+[F]![AssemblyA]*20+[F]![AsemblyB]*0+[F]![AssemblyC]*0+[F]![AssemblyD]*20+[F]![AssemblyE]*25+[F]![RoadTest]*0+[F]![QC]*10+[F]![OutDuction]*10)/100),"00.0%"))AS Percentages
FROM tblTempleFacilityFullup AS F;
Question by:gigifarrow
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 65

Expert Comment

by:Jim Horn
ID: 39279093
A vastly better way to deal with this other than multiple BETWEEN .. AND statements (btw parenthesis might help) is to have a separate table with all of these values, then in your SQL you can JOIN on it to determine if SourceTable.Production is one of the NewTable.Production values.

UPDATE SourceTable
SET Whatever = Something
FROM SourceTable
   JOIN NewTable ON SourceTable.Production = NewTable.Production

Assisted Solution

Dulton earned 250 total points
ID: 39279239
jimhorn is right...
To really make inroads in improving this, it would require an attribute table where could not only store the threshold values (your production between values), but also the attributes/amounts you want to use as a result of this. Even that could be normalized into 2 tables, but let's just try to get you going:

I've moved this logic out of the query and into a function which will need  created in a module.
here is the code for the function. I've made these values double as opposed to integer, but that could easily be changed.

Public Function ConvertProd(Prod As Double) As Double

Dim Val1 As Integer _
  , Val2 As Integer _
  , Val3 As Integer _
  , Val4 As Integer _
  , Val5 As Integer _
  , Val6 As Integer _
  , Val7 As Integer _
  , Val8 As Integer _
  , Val9 As Integer _
  , Val10 As Integer _
  , Val11 As Integer _
  , BlnTrue As Boolean

Select Case Prod
    Case 343 To 346
BlnTrue = True
    Case 347 To 374
BlnTrue = True
    Case 379 To 380
BlnTrue = True
    Case 391 To 403
BlnTrue = True
    Case 405 To 407
BlnTrue = True
    Case 409 To 410
BlnTrue = True
    Case 412 To 422
BlnTrue = True
    Case 424 To 428
BlnTrue = True
    Case 432 To 464
BlnTrue = True
    Case 467
BlnTrue = True
    Case 471 To 507
BlnTrue = True
    Case 524 To 536
BlnTrue = True
    Case Else
        BlnTrue = False
End Select

If BlnTrue Then
    Val1 = 6
    Val2 = 10
    Val3 = 10
    Val4 = 10
    Val5 = 10
    Val6 = 10
    Val7 = 10
    Val8 = 10
    Val9 = 4
    Val10 = 10
    Val11 = 10
    Val1 = 5
    Val2 = 10
    Val3 = 0
    Val4 = 20
    Val5 = 0
    Val6 = 0
    Val7 = 20
    Val8 = 25
    Val9 = 0
    Val10 = 10
    Val11 = 10
End If

ConvertProd = Abs(([F]![Induction] * Val1 + _
                   [F]![TearDown] * Val2 + _
                   [F]![Provision] * Val3 + _
                   [F]![AssemblyA] * Val4 + _
                   [F]![AsemblyB] * Val5 + _
                   [F]![AssemblyC] * Val6 + _
                   [F]![AssemblyD] * Val7 + _
                   [F]![AssemblyE] * Val8 + _
                   [F]![RoadTest] * Val9 + _
                   [F]![QC] * Val10 + _
                   [F]![OutDuction] * Val11) / 100)

End Function

---Now I realize there are knocks against scalar functions, and I get it. but in this ugly {iif} case, it will make your troubleshooting and the query more manageable overall, and there is no character limit.
The iff is replaced by the call to the function below (field #1), and then I just copied your second field back in.


Format(ConvertProd([Production]),"00.0%") AS ConvertedProd,

Format(Abs(([F]![Induction]*5+[F]![TearDown]*10+[F]![Provision]*0+[F]![AssemblyA]*20+[F]![AsemblyB]*0+[F]![AssemblyC]*0+[F]![AssemblyD]*20+[F]![AssemblyE]*25+[F]![RoadTest]*0+[F]![QC]*10+[F]![OutDuction]*10)/100),"00.0%"))AS Percentages
FROM tblTempleFacilityFullup AS F;

Author Comment

ID: 39279240
Thanks expets
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.


Author Comment

ID: 39279262
The query is a field in a form called percentages. I need this to appear in that field how would i have the function call the field that I want it in ?
LVL 65

Expert Comment

by:Jim Horn
ID: 39279267
In the below image, think of A as whatever table has the live Production data, and think of B as a table that is a list of Production #'s we just made up based on your wompload of SQL above.

In your case, these tables using INNER JOIN (aka JOIN), the picture right in the middle, will produce a set with ONLY the rows where the values are in both tables.

Then you can do your above calculations, without all of the BETWEEN ... AND goodness, only on those rows, and save yourself a hell of a lot of hard-coding values.  (That may change, then you'd have to update the hard-coding, you get the idea.)

Expert Comment

ID: 39279289
You can either set it as the control source of the field to = Format(ConvertProd([Production]),"00.0%")
or set it via vba code if you only want it to fire on some event.

Author Comment

ID: 39279523
Again thanks for your time and help

I only have one table with this data in it. So why would i need a join table. the production number and the check fields are in a form with a query in the form for the code.

Let me give you a copy of my database maybe that would help

I have a summary on the top of the form that has calculations in the text field. I have a copy of the database. sorry for the late response Im at work.
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 250 total points
ID: 39279604
your error could be coming from "OMMITTED space" in your query

[Production]Between 432 AND464 Or

this should be
[Production] Between 432 AND 464 Or

make sure that there is a space between items in your query

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question