Run a query in Access in VB

[DawnP277]

I am trying to modify some code.

Currently I have import statements that import test results from another
computer (.CSV).
I have queries that find me the last recrod and print a label and a report.

Now I need to have VB look at the MinLoad and MaxLoad and makesure that the
AvgLoad falls between these two points before printing the label.

Currently the user clicks a button and the reports just print out, I have no
forms open.

Does anyone have any suggestions on how to get VB to get the Min, max, and
Avg from the query so I can use them in a If statement?

Thanks for your help.

Dawn

 

[Dorian]

You can use the DLookup function to lookup the value from a query just the
same as looking up a value from a table.
Look in Access Help for the DLookup function for all the details.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[JimBurke via AccessMonster.com]

First let's make sure I understand what you're doing. It sounds like your
imported data goes into a table and one of the fields is called Load. You
want to find the min, max and avg of the 'Load' value. Is that right?

If I'm right, then this should work:

dim minLoad as double, maxLoad as double, avgLoad as double

minLoad = DMin("Load", "tblName")
maxLoad = DMax("Load","tblName")
avgLoad = DAvg("Load","tblName")

Where 'tblName' is the name of the table with the data in it. Not sure if
your column is actually called 'Load' or not, but the column name is the
first argument in those functions.

Put these in your subroutine, then you can use those variables in your If
statement. I declared them as Double - not sure what your requirements would
be for that.

 

[DawnP277]

Jim, thanks for the code. Will this work from a query as well as a table.

The results for min, max, and load are from a query.

Because I need the results from the last record.

Thanks Dawn

 

[DawnP277]

This worked, thanks for the help.

Dawn

You can use the DLookup function to lookup the value from a query just the
same as looking up a value from a table.
Look in Access Help for the DLookup function for all the details.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[JimBurke via AccessMonster.com]

Thos functions work with a query or a table. but I may be wrong in what I
thought you were trying to do. I was assuming you wanted the min, max and and
avg from a number of rows. You're saying you need results from the last
record, so it sounds like maybe my assumption was wrong. Can you be more
specific about your source of data - are you looking for min, max, avg from
many rows, or are there a bunch of values in that last row that you need to
get it from? I'm not clear on exactly how this data is 'stored'.

Jim, thanks for the code. Will this work from a query as well as a table.

The results for min, max, and load are from a query.

Because I need the results from the last record.

Thanks Dawn

First let's make sure I understand what you're doing. It sounds like your
imported data goes into a table and one of the fields is called Load. You

[quoted text clipped - 34 lines]

 

[DawnP277]

The min max and average values come from the import. My query pulls the
numbers.

Dawn

Thos functions work with a query or a table. but I may be wrong in what I
thought you were trying to do. I was assuming you wanted the min, max and and
avg from a number of rows. You're saying you need results from the last
record, so it sounds like maybe my assumption was wrong. Can you be more
specific about your source of data - are you looking for min, max, avg from
many rows, or are there a bunch of values in that last row that you need to
get it from? I'm not clear on exactly how this data is 'stored'.

Jim, thanks for the code. Will this work from a query as well as a table.

The results for min, max, and load are from a query.

Because I need the results from the last record.

Thanks Dawn

First let's make sure I understand what you're doing. It sounds like your
imported data goes into a table and one of the fields is called Load. You

[quoted text clipped - 34 lines]

Dawn