Type Mismatch In Expression

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

Kevin Sprinkel

The query below is generating this error message if a Null
value exists in Formula. Can anyone figure out where I've
gone astray? My design intent is to sum the two
expressions for each unique combination of JobNumber and
Reference.

The fields are:
Formula Text
Multiplier Integer
LBperLF Single
SFperLF Single

SELECT tblTakeoff.JobNumber, tblTakeoff.Reference,
Sum(Nz(Eval([Formula]),0)*[Multiplier]*[LBperLF]) AS LB,
Sum(Nz(Eval([Formula]),0)*[Multiplier]*[SFperLF]) AS SF
FROM tblTakeoff
GROUP BY tblTakeoff.JobNumber, tblTakeoff.Reference;

TIA
Kevin Sprinkel
 
Put Nz inside the Eval function, not outside it:

SELECT tblTakeoff.JobNumber, tblTakeoff.Reference,
Sum(Eval(Nz([Formula],0))*[Multiplier]*[LBperLF]) AS LB,
Sum(Eval(Nz([Formula],0))*[Multiplier]*[SFperLF]) AS SF
FROM tblTakeoff
GROUP BY tblTakeoff.JobNumber, tblTakeoff.Reference;
 
You need to use an If expression using the Null as one of
the parameters - this is a basic SQL solution and often
this very issue is used as one of the examples. You may
want to go online using www.netlibrary.com access one of
their SQL books. This is the national library and you
just need to become a library member - its free and they
have a good selection of reference materials.
 
I'm not sure if what I posted will actually fix your problem... I misread
Eval as Val (I'm an old man!), so let me know if it works or not. Thanks.

--
Ken Snell
<MS ACCESS MVP>

Kevin Sprinkel said:
Thanks, Ken!

Kevin Sprinkel
-----Original Message-----
Put Nz inside the Eval function, not outside it:

SELECT tblTakeoff.JobNumber, tblTakeoff.Reference,
Sum(Eval(Nz([Formula],0))*[Multiplier]*[LBperLF]) AS LB,
Sum(Eval(Nz([Formula],0))*[Multiplier]*[SFperLF]) AS SF
FROM tblTakeoff
GROUP BY tblTakeoff.JobNumber, tblTakeoff.Reference;
Click to expand...
Click to expand...
 
Back
Top