Go Premium for a chance to win a PS4. Enter to Win


Using Between And in a query

Posted on 2013-06-26
Medium Priority
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
  • 3
  • 2
  • 2
  • +1
LVL 66

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 1000 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal


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 66

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 1000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

885 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