H
HardWorkDude
So I have one master table with different products in it, each grouped by
category. Moreover, for one of the categories of products, there is a field
to enter a number range. For example, 10-50, 100-300; 30-400, just a simple
number range. Obviously, the field cannot be a number field because it
contains a hyphen. Anyway, what I am trying to do is create a query that
further groups these products by the number range. I have 3 categories of
ranges, products that are smaller than 300, (<300), products that are larger
than 300 and smaller than 600 (>300 and <600), and products that are larger
than 600 (>600). So if the product in the main table has a range of 100-200,
for example, it would be grouped in the "<300" category, that is, because the
larger of the numbers, 200, is smaller than 300. So what I did was create an
expression in the query that extracts the number after the hyphen ("-").
However, in the master table, there are some products that are entered in the
database that do not yet have values assigned, so they come up as ERROR in
the query. So to take care of this problem I first created a query in which I
specify to only return results where for that field, the value is Not Null,
so that it ignores ones where the field is empty. Next, I created a query
based on this query where I add the expression that takes the number after
the hyphen, and the query still looks fine. It would look like: PRODUCT,
Expr1: CInt(Mid([FieldName],InStr([FieldName],"-")+1))
Like I said, the expression takes the higher number from the number range in
the field for each product, the number to the right of the "-". Also, I added
the CInt to be sure that it converts it to a number, even though it is
already one. But once I write in the Criteria <"300", it says "Data type
mismatch in criteria expression" and I do not understand why, I have been
trying it a million different ways and it always says that error message. I
even tried creating a third query from this, and it still says it. What can I
do? I hope this makes sense. THANKS!
category. Moreover, for one of the categories of products, there is a field
to enter a number range. For example, 10-50, 100-300; 30-400, just a simple
number range. Obviously, the field cannot be a number field because it
contains a hyphen. Anyway, what I am trying to do is create a query that
further groups these products by the number range. I have 3 categories of
ranges, products that are smaller than 300, (<300), products that are larger
than 300 and smaller than 600 (>300 and <600), and products that are larger
than 600 (>600). So if the product in the main table has a range of 100-200,
for example, it would be grouped in the "<300" category, that is, because the
larger of the numbers, 200, is smaller than 300. So what I did was create an
expression in the query that extracts the number after the hyphen ("-").
However, in the master table, there are some products that are entered in the
database that do not yet have values assigned, so they come up as ERROR in
the query. So to take care of this problem I first created a query in which I
specify to only return results where for that field, the value is Not Null,
so that it ignores ones where the field is empty. Next, I created a query
based on this query where I add the expression that takes the number after
the hyphen, and the query still looks fine. It would look like: PRODUCT,
Expr1: CInt(Mid([FieldName],InStr([FieldName],"-")+1))
Like I said, the expression takes the higher number from the number range in
the field for each product, the number to the right of the "-". Also, I added
the CInt to be sure that it converts it to a number, even though it is
already one. But once I write in the Criteria <"300", it says "Data type
mismatch in criteria expression" and I do not understand why, I have been
trying it a million different ways and it always says that error message. I
even tried creating a third query from this, and it still says it. What can I
do? I hope this makes sense. THANKS!