Check if a value is in an array (date) 2

[JoPaBC]

Hi,

I am trying to check if a value (date) is in a array; this is my code:

Dim SVIATKY() As Date
Dim STD As Date
Dim K, KLK As Integer

'adding value into array
    sSQL = "Select [SH] From [STATS];"
    Set rst4 = New ADODB.Recordset
    rst4.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic

    KLK = rst4.RecordCount

    ReDim SVIATKY(1 To KLK) As Date

    For K = 1 To KLK
        SVIATKY(K) = rst4.Fields(0).Value
        rst4.MoveNext
    Next

'testing if my array contains a specific date - getting error message
If InStr(Join(SVIATKY), STD, 1) > 0 Then
'execute code if match found
End if

The InStr gives me an error message like 'error 5 was generated, invalid procedure call or argument' and the same message for updated code 'InStr(Join(SVIATKY), STD)'.

Any idea please?

 

[JoPaBC]

I changed both 'Dim SVIATKY() As Date' and "ReDim SVIATKY() As Date' to 'Dim SVIATKY() As String' and "ReDim SVIATKY() As String' and it looks like it working properly now...

 

[strongm]

>I am trying to check if a value (date) is in a array

well … not really, you actually seem to want to check if any SH row is a particular date. And we can do that much more cleanly:

sSQL = "Select Count([SH]) From [STATS] WHERE [SH]=#" & STD & "#;"

Then you just need to check the value of rst4.fields(0) - if it is anything other than 0, then the date was found. So your code could become:

[blue]
    Dim STD As Date [green]' by the way, where are you getting the value for this? It isn't shown in your code[/green]

    [green]' Count how many instances of STD we can find in SH[/green]
    sSQL = "Select Count([SH]) From [STATS] WHERE [SH]=#" & STD & "#;"
    Set rst4 = New ADODB.Recordset
    rst4.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic

   

    [green]' seeing if we found any matches[/green]
    If rst4.fields(0) > 0 Then
        [green]'execute code if match found[/green]
    End if [/blue]

 

[JoPaBC]

Thank you, strongm, interesting idea.
This program basically reads Excel data with total hours worked by each person in two week period and writes it into Access database breaking down by regular hours, overtime hours, double time hours and then prepares TXT file for import to the payroll program.
This specific part of program checks if there is a statutory holiday within processed pay period and decides if there should be 40 or 32 regular hours for that week.
Your solution would query the statutory holidays table for each day (read Excel cell); I am just wondering, if this would be more demanding and slower than get the list of statutory holidays first and test statutory holidays stored in an array(?)

 

[strongm]

Your solution is reading each cell, then putting in an array, then testing the array. Just on that basis I think it looks like my solution should be faster.

 

[JoPaBC]

Well, not exactly in that order - the original solution first reads all holidays dates from MS Access, put them into an array, then reads Excel cell by cell, tests if any holiday is the processed week and divides total hours as regular, overtime,or double time hours.
There are only like up to 50 employees per pay period so it would be probably processed in a few seconds for both solutions.
I will make version A and version B
Thanks!

 

[TopTeller]

Whether it's VB, FoxPro, or even Excel, it's always challenging to manage Date/Time. Thank you strongm for your contribution as always.