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

Using Between And in a query

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;
0
gigifarrow
Asked:
gigifarrow
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
0
 
DultonCommented:
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
Else
    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.

SELECT

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;
0
 
gigifarrowAuthor Commented:
Thanks expets
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gigifarrowAuthor Commented:
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 ?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.

JOINS
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.)
0
 
DultonCommented:
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.
0
 
gigifarrowAuthor Commented:
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.
HelpWithQuery.zip
0
 
Rey Obrero (Capricorn1)Commented:
your error could be coming from "OMMITTED space" in your query

------------------v--------------------------v
[Production]Between 432 AND464 Or

this should be
-------------------v--------------------------v
[Production] Between 432 AND 464 Or


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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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