greater than or less than zero problems

[cass84]

I'm having this weird issue, or maybe i'm just missing something. but I
have a table like so...
(edited,shorter version of table)
ID CLASS GROUP DIFF WEEK
62 759 MPT 0 WEEK 1
83 769 AB0 0 WEEK 1
82 769 AB0 0 WEEK 1
84 769 AB0 0 WEEK 1
23 750 M50 0 WEEK 1
50 759 MPT -10 WEEK 1
19 750 MT2 -10 WEEK 1
18 750 M16 -10 WEEK 1
15 750 M16 -10 WEEK 1
14 750 M16 -10 WEEK 1
10 617 CT5 -10 WEEK 1
9 617 CT5 -10 WEEK 1
7 612 BT5 -10 WEEK 1
39 757 MR8 11 WEEK 1
54 759 MPT 11 WEEK 1

I created 2 separate queries. One where it counts where the diff is >0
and the other where the diff is <0. For some reason when I count >0 it
includes all of the negatives plus all of the positives(-10,11) and
when I run the query for <0 it returns nothing. I also tried using the
filter to sort the table to make sure it wasn't a data error and it
also did the same thing. What am I missing? Here's my queries

SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS
EARLY
FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT
YR].WEEK = WEEKS.Week
WHERE ((([Shipments CURRENT YR].DIFF)>"0"))
GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK
ORDER BY WEEKS.[W View Order];



SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS
LATE
FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT
YR].WEEK = WEEKS.Week
WHERE ((([Shipments CURRENT YR].DIFF)<"0"))
GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK
ORDER BY WEEKS.[W View Order];

 

[Guest]

Hi


Change your

WHERE ((([Shipments CURRENT YR].DIFF)>"0"))

to
WHERE ((([Shipments CURRENT YR].DIFF)>0))


--
Wayne
Manchester, England.
Enjoy whatever it is you do

I'm having this weird issue, or maybe i'm just missing something. but I
have a table like so...
(edited,shorter version of table)
ID CLASS GROUP DIFF WEEK
62 759 MPT 0 WEEK 1
83 769 AB0 0 WEEK 1
82 769 AB0 0 WEEK 1
84 769 AB0 0 WEEK 1
23 750 M50 0 WEEK 1
50 759 MPT -10 WEEK 1
19 750 MT2 -10 WEEK 1
18 750 M16 -10 WEEK 1
15 750 M16 -10 WEEK 1
14 750 M16 -10 WEEK 1
10 617 CT5 -10 WEEK 1
9 617 CT5 -10 WEEK 1
7 612 BT5 -10 WEEK 1
39 757 MR8 11 WEEK 1
54 759 MPT 11 WEEK 1

I created 2 separate queries. One where it counts where the diff is >0
and the other where the diff is <0. For some reason when I count >0 it
includes all of the negatives plus all of the positives(-10,11) and
when I run the query for <0 it returns nothing. I also tried using the
filter to sort the table to make sure it wasn't a data error and it
also did the same thing. What am I missing? Here's my queries

SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS
EARLY
FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT
YR].WEEK = WEEKS.Week
WHERE ((([Shipments CURRENT YR].DIFF)>"0"))
GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK
ORDER BY WEEKS.[W View Order];



SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS
LATE
FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT
YR].WEEK = WEEKS.Week
WHERE ((([Shipments CURRENT YR].DIFF)<"0"))
GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK
ORDER BY WEEKS.[W View Order];

 

[cass84]

That makes sense and trust me i've tried it. But everytime I make the
change for some reason, access put the quotes right back

Hi


Change your

WHERE ((([Shipments CURRENT YR].DIFF)>"0"))

to
WHERE ((([Shipments CURRENT YR].DIFF)>0))


--
Wayne
Manchester, England.
Enjoy whatever it is you do

I'm having this weird issue, or maybe i'm just missing something. but I
have a table like so...
(edited,shorter version of table)
ID CLASS GROUP DIFF WEEK
62 759 MPT 0 WEEK 1
83 769 AB0 0 WEEK 1
82 769 AB0 0 WEEK 1
84 769 AB0 0 WEEK 1
23 750 M50 0 WEEK 1
50 759 MPT -10 WEEK 1
19 750 MT2 -10 WEEK 1
18 750 M16 -10 WEEK 1
15 750 M16 -10 WEEK 1
14 750 M16 -10 WEEK 1
10 617 CT5 -10 WEEK 1
9 617 CT5 -10 WEEK 1
7 612 BT5 -10 WEEK 1
39 757 MR8 11 WEEK 1
54 759 MPT 11 WEEK 1

I created 2 separate queries. One where it counts where the diff is >0
and the other where the diff is <0. For some reason when I count >0 it
includes all of the negatives plus all of the positives(-10,11) and
when I run the query for <0 it returns nothing. I also tried using the
filter to sort the table to make sure it wasn't a data error and it
also did the same thing. What am I missing? Here's my queries

SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS
EARLY
FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT
YR].WEEK = WEEKS.Week
WHERE ((([Shipments CURRENT YR].DIFF)>"0"))
GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK
ORDER BY WEEKS.[W View Order];



SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS
LATE
FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT
YR].WEEK = WEEKS.Week
WHERE ((([Shipments CURRENT YR].DIFF)<"0"))
GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK
ORDER BY WEEKS.[W View Order];

 

[Guest]

Have you gone back to the table and checked the field format is a number ?


--
Wayne
Manchester, England.
Enjoy whatever it is you do

That makes sense and trust me i've tried it. But everytime I make the
change for some reason, access put the quotes right back

Hi


Change your

WHERE ((([Shipments CURRENT YR].DIFF)>"0"))

to
WHERE ((([Shipments CURRENT YR].DIFF)>0))


--
Wayne
Manchester, England.
Enjoy whatever it is you do

I'm having this weird issue, or maybe i'm just missing something. but I
have a table like so...
(edited,shorter version of table)
ID CLASS GROUP DIFF WEEK
62 759 MPT 0 WEEK 1
83 769 AB0 0 WEEK 1
82 769 AB0 0 WEEK 1
84 769 AB0 0 WEEK 1
23 750 M50 0 WEEK 1
50 759 MPT -10 WEEK 1
19 750 MT2 -10 WEEK 1
18 750 M16 -10 WEEK 1
15 750 M16 -10 WEEK 1
14 750 M16 -10 WEEK 1
10 617 CT5 -10 WEEK 1
9 617 CT5 -10 WEEK 1
7 612 BT5 -10 WEEK 1
39 757 MR8 11 WEEK 1
54 759 MPT 11 WEEK 1

I created 2 separate queries. One where it counts where the diff is >0
and the other where the diff is <0. For some reason when I count >0 it
includes all of the negatives plus all of the positives(-10,11) and
when I run the query for <0 it returns nothing. I also tried using the
filter to sort the table to make sure it wasn't a data error and it
also did the same thing. What am I missing? Here's my queries

SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS
EARLY
FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT
YR].WEEK = WEEKS.Week
WHERE ((([Shipments CURRENT YR].DIFF)>"0"))
GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK
ORDER BY WEEKS.[W View Order];



SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS
LATE
FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT
YR].WEEK = WEEKS.Week
WHERE ((([Shipments CURRENT YR].DIFF)<"0"))
GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK
ORDER BY WEEKS.[W View Order];

 

[cass84]

I was working with someone else on this and she was the creator of the
table. i had her go in and check and that was the problem. Something so
simple! Thanks

Have you gone back to the table and checked the field format is a number ?


--
Wayne
Manchester, England.
Enjoy whatever it is you do

That makes sense and trust me i've tried it. But everytime I make the
change for some reason, access put the quotes right back

Hi


Change your
WHERE ((([Shipments CURRENT YR].DIFF)>"0"))

to
WHERE ((([Shipments CURRENT YR].DIFF)>0))


--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

I'm having this weird issue, or maybe i'm just missing something. but I
have a table like so...
(edited,shorter version of table)
ID CLASS GROUP DIFF WEEK
62 759 MPT 0 WEEK 1
83 769 AB0 0 WEEK 1
82 769 AB0 0 WEEK 1
84 769 AB0 0 WEEK 1
23 750 M50 0 WEEK 1
50 759 MPT -10 WEEK 1
19 750 MT2 -10 WEEK 1
18 750 M16 -10 WEEK 1
15 750 M16 -10 WEEK 1
14 750 M16 -10 WEEK 1
10 617 CT5 -10 WEEK 1
9 617 CT5 -10 WEEK 1
7 612 BT5 -10 WEEK 1
39 757 MR8 11 WEEK 1
54 759 MPT 11 WEEK 1

I created 2 separate queries. One where it counts where the diff is >0
and the other where the diff is <0. For some reason when I count >0 it
includes all of the negatives plus all of the positives(-10,11) and
when I run the query for <0 it returns nothing. I also tried using the
filter to sort the table to make sure it wasn't a data error and it
also did the same thing. What am I missing? Here's my queries

SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS
EARLY
FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT
YR].WEEK = WEEKS.Week
WHERE ((([Shipments CURRENT YR].DIFF)>"0"))
GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK
ORDER BY WEEKS.[W View Order];



SELECT WEEKS.[W View Order], [Shipments CURRENT YR].WEEK, Count(*) AS
LATE
FROM [Shipments CURRENT YR] INNER JOIN WEEKS ON [Shipments CURRENT
YR].WEEK = WEEKS.Week
WHERE ((([Shipments CURRENT YR].DIFF)<"0"))
GROUP BY WEEKS.[W View Order], [Shipments CURRENT YR].WEEK
ORDER BY WEEKS.[W View Order];