Opening Reports

[romeo.iula]

Hi,

I'm writing an application in Access 2003 and am having issues. I've
created a form for a user to enter two date ranges. These ranges are
used in conjunction with another variable to create a filter. This
filter is created and applied in the following manner:

startDate = Me.txtStartDate
endDate = Me.txtEndDate

filter = "[staffid] = " & employeeOfInterest & " AND [date] >= '" &
startDate & "' AND [date] <= '" & endDate & "'"

DoCmd.OpenReport "rptOverTime", acViewPreview, , filter, , startDate &
" to " & endDate

Now my issue is this: it works perfectly fine on my computer yet on
any other computer, the report does not open. I get no errors or
anything. I've checked that all the computers are running the same
versions of Access and VBA, and that they all are using the same
references. I've spent the last few hours trying to figure this out
and i think i really just need a fresh pair of eyes to look at it. any
help will be greatly appreciated. Thanks !!

 

[pietlinden]

Hi,

I'm writing an application in Access 2003 and am having issues. I've
created a form for a user to enter two date ranges. These ranges are
used in conjunction with another variable to create a filter. This
filter is created and applied in the following manner:

startDate = Me.txtStartDate
endDate = Me.txtEndDate

filter = "[staffid] = " & employeeOfInterest & " AND [date] >= '" &
startDate & "' AND [date] <= '" & endDate & "'"

DoCmd.OpenReport "rptOverTime", acViewPreview, , filter, , startDate &
" to " & endDate

Now my issue is this: it works perfectly fine on my computer yet on
any other computer, the report does not open. I get no errors or
anything. I've checked that all the computers are running the same
versions of Access and VBA, and that they all are using the same
references. I've spent the last few hours trying to figure this out
and i think i really just need a fresh pair of eyes to look at it. any
help will be greatly appreciated. Thanks !!

Did you try compiling your code on the machines that it doesn't work
on? You could be missing a library referende or something.

 

[romeo.iula]

Hi,

I'm writing an application in Access 2003 and am having issues. I've
created a form for a user to enter two date ranges. These ranges are
used in conjunction with another variable to create a filter. This
filter is created and applied in the following manner:

startDate = Me.txtStartDate
endDate = Me.txtEndDate

filter = "[staffid] = " & employeeOfInterest & " AND [date] >= '" &
startDate & "' AND [date] <= '" & endDate & "'"

DoCmd.OpenReport "rptOverTime", acViewPreview, , filter, , startDate &
" to " & endDate

Now my issue is this: it works perfectly fine on my computer yet on
any other computer, the report does not open. I get no errors or
anything. I've checked that all the computers are running the same
versions of Access and VBA, and that they all are using the same
references. I've spent the last few hours trying to figure this out
and i think i really just need a fresh pair of eyes to look at it. any
help will be greatly appreciated. Thanks !!

Did you try compiling your code on the machines that it doesn't work
on? You could be missing a library referende or something.

I actually couldn't remember if i had or not so i gave it a try.
Unfortunately it didn't change anything. Compiling did not give me any
errors and all other aspects of the application work fine. I have
noticed that if i remove the filter from the DoCmd the report will
show up, but i can't see any errors with the filter itself so i don't
understand why this is the case.

 

[krissco]

filter = "[staffid] = " & employeeOfInterest & " AND [date] >= '" &

startDate & "' AND [date] <= '" & endDate & "'"

This looks strange. Are your dates really strings? Try this instead:

filter = "[staffid] = " & employeeOfInterest & " AND [date] >= #" &
startDate & "# AND [date] <= #" & endDate & "#"


-Kris

 

[krissco]

Hi,

I'm writing an application in Access 2003 and am having issues. I've
created a form for a user to enter two date ranges. These ranges are
used in conjunction with another variable to create a filter. This
filter is created and applied in the following manner:

startDate = Me.txtStartDate
endDate = Me.txtEndDate

filter = "[staffid] = " & employeeOfInterest & " AND [date] >= '" &
startDate & "' AND [date] <= '" & endDate & "'"

DoCmd.OpenReport "rptOverTime", acViewPreview, , filter, , startDate &
" to " & endDate

Now my issue is this: it works perfectly fine on my computer yet on
any other computer, the report does not open. I get no errors or
anything. I've checked that all the computers are running the same
versions of Access and VBA, and that they all are using the same
references. I've spent the last few hours trying to figure this out
and i think i really just need a fresh pair of eyes to look at it. any
help will be greatly appreciated. Thanks !!

Oh. I just noticed, your DoCmd.OpenReport has many more arguments than
mine does. What version are you on? Pass your "filter" variable as the
"whereClause" argument.

-Kris

 

[fredg]

Hi,

I'm writing an application in Access 2003 and am having issues. I've
created a form for a user to enter two date ranges. These ranges are
used in conjunction with another variable to create a filter. This
filter is created and applied in the following manner:

startDate = Me.txtStartDate
endDate = Me.txtEndDate

filter = "[staffid] = " & employeeOfInterest & " AND [date] >= '" &
startDate & "' AND [date] <= '" & endDate & "'"

DoCmd.OpenReport "rptOverTime", acViewPreview, , filter, , startDate &
" to " & endDate

Now my issue is this: it works perfectly fine on my computer yet on
any other computer, the report does not open. I get no errors or
anything. I've checked that all the computers are running the same
versions of Access and VBA, and that they all are using the same
references. I've spent the last few hours trying to figure this out
and i think i really just need a fresh pair of eyes to look at it. any
help will be greatly appreciated. Thanks !!

1) Date is a reserved Access/VBA/Jet word and should not be used as
a field name. You really should change it to something else, perhaps
"TheDate", or whatever.

For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

2) You appear to be passing the date criteria as a string rather than
a date datatype.

Try it this way.

No need for the StartDate nor EndDate variables.

filter = "[staffid] = " & employeeOfInterest & " AND [dateField >= #"
&
txtStartDate & "# AND [dateField] <= #" & txtEndDate &"#"

DoCmd.OpenReport "rptOverTime", acViewPreview, , filter, ,
format(txtStartDate],"mmmm/d/yyyy") & " to " &
Format(txtEndDate,"mmmm/d/yyyy")

 

[romeo.iula]

Hi,

I'm writing an application in Access 2003 and am having issues. I've
created a form for a user to enter two date ranges. These ranges are
used in conjunction with another variable to create a filter. This
filter is created and applied in the following manner:

startDate = Me.txtStartDate
endDate = Me.txtEndDate

filter = "[staffid] = " & employeeOfInterest & " AND [date] >= '" &
startDate & "' AND [date] <= '" & endDate & "'"

DoCmd.OpenReport "rptOverTime", acViewPreview, , filter, , startDate &
" to " & endDate

Now my issue is this: it works perfectly fine on my computer yet on
any other computer, the report does not open. I get no errors or
anything. I've checked that all the computers are running the same
versions of Access and VBA, and that they all are using the same
references. I've spent the last few hours trying to figure this out
and i think i really just need a fresh pair of eyes to look at it. any
help will be greatly appreciated. Thanks !!

1) Date is a reserved Access/VBA/Jet word and should not be used as
a field name. You really should change it to something else, perhaps
"TheDate", or whatever.

For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:http://www.allenbrowne.com/AppIssueBadWord.html

2) You appear to be passing the date criteria as a string rather than
a date datatype.

Try it this way.

No need for the StartDate nor EndDate variables.

filter = "[staffid] = " & employeeOfInterest & " AND [dateField >= #"
&
txtStartDate & "# AND [dateField] <= #" & txtEndDate &"#"

DoCmd.OpenReport "rptOverTime", acViewPreview, , filter, ,
format(txtStartDate],"mmmm/d/yyyy") & " to " &
Format(txtEndDate,"mmmm/d/yyyy")

Hey Guys,

I understand that "date" is a reserved word but unfortunately this is
a project that i've taken over and, as it stands, it works completely
fine on my system. If push comes to shove, i'll definitely go through
and make the appropriate changes, but i'm just trying to avoid such a
move. In terms of my date criteria that i am passing, i was using a
string as the argument is used for nothing but display purposes by the
report. I therefore figured it would be fine.

Kris, I haven't tried the whereClause argument, but i'll give it a
shot and see if that clears it up. So far i've tried all other
suggestions and the result is the exact same. Reports open perfectly
on my machine, filtered and all, yet fail to open on others. I get no
errors or anything else. I appreciate all the help guys !! If you have
any more suggestions keep them coming. Thanks !

Romeo