Access 2000: Difficulties with DataType and Filtering

[icon242]

I made a popup form to control the filter for a report similar to the
tutorial on the Microsoft website:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208529&Product=acc
..

This worked for quite awhile, but I have a "Date" field I need to
control. When I changed the datatype on the original table to
Date/Time, I got the error "Error '2001': You have canceled the
previous operation", which after researching I have found is indeed due
to conflicting data types. How can I change the datatype for the
filtering of only that field when filtering programatically like this?

I cannot use simply a "text" Datatype, unfortunately, because of some
other functionalities I require.

Thanks,

C. Davis

 

[JK]

I could be wrong but it does not sounds right to me. "You have cancelled the
previous operation", usually a sign of a syntax error, missing quation mark,
brackets etc. Wrong data type will give you a "Mismatch" error

However changing data type depends on what you want to change into:

CStr(YrField) will change to String
CInt(YrField) will change to Integer
CLng(YrField) will change to Long Integer
CDate(YrField) will change to Date
CDbl(YrField) will change to Double

(did I miss any?, propably!

Regards/JK

 

[icon242]

I agree, it sounds fishy, but the only reason I figured this out was
from googling the error.

Example:
http://72.14.203.104/search?q=cache...+previous+operation"&hl=en&gl=us&ct=clnk&cd=5

There isn't a syntax error, because I have a previous application of
this database (with the datatype for the Date field being "Text"), and
it works just fine if I change the Date/Time type back into a "Text".

I kind of understand what you are getting at with the examples, but I
have no idea how to integrate it into the code written by MS demons (I
have very little experience with VBA). I need to just change that
specific field's filter to the Date format, but I'm not sure how to do
it.

Thanks for your help.

 

[JK]

Post the your syntax here together with the field types, maybe I can spot
the offender ;-)

Regards/JK

 

[John Vinson]

I made a popup form to control the filter for a report similar to the
tutorial on the Microsoft website:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208529&Product=acc
.

This worked for quite awhile, but I have a "Date" field I need to
control. When I changed the datatype on the original table to
Date/Time, I got the error "Error '2001': You have canceled the
previous operation", which after researching I have found is indeed due
to conflicting data types. How can I change the datatype for the
filtering of only that field when filtering programatically like this?

Could you post your code?

Date criteria need # as delimiters, and you might not have included
these; but that's purely hazarding a vague guess at what you might be
doing.

John W. Vinson[MVP]

 

[icon242]

I didn't post my code because it is pretty much exactly out of the
example:

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.

For intCounter = 1 To 7
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " &
" = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "

End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))

' Set the Filter property.
Me.[Component List].Form.Filter = strSQL
Me.[Component List].Form.FilterOn = True
End If

End Sub


This obviously gives nothing for the datatype in the "Date" field
(Filter1). I want to know how I would go about specifying that (and I
suppost breaking the filter up into 2 parts).

All of the Filters except the "Date" field (Filters 2-7) have the
"Text" datatype, so they work just fine.

I tried making the "For" loop just filters 2-7, and then adding an
additional filter like this:

If intCounter = 1 Then
If Me("Filter1") <> "" Then

strSQL2 = "[" & Me("Filter" & intCounter).Tag & "] " & " = " &
Chr(34) & Me("Filter" & intCounter) & Chr(34)

End If
End If

I dimensioned strSQL2 As "Date", which didn't work..I got a syntax
error:

"Run-time error '3075':
Syntax error (missing operator) in query expression '12:00:00 AM'"

I'm pretty sure I'm going in completely the wrong direction as I know
almost nothing about computer programming, and even less about VB.

 

[John Vinson]

I didn't post my code because it is pretty much exactly out of the
example:

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.

For intCounter = 1 To 7
If Me("Filter" & intCounter) <> "" Then

I presume that this assumes that you have seven textboxes on the form
named Filter1, Filter2, ...?

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " &
" = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "

The Chr(34) is specifically a Text delimiter, and it will in fact fail
for date/time field values.

End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 5))

' Set the Filter property.
Me.[Component List].Form.Filter = strSQL
Me.[Component List].Form.FilterOn = True
End If

End Sub


This obviously gives nothing for the datatype in the "Date" field
(Filter1). I want to know how I would go about specifying that (and I
suppost breaking the filter up into 2 parts).

All of the Filters except the "Date" field (Filters 2-7) have the
"Text" datatype, so they work just fine.

I tried making the "For" loop just filters 2-7, and then adding an
additional filter like this:

If intCounter = 1 Then
If Me("Filter1") <> "" Then

strSQL2 = "[" & Me("Filter" & intCounter).Tag & "] " & " = " &
Chr(34) & Me("Filter" & intCounter) & Chr(34)

Change these Chr(34) to "#" and you should be OK!

End If
End If

I dimensioned strSQL2 As "Date", which didn't work..I got a syntax
error:

Well, no. strSQL isn't a date; it's a SQL string. It should be dim'd
as String.

John W. Vinson[MVP]

 

[icon242]

Change these Chr(34) to "#" and you should be OK!

Woo! And this totally worked! Thank you!

~ CWD