Access 2000: Difficulties with DataType and Filtering

  • Thread starter Thread starter icon242
  • Start date Start date
I

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
 
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
 
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.
 
Post the your syntax here together with the field types, maybe I can spot
the offender ;-)

Regards/JK
 
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?
Click to expand...

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]
 
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.
 
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
Click to expand...

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 "
Click to expand...

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)
Click to expand...

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:
Click to expand...

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

John W. Vinson[MVP]
 
Back
Top