Dates In A Calendar

[zat via AccessMonster.com]

I have a form that allows users to select a start and end date from two
different calendars that are displayed on the form. The dates are selected
from a received date field in a table. Once the calendar dates are selected
on the from and user selects the Type and Status he wants from the report, he
clicks a button and the report displays on his screen. I have the Type and
Status options working and the calendars are showing the proper dates, but
with the following code I cannot get a report to run.

Private Sub Command29_Click()


If "Me.[dtmInRecDate]" >= ActiveXCtl27.Value And "Me.[dtmInRecDate]" <
ActiveXCtl28.Value And Me.Frame73 = 1 And Me.Frame82 = 1 Then
DoCmd.OpenReport "rptInOpen", acViewPreview

When I write the following code, I get all the records instead of Just the
ones between the start and end dates.
Private Sub Command29_Click()


If "Me.[dtmInRecDate]" >= ActiveXCtl27.Value And ActiveXCtl28.Value And Me.
Frame73 = 1 And Me.Frame82 = 1 Then
DoCmd.OpenReport "rptInOpen", acViewPreview

I would appreciate any help someone could give me on this.

 

[Klatuu]

One problem I see is you are enclosing the name of your control in quotes.
If "Me.[dtmInRecDate]" >=
Should be
If Me.[dtmInRecDate] >=

Also, I don't see where you are filtering the records by date. Is it in the
query or in the report?

 

[zat via AccessMonster.com]

When I take the quotes off I get "Microsoft Access can't find the field "|"
referred in your expression".

I was trying to filter the dates with the code on this form. Should the
filtering be done in the query or report. If so, how do I go about
filtering between dates on one date field in a query or report?

One problem I see is you are enclosing the name of your control in quotes.
If "Me.[dtmInRecDate]" >=
Should be
If Me.[dtmInRecDate] >=

Also, I don't see where you are filtering the records by date. Is it in the
query or in the report?

I have a form that allows users to select a start and end date from two
different calendars that are displayed on the form. The dates are selected

[quoted text clipped - 19 lines]

I would appreciate any help someone could give me on this.

 

[Klatuu]

If Access can't find the the field, then you have another problem. Putting
it quotes, you are comparing a string value of "Me.[dtmInRecDate]" to a date,
not the vaule in the control.
Is dtmInRecDate a control on your form or the name of a field in your table
or query? If you have a control with that name, first it is a bad name for a
control, but in any case the syntax Me!dtmInRecDate might be better.

If you want to filter the report based on the date range, you will need to
either put criteria in the query that references the date controls on the
form or use the Where argument of the OpenReport method to do the filtering.

When I take the quotes off I get "Microsoft Access can't find the field "|"
referred in your expression".

I was trying to filter the dates with the code on this form. Should the
filtering be done in the query or report. If so, how do I go about
filtering between dates on one date field in a query or report?

One problem I see is you are enclosing the name of your control in quotes.
If "Me.[dtmInRecDate]" >=
Should be
If Me.[dtmInRecDate] >=

Also, I don't see where you are filtering the records by date. Is it in the
query or in the report?

I have a form that allows users to select a start and end date from two
different calendars that are displayed on the form. The dates are selected

[quoted text clipped - 19 lines]

I would appreciate any help someone could give me on this.

 

[zat via AccessMonster.com]

dtmInRecDate is a field name in a table. I'm trying to avoid date input
errors by providing the users a calendar on the form; therefore, I'm am
trying to do this without putting the filter in the query. I'm not familiar
with a Where argument of the OpenReport method. How would I go about coding
this? Thanks!

If Access can't find the the field, then you have another problem. Putting
it quotes, you are comparing a string value of "Me.[dtmInRecDate]" to a date,
not the vaule in the control.
Is dtmInRecDate a control on your form or the name of a field in your table
or query? If you have a control with that name, first it is a bad name for a
control, but in any case the syntax Me!dtmInRecDate might be better.

If you want to filter the report based on the date range, you will need to
either put criteria in the query that references the date controls on the
form or use the Where argument of the OpenReport method to do the filtering.

When I take the quotes off I get "Microsoft Access can't find the field "|"
referred in your expression".

[quoted text clipped - 16 lines]

 

[Klatuu]

If you are trying to use a field name in a table, it can't be Me. anything.
Me. or Me! refers to controls on the currrent form. That is what you want to
compare against, not a field in the table.
I am not sure whether you only want to run the report if the two option
groups = 1 or if you are trying to filter based on the option group values.
The code below assumes you only want the report to run if the option groups
are both = 1. It filters the report based on comparing the values in your
table field [dtmInRecDate] to the calendar controls on your form.

If Me.Frame73 = 1 And Me.Frame82 = 1 Then
strWhere = "[dtmInRecDate] >= #" & Me.ActiveXCtl27 & "# And
[dtmInRecDate] <= #" & ActiveXCtl28 & "#"
DoCmd.OpenReport "rptInOpen", acViewPreview, , strWhere
End If

dtmInRecDate is a field name in a table. I'm trying to avoid date input
errors by providing the users a calendar on the form; therefore, I'm am
trying to do this without putting the filter in the query. I'm not familiar
with a Where argument of the OpenReport method. How would I go about coding
this? Thanks!

If Access can't find the the field, then you have another problem. Putting
it quotes, you are comparing a string value of "Me.[dtmInRecDate]" to a date,
not the vaule in the control.
Is dtmInRecDate a control on your form or the name of a field in your table
or query? If you have a control with that name, first it is a bad name for a
control, but in any case the syntax Me!dtmInRecDate might be better.

If you want to filter the report based on the date range, you will need to
either put criteria in the query that references the date controls on the
form or use the Where argument of the OpenReport method to do the filtering.

When I take the quotes off I get "Microsoft Access can't find the field "|"
referred in your expression".

[quoted text clipped - 16 lines]

I would appreciate any help someone could give me on this.

 

[zat via AccessMonster.com]

I select 03/02/2006 in the first calendar and 03/07/2006 in the second one
and the report shows all dates including 05/07/2006. I have the field in the
table formatted as a short date. Not sure what else to look at. This is the
code I currently have.

Private Sub Command29_Click()

If Me.Frame73 = 1 And Me.Frame82 = 1 Then
strWhere = "[dtmInRecDate]>=#" & Me.ActiveXCtl27 & "# And [dtmInRecDate]
<=#" & ActiveXCtl28 & "#"
DoCmd.OpenReport "rptInOpen", acViewPreview, strWhere

End If

End Sub

If you are trying to use a field name in a table, it can't be Me. anything.
Me. or Me! refers to controls on the currrent form. That is what you want to
compare against, not a field in the table.
I am not sure whether you only want to run the report if the two option
groups = 1 or if you are trying to filter based on the option group values.
The code below assumes you only want the report to run if the option groups
are both = 1. It filters the report based on comparing the values in your
table field [dtmInRecDate] to the calendar controls on your form.

If Me.Frame73 = 1 And Me.Frame82 = 1 Then
strWhere = "[dtmInRecDate] >= #" & Me.ActiveXCtl27 & "# And
[dtmInRecDate] <= #" & ActiveXCtl28 & "#"
DoCmd.OpenReport "rptInOpen", acViewPreview, , strWhere
End If

dtmInRecDate is a field name in a table. I'm trying to avoid date input
errors by providing the users a calendar on the form; therefore, I'm am

[quoted text clipped - 18 lines]

 

[Klatuu]

You are missing a comma in the open report line:
DoCmd.OpenReport "rptInOpen", acViewPreview, strWhere
Should be
DoCmd.OpenReport "rptInOpen", acViewPreview, , strWhere

I select 03/02/2006 in the first calendar and 03/07/2006 in the second one
and the report shows all dates including 05/07/2006. I have the field in the
table formatted as a short date. Not sure what else to look at. This is the
code I currently have.

Private Sub Command29_Click()

If Me.Frame73 = 1 And Me.Frame82 = 1 Then
strWhere = "[dtmInRecDate]>=#" & Me.ActiveXCtl27 & "# And [dtmInRecDate]
<=#" & ActiveXCtl28 & "#"
DoCmd.OpenReport "rptInOpen", acViewPreview, strWhere

End If

End Sub

If you are trying to use a field name in a table, it can't be Me. anything.
Me. or Me! refers to controls on the currrent form. That is what you want to
compare against, not a field in the table.
I am not sure whether you only want to run the report if the two option
groups = 1 or if you are trying to filter based on the option group values.
The code below assumes you only want the report to run if the option groups
are both = 1. It filters the report based on comparing the values in your
table field [dtmInRecDate] to the calendar controls on your form.

If Me.Frame73 = 1 And Me.Frame82 = 1 Then
strWhere = "[dtmInRecDate] >= #" & Me.ActiveXCtl27 & "# And
[dtmInRecDate] <= #" & ActiveXCtl28 & "#"
DoCmd.OpenReport "rptInOpen", acViewPreview, , strWhere
End If

dtmInRecDate is a field name in a table. I'm trying to avoid date input
errors by providing the users a calendar on the form; therefore, I'm am

[quoted text clipped - 18 lines]

I would appreciate any help someone could give me on this.

 

[zat via AccessMonster.com]

Thank you! Thank you! You are good!!!

You are missing a comma in the open report line:
DoCmd.OpenReport "rptInOpen", acViewPreview, strWhere
Should be
DoCmd.OpenReport "rptInOpen", acViewPreview, , strWhere

I select 03/02/2006 in the first calendar and 03/07/2006 in the second one
and the report shows all dates including 05/07/2006. I have the field in the

[quoted text clipped - 32 lines]