Need to check if date returned falls on selected month (1 Viewer)

[5hadow]

Hi all,

This is my continuation of previous thread. I figured this could be it's own thread since it's a follow-up question.

I would like to use date value returned in my code and check if the month of that date is also present in another recordset which I use to activate/deactivate certain months. If the month matches activated month then code can proceed, if not, then it needs to run rest of my code again to find another month which is activated.
With this code below:

    Do While Not rstLP.EOF
        rstLP.Edit
        nxtinspection = rstLP!fldIQADue
            Do Until DCount("fldIQA", "tblWIUnion", "Format$(fldIQA,'yyyymm')='" & Format$(nxtinspection, "yyyymm") & "'") < 3
                nxtinspection = DateAdd("m", -1, nxtinspection)
            Loop
        rstLP!fldIQA = nxtinspection
        rstLP.Update
        rstLP.MoveNext
    Loop
    rstLP.Close
    Set rstLP = Nothing

------------------------------------------------------------------
This line of code takes my date field and checks if it falls on a month/year which has less than 3 items assigned to it.
Do Until DCount("fldIQA", "tblWIUnion", "Format$(fldIQA,'yyyymm')='" & Format$(nxtinspection, "yyyymm") & "'") < 3

If it doesn't, it subtracts a month and checks again:
nxtinspection = DateAdd("m", -1, nxtinspection)

Now, I have a recordset rstMonth with SQL string which stores all month names that also have a checkmark next to them. To help visualize, I have included the following picture:

So I want in incorporate this variable in code above.

I don't know where to intersect the code, and which method to use...
Do I use an array?
How do I compare to see if value of my date checks if month is in the recordset?
Do I put it all in a loop, if so in which part of the code?
Do I use something like: Format(rstMonth.Fields("fldMonth"), "mmmm") to compare?

Any help, or at least direction would be appreciated!

 

[theDBguy]

Hi. What value does your code return?

 

[5hadow]

Hi. What value does your code return?

It returns what ever the value of fldIQADue is (a date such as 2025-11-08)

 

[theDBguy]

It returns what ever the value of fldIQADue is (a date such as 2025-11-08)

View attachment 96090

Wherever you're calling your function, you could try something like:

VariableName=0
Do While DCount("*", "ActivateTable", "Activate=True AND ID=" & Month(VariableName))=0
    VariableName = YourFunction()
Loop

Hope that helps...

 

[arnelgp]

Dim bolOK As Boolean
    Do While Not rstLP.EOF
        bolOK = False
        rstLP.Edit
        nxtinspection = rstLP!fldIQADue
            Do Until bolOK
                If DCount("fldIQA", "tblWIUnion", "Format$(fldIQA,'yyyymm')='" & Format$(nxtinspection, "yyyymm") & "'") < 3 Then
                    If DCount("1", "yourOtherTable", "Month(fldMonth) = " & Month(ntxInspection) " And Activate = -1") <> 0 Then
                        bolOk = True
                    End If
                End If
                If Not bolOK Then
                    nxtinspection = DateAdd("m", -1, nxtinspection)
                End If     
            Loop
        rstLP!fldIQA = nxtinspection
        rstLP.Update
        rstLP.MoveNext
    Loop
    rstLP.Close
    Set rstLP = Nothing

 

[5hadow]

Dim bolOK As Boolean
    Do While Not rstLP.EOF
        bolOK = False
        rstLP.Edit
        nxtinspection = rstLP!fldIQADue
            Do Until bolOK
                If DCount("fldIQA", "tblWIUnion", "Format$(fldIQA,'yyyymm')='" & Format$(nxtinspection, "yyyymm") & "'") < 3 Then
                    If DCount("1", "yourOtherTable", "Month(fldMonth) = " & Month(ntxInspection) " And Activate = -1") <> 0 Then
                        bolOk = True
                    End If
                End If
                If Not bolOK Then
                    nxtinspection = DateAdd("m", -1, nxtinspection)
                End If    
            Loop
        rstLP!fldIQA = nxtinspection
        rstLP.Update
        rstLP.MoveNext
    Loop
    rstLP.Close
    Set rstLP = Nothing

Hey, thanks for help!

Any idea why I'm getting syntax error in nested if ?

Even if I just type if it immediately turns red as you can see

 

[arnelgp]

remove the extra "if", did you see it?

 

[5hadow]

My apologies, that was me.
Still something is up with syntax

 

[arnelgp]

"Month(fldMonth) = " & Month(nxtInspection) & " And fldActive = -1") <> 0 Then

 

[5hadow]

"Month(fldMonth) = " & Month(nxtInspection) & " And fldActive = -1") <> 0 Then

Hey, thanks again! I found the problem. For some reason I didn't have nxtInspection dimentioned as date in my code. Now everything works!