Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My question is, I have setup a query to build a report. In the query I have a
date, which I would like to select a range. 'Between x and y dates' In the
query x = 'startdate' and y = 'enddate'. I have a report selector Form. The
report needs three inputs 1. is a name, 2. is startdate and 3. is enddate.
The fields in the Form are 1. name 2. StartDate 3.EndDate. I am trying to
build the following Filter and I do not know how to complete the filter with
the info;

strFilter = "[estimator] = '" & destselector & "'"
 
Hi,
if estimator - is your name field, then complete filter will look like:

strFilter = "[estimator] = '" & forms!Filter!destselector & "' AND
(DateField between #" & format(forms!Filter!startdate,"mm\/dd\/yyyy") & "#
AND #" & format(forms!Filter!enddate,"mm\/dd\/yyyy") & "#)"


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
 
Thank you for your help. I coded the line as you directed, there is a problem
in my unstanding your example.

strFilter = "[estimator] = '" & destselector & "' AND StartDate between # "
& Format(Forms!ReportSelect!estartdate, "mm\/dd\/yyyy") & "# AND #" &
Format(Forms!ReportSelect!eenddate, "mm\/dd\/yyyy") & "#"

The query looks like this; 'Between #7/01/2007# And #7/31/2007#'

The message that I get is:
'Syntax error (missing operator) in query expression '([estimator]='Chris'
AND between #07/01/2007# AND #07/31/2007#)'.

--
Norm Bohana


Alex Dybenko said:
Hi,
if estimator - is your name field, then complete filter will look like:

strFilter = "[estimator] = '" & forms!Filter!destselector & "' AND
(DateField between #" & format(forms!Filter!startdate,"mm\/dd\/yyyy") & "#
AND #" & format(forms!Filter!enddate,"mm\/dd\/yyyy") & "#)"


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


nbohana said:
My question is, I have setup a query to build a report. In the query I
have a
date, which I would like to select a range. 'Between x and y dates' In
the
query x = 'startdate' and y = 'enddate'. I have a report selector Form.
The
report needs three inputs 1. is a name, 2. is startdate and 3. is enddate.
The fields in the Form are 1. name 2. StartDate 3.EndDate. I am trying to
build the following Filter and I do not know how to complete the filter
with
the info;

strFilter = "[estimator] = '" & destselector & "'"
Click to expand...
Click to expand...
 
There would appear to be a difference between what you've shown for
strFilter and what Access is actually getting for strFilter, since the error
message doesn't show StartDate in it.

You might want to put parentheses around the Date clause:

strFilter = "[estimator] = '" & destselector & "' " & _
" AND (StartDate between # " & _
Format(Forms!ReportSelect!estartdate, "mm\/dd\/yyyy") & _
"# AND #" &
Format(Forms!ReportSelect!eenddate, "mm\/dd\/yyyy") & "#)"

Also, I find it simpler to include the # characters in the Format statement:

strFilter = "[estimator] = '" & destselector & "' " & _
" AND (StartDate between " & _
Format(Forms!ReportSelect!estartdate, "\#mm\/dd\/yyyy\#") & _
" AND " &
Format(Forms!ReportSelect!eenddate, "\#mm\/dd\/yyyy\#") & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


nbohana said:
Thank you for your help. I coded the line as you directed, there is a
problem
in my unstanding your example.

strFilter = "[estimator] = '" & destselector & "' AND StartDate between #
"
& Format(Forms!ReportSelect!estartdate, "mm\/dd\/yyyy") & "# AND #" &
Format(Forms!ReportSelect!eenddate, "mm\/dd\/yyyy") & "#"

The query looks like this; 'Between #7/01/2007# And #7/31/2007#'

The message that I get is:
'Syntax error (missing operator) in query expression '([estimator]='Chris'
AND between #07/01/2007# AND #07/31/2007#)'.

--
Norm Bohana


Alex Dybenko said:
Hi,
if estimator - is your name field, then complete filter will look like:

strFilter = "[estimator] = '" & forms!Filter!destselector & "' AND
(DateField between #" & format(forms!Filter!startdate,"mm\/dd\/yyyy") &
"#
AND #" & format(forms!Filter!enddate,"mm\/dd\/yyyy") & "#)"


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


nbohana said:
My question is, I have setup a query to build a report. In the query I
have a
date, which I would like to select a range. 'Between x and y dates' In
the
query x = 'startdate' and y = 'enddate'. I have a report selector Form.
The
report needs three inputs 1. is a name, 2. is startdate and 3. is
enddate.
The fields in the Form are 1. name 2. StartDate 3.EndDate. I am trying
to
build the following Filter and I do not know how to complete the filter
with
the info;

strFilter = "[estimator] = '" & destselector & "'"
Click to expand...
Click to expand...
Click to expand...
 
Thanks Douglas, I am still having a problem with this code:

strFilter = "[estimator] = '" & destselector & "' " & " AND (StartDate
between " & Format(Forms!ReportSelect!estartdate, "\#mm\/dd\/yyyy\#") & " AND
" & Format(Forms!ReportSelect!eenddate, "\#mm\/dd\/yyyy\#") & ")"

The message I get is 'Enter Parameter Value' and its for 'StartDate'. When
I trace thr progress the filter looks right. But I get the above message.
--
Norm Bohana


Douglas J. Steele said:
There would appear to be a difference between what you've shown for
strFilter and what Access is actually getting for strFilter, since the error
message doesn't show StartDate in it.

You might want to put parentheses around the Date clause:

strFilter = "[estimator] = '" & destselector & "' " & _
" AND (StartDate between # " & _
Format(Forms!ReportSelect!estartdate, "mm\/dd\/yyyy") & _
"# AND #" &
Format(Forms!ReportSelect!eenddate, "mm\/dd\/yyyy") & "#)"

Also, I find it simpler to include the # characters in the Format statement:

strFilter = "[estimator] = '" & destselector & "' " & _
" AND (StartDate between " & _
Format(Forms!ReportSelect!estartdate, "\#mm\/dd\/yyyy\#") & _
" AND " &
Format(Forms!ReportSelect!eenddate, "\#mm\/dd\/yyyy\#") & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


nbohana said:
Thank you for your help. I coded the line as you directed, there is a
problem
in my unstanding your example.

strFilter = "[estimator] = '" & destselector & "' AND StartDate between #
"
& Format(Forms!ReportSelect!estartdate, "mm\/dd\/yyyy") & "# AND #" &
Format(Forms!ReportSelect!eenddate, "mm\/dd\/yyyy") & "#"

The query looks like this; 'Between #7/01/2007# And #7/31/2007#'

The message that I get is:
'Syntax error (missing operator) in query expression '([estimator]='Chris'
AND between #07/01/2007# AND #07/31/2007#)'.

--
Norm Bohana


Alex Dybenko said:
Hi,
if estimator - is your name field, then complete filter will look like:

strFilter = "[estimator] = '" & forms!Filter!destselector & "' AND
(DateField between #" & format(forms!Filter!startdate,"mm\/dd\/yyyy") &
"#
AND #" & format(forms!Filter!enddate,"mm\/dd\/yyyy") & "#)"


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


My question is, I have setup a query to build a report. In the query I
have a
date, which I would like to select a range. 'Between x and y dates' In
the
query x = 'startdate' and y = 'enddate'. I have a report selector Form.
The
report needs three inputs 1. is a name, 2. is startdate and 3. is
enddate.
The fields in the Form are 1. name 2. StartDate 3.EndDate. I am trying
to
build the following Filter and I do not know how to complete the filter
with
the info;

strFilter = "[estimator] = '" & destselector & "'"
Click to expand...
Click to expand...
Click to expand...
 
Do you have a field named StartDate in your table? If not, what's the name
of the field on which you're trying to filter?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


nbohana said:
Thanks Douglas, I am still having a problem with this code:

strFilter = "[estimator] = '" & destselector & "' " & " AND (StartDate
between " & Format(Forms!ReportSelect!estartdate, "\#mm\/dd\/yyyy\#") & "
AND
" & Format(Forms!ReportSelect!eenddate, "\#mm\/dd\/yyyy\#") & ")"

The message I get is 'Enter Parameter Value' and its for 'StartDate'.
When
I trace thr progress the filter looks right. But I get the above message.
--
Norm Bohana


Douglas J. Steele said:
There would appear to be a difference between what you've shown for
strFilter and what Access is actually getting for strFilter, since the
error
message doesn't show StartDate in it.

You might want to put parentheses around the Date clause:

strFilter = "[estimator] = '" & destselector & "' " & _
" AND (StartDate between # " & _
Format(Forms!ReportSelect!estartdate, "mm\/dd\/yyyy") & _
"# AND #" &
Format(Forms!ReportSelect!eenddate, "mm\/dd\/yyyy") & "#)"

Also, I find it simpler to include the # characters in the Format
statement:

strFilter = "[estimator] = '" & destselector & "' " & _
" AND (StartDate between " & _
Format(Forms!ReportSelect!estartdate, "\#mm\/dd\/yyyy\#") & _
" AND " &
Format(Forms!ReportSelect!eenddate, "\#mm\/dd\/yyyy\#") & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


nbohana said:
Thank you for your help. I coded the line as you directed, there is a
problem
in my unstanding your example.

strFilter = "[estimator] = '" & destselector & "' AND StartDate between
#
"
& Format(Forms!ReportSelect!estartdate, "mm\/dd\/yyyy") & "# AND #" &
Format(Forms!ReportSelect!eenddate, "mm\/dd\/yyyy") & "#"

The query looks like this; 'Between #7/01/2007# And #7/31/2007#'

The message that I get is:
'Syntax error (missing operator) in query expression
'([estimator]='Chris'
AND between #07/01/2007# AND #07/31/2007#)'.

--
Norm Bohana


:

Hi,
if estimator - is your name field, then complete filter will look
like:

strFilter = "[estimator] = '" & forms!Filter!destselector & "' AND
(DateField between #" & format(forms!Filter!startdate,"mm\/dd\/yyyy")
&
"#
AND #" & format(forms!Filter!enddate,"mm\/dd\/yyyy") & "#)"


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


My question is, I have setup a query to build a report. In the query
I
have a
date, which I would like to select a range. 'Between x and y dates'
In
the
query x = 'startdate' and y = 'enddate'. I have a report selector
Form.
The
report needs three inputs 1. is a name, 2. is startdate and 3. is
enddate.
The fields in the Form are 1. name 2. StartDate 3.EndDate. I am
trying
to
build the following Filter and I do not know how to complete the
filter
with
the info;

strFilter = "[estimator] = '" & destselector & "'"
Click to expand...
Click to expand...
Click to expand...
 
Thanks Douglas, The name of field is 'datesold'. I tried useing that name
and it ask for a parameter.
--
Norm Bohana


Douglas J. Steele said:
Do you have a field named StartDate in your table? If not, what's the name
of the field on which you're trying to filter?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


nbohana said:
Thanks Douglas, I am still having a problem with this code:

strFilter = "[estimator] = '" & destselector & "' " & " AND (StartDate
between " & Format(Forms!ReportSelect!estartdate, "\#mm\/dd\/yyyy\#") & "
AND
" & Format(Forms!ReportSelect!eenddate, "\#mm\/dd\/yyyy\#") & ")"

The message I get is 'Enter Parameter Value' and its for 'StartDate'.
When
I trace thr progress the filter looks right. But I get the above message.
--
Norm Bohana


Douglas J. Steele said:
There would appear to be a difference between what you've shown for
strFilter and what Access is actually getting for strFilter, since the
error
message doesn't show StartDate in it.

You might want to put parentheses around the Date clause:

strFilter = "[estimator] = '" & destselector & "' " & _
" AND (StartDate between # " & _
Format(Forms!ReportSelect!estartdate, "mm\/dd\/yyyy") & _
"# AND #" &
Format(Forms!ReportSelect!eenddate, "mm\/dd\/yyyy") & "#)"

Also, I find it simpler to include the # characters in the Format
statement:

strFilter = "[estimator] = '" & destselector & "' " & _
" AND (StartDate between " & _
Format(Forms!ReportSelect!estartdate, "\#mm\/dd\/yyyy\#") & _
" AND " &
Format(Forms!ReportSelect!eenddate, "\#mm\/dd\/yyyy\#") & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thank you for your help. I coded the line as you directed, there is a
problem
in my unstanding your example.

strFilter = "[estimator] = '" & destselector & "' AND StartDate between
#
"
& Format(Forms!ReportSelect!estartdate, "mm\/dd\/yyyy") & "# AND #" &
Format(Forms!ReportSelect!eenddate, "mm\/dd\/yyyy") & "#"

The query looks like this; 'Between #7/01/2007# And #7/31/2007#'

The message that I get is:
'Syntax error (missing operator) in query expression
'([estimator]='Chris'
AND between #07/01/2007# AND #07/31/2007#)'.

--
Norm Bohana


:

Hi,
if estimator - is your name field, then complete filter will look
like:

strFilter = "[estimator] = '" & forms!Filter!destselector & "' AND
(DateField between #" & format(forms!Filter!startdate,"mm\/dd\/yyyy")
&
"#
AND #" & format(forms!Filter!enddate,"mm\/dd\/yyyy") & "#)"


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


My question is, I have setup a query to build a report. In the query
I
have a
date, which I would like to select a range. 'Between x and y dates'
In
the
query x = 'startdate' and y = 'enddate'. I have a report selector
Form.
The
report needs three inputs 1. is a name, 2. is startdate and 3. is
enddate.
The fields in the Form are 1. name 2. StartDate 3.EndDate. I am
trying
to
build the following Filter and I do not know how to complete the
filter
with
the info;

strFilter = "[estimator] = '" & destselector & "'"
Click to expand...
Click to expand...
Click to expand...
 
What was the name of the parameter for which it asked? If it was datesold,
are you sure the field name is datesold, and not, for instance, date sold
(with a space)? If there's a space (which isn't recommended), you need to
put square brackets around the name: [date sold]

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


nbohana said:
Thanks Douglas, The name of field is 'datesold'. I tried useing that name
and it ask for a parameter.
--
Norm Bohana


Douglas J. Steele said:
Do you have a field named StartDate in your table? If not, what's the
name
of the field on which you're trying to filter?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


nbohana said:
Thanks Douglas, I am still having a problem with this code:

strFilter = "[estimator] = '" & destselector & "' " & " AND (StartDate
between " & Format(Forms!ReportSelect!estartdate, "\#mm\/dd\/yyyy\#") &
"
AND
" & Format(Forms!ReportSelect!eenddate, "\#mm\/dd\/yyyy\#") & ")"

The message I get is 'Enter Parameter Value' and its for 'StartDate'.
When
I trace thr progress the filter looks right. But I get the above
message.
--
Norm Bohana


:

There would appear to be a difference between what you've shown for
strFilter and what Access is actually getting for strFilter, since the
error
message doesn't show StartDate in it.

You might want to put parentheses around the Date clause:

strFilter = "[estimator] = '" & destselector & "' " & _
" AND (StartDate between # " & _
Format(Forms!ReportSelect!estartdate, "mm\/dd\/yyyy") & _
"# AND #" &
Format(Forms!ReportSelect!eenddate, "mm\/dd\/yyyy") & "#)"

Also, I find it simpler to include the # characters in the Format
statement:

strFilter = "[estimator] = '" & destselector & "' " & _
" AND (StartDate between " & _
Format(Forms!ReportSelect!estartdate, "\#mm\/dd\/yyyy\#") & _
" AND " &
Format(Forms!ReportSelect!eenddate, "\#mm\/dd\/yyyy\#") & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thank you for your help. I coded the line as you directed, there is
a
problem
in my unstanding your example.

strFilter = "[estimator] = '" & destselector & "' AND StartDate
between
#
"
& Format(Forms!ReportSelect!estartdate, "mm\/dd\/yyyy") & "# AND #"
&
Format(Forms!ReportSelect!eenddate, "mm\/dd\/yyyy") & "#"

The query looks like this; 'Between #7/01/2007# And
#7/31/2007#'

The message that I get is:
'Syntax error (missing operator) in query expression
'([estimator]='Chris'
AND between #07/01/2007# AND #07/31/2007#)'.

--
Norm Bohana


:

Hi,
if estimator - is your name field, then complete filter will look
like:

strFilter = "[estimator] = '" & forms!Filter!destselector & "' AND
(DateField between #" &
format(forms!Filter!startdate,"mm\/dd\/yyyy")
&
"#
AND #" & format(forms!Filter!enddate,"mm\/dd\/yyyy") & "#)"


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


My question is, I have setup a query to build a report. In the
query
I
have a
date, which I would like to select a range. 'Between x and y
dates'
In
the
query x = 'startdate' and y = 'enddate'. I have a report selector
Form.
The
report needs three inputs 1. is a name, 2. is startdate and 3. is
enddate.
The fields in the Form are 1. name 2. StartDate 3.EndDate. I am
trying
to
build the following Filter and I do not know how to complete the
filter
with
the info;

strFilter = "[estimator] = '" & destselector & "'"
Click to expand...
Click to expand...
Click to expand...
 
Back
Top