ISSUES DATABASE

[Guest]

I have some problems with this database it do not work on search Issues form.
It work fine with all searches but it do not work with date searches.

please help

 

[Stefan Hoffmann]

hi,

I have some problems with this database it do not work on search Issues form.
It work fine with all searches but it do not work with date searches.

please help

I'd like Jeopardy, really i do, but no one here can guess what you are
doing. Give us a little (lot) more information.



mfG
--> stefan <--

 

[Guest]

hi,

I'd like Jeopardy, really i do, but no one here can guess what you are
doing. Give us a little (lot) more information.



mfG
--> stefan <--

Hi Stefan,

this is the code:
Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Assigned To] = " &
Me.AssignedTo & ""
End If

' If Opened By
If Not IsNull(Me.OpenedBy) Then
'Add the predicate
strWhere = strWhere & " AND " & "Issues.[Opened By] = " &
Me.OpenedBy & ""
End If

' If Status
If Nz(Me.Status) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Status = '" & Me.Status & "'"
End If

' If Category
If Nz(Me.Category) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Category = '" & Me.Category
& "'"
End If

' If Priority
If Nz(Me.Priority) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Priority = '" & Me.Priority
& "'"
End If

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " &
GetDateFilter(Me.OpenedDateFrom)
ElseIf Nz(Me.OpenedDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Opened Date To
If IsDate(Me.OpenedDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] <= " &
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Due Date From
If IsDate(Me.DueDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Due Date] >= " &
GetDateFilter(Me.DueDateFrom)
ElseIf Nz(Me.DueDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Due Date To
If IsDate(Me.DueDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Due Date] <= " &
GetDateFilter(Me.DueDateTo)
ElseIf Nz(Me.DueDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.Title Like '*" & Me.Title &
"*'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse ALL Issues", acFormDS, , strWhere,
acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Issues.Form.Filter = strWhere
Me.Browse_All_Issues.Form.FilterOn = True

End If

End Sub

and when I put the date from to search on form the program send message:

"run-time error 2001:
you have canceled the previous operation"

it means on last two rows of the code <Me.Brows_all_Issues.Form.Filter......

mfg

 

[Stefan Hoffmann]

hi,

I'd like Jeopardy, really i do, but no one here can guess what you are
doing. Give us a little (lot) more information.

strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " &
GetDateFilter(Me.OpenedDateFrom)

What does GetDateFilter return? Use MsgBox to display you filter string,
maybe it is not a valid one.

If strError <> "" Then
MsgBox strError
Else

MsgBox strWhere ' or Debug.? strWhere

'DoCmd.OpenForm "Browse ALL Issues", acFormDS, , strWhere,
acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Issues.Form.Filter = strWhere
Me.Browse_All_Issues.Form.FilterOn = True

End If

mfG
--> stefan <--

 

[Guest]

hi,

I have some problems with this database it do not work on search Issues form.
It work fine with all searches but it do not work with date searches.
I'd like Jeopardy, really i do, but no one here can guess what you are
doing. Give us a little (lot) more information.

strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " &
GetDateFilter(Me.OpenedDateFrom)

What does GetDateFilter return? Use MsgBox to display you filter string,
maybe it is not a valid one.

If strError <> "" Then
MsgBox strError
Else

MsgBox strWhere ' or Debug.? strWhere

'DoCmd.OpenForm "Browse ALL Issues", acFormDS, , strWhere,
acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Issues.Form.Filter = strWhere
Me.Browse_All_Issues.Form.FilterOn = True

End If

mfG
--> stefan <--

hi stefan,

this is the code for GetDateFilter:

Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "'" & Format(dtDate, "MM/DD/YYYY") & "'"
End Function

mfG
Zoran

 

[Stefan Hoffmann]

hi Zoran,

this is the code for GetDateFilter:

Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "'" & Format(dtDate, "MM/DD/YYYY") & "'"

Dates in Access are enclosed in #..# so try:

GetDateFilter = "#" & Format() & "#"

End Function

mfG
--> stefan <--

 

[Duane Hookom]

If this is all Access/Jet then use "#" to delimit dates.

Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY") & "#"
End Function

If that doesn't work then come back with the results of your
Debug.Print strWhere

--
Duane Hookom
MS Access MVP

hi,

I have some problems with this database it do not work on search
Issues form.
It work fine with all searches but it do not work with date searches.
I'd like Jeopardy, really i do, but no one here can guess what you are
doing. Give us a little (lot) more information.
strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " &
GetDateFilter(Me.OpenedDateFrom)

What does GetDateFilter return? Use MsgBox to display you filter string,
maybe it is not a valid one.

If strError <> "" Then
MsgBox strError
Else

MsgBox strWhere ' or Debug.? strWhere

'DoCmd.OpenForm "Browse ALL Issues", acFormDS, , strWhere,
acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight +
Me.FormFooter.Height
End If
Me.Browse_All_Issues.Form.Filter = strWhere
Me.Browse_All_Issues.Form.FilterOn = True

End If

mfG
--> stefan <--

hi stefan,

this is the code for GetDateFilter:

Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "'" & Format(dtDate, "MM/DD/YYYY") & "'"
End Function

mfG
Zoran

 

[Guest]

hi Zoran,


Dates in Access are enclosed in #..# so try:

GetDateFilter = "#" & Format() & "#"


mfG
--> stefan <--

hi stefan,

yes I tryed it still do not work.

can I ask you to download this database from microsoft and try this search.

Thanks,
Zoran

 

[Guest]

hi duane,

I tryed this but this is the result:

<Run-time error '3075'=

Syntax error in date in query expression '1=1 AND Issues.[Opened Date] >=
#07.06.2005#'

Mfg

Zoran

 

[RoyVidar]

hi duane,

I tryed this but this is the result:

<Run-time error '3075'=

Syntax error in date in query expression '1=1 AND Issues.[Opened
Date] >= #07.06.2005#'

Mfg

Zoran

If this is all Access/Jet then use "#" to delimit dates.

Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY") & "#"
End Function

If that doesn't work then come back with the results of your
Debug.Print strWhere

Ah, yes, some of us use other date delimiters, which is why we must
take it one step further, either

GetDateFilter = "#" & Format(dtDate, "MM\/DD\/YYYY") & "#"

or

GetDateFilter = "#" & Format(dtDate, "YYYY-MM-DD") & "#"

Allen Browne explains quite well here
http://allenbrowne.com/ser-36.html

 

[Guest]

Hello RoyVidar

thanks very much it works now.

This is he right code:

GetDateFilter = "#" & Format(dtDate, "MM\/DD\/YYYY") & "#"

Thanks,

 

[Tim Ferguson]

This is he right code:

GetDateFilter = "#" & Format(dtDate, "MM\/DD\/YYYY") & "#"

or this:

getDateFilter = format(dtDate, "\#mm\/dd\/yyyy\#")

or even this

' these are two Jet-recognised formats;
' use whichever you find more politically correct!
const jetISODate = "\#yyyy\-mm\-dd\#"
const jetUSADate = "\#mm\/dd\/yyyy\#"

getDateFilter = format(dtDate, jetISODate)



Hope that helps


Tim F