Looping through a query

[ejhatch]

Hi All,

I am hoping some kind person will be able to assist my learning curve. I have managed to get through the compile errors with my query but I am battling to get the loop to work correctly.

Basically I have a table called tblIT0008WageTypeValues which holds constant values and looks like this:

WageTypeNum Min
1000 12
1033 750
2050 1000

My data table looks like this:

WT Num BET01
1000 5
1000 50
1000 870
1033 45
1033 3465
1033 9000
2050 500
2050 20000

I have written a query which goes and fires off the code to bring back the records where the "BET01" in the data table is less than the "min" value.

In my query I have the following value in the top line:
Below Min: wagetypechecks([BET01])

My code is as follows:

Option Compare Database

Public Function wagetypechecks(wtamt As String) As Boolean

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim I As Integer
Dim wtmin As String

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblIT0008WageTypeValues", dbOpenSnapshot)

wtmin = ""
wagetypechecks = False

With rst

For I = 0 To RecordCount
wtmin = .Fields("min")

If wtamt < wagetypechecks Then
wagetypechecks = True

Else: wagetypechecks = False

End If

Next I

End With

End Function

----------------------------------

I have managed to get the code to at least run through. But it returns all the records, whereas I only want it to return the records where the BET01 value is less than the "min" value.

In addition the looping part is not working properly.

I also need it to match up the "wage type num" and "WT Num" and check only the corresponding values when the wage types are equal.

I hope I have been clear in my issue. Please let me know if you need more clarity. I know by looking at the code you will notice that I am still learning VB.

Any help will be most gratefully received.

Thanks,

Evan

 

[antomack]

If the output below is what you are looking for then a simple query will suffice rather than using the function assuming your table tblIT0008WageTypeValues has only one record for every WageTypeNumber.

WTNum BET01
1000 5
1033 45
2050 500

Query as follows will suffice for above, I've called the data table WageDataTable so you will need to edit the query to your actual name
SELECT WageDataTable.WTNum, WageDataTable.BET01 FROM tblIT0008WageTypeValues INNER JOIN WageDataTable ON tblIT0008WageTypeValues.WageTypeNum = WageDataTable.WTNum WHERE (((WageDataTable.BET01)<[Min]));

 

[ejhatch]

Hi Antomack,

Firstly thanks a stack for your clever reply. I just assumed it would have to be done in the VB code part.

I have adapted the code to the following:

SELECT PA0008.LGA01, PA0008.BET01, PA0008.PERNR
FROM tblIT0008WageTypeValues INNER JOIN PA0008 ON tblIT0008WageTypeValues.WageTypeNumber = PA0008.LGA01
WHERE (((PA0008.BET01)<[Min]));

The code works fine, however, if a user wanted to use a global value as well as individual values, how could I adapt it.

So looking at the above code the program will find the joins between PA0008.LGA01 and tblIT0008WageTypeValues.WageTypeNumber and then see whether PA0008.BET01 < tblIT0008WageTypeValues.min

Instead of putting in individual values for all of the WageTypeNumber's, I actually want to hold amounts against WageTypeNumber's 1000, 1001 and 1002 and for the remaining ones I want to hold an overall value and the WageTypeNumber is called "all" for example.

Catering for this mix is what I really need to address.

Thanks,

Evan