MIN/MAX not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jutlaux - 23 Apr 2008 16:16 GMT

I have a query that has a field that pulls out some data from another field
by the following statement

WIDTH:
Mid([PART_ID],InStr(1,[PART_ID],"W")+1,InStr(2,[PART_ID],"A")-InStr(1,[PART_ID],"W")-1)

This part works great.

The problem is that I now want to find the MIN & MAX of the WIDTH so I
created another query that has 2 fields 1)Total: MAX 2) Total: MIN.

The problem is when I run this query it doesn't give me the actually MIN and
MAX of the data. It gives me numbers but they are not correct. I know this
b/c if I export the original query that got me WIDTH to excel and find the
MIN & MAX on it I get the correct values. What am I doing wrong?

Reply to this Message

Jerry Whittle - 23 Apr 2008 16:28 GMT

That's because it's not returning numbers. Rather it's returning numerical
characters in a string. From Help: Mid Function Returns a Variant (String)
containing a specified number of characters from a string.

You need to wrap your code in something like CDbl to covert the strings to
numbers. The Val() function should work also.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I have a query that has a field that pulls out some data from another field
> by the following statement
[quoted text clipped - 11 lines]
> b/c if I export the original query that got me WIDTH to excel and find the
> MIN & MAX on it I get the correct values. What am I doing wrong?

Reply to this Message