Dividing in MS Access
-
Hey guys, Need some help
I am not good with MS Access, and this was an already built program by former IT guy. I am trying to modify it and make it divide 2 numbers if it meets a certain criteria, as certain customers are invoiced as the Secondary UOM vs Primary UOM.. It does divide, but it seems to be dividing twice. This is what I got as the query. This is a more complicated program, that grabs data from our ERP system, and in the ends creates a file that is them sent to MSA, for our Cig/Tobacco sales for our Distribution side of the company. Is this the correct code/syntax thing to use for division?
Does anyone else here deal with MSA for Hersheys, Grocery, Cig/Tob reporting? If so, what do you use to do the reporting?
Thanks for any help, and sorry I it's really hard to explain this program/report.
-
I dont know much about MS Access but I have tons of experience in SSRS. The way Microsoft expects code in most of their products is based on VB syntax in my experience. I found that in their products you cannot group filters, even in SSRS, so in that case I evaluate multiple conditions in a single expression returning TRUE or FALSE, then set the filter calling the expression to check against the value returned from the expression. So I am not sure what the MS Access equivalent would be without installing MS access, but I would write an expression like this in SSRS as example:
=iif(((Parameters!pShowCurrent.Value
AndAlso Fields!Detail_Age_Days.Value >= 0
AndAlso Fields!Detail_Age_Days.Value <= 30)
OrElse
(Parameters!pShow31to60.Value
AndAlso Fields!Detail_Age_Days.Value > 30
AndAlso Fields!Detail_Age_Days.Value < 61)
OrElse
(Parameters!pShow61to90.Value
AndAlso Fields!Detail_Age_Days.Value > 60
AndAlso Fields!Detail_Age_Days.Value < 91)
OrElse
(Parameters!pShow91to120.Value
AndAlso Fields!Detail_Age_Days.Value > 90
AndAlso Fields!Detail_Age_Days.Value < 121)
OrElse
(Parameters!pShowPlus120.Value
AndAlso Fields!Detail_Age_Days.Value > 120)
) AndAlso (
(Parameters!pResubmitFilter.Value
AndAlso Fields!Detail_Age_Days.Value > Parameters!pResubmitFilter.Value ))
, true, false)This allows me to group conditions how I would directly in the SQL statement. My filter then would evaluate if the expression is true or false and then filter the results as needed. I know this specifically pertains to SSRS, but I bet MS Access does something similar.
Ill poke around and see if MS Access has a similar way of doing this when I have a chance.
thx
-d -
It looks like there is an expression builder for calculated fields MS Access. Maybe that is what you require?
-
drew - it seems to work to a point, it just divides it twice, ex 10/10/10=.1, it should only do it once, ex 10/10=1 (or multiplies it twice if I change it to multiply). Other calculations are done fine in the program, but i cant figure out why I can't get this one to work. I'm trying to follow other setups, altho none are division (that I remember), they are multiplication.
There is alot of linked tables to another Access db, as well as where it pulls the data via odbc (Program as 120+ tables, quires, macros, modules).. The divisor is in the linked access db, plus it has to only do it to the certain accounts and items. We are looking at getting a real program to do this, but for now we are stuck with this.
I will try do what the video you linked has, but we use no forms for this.. All it does, is grab data and put it in a special format mandated by MSA (
