Query kicks me out.

  • Thread starter Thread starter Cynthia Laws
  • Start date Start date
C

Cynthia Laws

I have created over 34 queries in my database and up until
recently they all worked fine. Now 2 of them will not
work. Here's the deal.

When I click on the query in question it opens up in the
Datasheet view just fine.

Then I switch to the design view of the database whereby I
specify the criteria that I want for the fields. That
works fine as well.
I hit the execute key and it returns the values that I
specified in the datasheet view just fine.
However, when I go and click on the save button regardless
of whether I'm in the datasheet view or the design view, I
am kicked out of the database and returned to the drive
whereby I have to open the database all over again. It
will not save the changes to the criteria whatsoever.

It also creates another database with a padlock in the
upper right hand corner and states that the type of
application is "Microsoft Access Record Locking
Information with the size being 1KB.

I don't know what's wrong because it only does this on 2
of the 34 queries. The other 32 queries work just fine.
I've deleted these two queries and tried recreating them
but to no avail.

Any suggestions.
 
Can you post the SQL statements of the original query on
here? Also post the changed SQL Statements. Also, why
are you opening up the queries to change (or specify) the
criteria???? Use query by form or some other method.
You should never have to open up a query in design mode
once it is designed and saved. You should use your query
through a form or report and display the information
according to values you send it.

Sorry for such a late posting to this question.
 
I never got around to creating the query by form.....I
will go back and do that. Thanks...


SELECT MonthlyDRRWorksheet.[RevMth/Yr],
MonthlyDRRWorksheet.RecordReviewDate,
MonthlyDRRWorksheet.DrrQraSubmit, Hospital.HospitalSystem,
Hospital.DartsCode, Hospital_Region.Region, Hospital.Name,
MonthlyDRRWorksheet.VentCallsDRL, [MonthlyDRRWorksheet]!
[IptDthsMortList]+[MonthlyDRRWorksheet]!
[TramErDthsMortList]+[MonthlyDRRWorksheet]!
[DthsCalledDRLNotOnMortLst]-[MonthlyDRRWorksheet]!
[StillbornDthsMortList]-[MonthlyDRRWorksheet]!
[DthsLstTwiceMortList]-[MonthlyDRRWorksheet]!
[DidNotDieMortList]-[MonthlyDRRWorksheet]!
[PtbelongtoSisterFacility] AS TotalHospitalDeaths,
[MonthlyDRRWorksheet]![BeginCallsDRL]+
[MonthlyDRRWorksheet]![DiedCurrMthCallMdePrvMth]+
[MonthlyDRRWorksheet]![DiedCurrMthCallMdeInFoll]-
[MonthlyDRRWorksheet]![DthsCalledTwiceDRL]-
[MonthlyDRRWorksheet]![StillbornDthsCalledDRL]-
[MonthlyDRRWorksheet]![DidNotDieDRL]-[MonthlyDRRWorksheet]!
[SnfRehabHospCallsDRL]-[MonthlyDRRWorksheet]!
[CallMdeCurrMthDiedFollMth] AS TotalStatlineCalls,
Format$([MonthlyDRRWorksheet].[RevMth/Yr],'mmmm yyyy') AS
DeathMthYr, Hospital_Region.EndDate
FROM (Hospital RIGHT JOIN MonthlyDRRWorksheet ON
Hospital.DartsCode = MonthlyDRRWorksheet.DartsCode) LEFT
JOIN Hospital_Region ON MonthlyDRRWorksheet.DartsCode =
Hospital_Region.DartsCode
GROUP BY MonthlyDRRWorksheet.[RevMth/Yr],
MonthlyDRRWorksheet.RecordReviewDate,
MonthlyDRRWorksheet.DrrQraSubmit, Hospital.HospitalSystem,
Hospital.DartsCode, Hospital_Region.Region, Hospital.Name,
MonthlyDRRWorksheet.VentCallsDRL, [MonthlyDRRWorksheet]!
[IptDthsMortList]+[MonthlyDRRWorksheet]!
[TramErDthsMortList]+[MonthlyDRRWorksheet]!
[DthsCalledDRLNotOnMortLst]-[MonthlyDRRWorksheet]!
[StillbornDthsMortList]-[MonthlyDRRWorksheet]!
[DthsLstTwiceMortList]-[MonthlyDRRWorksheet]!
[DidNotDieMortList]-[MonthlyDRRWorksheet]!
[PtbelongtoSisterFacility], [MonthlyDRRWorksheet]!
[BeginCallsDRL]+[MonthlyDRRWorksheet]!
[DiedCurrMthCallMdePrvMth]+[MonthlyDRRWorksheet]!
[DiedCurrMthCallMdeInFoll]-[MonthlyDRRWorksheet]!
[DthsCalledTwiceDRL]-[MonthlyDRRWorksheet]!
[StillbornDthsCalledDRL]-[MonthlyDRRWorksheet]!
[DidNotDieDRL]-[MonthlyDRRWorksheet]!
[SnfRehabHospCallsDRL]-[MonthlyDRRWorksheet]!
[CallMdeCurrMthDiedFollMth], Format$([MonthlyDRRWorksheet].
[RevMth/Yr],'mmmm yyyy'), Hospital_Region.EndDate,
MonthlyDRRWorksheet.Reviewee
HAVING (((MonthlyDRRWorksheet.[RevMth/Yr]) Between
#1/1/2003# And #6/30/2003#) AND ((Hospital_Region.EndDate)
Is Null);
 
Sorry for the lateness of my response, but under a
timeline to finish a project and "life" is going in a
certain direct (if you get my drift!)

You are going to have a big problem with your query of
you are using anything earlier than XP. Prior to XP
(not sure about 2000), queries were limited to 2054
characters. Your query has 2365 characters. This will
cause a major error, although not sure it would kick you
out and keep the record locking on, but it could.

Second question, why are using Excel as a database?
Everything I read through your SQL tells me that the
information you are putting in Excel would be set in
Acces and allow Acces to do the work required.

Many people get Excel confused for what it really is
meant to do. I reprogrammed the 5th largest publisher in
US sales routines and practices because they were
tracking daily sales, calls, appointments, etc. using
Excel. By the time they had a couple 100 people enter
their info, the time and effort it took was immense,
because the data had to be duplicated for each region,
territory and such. I only tell you this because I dont
wish to sound harsh by mentioning you may be using Exvel
for things it was not really meant to do, when a VERY
large company was doing the very same thing.

IF you are using Access XP then there is another problem
that was not easily recognizable. Before I dive deeper,
I would like to know which version of Access you are
using.

Thanks

Drew

-----Original Message-----
I never got around to creating the query by form.....I
will go back and do that. Thanks...


SELECT MonthlyDRRWorksheet.[RevMth/Yr],
MonthlyDRRWorksheet.RecordReviewDate,
MonthlyDRRWorksheet.DrrQraSubmit, Hospital.HospitalSystem,
Hospital.DartsCode, Hospital_Region.Region, Hospital.Name,
MonthlyDRRWorksheet.VentCallsDRL, [MonthlyDRRWorksheet]!
[IptDthsMortList]+[MonthlyDRRWorksheet]!
[TramErDthsMortList]+[MonthlyDRRWorksheet]!
[DthsCalledDRLNotOnMortLst]-[MonthlyDRRWorksheet]!
[StillbornDthsMortList]-[MonthlyDRRWorksheet]!
[DthsLstTwiceMortList]-[MonthlyDRRWorksheet]!
[DidNotDieMortList]-[MonthlyDRRWorksheet]!
[PtbelongtoSisterFacility] AS TotalHospitalDeaths,
[MonthlyDRRWorksheet]![BeginCallsDRL]+
[MonthlyDRRWorksheet]![DiedCurrMthCallMdePrvMth]+
[MonthlyDRRWorksheet]![DiedCurrMthCallMdeInFoll]-
[MonthlyDRRWorksheet]![DthsCalledTwiceDRL]-
[MonthlyDRRWorksheet]![StillbornDthsCalledDRL]-
[MonthlyDRRWorksheet]![DidNotDieDRL]- [MonthlyDRRWorksheet]!
[SnfRehabHospCallsDRL]-[MonthlyDRRWorksheet]!
[CallMdeCurrMthDiedFollMth] AS TotalStatlineCalls,
Format$([MonthlyDRRWorksheet].[RevMth/Yr],'mmmm yyyy') AS
DeathMthYr, Hospital_Region.EndDate
FROM (Hospital RIGHT JOIN MonthlyDRRWorksheet ON
Hospital.DartsCode = MonthlyDRRWorksheet.DartsCode) LEFT
JOIN Hospital_Region ON MonthlyDRRWorksheet.DartsCode =
Hospital_Region.DartsCode
GROUP BY MonthlyDRRWorksheet.[RevMth/Yr],
MonthlyDRRWorksheet.RecordReviewDate,
MonthlyDRRWorksheet.DrrQraSubmit, Hospital.HospitalSystem,
Hospital.DartsCode, Hospital_Region.Region, Hospital.Name,
MonthlyDRRWorksheet.VentCallsDRL, [MonthlyDRRWorksheet]!
[IptDthsMortList]+[MonthlyDRRWorksheet]!
[TramErDthsMortList]+[MonthlyDRRWorksheet]!
[DthsCalledDRLNotOnMortLst]-[MonthlyDRRWorksheet]!
[StillbornDthsMortList]-[MonthlyDRRWorksheet]!
[DthsLstTwiceMortList]-[MonthlyDRRWorksheet]!
[DidNotDieMortList]-[MonthlyDRRWorksheet]!
[PtbelongtoSisterFacility], [MonthlyDRRWorksheet]!
[BeginCallsDRL]+[MonthlyDRRWorksheet]!
[DiedCurrMthCallMdePrvMth]+[MonthlyDRRWorksheet]!
[DiedCurrMthCallMdeInFoll]-[MonthlyDRRWorksheet]!
[DthsCalledTwiceDRL]-[MonthlyDRRWorksheet]!
[StillbornDthsCalledDRL]-[MonthlyDRRWorksheet]!
[DidNotDieDRL]-[MonthlyDRRWorksheet]!
[SnfRehabHospCallsDRL]-[MonthlyDRRWorksheet]!
[CallMdeCurrMthDiedFollMth], Format$([MonthlyDRRWorksheet].
[RevMth/Yr],'mmmm yyyy'), Hospital_Region.EndDate,
MonthlyDRRWorksheet.Reviewee
HAVING (((MonthlyDRRWorksheet.[RevMth/Yr]) Between
#1/1/2003# And #6/30/2003#) AND ((Hospital_Region.EndDate)
Is Null);
-----Original Message-----
Can you post the SQL statements of the original query on
here? Also post the changed SQL Statements. Also, why
are you opening up the queries to change (or specify) the
criteria???? Use query by form or some other method.
You should never have to open up a query in design mode
once it is designed and saved. You should use your query
through a form or report and display the information
according to values you send it.

Sorry for such a late posting to this question.

whereby
I view,
I
.
Click to expand...
.
Click to expand...
 
Back
Top