Multi Select List Box with Dates 1

[chanman525]

I found a snippet of code from this forum but can not recall who placed the link. At any rate it was a sample DB to use multi select list boxes to provide criteria to a query, and the end result populated a form with matching criteria.(Form is based off of query) The code works great except when it comes to handling dates, and I can not figure how to manipulate it.

The problem is the list box is sending the date selected as a string (ex: "6/11/2007") to the function and the query is looking for a match of a date (ex: 6/11/2007). Since they don't match it displays nothing.

Here is the sql for the query:

SELECT TblProject.*
FROM TblProject
WHERE (((IsSelectedVar("FrmReportCriteria","lboDateAdded",[DateAdded]))=-1));


Here is the function that is called in the query:


Function IsSelectedVar(strFormName As String, strListBoxName As String, varValue As Variant) As Boolean
'strFormName is the name of the form
'strListBoxName is the name of the listbox
'varValue is the field to check against the listbox
Dim lbo As ListBox
Dim item As Variant
If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If
Set lbo = Forms(strFormName)(strListBoxName)
For Each item In lbo.ItemsSelected
If lbo.ItemData(item) = varValue Then
IsSelectedVar = True
Exit Function
End If
Next
End Function


The list box is simply populated by this query:


SELECT TblProject.DateAdded
FROM TblProject
GROUP BY TblProject.DateAdded;


The dateadded field is simply a short date field and is populated with a default value of Date().

If some one has any ideas on how to change the way the listbox is passing the date to the function that would be great.

Thanks for your time and brain power!

 

[SkipVought]

Hi.

You need to CONVERT the string to a REAL DATE...

RealDate = #[YourDateString]#

assuming that [YourDateString] is a recognizable date-string format. The most un-ambiguous format is yyyy-mm-dd. Other formats depend on regional settings and may not be reliably interpreted.

Or you can parse the year, month and day values and populate the DateSerial function accordingly.

Skip,

_[red][/red]

 

[chanman525]

That makes sense to me Skip, but that is my challenge on how/when to change over the listbox itemdata to be seen as a date.

Currently the listbox value (when stepping thru the code) is returned as "6/11/2007" (includes quotes) and the varValue = 6/11/2007 (no quotes).

So on the If lbo.ItemData(item) = varValue Then line it is actually reading as;

if "6/11/2007" = 6/11/2007 then

of course they don't match so it skips over.

Can you maybe give me a snippet of code on how you would implement the realdate?

 

[SkipVought]

The you could go the other way...

If lbo.ItemData(item) = Format(varValue, "m/dd/yyyy") Then

Skip,

_[red][/red]

 

[chanman525]

That worked great, with the exception that I had to assign the format to a variable but no biggy. The only problem know is how do I handle the formatting when it is a double digit month?

Hopefully this thread hasn't died yet.....

 

[PHV]

What about this ?

Function IsSelectedVar(strFormName As String, strListBoxName As String, varValue As Variant) As Boolean
  'strFormName is the name of the form
  'strListBoxName is the name of the listbox
  'varValue is the field to check against the listbox
  Dim lbo As ListBox
  Dim item As Variant
  Set lbo = Forms(strFormName)(strListBoxName)
  For Each item In lbo.ItemsSelected
    If IsDate(varValue) Then
      IsSelectedVar = (CVDate(varValue) = CVDate(lbo.ItemData(item)))
    ElseIf IsNum(varValue) Then
      IsSelectedVar = (Val(varValue) = Val(lbo.ItemData(item)))
    Else
      IsSelectedVar = (varValue = lbo.ItemData(item))
    End If
    If IsSelectedVar = True Then
      Exit Function
    End If
  Next
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[chanman525]

Man PHV you got me excited to see that code but then it bails on IsNum - sub or Function not defined?

Am i missing a reference or something? IsDate works fine.

 

[PHV]

OOps, sorry for the typo :~/
ElseIf IsNum[!]eric[/!](varValue) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[chanman525]

That fixed it PHV and the dates work great! However now the other fields are failing. I have another box that refernces the project type (ie. Access, Script, Excel). When I select one of these it returns an invalid use of Null at the following line (below in red);

For Each item In lbo.ItemsSelected
If IsDate(varValue) Then
IsSelectedVar = (CVDate(varValue) = CVDate(lbo.ItemData(item)))
ElseIf IsNumeric(varValue) Then
IsSelectedVar = (Val(varValue) = Val(lbo.ItemData(item)))
Else
IsSelectedVar = (varValue = lbo.ItemData(item))
End If
If IsSelectedVar = True Then
Exit Function
End If

varvalue = Null and lboitemdata(item) = "Access"

I am not sure why it is doing this now, it worked before with the original code? Any Ideas?

 

[PHV]

You may try this:
IsSelectedVar = ([!]Nz([/!]varValue[!], "")[/!] = lbo.ItemData(item))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[chanman525]

That worked! Thanks PHV for the time and knowledge!!! Star for you!!