473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Opening reports in ADP

Can anyone tell me what is wrong with the following? Whatever I do
the report brings back all records in the table, rather than just
those between the dates specified.

Dim strDocName As String
Dim strIncidentDate1 As Date
Dim strIncidentDate2 As Date

strIncidentDate1 = Me!txtReportStartDate
strIncidentDate2 = Me!txtReportEndDate
strDocName = "rptRedYellow"

DoCmd.OpenReport stDocName, acViewPreview, , "[IncidentDate]>=" &
strIncidentDate1 & "And" & "[IncidentDate]<=" & strIncidentDate2

This is crucial to my whole database project and I am tearing out what
little I have left of my hair.

Help!!!
Nov 13 '05 #1
11 2021
You wrote
Can anyone tell me what is wrong with the following? Whatever I do
the report brings back all records in the table, rather than just
those between the dates specified.

Dim strDocName As String
Dim strIncidentDate1 As Date
Dim strIncidentDate2 As Date

strIncidentDate1 = Me!txtReportStartDate
strIncidentDate2 = Me!txtReportEndDate
strDocName = "rptRedYellow"

DoCmd.OpenReport stDocName, acViewPreview, , "[IncidentDate]>=" &
strIncidentDate1 & "And" & "[IncidentDate]<=" & strIncidentDate2

This is crucial to my whole database project and I am tearing out what
little I have left of my hair.

Help!!!


If you are in an ADP I assume that the data is held in SQL Server.
I've never worked with ADPs but I believe that the syntax of SQL
generally in ADPs has to match T-SQL. In that case I would expect the
concatenation operator to be + rather than &. It may be that your filter
string is being completely ignored.

Before getting involved in the report and the code to manage it make
sure that you can write a query that returns the data you require.
What you should try is using a MsgBox to display the filter string that
you are generating.

Also, the IncidentDate strings need to be identified as such in the
filter string. If you were hard coding it would look like this:
"[IncidentDate]>= '1-MAR-2005'"

Try putting single quotes in to surround the string variables.

DoCmd.OpenReport stDocName, acViewPreview, , "[IncidentDate]>= '" &
strIncidentDate1 & "' And " & "[IncidentDate]<= '" & strIncidentDate2
& "'"

Finally, making date queries work in SQL Server depends on getting the
format of the criteria right. You haven't told us the format of
strIncidentDate1 and strIncidentDate2 -- are they hand entered by the
user or derived from tables?

--
Albert Marshall
Marshall Le Botmel Ltd
01242 222017
Nov 13 '05 #2

Thanks Albert

I tried that but it just gives 'Invalid use of Null'.

The strIncidentDates(1&2) ultimately derive from a table which stores
School Term dates originally entered by a previous user.

Thanks again
Colin
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3
"Colin Mardell" <co***********@btopenworld.com> wrote
I tried that but it just gives 'Invalid use of Null'.

The strIncidentDates(1&2) ultimately derive from a table which stores
School Term dates originally entered by a previous user.


If these dates come from a textbox, but it is not a bound textbox, you will
need to enclose them in the CVDate() function.
--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927
Nov 13 '05 #4
Thanks Darryl

I'm sure that I'm being really stupid but it still doesn't work. Have i
got the following syntax right?

DoCmd.OpenReport stDocName, acViewPreview, , "[IncidentDate]>= '" &
CVDate(Format(strIncidentDate1, "short date")) & "' And " &
"[IncidentDate]<= '" & CVDate(Format(strIncidentDate2, "short date")) &
"'"

Regards
Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #5

All else having failed I have now tried a different track. I have
created an input parameter stored procedure for the report. which asks
for the parameters 'strIncidentDate1' and 'strIncidentDate2'.

How do I get VB to tell the SP to accept the declared parameters without
coming up with those irritating input boxes?

I have tried the following in the Open Event:

Dim strIncidentDate1 As Date
Dim strIncidentDate2 As Date

strIncidentDate1 = Forms!BehaviourStatistics!txtReportStartDate
strIncidentDate2 = Forms!BehaviourStatistics!txtReportEndDate

'Me.RecordSource = "EXEC BehaviourStatistics1" & strIncidentDate1 &
strIncidentDate2

I have also tried the following the the Input Parameters property box:

@strIncidentDate1 = Forms!BehaviourStatistics!txtStartDate,
@strIncidentDate2 = Forms!BehaviourStatistics!txtEndDate

The last one just tells it can't convert nvarchar to datetime.

By this time I have almost no hair left at all and my wife isn't
speaking to me.

Desperate,
Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #6
I just started working with ADP's, so I don't have any experience
setting reports to ADP data sources yet, but here is how I pass a
parameter to a Sql Server SP from an Access mdb

Dim cmd As New ADODB.Command, ...
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=server1;Initial
Catalog=DB1;UID=SA;PWD=password;"
cmd.CommandTimeout = 600
UrgAlsStart = #8/9/2004#
UrgAlsEnd = #8/15/2004#
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_SoCalUrgentAlsCCT"
cmd.Parameters("@bDate").Value = UrgAlsStart
cmd.Parameters("@eDate").Value = UrgAlsEnd
'Set RSado = cmd.Execute
cmd.Execute

You could base your report on some table on the Sql Server and have the
SP insert the desired records to that table. Note: @bDate and @eDate
have been declared at the SP in the Sql Server. I am guessing that if
you create an SP in the ADP you are really createing it in the Sql
Server, so it should behave the same way. Let me know if this works for
you.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #7
Colin,

I just posted this as part of another message in another thread in regards
to ADP reports.

My preferred method is to base a report off of a stored procedure and
have the input parameters refer to a "Control" form that stores all of the
necessary info that the stored procedure needs to return a recordset to the
report. If you have very different data, you will most likely want to open
a different report based on an option on the "Control" form.

Using this method, you would set your report recordsouce in the properties
window to "dbo.SomeStoredProcedure". Then in the inputParameters you would
put something like:

@Param1 INT = Forms("ControlForm")("txtBoxWithInfo1"), @Param2 VARCHAR(20)
= Forms("ControlForm")("txtBoxWithInfo2")

HTH,
Jim

"Colin Mardell" <co***********@btopenworld.com> wrote in message
news:e9*************************@posting.google.co m...
Can anyone tell me what is wrong with the following? Whatever I do
the report brings back all records in the table, rather than just
those between the dates specified.

Dim strDocName As String
Dim strIncidentDate1 As Date
Dim strIncidentDate2 As Date

strIncidentDate1 = Me!txtReportStartDate
strIncidentDate2 = Me!txtReportEndDate
strDocName = "rptRedYellow"

DoCmd.OpenReport stDocName, acViewPreview, , "[IncidentDate]>=" &
strIncidentDate1 & "And" & "[IncidentDate]<=" & strIncidentDate2

This is crucial to my whole database project and I am tearing out what
little I have left of my hair.

Help!!!



----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
Nov 13 '05 #8
"Colin Balding" <pu***@alottolearn.com> wrote
Have i
got the following syntax right?

DoCmd.OpenReport stDocName, acViewPreview, , "[IncidentDate]>= '" &
CVDate(Format(strIncidentDate1, "short date")) & "' And " &
"[IncidentDate]<= '" & CVDate(Format(strIncidentDate2, "short date")) &
"'"


Instead of making one change to the code you posted, you made two, by adding
in the Format function. I also see now that you do not have the # date
enclosures, but instead have the single quote character used for strings.
You may have already moved on to a different solution, but if not, try this:

DoCmd.OpenReport "rptRedYellow", acViewPreview, , _
"[IncidentDate]>= #" & CVDate(Me!txtReportStartDate) & "# AND " &
"[IncidentDate]<= #" & CVDate(Me!txtReportEndDate) & "#"
--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927
Nov 13 '05 #9
Now i am really depressed. I have tried all of the suggestions all of
the very helpful respondents have made, all to no avail. As you can see
from this communication thread, the suggestions have been as many and
varied as the ways Access and SQL have of reasons why they won't work.

The suggestion at
support.microsoft.com/default.aspx?scid=kb;en-us;300693 shown below
works fine; but as soon as I try to change the hard coded date
parameters to those provided in the form from which I open the report.
I get:

"Error converting datatype varchar to datetime"

Private Sub Report_Open(Cancel As Integer)
Dim strRecordSource As String
strRecordSource = _
"Exec [SalesByYear] '1/1/97','12/31/98'"
Me.RecordSource = strRecordSource
End Sub

What I am trying to achieve must be a basic objective of many databases,
i.e. to show a report of events between two variable dates. So I don't
understand why something so basic needs to be this complicated or why
the solution isn't readily available in MS help files or documentation.

Regards
Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #10
If any one is interested I have finally solved my problem by removing
all the code from the OnOpen event and passing the Input parameters to
SQL with the following:

@strIncidentDate1 datetime =
Forms![BehaviourStatistics]![txtReportStartDate] , @strIncidentDate2
datetime = Forms![BehaviourStatistics]![txtReportEndDate]

Yippee & Whoop de doo!!!!
Thanks to all those who tried to help this ageing amateur learner.

Regards
Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #11
Thanks for sharing your solution, as I am back on the learning curve
(humbly) with ADPs. Your solution sounds pretty slick. I am going to
try it, along with the suggestions from the other respondents.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Deano | last post by:
I've been mulling over how to prevent users from clicking back onto my form after they have run a report. I want them to close the report first. The simple solution which I have tested...
4
by: Jelmer | last post by:
Hi I've been trying to create an addin similar to Find & Replace from Rick Fisher that looks thru your tables / queries / forms / modules etc.. for a reference to a string and optionally let's you...
3
by: Mike Wilson | last post by:
Is there a way to open an OLE DB database from within Access? I would like to use the Access GUI with its table and query explorer to examine a database only available through an OLEDB provider...
0
by: Mo Sihra | last post by:
hi when I try to print Access reports from VB 6.0 , Access gives some security warning and instead of printing the report, it tries to save it in some .mdi format. Please help me with opening and...
8
by: John Baker | last post by:
Hi: I have a situation where I need to programmatically open an Access application from an Access applications on the same system. Eventually the second application will close and return control...
2
by: Jeff | last post by:
I have a series of Reports (50), however, some of the reports sometimes do not contain data. When I attempt to open a Report that doesn't contain data, the database crashes (closes). Is there a...
12
by: insomniux | last post by:
Hi, In my database (access 2000) everything is done within the main form ("OpenForm"). It has a timer which triggers the code below. It first closes all reports and forms. Then it reopens the main...
0
by: kkrizl | last post by:
I've tried to research this problem, and I haven't been able to find any references to it. Probably because I shouldn't be doing it, but it was working, and now it's not. I'm trying to develop a...
1
by: kkrizl | last post by:
I've tried to research this problem, and I haven't been able to find any references to it. Probably because I shouldn't be doing it, but it was working, and now it's not. I'm trying to develop a...
1
by: mohitpande | last post by:
i am using windows 2003 server and iis 5.1 for hosting my web application and windows xp as client OS i ahv excel reports in my web application which are not opening in client machine i hav...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.