Find field value Horizontally

[jgelpi16]

I am comfortable finding a column's value moving vertically. How can you return a field value moving horizontally? I am checking each field for a sepcific record. The fields contain dates in weekly increments. I am passing a date to the function and want to check that date against each date field for that record.



i.e....
Pass emplID, emplDate to funVerify

For the record containing emplID I need to compare emplDate to the 10 dates in the row.

[ajetrumpet]

use a "for each" loop with a field object variable declared or incrementing integer loop (for x = low to high)

[jgelpi16]

Ok, let me add a little twist....

For each date field their is a corresponding Yes/No field. When I identify the correct week field I need to flag the corresponding Yes/No field to a "Yes". Is there a way to return the column name? I know how to look up a specific column, but not the other way around.

[ajetrumpet]

what do you mean "the other way around"? I don't know the opposite of column name.

you have to figure out if there is any patternization to this. That's the backbone of programming anyway. That's how programs are written.

so if your structure is something like:

date1 yes/no1 date2 yes/no2

then all you have to do is issue the flag on the next loop. On the current loop, if the date matches, set an indicator and keep on checking that indicator on the subsequent loops.

could you do that i suppose?

[jgelpi16]

Ok....Yes I have it set up in a pattern.....Week1, Exempt1, Week2, Exempt2, etc...

In regards to the "other way around" I know you can use rs("ColumnName") to return the value of the column. I'm just looking for a way to return the column name.

So I have the below code set up. What you are saying is this will act the same way a "While" loop works? So I could put a counter in there?

For Each varField In rs.Fields
    'Check to see if week date = trans_date
    i = i + 1
    'Update value to "Yes"
Next varField

[ajetrumpet]

For Each varField In rs.Fields
    'Check to see if week date = trans_date
    i = i + 1
    'Update value to "Yes"
Next varField

works fine it ''varfield'' is dimmed like so:

dim varfield as dao.field

vba understands that. you're asking for each object in that same collection of objects, so it complies with you.

to get a column name, use the property. almost all objects have it:

rs("column").name

usually, people use the long reference though:

rs.fields("column").name

[jgelpi16]

Why use rs.fields("column").name vs. rs("column").name? Is it strictly preferential?

I currently have varField dimmed as a field

Dim varField as Field

Will that work or is it absolutely necessary to dim it as dao.field? Just curious.

[jgelpi16]

One more question...

I am passing a record identified to the function so I only want to perform the "for each" "loop" (for lack of a better term). How do I tell the recordset to only look through the fields for that one record as opposed to all records in the table?

Here is my code for the filter, but the filter does not work. I tried setting the rs back to itself again because I read you had to do that. I have tried without setting it back to itself and it still doesn't work.

Function funUpdateWeeks(emplID As Long, emplDate As Date)
Dim varField As Field
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim i As Integer
Dim varWeekVal, varExemptVal
Set db = CurrentDb
Set rs = db.OpenRecordset("tblExempt", dbOpenDynaset)
Set rs1 = db.OpenRecordset("SLAUDIT_FDT_SA_EXCP_SUMM_DIST", dbOpenDynaset)
'rs.FindFirst "[EmplID] = " & emplID
rs.Filter = "[EmplID] = " & emplID
Set rs = db.OpenRecordset("tblExempt", dbOpenDynaset)
i = -2
For Each varField In rs.Fields
    'Check to see if week date = trans_date
    i = i + 1
    If i > 0 Then
        varWeekVal = rs("Week" & i)
        If DatePart("ww", varWeekVal) = DatePart("ww", emplDate) Then
            If DatePart("yyyy", varWeekVal) = DatePart("yyyy", emplDate) Then
                rs.Edit
                rs("Exempt" & i).Value = -1
                rs.Update
            End If
        End If
    End If
Next varField
rs.Close
rs1.Close
End Function

[ajetrumpet]

One more question...

I am passing a record identified to the function so I only want to perform the "for each" "loop" (for lack of a better term). How do I tell the recordset to only look through the fields for that one record as opposed to all records in the table?

do you know what a pointer is? recordsets use this same thing. you work in one record ONLY, until you tell the pointer to move. e.g. -

rs.movelast

moves the pointer to the last record of the set you opened. then, you are working strictly on the record, basically with whatever you do, until you move again.

I worked in a call center a long time ago, and it was an ongoing joke about the Java pointer because everyday we saw the 'null pointer exception' error on at least 5 screens. it was a funny, but sad indication of how poorly the software was written way back in the day.

as far as dimming, vba doesn't require declarations. But you'd never know it, because virtually all professionals do it anyway. so no, but to my knowledge you cannot suppress a declaration and attempt to that variable in a loop. for instance, this will not work without an explicit dec:

for each fld in rs.fields

I believe it throws a 'type not defined' or something. That has never worked for me.

I don't like dec'ing variables because it takes so much time to do. I used to, but not anymore. But if you're not an expert in this language, I would recommend always doing it, so you don't confuse yourself in your own programs later on!

[jgelpi16]

agetrumpet I really appreciate you taking the time to explain all of this. I really enjoy learning 10 times more than I enjoy someone giving me the code....Although somtimes that's nice too . I certainly have come to understand recordset a lot better. Thanks again.

[ajetrumpet]

great!

And for the record...giving code out promotes laziness. Sorry, but I don't belong to that organization (would that be the government of the USA??).

[jgelpi16]

LOL! I don't know how you could hint at such a thing.