Best way to chek for a value in a list (1 Viewer)

[martinjward]

I have a list of dates and I would like to determine if a date is within the list. What is the best way to do this and can someone supply an example ?

Sould I use an array or just look up to a table?

 

[RuralGuy]

I depends on how often during a session you will be looking up these dates and the size of the list. DCount() or DLookup() should work for you if it is just once in a while.

 

[martinjward]

Sorry

Forgot to mention that I will be doing this in VBA - it's just a small part of a bigger process.

I only be using this once per call to a function - but the function may be called many times during a users session.

 

[RuralGuy]

I would think that with the right indexing DCount() or DLookup() should be more that speedy enough for the application you described.

 

[martinjward]

So it's simply a case of...

if DLookup("[HolidayDate]","tbl_HolidayList","[HolidayDate] = tmpDate") = True then
Count = Count +1
End if

?

 

[RuralGuy]

Not exactly. DLookup() returns a field value or a Null. Here's a reference for the syntax for the function. DLookup Usage Samples You will probably want to use DCount() but you can code either. You will need to surround a date value with the octothorp "#" which is the date delimiter. So it will be something like:

If DCount("[HolidayDate]","tbl_HolidayList","[HolidayDate] = #" & tmpDate & "#") > 0 Then
   Count = Count +1
End If

 

[martinjward]

I'll have a go tonight after the Kids have gone to bed, but thanks looks like this will do the job.

 

[RuralGuy]

Post back here if it still is stubborn about it.