multi-search criteria form

[accessbeginner]

Hi all,

I have created a multi-search criteria form to filter records in an Access database. My filter is working well except regarding the search between two dates. I have really checked several times names and format for data, and everything appears to be consistent, but still I am getting an error “Type mismatch (Error 13)”.

I am a pure beginner with Access, and I am sure that an experienced user will find a solution straight away, hence my request!

Could anybody help with this or give some tips?!
Thank you to let me know
Franck



Private Function BuildFilter() As Variant
Dim varWhere As Variant
Const conJetDate = "\#dd\/mm\/yyyy\#"
varWhere = Null

If Not IsNull(Me.txtStartDate) Then
varWhere = varWhere & "([Decision Date] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
varWhere = varWhere & "([Decision Date] < " & Format(Me.txtEndDate, conJetDate) & ") AND "
End If


If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function

 

[Flona2012]

Hello Frank
Let me get this stright

if for example Me.txtStartDate=01/12/2012
and Me.txtEndDate=01/12/2013

then
varWhere=" [Decision Date] >=#01/12/2012# and [Decision Date] < #01/12/2013# and where"


True???
am I getting it right???
that the costruction of your string is wrong
Plus there's a built in function in sql that you can use "BETWEEN"

"Where [Decision Date] Between #01/12/2012# and #01/12/2013#"
which will be more clear to use


please Review

 

[accessbeginner]

Hello Flona
Thanks a lot for helping. You are absolutely right.
Do you suggest to create an event with an SQL statement to filter records on top of the VBA multi-criteria search?
BR

 

[Flona2012]

am sorry but am not so femilier with microsoft access forms, i only use queries, so this is as far as i can get
but i already did some vb.net projects with sql filtering statements so thats why i helped you in the above
and here's another way to construct your string


varWhere ="Where"
If Not IsNull(Me.txtStartDate) Then
varWhere = varWhere & " ([Decision Date] >= " & Format(Me.txtStartDate, conJetDate) & ")"
End If

If Not IsNull(Me.txtEndDate) Then
varWhere = varWhere & " AND ([Decision Date] < " & Format(Me.txtEndDate, conJetDate) & ")"
End If


If varWhere="Where" Then
varWhere = ""
End if


Please note the stuff i removed too from your sql Statement

i hope this will help