Solved

Using Between And in a query

Posted on 2013-06-26
8
260 Views
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;
0
Comment
Question by:gigifarrow
  • 3
  • 2
  • 2
  • +1
8 Comments
 
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
0
 
LVL 6

Assisted Solution

by:Dulton
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
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
 

Author Comment

by:gigifarrow
ID: 39279240
Thanks expets
0
 

Author Comment

by:gigifarrow
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 ?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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.

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
 
LVL 6

Expert Comment

by:Dulton
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.
0
 

Author Comment

by:gigifarrow
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.
HelpWithQuery.zip
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
ID: 39279604
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now