What is the criteria problem with this expression?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an expression that was suggested by someone here a couple of years ago
and it has worked great. I use it to isolate the even or odd side of my
storage locations. I have always used it in a make table query (with no
criteria) and then worked with the data in the new table. Today I tried to
use it in a select query and when I tried to apply a criteria , E to seperate
the even side or O to find the odd, it came back with the message "Data type
mismatch in criteria field". The query works fine until I try to apply
criteria. I am using a linked table. It is a CSV file.

Here is the expression:

E-O: IIf(Val(Mid([location],InStr([location],"-")+1)) Mod 2=0,"E","O")

I kind of understand everything in this expression except "Mod 2=0", I
can't
seem to find anything about "Mod", is that a function?

Sample of the data from the field [location] B 123-159-A.

I have tried using "E, O, 0, 1, and 2" as the criteria and I always get the
above referenced error message.
Thanks, any help or suggestions will be greatly appreciated.

Sgurdon
 
Mod (derived from the mathematical term modulus) is an operator which
returns the remainder after dividing the first number by the second, so even
numbers divided by 2 have a remainder of 0, odd numbers a remainder of 1. To
return even numbers in a query you can remove the IIf function call form the
expression so it becomes:

Val(Mid([location],InStr([location],"-")+1)) Mod 2

A criterion of 1 should return all odd rows, a criterion of 0 all even rows.
If you uncheck the 'show' checkbox the computed column won't be returned in
the result set, it will merely determine which rows are returned.

Ken Sheridan
Stafford, England
 
Thanks Ken,
I inserted the formula:
-- E-O: Val(Mid([location],InStr([location],"-")+1)) Mod 2
and ran the query; it brought back 1's where the location was odd and 0's
where the location was even but when I tried to run the query with a 1 or a 0
as criteria it came back with a data mismatch whether I hide the field or
not. Still need an answer but now I understand MOD.
--
Sgurdon
Always in search of knowledge


Ken Sheridan said:
Mod (derived from the mathematical term modulus) is an operator which
returns the remainder after dividing the first number by the second, so even
numbers divided by 2 have a remainder of 0, odd numbers a remainder of 1. To
return even numbers in a query you can remove the IIf function call form the
expression so it becomes:

Val(Mid([location],InStr([location],"-")+1)) Mod 2

A criterion of 1 should return all odd rows, a criterion of 0 all even rows.
If you uncheck the 'show' checkbox the computed column won't be returned in
the result set, it will merely determine which rows are returned.

Ken Sheridan
Stafford, England

Sgurdon said:
I have an expression that was suggested by someone here a couple of years ago
and it has worked great. I use it to isolate the even or odd side of my
storage locations. I have always used it in a make table query (with no
criteria) and then worked with the data in the new table. Today I tried to
use it in a select query and when I tried to apply a criteria , E to seperate
the even side or O to find the odd, it came back with the message "Data type
mismatch in criteria field". The query works fine until I try to apply
criteria. I am using a linked table. It is a CSV file.

Here is the expression:

E-O: IIf(Val(Mid([location],InStr([location],"-")+1)) Mod 2=0,"E","O")

I kind of understand everything in this expression except "Mod 2=0", I
can't
seem to find anything about "Mod", is that a function?

Sample of the data from the field [location] B 123-159-A.

I have tried using "E, O, 0, 1, and 2" as the criteria and I always get the
above referenced error message.
Thanks, any help or suggestions will be greatly appreciated.

Sgurdon
Click to expand...
Click to expand...
 
That's very strange. Post the SQL of the query here. Maybe we'll be able to
pinpoint the problem then.

Ken Sheridan
Stafford, England

Sgurdon said:
Thanks Ken,
I inserted the formula:
-- E-O: Val(Mid([location],InStr([location],"-")+1)) Mod 2
and ran the query; it brought back 1's where the location was odd and 0's
where the location was even but when I tried to run the query with a 1 or a 0
as criteria it came back with a data mismatch whether I hide the field or
not. Still need an answer but now I understand MOD.
--
Sgurdon
Always in search of knowledge


Ken Sheridan said:
Mod (derived from the mathematical term modulus) is an operator which
returns the remainder after dividing the first number by the second, so even
numbers divided by 2 have a remainder of 0, odd numbers a remainder of 1. To
return even numbers in a query you can remove the IIf function call form the
expression so it becomes:

Val(Mid([location],InStr([location],"-")+1)) Mod 2

A criterion of 1 should return all odd rows, a criterion of 0 all even rows.
If you uncheck the 'show' checkbox the computed column won't be returned in
the result set, it will merely determine which rows are returned.

Ken Sheridan
Stafford, England

Sgurdon said:
I have an expression that was suggested by someone here a couple of years ago
and it has worked great. I use it to isolate the even or odd side of my
storage locations. I have always used it in a make table query (with no
criteria) and then worked with the data in the new table. Today I tried to
use it in a select query and when I tried to apply a criteria , E to seperate
the even side or O to find the odd, it came back with the message "Data type
mismatch in criteria field". The query works fine until I try to apply
criteria. I am using a linked table. It is a CSV file.

Here is the expression:

E-O: IIf(Val(Mid([location],InStr([location],"-")+1)) Mod 2=0,"E","O")

I kind of understand everything in this expression except "Mod 2=0", I
can't
seem to find anything about "Mod", is that a function?

Sample of the data from the field [location] B 123-159-A.

I have tried using "E, O, 0, 1, and 2" as the criteria and I always get the
above referenced error message.
Thanks, any help or suggestions will be greatly appreciated.

Sgurdon
Click to expand...
Click to expand...
Click to expand...
 
Hi Ken,
Sorry for the delay in answering. Here is the SQL of my query.
SELECT [QUANTITY ON HAND - DAILY].ITEM, Mid([ITEM],3,12) AS SFD_ITEM,
[QUANTITY ON HAND - DAILY].DESCRIPTION, [QUANTITY ON HAND - DAILY].LOCATION,
Left([LOCATION],1) AS [ZONE], Mid([LOCATION],3,3) AS AISLE,
Mid([LOCATION],7,3) AS BIN, Right([LOCATION],1) AS TIER, [QUANTITY ON HAND -
DAILY].QTY, [QUANTITY ON HAND - DAILY].RESV, [QUANTITY ON HAND - DAILY].CHK,
IIf(Val(Mid([location],InStr([location],"-")+1)) Mod 2=0,"E","O") AS [E-O]
FROM [QUANTITY ON HAND - DAILY]
WHERE ((([QUANTITY ON HAND - DAILY].ITEM) Is Not Null) AND
((Mid([LOCATION],3,3))=100) AND
((IIf(Val(Mid([location],InStr([location],"-")+1)) Mod 2=0,"E","O"))="e"));

The ="e" at the very end is the criteria that make it go wacko.

Here is a sample line of the data the query is ran on:

ITEM DESCRIPTION LOCATION QTY RESV UOM CHK AUD
4610077602 U17243 401599 DISPENSER ALLSTAR PREMIER AEROSOL W/LCD A
100-156-C 80 yes DI 66J 0

Thanks,
Sgurdon
--
Always in search of knowledge


Ken Sheridan said:
That's very strange. Post the SQL of the query here. Maybe we'll be able to
pinpoint the problem then.

Ken Sheridan
Stafford, England

Sgurdon said:
Thanks Ken,
I inserted the formula:
-- E-O: Val(Mid([location],InStr([location],"-")+1)) Mod 2
and ran the query; it brought back 1's where the location was odd and 0's
where the location was even but when I tried to run the query with a 1 or a 0
as criteria it came back with a data mismatch whether I hide the field or
not. Still need an answer but now I understand MOD.
--
Sgurdon
Always in search of knowledge


Ken Sheridan said:
Mod (derived from the mathematical term modulus) is an operator which
returns the remainder after dividing the first number by the second, so even
numbers divided by 2 have a remainder of 0, odd numbers a remainder of 1. To
return even numbers in a query you can remove the IIf function call form the
expression so it becomes:

Val(Mid([location],InStr([location],"-")+1)) Mod 2

A criterion of 1 should return all odd rows, a criterion of 0 all even rows.
If you uncheck the 'show' checkbox the computed column won't be returned in
the result set, it will merely determine which rows are returned.

Ken Sheridan
Stafford, England

:

I have an expression that was suggested by someone here a couple of years ago
and it has worked great. I use it to isolate the even or odd side of my
storage locations. I have always used it in a make table query (with no
criteria) and then worked with the data in the new table. Today I tried to
use it in a select query and when I tried to apply a criteria , E to seperate
the even side or O to find the odd, it came back with the message "Data type
mismatch in criteria field". The query works fine until I try to apply
criteria. I am using a linked table. It is a CSV file.

Here is the expression:

E-O: IIf(Val(Mid([location],InStr([location],"-")+1)) Mod 2=0,"E","O")

I kind of understand everything in this expression except "Mod 2=0", I
can't
seem to find anything about "Mod", is that a function?

Sample of the data from the field [location] B 123-159-A.

I have tried using "E, O, 0, 1, and 2" as the criteria and I always get the
above referenced error message.
Thanks, any help or suggestions will be greatly appreciated.

Sgurdon
Click to expand...
Click to expand...
Click to expand...
 
It looks to me like the problem is with the data type of the return value of
the Mid function, rather than that of the IIf function. The former returns a
string so you need to wrap the 100 in quotes:

SELECT [QUANTITY ON HAND - DAILY].ITEM, Mid([ITEM],3,12) AS SFD_ITEM,
[QUANTITY ON HAND - DAILY].DESCRIPTION, [QUANTITY ON HAND - DAILY].LOCATION,
Left([LOCATION],1) AS [ZONE], Mid([LOCATION],3,3) AS AISLE,
Mid([LOCATION],7,3) AS BIN, Right([LOCATION],1) AS TIER, [QUANTITY ON HAND -
DAILY].QTY, [QUANTITY ON HAND - DAILY].RESV, [QUANTITY ON HAND - DAILY].CHK,
IIf(Val(Mid([location],InStr([location],"-")+1)) Mod 2=0,"E","O") AS [E-O]
FROM [QUANTITY ON HAND - DAILY]
WHERE ((([QUANTITY ON HAND - DAILY].ITEM) Is Not Null) AND
((Mid([LOCATION],3,3))="100") AND
((IIf(Val(Mid([location],InStr([location],"-")+1)) Mod 2=0,"E","O"))="e"));

Ken Sheridan
Stafford, England
 
Hi Ken,
Tried putting quotes in; it still didn't work. I got the same message
again. I have just converted the query to a make table query and then I run
the query off of the new table. It's an extra step but it solves the problem.
Thanks for you help.
--
Always in search of knowledge


Ken Sheridan said:
It looks to me like the problem is with the data type of the return value of
the Mid function, rather than that of the IIf function. The former returns a
string so you need to wrap the 100 in quotes:

SELECT [QUANTITY ON HAND - DAILY].ITEM, Mid([ITEM],3,12) AS SFD_ITEM,
[QUANTITY ON HAND - DAILY].DESCRIPTION, [QUANTITY ON HAND - DAILY].LOCATION,
Left([LOCATION],1) AS [ZONE], Mid([LOCATION],3,3) AS AISLE,
Mid([LOCATION],7,3) AS BIN, Right([LOCATION],1) AS TIER, [QUANTITY ON HAND -
DAILY].QTY, [QUANTITY ON HAND - DAILY].RESV, [QUANTITY ON HAND - DAILY].CHK,
IIf(Val(Mid([location],InStr([location],"-")+1)) Mod 2=0,"E","O") AS [E-O]
FROM [QUANTITY ON HAND - DAILY]
WHERE ((([QUANTITY ON HAND - DAILY].ITEM) Is Not Null) AND
((Mid([LOCATION],3,3))="100") AND
((IIf(Val(Mid([location],InStr([location],"-")+1)) Mod 2=0,"E","O"))="e"));

Ken Sheridan
Stafford, England

Sgurdon said:
Hi Ken,
Sorry for the delay in answering. Here is the SQL of my query.
SELECT [QUANTITY ON HAND - DAILY].ITEM, Mid([ITEM],3,12) AS SFD_ITEM,
[QUANTITY ON HAND - DAILY].DESCRIPTION, [QUANTITY ON HAND - DAILY].LOCATION,
Left([LOCATION],1) AS [ZONE], Mid([LOCATION],3,3) AS AISLE,
Mid([LOCATION],7,3) AS BIN, Right([LOCATION],1) AS TIER, [QUANTITY ON HAND -
DAILY].QTY, [QUANTITY ON HAND - DAILY].RESV, [QUANTITY ON HAND - DAILY].CHK,
IIf(Val(Mid([location],InStr([location],"-")+1)) Mod 2=0,"E","O") AS [E-O]
FROM [QUANTITY ON HAND - DAILY]
WHERE ((([QUANTITY ON HAND - DAILY].ITEM) Is Not Null) AND
((Mid([LOCATION],3,3))=100) AND
((IIf(Val(Mid([location],InStr([location],"-")+1)) Mod 2=0,"E","O"))="e"));

The ="e" at the very end is the criteria that make it go wacko.

Here is a sample line of the data the query is ran on:

ITEM DESCRIPTION LOCATION QTY RESV UOM CHK AUD
4610077602 U17243 401599 DISPENSER ALLSTAR PREMIER AEROSOL W/LCD A
100-156-C 80 yes DI 66J 0

Thanks,
Sgurdon
Click to expand...
Click to expand...
 
Back
Top