Please help ~ Syntax Error

[Gina Whipp]

Hi All,

Thanks in advance...

Here's my line:


If Me("Filter1") <> "" Then
strSql = strSql & "Between " & Me("Filter1") & " And " &
Me("Filter2") & Chr(34) & " And "
End If

Here's my error:

Syntax error (missing operator) in query expression '(Between 9/8/2004 And
9/9/2005")'.

What am I missing?

Thanks,
Gina

 

[Klatuu]

Dates need to be enclosed in #, Without being able to test this, I can't
guarantee it, but try this:

If Me("Filter1") <> "" Then
strSql = strSql & "Between #" & Me("Filter1") & "# And #" & _
Me("Filter2") & "# And "
End If

 

[Gina Whipp]

Nope that didn't work either, didn't think it would because date field is
actually formated as text. Now error reads:

Syntax error (missing operator) in query expression '(Between #9/8/2004# And
#9/9/2005#)'.

 

[Gina Whipp]

Now not getting error but report is not filtering either. I changed the
code to

If Me("Filter1") <> "" Then
strSql = strSql & """Between #" & Me("Filter1") & "# And #" &
Me("Filter2") & "#"""
End If

It seems like it wants to be enclosed in something (to get rid of the Syntax
error issue) but what???

 

[Klatuu]

Take out the #. It is only for date/time fields. For text fields you need
the "

 

[Gina Whipp]

DId that, same error...
Even changed them all to Date/Time fields and put back the #'s, same error.
The only time the error stops, see below, but then the report doesn't
filter.

Perhaps my problem is my approach? I have an unbound form that when opened
the report opens showing all of it's records. The you make a selection on
the form press the Filter button and it's suppose to requery the report and
show the the new results. This silly form works on ALL my other reports
that don't use Between. It was when I wanted to say Between these 2 dates
that I can't seem to get it right.

So perhaps I should try another approach, maybe my unbound form wasn't meant
to handle Between?

 

[rayc via AccessMonster.com]

Did you try the CDate() function?

DId that, same error...
Even changed them all to Date/Time fields and put back the #'s, same error.
The only time the error stops, see below, but then the report doesn't
filter.

Perhaps my problem is my approach? I have an unbound form that when opened
the report opens showing all of it's records. The you make a selection on
the form press the Filter button and it's suppose to requery the report and
show the the new results. This silly form works on ALL my other reports
that don't use Between. It was when I wanted to say Between these 2 dates
that I can't seem to get it right.

So perhaps I should try another approach, maybe my unbound form wasn't meant
to handle Between?

Take out the #. It is only for date/time fields. For text fields you
need

[quoted text clipped - 52 lines]

 

[Klatuu]

hhhhhhhhhhhhmmmmmmmmmmmmmm?????????

Since you used the variable name strsql, my assumption was you were building
an sql string to use as a query. Between isn an SQL word, but not an Access
or VBA word. Sometimes it is hard to remember what language we are working
in. If this is not for a query, then you will need to change it to use
something like

 

[Gina Whipp]

Still getting Syntax error (

hhhhhhhhhhhhmmmmmmmmmmmmmm?????????

Since you used the variable name strsql, my assumption was you were
building
an sql string to use as a query. Between isn an SQL word, but not an
Access
or VBA word. Sometimes it is hard to remember what language we are
working
in. If this is not for a query, then you will need to change it to use
something like

 

[Gina Whipp]

Not sure where or how you think I can use that. I must be missing
something.

Did you try the CDate() function?

DId that, same error...
Even changed them all to Date/Time fields and put back the #'s, same
error.
The only time the error stops, see below, but then the report doesn't
filter.

Perhaps my problem is my approach? I have an unbound form that when
opened
the report opens showing all of it's records. The you make a selection on
the form press the Filter button and it's suppose to requery the report
and
show the the new results. This silly form works on ALL my other reports
that don't use Between. It was when I wanted to say Between these 2 dates
that I can't seem to get it right.

So perhaps I should try another approach, maybe my unbound form wasn't
meant
to handle Between?

Take out the #. It is only for date/time fields. For text fields you
need

[quoted text clipped - 52 lines]

Thanks,
Gina

 

[Randy Harris]

Perhaps if you were to supply sufficient information someone would be able
to help. Information such as what strSql is before that assignment
statement, what it is after the assignment and what instruction is actually
getting the error.

 

[Wayne Morgan]

Before you try to use the SQL you've put together in strSQL please issue the
following command:

Debug.Print strSQL

This will print out the SQL that is going to be used to the Immediate
window. Read what has been printed there. Does it look correct? Are there
any spaces missing between words?

If this doesn't help, please post the full code used to built strSQL and the
code line that has the error. Also, please post the Debug printout from the
Immediate window. Please copy and paste to do this to avoid typing errors in
the message.

 

[Gina Whipp]

Wayne,

I am beginning to think I am going about this all wrong and trying to MAKE a
forest despite the trees. Below is my code... What I am really trying to
accomplish is this:

Open an unbound form which opens rptCheckingLog showing all the records.
Select a StartDate and EndDate, press Filter and have it filter the report.
Without closing the unbound form select different StartDate and EndDate and
have it filter again and again till you get all the reports you want to
print.
Close the unbound form, thereby closing the report.

I started by taking another filter that I use and trying to modify it, as it
works on all my other reports. Perhaps I should ahve just started from
scratch; seems like I would have been done by now

Oh, the Debug.Print strSQL shows nada but when I run it I get the Syntax
error message.

Thanks Gina.

Dim strSql As String
'Build SQL String
If Me.Filter1 <> "" Then
strSql = strSql & "Between #" & Me.Filter1 & "# And #" & Me.Filter2
& "#"
End If

If strSql <> "" Then
'Set the Filter property
Reports![rptCheckingLog].Filter = strSql
Reports![rptCheckingLog].FilterOn = True
Else
Reports![rptCheckingLog].FilterOn = False
End If

 

[Bob Hairgrove]

What I am really trying to
accomplish is this:

Open an unbound form which opens rptCheckingLog showing all the records.
Select a StartDate and EndDate, press Filter and have it filter the report.
Without closing the unbound form select different StartDate and EndDate and
have it filter again and again till you get all the reports you want to
print.
Close the unbound form, thereby closing the report.

This is much too much work for what should be very simple. The report
should use a select query as its recordsource. In the query, specify
two parameters of type DateTime and name them [Start Date:] and [End
Date:]. Add a "where" clause to the query which looks like this:

WHERE [<your date field here...>] Between [Start Date:] And [End
Date:];

All you need to do then is to open the report, and a little dialog
will pop up asking for the parameter values.

 

[Gina Whipp]

Bob,

Like I said I believe I am trying to MAKE the forest for the trees!

I like your way, my only issue is I want the end-user to be able to requery
the report by reselecting parameters (StartDate : EndDate) but I suppose I
can add a requery to a button or something.

Thanks,
Gina

What I am really trying to
accomplish is this:

Open an unbound form which opens rptCheckingLog showing all the records.
Select a StartDate and EndDate, press Filter and have it filter the
report.
Without closing the unbound form select different StartDate and EndDate
and
have it filter again and again till you get all the reports you want to
print.
Close the unbound form, thereby closing the report.

This is much too much work for what should be very simple. The report
should use a select query as its recordsource. In the query, specify
two parameters of type DateTime and name them [Start Date:] and [End
Date:]. Add a "where" clause to the query which looks like this:

WHERE [<your date field here...>] Between [Start Date:] And [End
Date:];

All you need to do then is to open the report, and a little dialog
will pop up asking for the parameter values.

 

[Wayne Morgan]

Gina,

You can have the parameters mentioned by Bob point to the controls on your
form instead of popping up their own input box. This will let you use a
calendar control or some other date picker to make it easier on the user. It
will also allow you to use an input mask on the textbox to control how the
user inputs the date.

As far a "requerying" the report goes, I don't believe that you can (the
Report object has no Requery method). What you can do is open another copy
of the report with the new date parameters.

Another option is to pass the filter to the report when you open it.

Example:
DoCmd.OpenReport "MyReport", acViewPreview,,"[DateField] Between #" &
Me.Filter1 & "# And #" & Me.Filter2 & "#"

You could assign the filter to a variable first if you want and use the
variable instead.

Example:
strSQL = "[DateField] Between #" & Me.Filter1 & "# And #" & Me.Filter2 & "#"
DoCmd.OpenReport "MyReport", acViewPreview,, strSQL

 

[Gina Whipp]

Wayne,

Thank you for your reply but now I am more curious. Below is code on a form
unbound to any report. I use this form on multiple reports. The end-user
selects the choice from Filter 1 thru 5 and upon selecting press the
cmdSetFilter button and the report 'refilters', it does not open an
additional report or potentially 10 reports would be open. My problem
started when I tried to reuse this could to do Between StartDate and
EndDate.

In any event... What is happening here that allows my report to refilter
because if I understand you correctly it can't do that but it does.

Thanks,
Gina

Dim strSql As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
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) - 4))
'Set the Filter property
Reports![rptOpenLifts].Filter = strSql
Reports![rptOpenLifts].FilterOn = True
Else
Reports![rptOpenLifts].FilterOn = False
End If

Gina,

You can have the parameters mentioned by Bob point to the controls on your
form instead of popping up their own input box. This will let you use a
calendar control or some other date picker to make it easier on the user.
It will also allow you to use an input mask on the textbox to control how
the user inputs the date.

As far a "requerying" the report goes, I don't believe that you can (the
Report object has no Requery method). What you can do is open another copy
of the report with the new date parameters.

Another option is to pass the filter to the report when you open it.

Example:
DoCmd.OpenReport "MyReport", acViewPreview,,"[DateField] Between #" &
Me.Filter1 & "# And #" & Me.Filter2 & "#"

You could assign the filter to a variable first if you want and use the
variable instead.

Example:
strSQL = "[DateField] Between #" & Me.Filter1 & "# And #" & Me.Filter2 &
"#"
DoCmd.OpenReport "MyReport", acViewPreview,, strSQL

--
Wayne Morgan
MS Access MVP

Bob,

Like I said I believe I am trying to MAKE the forest for the trees!

I like your way, my only issue is I want the end-user to be able to
requery the report by reselecting parameters (StartDate : EndDate) but I
suppose I can add a requery to a button or something.

 

[Wayne Morgan]

Ok, you're right. You can apply the filter as you mention. The report
doesn't have to "requery" because it has all of the records, it just filters
which records it is showing.

So, that still brings me back to the Debug.Print statement in my first
message to make sure that what you're sending as a filter is what you think
you're sending.

--
Wayne Morgan
MS Access MVP

Wayne,

Thank you for your reply but now I am more curious. Below is code on a
form unbound to any report. I use this form on multiple reports. The
end-user selects the choice from Filter 1 thru 5 and upon selecting press
the cmdSetFilter button and the report 'refilters', it does not open an
additional report or potentially 10 reports would be open. My problem
started when I tried to reuse this could to do Between StartDate and
EndDate.

In any event... What is happening here that allows my report to refilter
because if I understand you correctly it can't do that but it does.

Thanks,
Gina

Dim strSql As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
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) - 4))
'Set the Filter property
Reports![rptOpenLifts].Filter = strSql
Reports![rptOpenLifts].FilterOn = True
Else
Reports![rptOpenLifts].FilterOn = False
End If

Gina,

You can have the parameters mentioned by Bob point to the controls on
your form instead of popping up their own input box. This will let you
use a calendar control or some other date picker to make it easier on the
user. It will also allow you to use an input mask on the textbox to
control how the user inputs the date.

As far a "requerying" the report goes, I don't believe that you can (the
Report object has no Requery method). What you can do is open another
copy of the report with the new date parameters.

Another option is to pass the filter to the report when you open it.

Example:
DoCmd.OpenReport "MyReport", acViewPreview,,"[DateField] Between #" &
Me.Filter1 & "# And #" & Me.Filter2 & "#"

You could assign the filter to a variable first if you want and use the
variable instead.

Example:
strSQL = "[DateField] Between #" & Me.Filter1 & "# And #" & Me.Filter2 &
"#"
DoCmd.OpenReport "MyReport", acViewPreview,, strSQL

--
Wayne Morgan
MS Access MVP

Bob,

Like I said I believe I am trying to MAKE the forest for the trees!

I like your way, my only issue is I want the end-user to be able to
requery the report by reselecting parameters (StartDate : EndDate) but I
suppose I can add a requery to a button or something.

 

[Gina Whipp]

I thought I answered that when I tried the Debug.Print method it showed me
nothing... well now you know. (Sometimes I think so fast I forget to
type.) Anyway, I GOT IT!!! Code posted below.

I want to thank everyone who helped me to MAKE the forest despite the trees.
It was a REAL learning experience for me. I had reused the same form/code
so many times I actually forgot how it worked and couldn't see the forest
anymore. I wans't READING the error message I was just getting upset the
stupid code wouldn't do what I wanted not what I typed. And Wayne had
actually explained it to me the first time... I was missing:
[tblAccountsPayable.apDate].

Thanks again to everyone who answered!!!


Dim strSQL As String

strSQL = "[tblAccountsPayable.apDate] Between #" & Me![Filter1] & "# And
#" & Me![Filter2] & "#"

If strSQL <> "" Then
'Set the Filter property
Reports![rptCheckingLog].Filter = strSQL
Reports![rptCheckingLog].FilterOn = True
Else
Reports![rptCheckingLog].FilterOn = False
End If

Ok, you're right. You can apply the filter as you mention. The report
doesn't have to "requery" because it has all of the records, it just
filters which records it is showing.

So, that still brings me back to the Debug.Print statement in my first
message to make sure that what you're sending as a filter is what you
think you're sending.

--
Wayne Morgan
MS Access MVP

Wayne,

Thank you for your reply but now I am more curious. Below is code on a
form unbound to any report. I use this form on multiple reports. The
end-user selects the choice from Filter 1 thru 5 and upon selecting press
the cmdSetFilter button and the report 'refilters', it does not open an
additional report or potentially 10 reports would be open. My problem
started when I tried to reuse this could to do Between StartDate and
EndDate.

In any event... What is happening here that allows my report to refilter
because if I understand you correctly it can't do that but it does.

Thanks,
Gina

Dim strSql As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
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) - 4))
'Set the Filter property
Reports![rptOpenLifts].Filter = strSql
Reports![rptOpenLifts].FilterOn = True
Else
Reports![rptOpenLifts].FilterOn = False
End If

Gina,

You can have the parameters mentioned by Bob point to the controls on
your form instead of popping up their own input box. This will let you
use a calendar control or some other date picker to make it easier on
the user. It will also allow you to use an input mask on the textbox to
control how the user inputs the date.

As far a "requerying" the report goes, I don't believe that you can (the
Report object has no Requery method). What you can do is open another
copy of the report with the new date parameters.

Another option is to pass the filter to the report when you open it.

Example:
DoCmd.OpenReport "MyReport", acViewPreview,,"[DateField] Between #" &
Me.Filter1 & "# And #" & Me.Filter2 & "#"

You could assign the filter to a variable first if you want and use the
variable instead.

Example:
strSQL = "[DateField] Between #" & Me.Filter1 & "# And #" & Me.Filter2 &
"#"
DoCmd.OpenReport "MyReport", acViewPreview,, strSQL

--
Wayne Morgan
MS Access MVP


Bob,

Like I said I believe I am trying to MAKE the forest for the trees!

I like your way, my only issue is I want the end-user to be able to
requery the report by reselecting parameters (StartDate : EndDate) but
I suppose I can add a requery to a button or something.