Database can't find the field "|" referred to in your expression.

K

keith.rocheck

Hey,

I am working on a database for my campus television station. One of
the sections is related to Show Tapes and the Episodes that are
contained within them.

I am trying to perform a custom filter on a sub-form using VBA when the
error in the title pops up. There are three fields associated with
this custom filter: ShowTitle, LowerDate, and UpperDate.

The routine looks to make sure two dates are entered or none then picks
whether to filter just the ShowTitle, just dates or both (or cancel the
filter).

The error shows up on any of the: "Me.ShowTapesQuery.Form.Filter = ..."
lines.

Any ideas on how to solve this are greatly appreciated.

Private Sub ApplyFilterButton_Click()
On Error GoTo Err_ApplyFilterButton_Click

If Me.ApplyFilterButton.Caption = "Apply Filter" Then
If Not (Me.LowerDate = "" And Me.UpperDate = "") Then
If Not Me.LowerDate = "" And Not Me.UpperDate = "" Then
Else
MsgBox "You must enter both dates before applying the
filter!"
GoTo Exit_ApplyFilterButton_Click
End If
End If

If Not Me.ShowTitle = "" And Not Me.UpperDate = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = " &
Me.ShowTitle.Value & " And ShowTapeSlots.Date > #" & Me.LowerDate.Value
& "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"
Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
Else
If Not Me.ShowTitle = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = " &
Me.ShowTitle.Value
Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
Else
If Not Me.LowerDate = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapeSlots.Date
#" & Me.LowerDate.Value & "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"
Click to expand...
Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
End If
End If
End If
Else
Me.ShowTapes.Form.FilterOn = False
Me.ShowTitle.Enabled = True
Me.LowerDate.Enabled = True
Me.UpperDate.Enabled = True
Me.ApplyFilterButton.Caption = "Apply Filter"
End If

Exit_ApplyFilterButton_Click:
Exit Sub

Err_ApplyFilterButton_Click:
MsgBox Err.Description
Resume Exit_ApplyFilterButton_Click
End Sub

Keith
 
J

J. Goddard

Hi -


You need to put the titles in the filter string in quotes:
Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = '" &
Me.ShowTitle.Value & "' And ShowTapeSlots.Date > #" & Me.LowerDate.Value
& "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"
and

Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = '" &
Me.ShowTitle.Value & "'"
Click to expand...

I've used single quotes here - they're easier to read.

Hope this helps

John
 
K

keith.rocheck

John,

Single quotes are not the issue because ShowTapes.Show is a Long
Integer and Me.ShowTitle.Value returns an Integer.

Any other ideas?

Keith
 
J

J. Goddard

Keith -


The error indicates that you are referencing a field that does not
exist, or has not been included in a query.

Is the subform based on a query? Your code implies this, because it
refers to Showtapes.show and ShowTapeSlots.Date - two tables or queries.

One thing you might try is to change showtapes.date to showtapes.[date],
to make sure Access knows "date" is a field. Using "Date" as a field or
control name is never a good idea anyway, because Access can confuse it
with Date() - usually when you least expect it to.

I have also found that giving a form control the same name as the
table/query field it is bound to can sometimes cause trouble.

Just a suggestion - you might change your date operators in the filter
expression from ">" and "<" to ">=" and "<=" - it makes the date range
inclusive. I think that's a little easier for users, especially if they
are looking for a specific date.

John

P.S. - If (e-mail address removed) is your valid E-Mail address, change
your signature **FAST**! Newsgroups are a favourite places for spammers
to harvest E-mail addresses. I know from sad experience!
 
K

krocheck

John,

Would the query in question being a crosstab be the reason?

The part that baffles me is that the error is not specific to when
using the date fields or the show field, but any combination. Could it
be that I cannot filter a crosstab in this way?

To help you understand the database a little,
delta.simnaweb.com/MUTVDBMSRelationships.gif contains the relationships
diagram.

The query that feeds the subform is as follows.

TRANSFORM Max(ShowTapeSlots.Episode) AS MaxOfEpisode
SELECT ShowTapes.ShowTapeID, Shows.Title
FROM Shows
RIGHT JOIN (ShowTapes
LEFT JOIN [SELECT ShowTapeSlots.*, Episodes.Date FROM
ShowTapeSlots
LEFT JOIN Episodes ON ShowTapeSlots.Episode =
Episodes.EpisodeID]. AS ShowTapeSlots ON ShowTapes.ShowTapeID =
ShowTapeSlots.ShowTape)
ON Shows.ShowID = ShowTapes.Show
GROUP BY ShowTapes.ShowTapeID, Shows.Title
PIVOT ShowTapeSlots.SlotNumber;

I tried your suggestion and it did not fix the problem.

Keith

J. Goddard said:
Keith -


The error indicates that you are referencing a field that does not
exist, or has not been included in a query.

Is the subform based on a query? Your code implies this, because it
refers to Showtapes.show and ShowTapeSlots.Date - two tables or queries.

One thing you might try is to change showtapes.date to showtapes.[date],
to make sure Access knows "date" is a field. Using "Date" as a field or
control name is never a good idea anyway, because Access can confuse it
with Date() - usually when you least expect it to.

I have also found that giving a form control the same name as the
table/query field it is bound to can sometimes cause trouble.

Just a suggestion - you might change your date operators in the filter
expression from ">" and "<" to ">=" and "<=" - it makes the date range
inclusive. I think that's a little easier for users, especially if they
are looking for a specific date.

John

P.S. - If (e-mail address removed) is your valid E-Mail address, change
your signature **FAST**! Newsgroups are a favourite places for spammers
to harvest E-mail addresses. I know from sad experience!

John,

Single quotes are not the issue because ShowTapes.Show is a Long
Integer and Me.ShowTitle.Value returns an Integer.

Any other ideas?

Keith
Click to expand...
Click to expand...
 
K

krocheck

Believeing the problem to be that the fields I was trying to filter
were not in the SELECT, I changed the query and form to use the
following SQL, but still the same problem.

TRANSFORM Max(ShowTapeSlots.Date) AS MaxOfDate
SELECT ShowTapes.ShowTapeID, ShowTapes.Show, Shows.Title
FROM Shows
RIGHT JOIN (ShowTapes
LEFT JOIN [SELECT ShowTapeSlots.*, Episodes.Date FROM
ShowTapeSlots
LEFT JOIN Episodes ON ShowTapeSlots.Episode =
Episodes.EpisodeID]. AS ShowTapeSlots
ON ShowTapes.ShowTapeID = ShowTapeSlots.ShowTape)
ON Shows.ShowID = ShowTapes.Show
GROUP BY ShowTapes.ShowTapeID, ShowTapes.Show, Shows.Title
PIVOT ShowTapeSlots.SlotNumber;

Keith said:
John,

Would the query in question being a crosstab be the reason?

The part that baffles me is that the error is not specific to when
using the date fields or the show field, but any combination. Could it
be that I cannot filter a crosstab in this way?

To help you understand the database a little,
delta.simnaweb.com/MUTVDBMSRelationships.gif contains the relationships
diagram.

The query that feeds the subform is as follows.

TRANSFORM Max(ShowTapeSlots.Episode) AS MaxOfEpisode
SELECT ShowTapes.ShowTapeID, Shows.Title
FROM Shows
RIGHT JOIN (ShowTapes
LEFT JOIN [SELECT ShowTapeSlots.*, Episodes.Date FROM
ShowTapeSlots
LEFT JOIN Episodes ON ShowTapeSlots.Episode =
Episodes.EpisodeID]. AS ShowTapeSlots ON ShowTapes.ShowTapeID =
ShowTapeSlots.ShowTape)
ON Shows.ShowID = ShowTapes.Show
GROUP BY ShowTapes.ShowTapeID, Shows.Title
PIVOT ShowTapeSlots.SlotNumber;

I tried your suggestion and it did not fix the problem.

Keith

J. Goddard said:
Keith -


The error indicates that you are referencing a field that does not
exist, or has not been included in a query.

Is the subform based on a query? Your code implies this, because it
refers to Showtapes.show and ShowTapeSlots.Date - two tables or queries.

One thing you might try is to change showtapes.date to showtapes.[date],
to make sure Access knows "date" is a field. Using "Date" as a field or
control name is never a good idea anyway, because Access can confuse it
with Date() - usually when you least expect it to.

I have also found that giving a form control the same name as the
table/query field it is bound to can sometimes cause trouble.

Just a suggestion - you might change your date operators in the filter
expression from ">" and "<" to ">=" and "<=" - it makes the date range
inclusive. I think that's a little easier for users, especially if they
are looking for a specific date.

John

P.S. - If (e-mail address removed) is your valid E-Mail address, change
your signature **FAST**! Newsgroups are a favourite places for spammers
to harvest E-mail addresses. I know from sad experience!

John,

Single quotes are not the issue because ShowTapes.Show is a Long
Integer and Me.ShowTitle.Value returns an Integer.

Any other ideas?

Keith
J. Goddard wrote:

Hi -


You need to put the titles in the filter string in quotes:

Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = '" &
Me.ShowTitle.Value & "' And ShowTapeSlots.Date > #" & Me.LowerDate.Value
& "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"

and

Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = '" &
Me.ShowTitle.Value & "'"

I've used single quotes here - they're easier to read.

Hope this helps

John



(e-mail address removed) wrote:


Hey,

I am working on a database for my campus television station. One of
the sections is related to Show Tapes and the Episodes that are
contained within them.

I am trying to perform a custom filter on a sub-form using VBA when the
error in the title pops up. There are three fields associated with
this custom filter: ShowTitle, LowerDate, and UpperDate.

The routine looks to make sure two dates are entered or none then picks
whether to filter just the ShowTitle, just dates or both (or cancel the
filter).

The error shows up on any of the: "Me.ShowTapesQuery.Form.Filter = ..."
lines.

Any ideas on how to solve this are greatly appreciated.

Private Sub ApplyFilterButton_Click()
On Error GoTo Err_ApplyFilterButton_Click

If Me.ApplyFilterButton.Caption = "Apply Filter" Then
If Not (Me.LowerDate = "" And Me.UpperDate = "") Then
If Not Me.LowerDate = "" And Not Me.UpperDate = "" Then
Else
MsgBox "You must enter both dates before applying the
filter!"
GoTo Exit_ApplyFilterButton_Click
End If
End If

If Not Me.ShowTitle = "" And Not Me.UpperDate = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = " &
Me.ShowTitle.Value & " And ShowTapeSlots.Date > #" & Me.LowerDate.Value
& "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"
Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
Else
If Not Me.ShowTitle = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = " &
Me.ShowTitle.Value
Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
Else
If Not Me.LowerDate = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapeSlots.Date


#" & Me.LowerDate.Value & "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"

Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
End If
End If
End If
Else
Me.ShowTapes.Form.FilterOn = False
Me.ShowTitle.Enabled = True
Me.LowerDate.Enabled = True
Me.UpperDate.Enabled = True
Me.ApplyFilterButton.Caption = "Apply Filter"
End If

Exit_ApplyFilterButton_Click:
Exit Sub

Err_ApplyFilterButton_Click:
MsgBox Err.Description
Resume Exit_ApplyFilterButton_Click
End Sub

Keith
Click to expand...
Click to expand...
Click to expand...
 
J

J. Goddard

The query itself being a crosstab is not likely the reason, but check
the SQL for your query -

I don't think showtapes.show is in it (showtapes.ShowTapeID is)
Did you try running the crosstab query as a stand-alone query (in query
design) to see whether you have all the fields you are expecting, and
that the column titles are what you expect them to be?

John



John,

Would the query in question being a crosstab be the reason?

The part that baffles me is that the error is not specific to when
using the date fields or the show field, but any combination. Could it
be that I cannot filter a crosstab in this way?

To help you understand the database a little,
delta.simnaweb.com/MUTVDBMSRelationships.gif contains the relationships
diagram.

The query that feeds the subform is as follows.

TRANSFORM Max(ShowTapeSlots.Episode) AS MaxOfEpisode
SELECT ShowTapes.ShowTapeID, Shows.Title
FROM Shows
RIGHT JOIN (ShowTapes
LEFT JOIN [SELECT ShowTapeSlots.*, Episodes.Date FROM
ShowTapeSlots
LEFT JOIN Episodes ON ShowTapeSlots.Episode =
Episodes.EpisodeID]. AS ShowTapeSlots ON ShowTapes.ShowTapeID =
ShowTapeSlots.ShowTape)
ON Shows.ShowID = ShowTapes.Show
GROUP BY ShowTapes.ShowTapeID, Shows.Title
PIVOT ShowTapeSlots.SlotNumber;

I tried your suggestion and it did not fix the problem.

Keith

J. Goddard said:
Keith -


The error indicates that you are referencing a field that does not
exist, or has not been included in a query.

Is the subform based on a query? Your code implies this, because it
refers to Showtapes.show and ShowTapeSlots.Date - two tables or queries.

One thing you might try is to change showtapes.date to showtapes.[date],
to make sure Access knows "date" is a field. Using "Date" as a field or
control name is never a good idea anyway, because Access can confuse it
with Date() - usually when you least expect it to.

I have also found that giving a form control the same name as the
table/query field it is bound to can sometimes cause trouble.

Just a suggestion - you might change your date operators in the filter
expression from ">" and "<" to ">=" and "<=" - it makes the date range
inclusive. I think that's a little easier for users, especially if they
are looking for a specific date.

John

P.S. - If (e-mail address removed) is your valid E-Mail address, change
your signature **FAST**! Newsgroups are a favourite places for spammers
to harvest E-mail addresses. I know from sad experience!

(e-mail address removed) wrote:

John,

Single quotes are not the issue because ShowTapes.Show is a Long
Integer and Me.ShowTitle.Value returns an Integer.

Any other ideas?

Keith
J. Goddard wrote:


Hi -


You need to put the titles in the filter string in quotes:


Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = '" &
Me.ShowTitle.Value & "' And ShowTapeSlots.Date > #" & Me.LowerDate.Value
& "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"

and


Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = '" &
Me.ShowTitle.Value & "'"

I've used single quotes here - they're easier to read.

Hope this helps

John



(e-mail address removed) wrote:



Hey,

I am working on a database for my campus television station. One of
the sections is related to Show Tapes and the Episodes that are
contained within them.

I am trying to perform a custom filter on a sub-form using VBA when the
error in the title pops up. There are three fields associated with
this custom filter: ShowTitle, LowerDate, and UpperDate.

The routine looks to make sure two dates are entered or none then picks
whether to filter just the ShowTitle, just dates or both (or cancel the
filter).

The error shows up on any of the: "Me.ShowTapesQuery.Form.Filter = ..."
lines.

Any ideas on how to solve this are greatly appreciated.

Private Sub ApplyFilterButton_Click()
On Error GoTo Err_ApplyFilterButton_Click

If Me.ApplyFilterButton.Caption = "Apply Filter" Then
If Not (Me.LowerDate = "" And Me.UpperDate = "") Then
If Not Me.LowerDate = "" And Not Me.UpperDate = "" Then
Else
MsgBox "You must enter both dates before applying the
filter!"
GoTo Exit_ApplyFilterButton_Click
End If
End If
If Me.ApplyFilterButton.Caption = "Apply Filter" Then
If Not (Me.LowerDate = "" And Me.UpperDate = "") Then
If Not Me.LowerDate = "" And Not Me.UpperDate = "" Then
Else
MsgBox "You must enter both dates before applying the
filter!"
GoTo Exit_ApplyFilterButton_Click
End If
End If
Click to expand...
If Not Me.ShowTitle = "" And Not Me.UpperDate = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = " &
Me.ShowTitle.Value & " And ShowTapeSlots.Date > #" & Me.LowerDate.Value
& "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"
Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
Else
If Not Me.ShowTitle = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = " &
Me.ShowTitle.Value
Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
Else
If Not Me.LowerDate = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapeSlots.Date



#" & Me.LowerDate.Value & "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"

Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
End If
End If
End If
Else
Me.ShowTapes.Form.FilterOn = False
Me.ShowTitle.Enabled = True
Me.LowerDate.Enabled = True
Me.UpperDate.Enabled = True
Me.ApplyFilterButton.Caption = "Apply Filter"
End If

Exit_ApplyFilterButton_Click:
Exit Sub

Err_ApplyFilterButton_Click:
MsgBox Err.Description
Resume Exit_ApplyFilterButton_Click
End Sub

Keith
Click to expand...
Click to expand...
Click to expand...
 
K

krocheck

I think that instead of using the filtering option I should just
manually change the subform's control source to an SQL statement and
change the control source and requery. I shouldn't have to, but it
seems to be the quickest way to fix this.

Will try it and let you know.

Keith
J. Goddard said:
The query itself being a crosstab is not likely the reason, but check
the SQL for your query -

I don't think showtapes.show is in it (showtapes.ShowTapeID is)
Did you try running the crosstab query as a stand-alone query (in query
design) to see whether you have all the fields you are expecting, and
that the column titles are what you expect them to be?

John



John,

Would the query in question being a crosstab be the reason?

The part that baffles me is that the error is not specific to when
using the date fields or the show field, but any combination. Could it
be that I cannot filter a crosstab in this way?

To help you understand the database a little,
delta.simnaweb.com/MUTVDBMSRelationships.gif contains the relationships
diagram.

The query that feeds the subform is as follows.

TRANSFORM Max(ShowTapeSlots.Episode) AS MaxOfEpisode
SELECT ShowTapes.ShowTapeID, Shows.Title
FROM Shows
RIGHT JOIN (ShowTapes
LEFT JOIN [SELECT ShowTapeSlots.*, Episodes.Date FROM
ShowTapeSlots
LEFT JOIN Episodes ON ShowTapeSlots.Episode =
Episodes.EpisodeID]. AS ShowTapeSlots ON ShowTapes.ShowTapeID =
ShowTapeSlots.ShowTape)
ON Shows.ShowID = ShowTapes.Show
GROUP BY ShowTapes.ShowTapeID, Shows.Title
PIVOT ShowTapeSlots.SlotNumber;

I tried your suggestion and it did not fix the problem.

Keith

J. Goddard said:
Keith -


The error indicates that you are referencing a field that does not
exist, or has not been included in a query.

Is the subform based on a query? Your code implies this, because it
refers to Showtapes.show and ShowTapeSlots.Date - two tables or queries.

One thing you might try is to change showtapes.date to showtapes.[date],
to make sure Access knows "date" is a field. Using "Date" as a field or
control name is never a good idea anyway, because Access can confuse it
with Date() - usually when you least expect it to.

I have also found that giving a form control the same name as the
table/query field it is bound to can sometimes cause trouble.

Just a suggestion - you might change your date operators in the filter
expression from ">" and "<" to ">=" and "<=" - it makes the date range
inclusive. I think that's a little easier for users, especially if they
are looking for a specific date.

John

P.S. - If (e-mail address removed) is your valid E-Mail address, change
your signature **FAST**! Newsgroups are a favourite places for spammers
to harvest E-mail addresses. I know from sad experience!

(e-mail address removed) wrote:


John,

Single quotes are not the issue because ShowTapes.Show is a Long
Integer and Me.ShowTitle.Value returns an Integer.

Any other ideas?

Keith
J. Goddard wrote:


Hi -


You need to put the titles in the filter string in quotes:


Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = '" &
Me.ShowTitle.Value & "' And ShowTapeSlots.Date > #" & Me.LowerDate.Value
& "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"

and


Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = '" &
Me.ShowTitle.Value & "'"

I've used single quotes here - they're easier to read.

Hope this helps

John



(e-mail address removed) wrote:



Hey,

I am working on a database for my campus television station. One of
the sections is related to Show Tapes and the Episodes that are
contained within them.

I am trying to perform a custom filter on a sub-form using VBA when the
error in the title pops up. There are three fields associated with
this custom filter: ShowTitle, LowerDate, and UpperDate.

The routine looks to make sure two dates are entered or none then picks
whether to filter just the ShowTitle, just dates or both (or cancel the
filter).

The error shows up on any of the: "Me.ShowTapesQuery.Form.Filter = ..."
lines.

Any ideas on how to solve this are greatly appreciated.

Private Sub ApplyFilterButton_Click()
On Error GoTo Err_ApplyFilterButton_Click

If Me.ApplyFilterButton.Caption = "Apply Filter" Then
If Not (Me.LowerDate = "" And Me.UpperDate = "") Then
If Not Me.LowerDate = "" And Not Me.UpperDate = "" Then
Else
MsgBox "You must enter both dates before applying the
filter!"
GoTo Exit_ApplyFilterButton_Click
End If
End If
If Me.ApplyFilterButton.Caption = "Apply Filter" Then
If Not (Me.LowerDate = "" And Me.UpperDate = "") Then
If Not Me.LowerDate = "" And Not Me.UpperDate = "" Then
Else
MsgBox "You must enter both dates before applying the
filter!"
GoTo Exit_ApplyFilterButton_Click
End If
End If

If Not Me.ShowTitle = "" And Not Me.UpperDate = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = " &
Me.ShowTitle.Value & " And ShowTapeSlots.Date > #" & Me.LowerDate.Value
& "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"
Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
Else
If Not Me.ShowTitle = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = " &
Me.ShowTitle.Value
Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
Else
If Not Me.LowerDate = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapeSlots.Date



#" & Me.LowerDate.Value & "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"

Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
End If
End If
End If
Else
Me.ShowTapes.Form.FilterOn = False
Me.ShowTitle.Enabled = True
Me.LowerDate.Enabled = True
Me.UpperDate.Enabled = True
Me.ApplyFilterButton.Caption = "Apply Filter"
End If

Exit_ApplyFilterButton_Click:
Exit Sub

Err_ApplyFilterButton_Click:
MsgBox Err.Description
Resume Exit_ApplyFilterButton_Click
End Sub

Keith
Click to expand...
Click to expand...
Click to expand...
 
K

krocheck

I think this is the route to take. It now works fine, but I did get a
'missing operator' error when I used >= in place of >.

Thank you for all your help.

Keith

P.S. If you know the reason for the missing operator error, please
explain.
I think that instead of using the filtering option I should just
manually change the subform's control source to an SQL statement and
change the control source and requery. I shouldn't have to, but it
seems to be the quickest way to fix this.

Will try it and let you know.

Keith
J. Goddard said:
The query itself being a crosstab is not likely the reason, but check
the SQL for your query -

I don't think showtapes.show is in it (showtapes.ShowTapeID is)
Did you try running the crosstab query as a stand-alone query (in query
design) to see whether you have all the fields you are expecting, and
that the column titles are what you expect them to be?

John



John,

Would the query in question being a crosstab be the reason?

The part that baffles me is that the error is not specific to when
using the date fields or the show field, but any combination. Could it
be that I cannot filter a crosstab in this way?

To help you understand the database a little,
delta.simnaweb.com/MUTVDBMSRelationships.gif contains the relationships
diagram.

The query that feeds the subform is as follows.

TRANSFORM Max(ShowTapeSlots.Episode) AS MaxOfEpisode
SELECT ShowTapes.ShowTapeID, Shows.Title
FROM Shows
RIGHT JOIN (ShowTapes
LEFT JOIN [SELECT ShowTapeSlots.*, Episodes.Date FROM
ShowTapeSlots
LEFT JOIN Episodes ON ShowTapeSlots.Episode =
Episodes.EpisodeID]. AS ShowTapeSlots ON ShowTapes.ShowTapeID =
ShowTapeSlots.ShowTape)
ON Shows.ShowID = ShowTapes.Show
GROUP BY ShowTapes.ShowTapeID, Shows.Title
PIVOT ShowTapeSlots.SlotNumber;

I tried your suggestion and it did not fix the problem.

Keith

J. Goddard wrote:

Keith -


The error indicates that you are referencing a field that does not
exist, or has not been included in a query.

Is the subform based on a query? Your code implies this, because it
refers to Showtapes.show and ShowTapeSlots.Date - two tables or queries.

One thing you might try is to change showtapes.date to showtapes.[date],
to make sure Access knows "date" is a field. Using "Date" as a field or
control name is never a good idea anyway, because Access can confuse it
with Date() - usually when you least expect it to.

I have also found that giving a form control the same name as the
table/query field it is bound to can sometimes cause trouble.

Just a suggestion - you might change your date operators in the filter
expression from ">" and "<" to ">=" and "<=" - it makes the date range
inclusive. I think that's a little easier for users, especially if they
are looking for a specific date.

John

P.S. - If (e-mail address removed) is your valid E-Mail address, change
your signature **FAST**! Newsgroups are a favourite places for spammers
to harvest E-mail addresses. I know from sad experience!

(e-mail address removed) wrote:


John,

Single quotes are not the issue because ShowTapes.Show is a Long
Integer and Me.ShowTitle.Value returns an Integer.

Any other ideas?

Keith
J. Goddard wrote:


Hi -


You need to put the titles in the filter string in quotes:


Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = '" &
Me.ShowTitle.Value & "' And ShowTapeSlots.Date > #" & Me.LowerDate.Value
& "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"

and


Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = '" &
Me.ShowTitle.Value & "'"

I've used single quotes here - they're easier to read.

Hope this helps

John



(e-mail address removed) wrote:



Hey,

I am working on a database for my campus television station. One of
the sections is related to Show Tapes and the Episodes that are
contained within them.

I am trying to perform a custom filter on a sub-form using VBA when the
error in the title pops up. There are three fields associated with
this custom filter: ShowTitle, LowerDate, and UpperDate.

The routine looks to make sure two dates are entered or none then picks
whether to filter just the ShowTitle, just dates or both (or cancel the
filter).

The error shows up on any of the: "Me.ShowTapesQuery.Form.Filter = ..."
lines.

Any ideas on how to solve this are greatly appreciated.

Private Sub ApplyFilterButton_Click()
On Error GoTo Err_ApplyFilterButton_Click

If Me.ApplyFilterButton.Caption = "Apply Filter" Then
If Not (Me.LowerDate = "" And Me.UpperDate = "") Then
If Not Me.LowerDate = "" And Not Me.UpperDate = "" Then
Else
MsgBox "You must enter both dates before applying the
filter!"
GoTo Exit_ApplyFilterButton_Click
End If
End If
If Me.ApplyFilterButton.Caption = "Apply Filter" Then
If Not (Me.LowerDate = "" And Me.UpperDate = "") Then
If Not Me.LowerDate = "" And Not Me.UpperDate = "" Then
Else
MsgBox "You must enter both dates before applying the
filter!"
GoTo Exit_ApplyFilterButton_Click
End If
End If

If Not Me.ShowTitle = "" And Not Me.UpperDate = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = " &
Me.ShowTitle.Value & " And ShowTapeSlots.Date > #" & Me.LowerDate.Value
& "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"
Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
Else
If Not Me.ShowTitle = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapes.Show = " &
Me.ShowTitle.Value
Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
Else
If Not Me.LowerDate = "" Then
Me.ShowTapesQuery.Form.Filter = "ShowTapeSlots.Date



#" & Me.LowerDate.Value & "# And ShowTapeSlots.Date < #" & Me.UpperDate.Value & "#"

Me.ShowTapesQuery.Form.FilterOn = True

Me.ShowTitle.Enabled = False
Me.LowerDate.Enabled = False
Me.UpperDate.Enabled = False
Me.ApplyFilterButton.Caption = "Cancel Filter"
End If
End If
End If
Else
Me.ShowTapes.Form.FilterOn = False
Me.ShowTitle.Enabled = True
Me.LowerDate.Enabled = True
Me.UpperDate.Enabled = True
Me.ApplyFilterButton.Caption = "Apply Filter"
End If

Exit_ApplyFilterButton_Click:
Exit Sub

Err_ApplyFilterButton_Click:
MsgBox Err.Description
Resume Exit_ApplyFilterButton_Click
End Sub

Keith
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If else subform reference access 2007 2
ERROR: "...Can't find the field 'i' referred to in your expression 1
Database Can't find the field '|' referred to in your expression 2
Can't find field 2
Access VBA error "Microsoft Access can't find the field '|' referred to in your experssion" 0
Access VBA error "Microsoft Access can't find the field '|' referred to in your experssion" 0
Date Field Problem in Userform 4
Open (sub)form with command button - link master/child fields 0

Top