Min/Max

[Rick Kalifa]

First of all, thanks for any assistance.

I'm trying to use Min/Max to find the highest and lowest from a list of
numbers.

The data is in a csv file, which I'm importing into access. In order to make
the import quick, I'm just taking the defaults (text). Although, if there is
a way to save the import settings, I would like to set it up the right way.
Anyhow, the numbers are in a text format. I'm running a query in which I'm
using the string convert function.

The actual problem I'm having is that Access is mistaking the wrong numbers
for min and max. I'm assuming that it is using the first number from the
list of numbers to determine the min/max. For instance, I'm getting a number
like 1034 as a min and 982 as a max. Am I doing something wrong? There has
to be a better way.

Thanks,
Rick Kalifa.

 

[Dale Fye]

Rick,

Assuming that you are trying to use MIN and MAX to determine the
minimum and maximum values from among a single field in your table,
and that field is currently a text string, then you need to use the
VAL function to convert the string to a number. Since '1034' starts
with a 1, it is less than '982' when compared as string values

Your SQL should look something like:

SELECT MIN(VAL([YourField])) as MinVal,
MAX(VAL([YourField])) as MaxVal
FROM yourTable

--
HTH

Dale Fye


First of all, thanks for any assistance.

I'm trying to use Min/Max to find the highest and lowest from a list
of
numbers.

The data is in a csv file, which I'm importing into access. In order
to make
the import quick, I'm just taking the defaults (text). Although, if
there is
a way to save the import settings, I would like to set it up the right
way.
Anyhow, the numbers are in a text format. I'm running a query in which
I'm
using the string convert function.

The actual problem I'm having is that Access is mistaking the wrong
numbers
for min and max. I'm assuming that it is using the first number from
the
list of numbers to determine the min/max. For instance, I'm getting a
number
like 1034 as a min and 982 as a max. Am I doing something wrong? There
has
to be a better way.

Thanks,
Rick Kalifa.

 

[John Spencer (MVP)]

Use the Val function or one of the numeric conversion functions to get a number,
Right now Access is comparing strings.

Max(Val(SomeField))
or
Max(CDbl(SomeField))

If the field would ever be null, then I would use the former. It will handle nulls.

Yes, you can save the import settings. Import manually and set up the field
definitions; when you get to the end but before importing, click on the Advanced
? button. The dialog that comes up, has a save button. This is where you save
the definition. Now you can get that definition using code or you can get it by
pressing the advanced button when you start to import manually.

Hope that helps. I'm going off memory on the name of the button - it could be
advanced, or options, or ???

 

[Dale Fye]

Yes,

1. Open a new query
2. Drag two copies of the field you want to get the min and max for
to the QBE grid
3. Wrap the field name in the Val() function
4. Click on the Sigma (looks like a capital M rotated 90 degrees
counter clockwise) to create an aggregate query
5. In the grid, change the GroupBy to Max for one instance and Min
for the other
6. Run your query.

--
HTH

Dale Fye


Can I put this sql in expression builder?

 

[Dale Fye]

Rick

This message indicates that you have some non-numeric (either Null or
empty string values) data in the field of your table. Try modifying
it as below. To do this, you have to have some idea of what the
minimum or maximum value in the field will already be, because you
have to tell Access how to handle the Null or blank values. I've used
the IIF() function to determine whether the length of the value in the
field is zero. The LEN(Trim([fieldname] & "")) part of the operation
adds an empty string to the field so that if the field value is null,
it will return a string, which Trim can evaluate without throwing an
error.

SELECT Min(Val(IIF(LEN(TRIM([Metrics]![GIONDLETOP0- Available Memory]
& ""))=0,
999999,
[Metrics]![GIONDLETOP0- Available
Memory]))) AS Expr1,
Max(Val(IIF(LEN(TRIM([Metrics]![GIONDLETOP0- Available
Memory] & ""))=0,
-999999,
[Metrics]![GIONDLETOP0- Available
Memory]))) AS Expr2
FROM Metrics;



--
HTH

Dale Fye


Here's the SQL:
SELECT Min(Val([Metrics]![GIONDLETOP0- Available Memory])) AS Expr1,
Max(Val([Metrics]![GIONDLETOP0- Available Memory])) AS Expr2
FROM Metrics;

Here's the error message:

Select View - SQL view, copy your SQL and paste it to a newsgroup
reply.

--
HTH

Dale Fye


I'm getting a data type mismatch.

Yes,

1. Open a new query
2. Drag two copies of the field you want to get the min and max for
to the QBE grid
3. Wrap the field name in the Val() function
4. Click on the Sigma (looks like a capital M rotated 90 degrees
counter clockwise) to create an aggregate query
5. In the grid, change the GroupBy to Max for one instance and Min
for the other
6. Run your query.

--
HTH

Dale Fye


Can I put this sql in expression builder?

Rick,

Assuming that you are trying to use MIN and MAX to determine the
minimum and maximum values from among a single field in your table,
and that field is currently a text string, then you need to use the
VAL function to convert the string to a number. Since '1034' starts
with a 1, it is less than '982' when compared as string values

Your SQL should look something like:

SELECT MIN(VAL([YourField])) as MinVal,
MAX(VAL([YourField])) as MaxVal
FROM yourTable

--
HTH

Dale Fye


First of all, thanks for any assistance.

I'm trying to use Min/Max to find the highest and lowest from a list
of
numbers.

The data is in a csv file, which I'm importing into access. In order
to make
the import quick, I'm just taking the defaults (text). Although, if
there is
a way to save the import settings, I would like to set it up the right
way.
Anyhow, the numbers are in a text format. I'm running a query in which
I'm
using the string convert function.

The actual problem I'm having is that Access is mistaking the wrong
numbers
for min and max. I'm assuming that it is using the first number from
the
list of numbers to determine the min/max. For instance, I'm

getting

a
number
like 1034 as a min and 982 as a max. Am I doing something wrong? There
has
to be a better way.

Thanks,
Rick Kalifa.

 

[Rick Kalifa]

Wow! This worked like a champ!

Now, what I'd like to do is group by Date... The original column had
Date/Time stamp but in my query, I'm using Left$([Date]/[Time],10) and then
the group by on the aggregate section but it's complaining about the query
being too complex??

I probably should have asked this from the start, but as you can tell... I'm
kind of a newbie to using Newsgroups as a resource.

Thanks again for all the help, it's greatly appreciated.

Eventually, I'm wanting to post all of this to a web page. Any
recommendations or ideas? Is there another Newsgroup that specializes in
that area?

Rick

This message indicates that you have some non-numeric (either Null or
empty string values) data in the field of your table. Try modifying
it as below. To do this, you have to have some idea of what the
minimum or maximum value in the field will already be, because you
have to tell Access how to handle the Null or blank values. I've used
the IIF() function to determine whether the length of the value in the
field is zero. The LEN(Trim([fieldname] & "")) part of the operation
adds an empty string to the field so that if the field value is null,
it will return a string, which Trim can evaluate without throwing an
error.

SELECT Min(Val(IIF(LEN(TRIM([Metrics]![GIONDLETOP0- Available Memory]
& ""))=0,
999999,
[Metrics]![GIONDLETOP0- Available
Memory]))) AS Expr1,
Max(Val(IIF(LEN(TRIM([Metrics]![GIONDLETOP0- Available
Memory] & ""))=0,
-999999,
[Metrics]![GIONDLETOP0- Available
Memory]))) AS Expr2
FROM Metrics;



--
HTH

Dale Fye


Here's the SQL:
SELECT Min(Val([Metrics]![GIONDLETOP0- Available Memory])) AS Expr1,
Max(Val([Metrics]![GIONDLETOP0- Available Memory])) AS Expr2
FROM Metrics;

Here's the error message:

Select View - SQL view, copy your SQL and paste it to a newsgroup
reply.

--
HTH

Dale Fye


I'm getting a data type mismatch.

Yes,

1. Open a new query
2. Drag two copies of the field you want to get the min and max for
to the QBE grid
3. Wrap the field name in the Val() function
4. Click on the Sigma (looks like a capital M rotated 90 degrees
counter clockwise) to create an aggregate query
5. In the grid, change the GroupBy to Max for one instance and Min
for the other
6. Run your query.

--
HTH

Dale Fye


Can I put this sql in expression builder?

Rick,

Assuming that you are trying to use MIN and MAX to determine the
minimum and maximum values from among a single field in your table,
and that field is currently a text string, then you need to use the
VAL function to convert the string to a number. Since '1034' starts
with a 1, it is less than '982' when compared as string values

Your SQL should look something like:

SELECT MIN(VAL([YourField])) as MinVal,
MAX(VAL([YourField])) as MaxVal
FROM yourTable

--
HTH

Dale Fye


First of all, thanks for any assistance.

I'm trying to use Min/Max to find the highest and lowest from a list
of
numbers.

The data is in a csv file, which I'm importing into access. In order
to make
the import quick, I'm just taking the defaults (text). Although, if
there is
a way to save the import settings, I would like to set it up the
right
way.
Anyhow, the numbers are in a text format. I'm running a query in
which
I'm
using the string convert function.

The actual problem I'm having is that Access is mistaking the wrong
numbers
for min and max. I'm assuming that it is using the first number from
the
list of numbers to determine the min/max. For instance, I'm getting
a
number
like 1034 as a min and 982 as a max. Am I doing something wrong?
There
has
to be a better way.

Thanks,
Rick Kalifa.