gigifarrow
asked on
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
Format(Abs(([F]![Induction
FROM tblTempleFacilityFullup AS F;
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
Format(Abs(([F]
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks expets
ASKER
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 ?
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.)
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.)
You can either set it as the control source of the field to = Format(ConvertProd([Produc
or set it via vba code if you only want it to fire on some event.
or set it via vba code if you only want it to fire on some event.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
UPDATE SourceTable
SET Whatever = Something
FROM SourceTable
JOIN NewTable ON SourceTable.Production = NewTable.Production