Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

Why are all my Access Reports now crashing with a 242 run-time error?

Can anyone shed any light on why many of my Access Reports should now encounter a run-time error # 242? I have been struggling with this problem all day and cannot make sense of it.

I am developing an Access 2007 "project" (.adp) as a front-end to a SQL Server 2005 Express database.

For my Access Reports I pass in "WHERE" and "ORDER BY" clauses via the "OpenArgs" parameter, and in the Report's "Report_Open" procedure I construct a T-SQL statement to be used as the Report's "RecordSource" property. These are usually based on Tables or Views from the SQL Server database.

This has worked fine on many reports for the past two years, but all of a sudden, if a "date" condition is included in the "WHERE" condition for a Report, I am now getting a run-time error before the Report is displayed on the screen (even if no dates are actually displayed in the report). If there is no "date" condition included in the "WHERE" condition, the reports display OK, even if there are date fields included in the Report.

The error description appearing with the run-time error number 242 is either :
"Application-defined or object-defined error", or
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

In the "Report_Open" procedure I have even added extra VBA code to open a temporary, local RecordSet based on the "RecordSource". This appears to work OK, generating the correct number of records with the correct number of fields, so the T-SQL syntax appears to be OK. It is after this event, when presumably the Report is creating its own recordset that the run-time error occurs.

I think the problem lies at the Access end of things rather than the SQL Server end. I maintain "Version/Revision" numbers for both my SQL Server database and Access application (eg 178.001). When the Access application launches and first connects with the SQL Server database, I check that both have the same "Version" number (eg 178) and don't mind if they have different "Revision" numbers. ("Revisions" represent minor changes.)

I keep archive copies of all my previous "Versions" of both the Access application and SQL Server database and have found that the problem lies between my Access application version "178.001" and "178.002". (I know these numbers will mean nothing to anyone else, but they help me identify when the problem has arisen.) As far as I'm aware I made no significant change to the application between these revisions.

Is there some sort of "global" Access or Windows XP setting that might have changed and introduced this problem?

If I have to go back to working from the "good" Access application (Version 178.001), is there a way I can copy a new Access Form created in Version 178.002 and "paste" it into Version 178.001? I can cut and paste underlying VBA code easily enough, it is more the "design and layout" of a new Form I would also like to copy.

Any help or suggestions gratefully received.
0
colinasad
Asked:
colinasad
  • 6
  • 5
1 Solution
 
plummetCommented:
Hi,

Sounds like a date format problem to me. How are you constructing the WHERE clause that includes a date field, can you post up an example?

Thx
John
0
 
colinasadAuthor Commented:
Thanks for the prompt response, John.

Below are two examples of the full "Report.RecordSource" property constructed in "Report_Open", based on the "WHERE" and "ORDER BY" clauses passed in via "Report.OpenArgs".
(These sample dates are for September, between 5th and 18th inclusive.)

Example 1 : Is where the operator has NOT specified a date range in the Form that calls the Report.
Example 2 : Is where the operator HAS specified a date range in the Form that calls the Report.

Example 1 :
SELECT *
FROM TBL_ShopStockHistory
WHERE (ShpStkHst_ShopCode = 'JOE')
ORDER BY ShpStkHst_Date

Example 2 :
SELECT *
FROM TBL_ShopStockHistory
WHERE (ShpStkHst_ShopCode = 'JOE' AND ShpStkHst_Date >= '09/05/2012 00:00:00' AND ShpStkHst_Date <= '09/18/2012 23:59:59')
ORDER BY ShpStkHst_Date

As I said in my original posting, this technique has served me well for two years and works OK when I revert to a version of my application from two weeks ago.
Also, with the current version, the Example 2 T-SQL creates a recordset OK when I explicitly do that as a test in the "Report_Open" procedure.

Also, the Report is generated OK if the date condition is omitted, as in Example 1, and any dates included in the Report are printed OK.

Thanks.
0
 
plummetCommented:
I generally specify dates in the format YYYYMDD, to avoid the potential confusion over month and day that we get between our European dd/mm/yyyy and the US mm/dd/yyyy. So I'd pass this sort of query to SQL server:

SELECT * 
FROM TBL_ShopStockHistory
WHERE ShpStkHst_ShopCode = 'JOE' 
AND (ShpStkHst_Date between '20120905 00:00:00' AND '20120918 23:59:59')
ORDER BY ShpStkHst_Date

Open in new window

See if that makes any difference?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
colinasadAuthor Commented:
Thanks for the further suggestion, plummet.
Will try these tomorrow afternoon when I work on that system again.
Is there a global Windows/Microsoft/Access setting that might have changed to cause a date format problem? As far as I can see, my "region" settings are as they always have been.
Will report back.
0
 
plummetCommented:
Just a thought, are you using the same user to connect to the database for the new and the old versions? Each user can have a different default language, and this has an effect on the date format that's expected.

Go to SQL Server Management Studio and check the properties for the user(s) you're using to access your database - you're looking for Default Language under the General page of the user's properties.

I hope this helps.

John
0
 
colinasadAuthor Commented:
Thanks again John.
Yes, as far as I'm aware I am always connecting to the SQL Server database with the same user.
However, when I used SQL Server Management Studio I could not see the "Default Language" setting you mention.
I worked my way through Databases - (My Database) - Security - Users and right-clicked on the User that I use for my connection and pulled up the "Properties" window. The "General" page did not seem to have a "Default Language" option.
Any thoughts?
0
 
plummetCommented:
Hi Colin

The Default language setting is under the Security section which is under the Server, not the one under each database:

Server -> Security -> Logins

I should have explained that but it was getting late. It would be good to check your SQL server connection in the ADP files, but you'd know if you used a different user so I don't reckon that is likely to be the cause.

When you try the exact same date range (09/05/2012 00:00:00 to 09/18/2012 23:59:59) on a previous version it works ok? On the same computer, logged in with the same user to the same database?
0
 
colinasadAuthor Commented:
Thanks again.
I will be working on that system again this afternoon (UK time).
Just for clarification, I am doing this on my own development laptop that runs both the SQL Server database and the Access ".adp" application. So, I am always using the same computer and user login.

A test I also carried out yesterday was changing the "Version" of my SQL Server database (this is stored in a Table that the Access application checks as soon as it connects with the database) to keep it "compatible" with different versions of my Access applications as I worked back through them to find a version that did not fail.

With the same (most up-to-date) SQL Server database in place, my Access Versions 179.001, 178.003 & 178.002 all produced the problem. Access application Version 178.001 worked OK. Because this was all with the same SQL Server database (albeit with a tweaked version number) I decided the problem had crept into the Access application.

I guess thinking about it; because this was all done in the same afternoon on the same laptop without re-starting it (and repeating the test a couple of times), it is unlikely to be a global Windows setting because it would have remained unchanged on my laptop.

Will work on it again this afternoon. It would be good to discover exactly what has happened, but I keep fairly good notes on the work I do, so it should be failry easy to bring the Access application back up from Version 178.001. That was why I was also asking if there was an easy way to copy a new "Form" from one Access application into another.

Thanks again.
0
 
plummetCommented:
It's a puzzler, no question. I'd still give the YYYYMMDD format a go, as that has got me out of these situations many times. I was asking about the different users because I had the exact same issue on a recent contract and it turned out to be because newly added users had "English" rather than "British English" as the Default language. This isn't the case here.

It might be useful to see the code that constructs the SQL statement you use as the recordsource.

You can import a form from another database easily enough, by the way (External data, Import & Link, Access, etc)

Cheers
John
0
 
colinasadAuthor Commented:
Although I have not got to the bottom of why my original problem arose (ie my Access application crashing when dates occur in the WHERE element of a Report's RecordSource), I have decided to go back to my last working version and re-do the work I have done since then.It would have been good to have understood exactly what happened, but time is of the essence and I need to get going again.

Part of my original question asked how I could transfer Forms and Reports from one database to the other and the responder has pointed me to the "External Data - Export" option which I had not previously used for this purpose. It seems to have worked well for me.

Also I will make use of the "BETWEEN" T-SQL function rather than my more verbose syntax for comparing dates. However, I think I will leave the data format as it is, as I suspect I would have to also introduce some "FORMAT" or "CONVERT" or "CAST" expressions that might require updating the VBA behind many Forms and Reports. My existing format has worked OK for the past two years (apart from the current blip) and I think I'll just stick with it.

Many thanks for your help.
0
 
plummetCommented:
Thanks for this.

I would really recommend changing the date format as it can lead to terrible confusion, such as 1/9/2012 being recognised as the 9th of January instead of 1st of Sept, and furthermore dates such as 13/9/2012 might cause a problem, depending on the user/database/query settings.

Rather than rely on checking settings I prefer to format the date YYYYMDD to try to ensure the date is consistent.

But that's just my view :-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now