Error when running Query, but not Report

[Blaze]

Charges is a linked table via Sharepoint.

Blaze

One query can use another query as a "table" -- just as if it existed as a
permanent table. The query will run the other query in order to get its
records and use them as data source.

OK - post the SQL statement of Charges query. I'm betting that what we're
seeking is in there....

--

Ken Snell
<MS ACCESS MVP>

Charges is just another Query. I didn't know I could use a Query to run a
Query. That's an interesting addition to the utility.

Sorry for the wrong SQL Statement.

Blaze

****

Ahhh.. this is a different query than the one you posted on 8/31/2005.

OK - you said that, if you remove the "Charges" portion of the query, it
works without giving you the error. So, is Charges a table, or is it a
query? If it's a query, what is its SQL statement? Somewhere in the
Charges
portion, the query thinks it needs to use some reference to that Arrest
Report Generator object.
--

Ken Snell
<MS ACCESS MVP>

Well, I thought I posted the correct thing. Here's what I did. In the
Query
that I'm having issues with, I clicked "Design View".

From there I click the View, SQL View, thus showing the SQL Query. Here
it
is again. Thank you for hanging in there, because it makes absolutly NO
sense to me how this can happen. But as I am sure you are aware of, it
is
near impossible to develop if you can run the query to verify whether it
works or not.

SELECT [Booking ID (BID): All Items].ID, [Arrest Reports: All
Forms].Person1Bid, [Booking ID (BID): All Items].[Full Name (First
Last)],
[Inmate Records: All Forms].DOB, [Inmate Records: All Forms]!DLState & "
"
&
[Inmate Records: All Forms]!DLNumber AS Expr4, [Inmate Records: All
Forms].PhoneNumber, [FirstName] & " " & [LastName] AS Expr1, [Inmate
Records:
All Forms].NameSuffix, [Inmate Records: All Forms].MiddleName, [Inmate
Records: All Forms].[SSNY-N], [Inmate Records: All Forms].InmateSSN,
[Inmate
Records: All Forms].PrimaryAddy, [PrimaryCity] & "," & [PrimaryState] & "
"
& [PrimaryZipCode] AS Expr2, [Inmate Records: All Forms].PrimaryCity,
[Inmate
Records: All Forms].PrimaryState, [Inmate Records: All
Forms].PrimaryZipCode,
Charges.[Charge Name] AS Charge0, Charges.[Statute Number] AS Statute0,
IIf([Charges.Charge Name] Is Null,"No Charges",[Charges.Charge Name] & "
"
&
[Statute0]) AS [Charge0 Name and Statute], Charges_1.[Charge Name] AS
Charge1, Charges_1.[Statute Number] AS Statute1, IIf([Charges 2] Is
Null,"
",[Charges_1.Charge Name] & " , " & [Statute1]) AS [Charge 1 Name and
Statute], Charges_2.[Charge Name] AS Charge2, Charges_2.[Statute Number]
AS
Statute2, IIf([Charges 3] Is Null," ",[Charges_2.Charge Name] & " , " &
[Statute2]) AS [Charge 2 Name and Statute], Charges_3.[Charge Name] AS
Charge3, Charges_3.[Statute Number] AS Statute3, Charges_4.[Charge Name]
AS
Charge4, Charges_4.[Statute Number] AS Statute4, [Booking ID (BID): All
Items].[Where Arrested], Locations.[City Name], Locations.[Zip Code],
[Radio
ID].[Radio ID], [Radio ID].[Full Name], [Radio ID].[Job Title], [Arrest
Reports: All Forms].Narrative2, [Arrest Reports: All
Forms].ReportedDateTime,
[Arrest Reports: All Forms].Name, [Booking ID (BID): All Items].[How
Discharged], [Booking ID (BID): All Items].[Discharge Date], [Booking ID
(BID): All Items]![Discharge Date]-[Booking ID (BID): All Items]![Date
Commited] AS Expr3, [Dispatch Log: All Items].[Event Subject], [Dispatch
Log:
All Items].[Type of Call], [Dispatch Log: All Items].[Description of
Call],
[Dispatch Log: All Items].[Created By], [Dispatch Log: All Items].Date,
[Dispatch Log: All Items].Time, [Dispatch Log: All Items].[Radio ID],
[Evidence Forms: All Documents].Name AS [Evidence File Name],
IIf([Evidence
Forms: All Documents.Name] Is Null,"No Evidence File on Record",[Evidence
File Name]) AS [Evidence File Name if True], "" AS [Narrative Section],
[Arrest Reports: All Forms].NarrativeBuilderText AS [Narrative Builder
NARRATIVE TEXT], Replace([Arrest Reports: All
Forms]!NarrativeBuilderText,Chr(10),Chr(13) & Chr(10)) AS [Narrative
Builder
REPLACE]
FROM (((((([Arrest Reports: All Forms] LEFT JOIN (((((([Booking ID (BID):
All Items] LEFT JOIN [Inmate Records: All Forms] ON [Booking ID (BID):
All
Items].[Inmate Record (ISSN)] = [Inmate Records: All Forms].ID) LEFT JOIN
Charges ON [Booking ID (BID): All Items].Charges = Charges.ID) LEFT JOIN
Charges AS Charges_1 ON [Booking ID (BID): All Items].[Charges 2] =
Charges_1.ID) LEFT JOIN Charges AS Charges_2 ON [Booking ID (BID): All
Items].[Charges 3] = Charges_2.ID) LEFT JOIN Locations ON [Booking ID
(BID):
All Items].[Location ID] = Locations.ID) LEFT JOIN [Radio ID] ON [Booking
ID
(BID): All Items].[Arresting Officer] = [Radio ID].ID) ON [Arrest
Reports:
All Forms].Person1Bid = [Booking ID (BID): All Items].ID) LEFT JOIN
[Dispatch
Log: All Items] ON [Booking ID (BID): All Items].ID = [Dispatch Log: All
Items].[BID Reference]) LEFT JOIN Charges AS Charges_3 ON [Booking ID
(BID):
All Items].[Charges 4] = Charges_3.ID) LEFT JOIN Charges AS Charges_4 ON
[Booking ID (BID): All Items].[Charges 5] = Charges_4.ID) LEFT JOIN
Charges
AS Charges_5 ON [Booking ID (BID): All Items].[Charges 6] = Charges_5.ID)
LEFT JOIN Charges AS Charges_6 ON [Booking ID (BID): All Items].[Charges
7] =
Charges_6.ID) LEFT JOIN [Evidence Forms: All Documents] ON [Booking ID
(BID):
All Items].ID = [Evidence Forms: All Documents].[BID Reference]
WHERE ((([Arrest Reports: All Forms].Person1Bid)=[BID]));


Again, thank you for your experience in this situation!

Blaze

Hmm.. I don't have experience with SharePoint, but let's see the SQL
statement that you're using and let's see what we might suggest...

--

Ken Snell
<MS ACCESS MVP>

I have a Query that runs and it grabs information from about 7-10 tables
(sharepoint based linked tables). This query is a somewhat simple
query
that
has quite a few IIF statements. The name is called "Arrest Report
Generator". This then creates a report called "Arrest Report
Generator".

The Report runs without issue and works fine asking a basic question to
pick
the right report out of a table. It then populates the report
correctly
running the Query "Arrest Report Generator".

Now if I run the Query from outside the Design, it gives me the error
""The
Microsoft Jet database engine could not find the object ". Make sure
the
object exists and that you spell its name and the path name correctly.
(Error
3011)"

I am trying to edit the Query and when I click the "!" to temp run the
Query, it asks the basic question and then gives an error stating:

"The Microsoft Jet database engine could not find the object 'Arrest
Report
Generator'. Make sure the object exists and that you spell its name
and
the
path name correctly. (Error 3011)"

Do you still need SQL Syntax?

Blaze



:

Define "won't run by itself" vs. "the report runs fine".... what are
you
running as a query? what is the query's relationship to the report?
what
is
the query's SQL statement? etc. need more details, please....

--

Ken Snell
<MS ACCESS MVP>


I have a Report that runs fine, but the query will not "run" by
itself.

Any ideas out there.

Thanks,

Blaze

 

[Ken Snell [MVP]]

Well, this is most puzzling.

I am not sure what to ask next... I don't know if it has anything to do with
using SharePoint.

I don't see any functions being called that might have the object reference
that the query seeks; I don't see any references to that object in the
query; I don't see any parameters that might be seeking that object for
data.

Just to confirm my previous understanding, you see the error when you try to
run the query from the database window, and not from any form, right?

--

Ken Snell
<MS ACCESS MVP>

Charges is a linked table via Sharepoint.

Blaze

One query can use another query as a "table" -- just as if it existed as
a
permanent table. The query will run the other query in order to get its
records and use them as data source.

OK - post the SQL statement of Charges query. I'm betting that what we're
seeking is in there....

--

Ken Snell
<MS ACCESS MVP>

Charges is just another Query. I didn't know I could use a Query to
run a
Query. That's an interesting addition to the utility.

Sorry for the wrong SQL Statement.

Blaze

****

Ahhh.. this is a different query than the one you posted on 8/31/2005.

OK - you said that, if you remove the "Charges" portion of the query,
it
works without giving you the error. So, is Charges a table, or is it a
query? If it's a query, what is its SQL statement? Somewhere in the
Charges
portion, the query thinks it needs to use some reference to that Arrest
Report Generator object.
--

Ken Snell
<MS ACCESS MVP>



Well, I thought I posted the correct thing. Here's what I did. In
the
Query
that I'm having issues with, I clicked "Design View".

From there I click the View, SQL View, thus showing the SQL Query.
Here
it
is again. Thank you for hanging in there, because it makes absolutly
NO
sense to me how this can happen. But as I am sure you are aware of,
it
is
near impossible to develop if you can run the query to verify whether
it
works or not.

SELECT [Booking ID (BID): All Items].ID, [Arrest Reports: All
Forms].Person1Bid, [Booking ID (BID): All Items].[Full Name (First
Last)],
[Inmate Records: All Forms].DOB, [Inmate Records: All Forms]!DLState &
"
"
&
[Inmate Records: All Forms]!DLNumber AS Expr4, [Inmate Records: All
Forms].PhoneNumber, [FirstName] & " " & [LastName] AS Expr1, [Inmate
Records:
All Forms].NameSuffix, [Inmate Records: All Forms].MiddleName, [Inmate
Records: All Forms].[SSNY-N], [Inmate Records: All Forms].InmateSSN,
[Inmate
Records: All Forms].PrimaryAddy, [PrimaryCity] & "," & [PrimaryState]
& "
"
& [PrimaryZipCode] AS Expr2, [Inmate Records: All Forms].PrimaryCity,
[Inmate
Records: All Forms].PrimaryState, [Inmate Records: All
Forms].PrimaryZipCode,
Charges.[Charge Name] AS Charge0, Charges.[Statute Number] AS
Statute0,
IIf([Charges.Charge Name] Is Null,"No Charges",[Charges.Charge Name] &
"
"
&
[Statute0]) AS [Charge0 Name and Statute], Charges_1.[Charge Name] AS
Charge1, Charges_1.[Statute Number] AS Statute1, IIf([Charges 2] Is
Null,"
",[Charges_1.Charge Name] & " , " & [Statute1]) AS [Charge 1 Name and
Statute], Charges_2.[Charge Name] AS Charge2, Charges_2.[Statute
Number]
AS
Statute2, IIf([Charges 3] Is Null," ",[Charges_2.Charge Name] & " , "
&
[Statute2]) AS [Charge 2 Name and Statute], Charges_3.[Charge Name] AS
Charge3, Charges_3.[Statute Number] AS Statute3, Charges_4.[Charge
Name]
AS
Charge4, Charges_4.[Statute Number] AS Statute4, [Booking ID (BID):
All
Items].[Where Arrested], Locations.[City Name], Locations.[Zip Code],
[Radio
ID].[Radio ID], [Radio ID].[Full Name], [Radio ID].[Job Title],
[Arrest
Reports: All Forms].Narrative2, [Arrest Reports: All
Forms].ReportedDateTime,
[Arrest Reports: All Forms].Name, [Booking ID (BID): All Items].[How
Discharged], [Booking ID (BID): All Items].[Discharge Date], [Booking
ID
(BID): All Items]![Discharge Date]-[Booking ID (BID): All Items]![Date
Commited] AS Expr3, [Dispatch Log: All Items].[Event Subject],
[Dispatch
Log:
All Items].[Type of Call], [Dispatch Log: All Items].[Description of
Call],
[Dispatch Log: All Items].[Created By], [Dispatch Log: All
Items].Date,
[Dispatch Log: All Items].Time, [Dispatch Log: All Items].[Radio ID],
[Evidence Forms: All Documents].Name AS [Evidence File Name],
IIf([Evidence
Forms: All Documents.Name] Is Null,"No Evidence File on
Record",[Evidence
File Name]) AS [Evidence File Name if True], "" AS [Narrative
Section],
[Arrest Reports: All Forms].NarrativeBuilderText AS [Narrative Builder
NARRATIVE TEXT], Replace([Arrest Reports: All
Forms]!NarrativeBuilderText,Chr(10),Chr(13) & Chr(10)) AS [Narrative
Builder
REPLACE]
FROM (((((([Arrest Reports: All Forms] LEFT JOIN (((((([Booking ID
(BID):
All Items] LEFT JOIN [Inmate Records: All Forms] ON [Booking ID (BID):
All
Items].[Inmate Record (ISSN)] = [Inmate Records: All Forms].ID) LEFT
JOIN
Charges ON [Booking ID (BID): All Items].Charges = Charges.ID) LEFT
JOIN
Charges AS Charges_1 ON [Booking ID (BID): All Items].[Charges 2] =
Charges_1.ID) LEFT JOIN Charges AS Charges_2 ON [Booking ID (BID): All
Items].[Charges 3] = Charges_2.ID) LEFT JOIN Locations ON [Booking ID
(BID):
All Items].[Location ID] = Locations.ID) LEFT JOIN [Radio ID] ON
[Booking
ID
(BID): All Items].[Arresting Officer] = [Radio ID].ID) ON [Arrest
Reports:
All Forms].Person1Bid = [Booking ID (BID): All Items].ID) LEFT JOIN
[Dispatch
Log: All Items] ON [Booking ID (BID): All Items].ID = [Dispatch Log:
All
Items].[BID Reference]) LEFT JOIN Charges AS Charges_3 ON [Booking ID
(BID):
All Items].[Charges 4] = Charges_3.ID) LEFT JOIN Charges AS Charges_4
ON
[Booking ID (BID): All Items].[Charges 5] = Charges_4.ID) LEFT JOIN
Charges
AS Charges_5 ON [Booking ID (BID): All Items].[Charges 6] =
Charges_5.ID)
LEFT JOIN Charges AS Charges_6 ON [Booking ID (BID): All
Items].[Charges
7] =
Charges_6.ID) LEFT JOIN [Evidence Forms: All Documents] ON [Booking ID
(BID):
All Items].ID = [Evidence Forms: All Documents].[BID Reference]
WHERE ((([Arrest Reports: All Forms].Person1Bid)=[BID]));


Again, thank you for your experience in this situation!

Blaze


:

Hmm.. I don't have experience with SharePoint, but let's see the SQL
statement that you're using and let's see what we might suggest...

--

Ken Snell
<MS ACCESS MVP>

I have a Query that runs and it grabs information from about 7-10
tables
(sharepoint based linked tables). This query is a somewhat simple
query
that
has quite a few IIF statements. The name is called "Arrest Report
Generator". This then creates a report called "Arrest Report
Generator".

The Report runs without issue and works fine asking a basic question
to
pick
the right report out of a table. It then populates the report
correctly
running the Query "Arrest Report Generator".

Now if I run the Query from outside the Design, it gives me the
error
""The
Microsoft Jet database engine could not find the object ". Make
sure
the
object exists and that you spell its name and the path name
correctly.
(Error
3011)"

I am trying to edit the Query and when I click the "!" to temp run
the
Query, it asks the basic question and then gives an error stating:

"The Microsoft Jet database engine could not find the object 'Arrest
Report
Generator'. Make sure the object exists and that you spell its name
and
the
path name correctly. (Error 3011)"

Do you still need SQL Syntax?

Blaze



:

Define "won't run by itself" vs. "the report runs fine".... what
are
you
running as a query? what is the query's relationship to the report?
what
is
the query's SQL statement? etc. need more details, please....

--

Ken Snell
<MS ACCESS MVP>


I have a Report that runs fine, but the query will not "run" by
itself.

Any ideas out there.

Thanks,

Blaze

 

[Blaze]

That is correct. I try through the Access/Query section to run the query, as
I am still in development, and get this error. If I run the Query via the
Report, it works.

Now I have new developments in the issue that may help someone that
understands this a little better...

I decided to recreate the query using another query name. Because I am
developing, I had to do something for the time being, incase this can't be
resolved (gosh, I hope it can!)

I setup the query with a simple get name from inmate record, get charges.
Since I knew the last issue was caused by the charges table, somehow. I get
the same error. Now if I reduce the query to only show Charge 1 & 2 /
Statute 1 & 2, it runs, but I don't get any data in my query. I do have it
setup to filter by a BID number, which is my unique identifier for all new
cases. When I run the report on a BID with only 1 Charge, and the Query only
references 1 charge, it works fine, without issue. If I have a report with
only 1 charge and Query 2 charges, it gives me no data. I think this may be
part of the issue, but I can't understand why it would care if there weren't
other charges.

So this brings up the question... Since reports have a possible 6 charges
they can put on a report, and most of the time it will be 1 or 2 charges, how
do I deal with that? Is this a situation where I would setup it's own query
to pull and deal with the missing charges?

Thanks again!

Blaze

Well, this is most puzzling.

I am not sure what to ask next... I don't know if it has anything to do with
using SharePoint.

I don't see any functions being called that might have the object reference
that the query seeks; I don't see any references to that object in the
query; I don't see any parameters that might be seeking that object for
data.

Just to confirm my previous understanding, you see the error when you try to
run the query from the database window, and not from any form, right?

--

Ken Snell
<MS ACCESS MVP>

Charges is a linked table via Sharepoint.

Blaze

One query can use another query as a "table" -- just as if it existed as
a
permanent table. The query will run the other query in order to get its
records and use them as data source.

OK - post the SQL statement of Charges query. I'm betting that what we're
seeking is in there....

--

Ken Snell
<MS ACCESS MVP>


Charges is just another Query. I didn't know I could use a Query to
run a
Query. That's an interesting addition to the utility.

Sorry for the wrong SQL Statement.

Blaze

****

Ahhh.. this is a different query than the one you posted on 8/31/2005.

OK - you said that, if you remove the "Charges" portion of the query,
it
works without giving you the error. So, is Charges a table, or is it a
query? If it's a query, what is its SQL statement? Somewhere in the
Charges
portion, the query thinks it needs to use some reference to that Arrest
Report Generator object.
--

Ken Snell
<MS ACCESS MVP>



Well, I thought I posted the correct thing. Here's what I did. In
the
Query
that I'm having issues with, I clicked "Design View".

From there I click the View, SQL View, thus showing the SQL Query.
Here
it
is again. Thank you for hanging in there, because it makes absolutly
NO
sense to me how this can happen. But as I am sure you are aware of,
it
is
near impossible to develop if you can run the query to verify whether
it
works or not.

SELECT [Booking ID (BID): All Items].ID, [Arrest Reports: All
Forms].Person1Bid, [Booking ID (BID): All Items].[Full Name (First
Last)],
[Inmate Records: All Forms].DOB, [Inmate Records: All Forms]!DLState &
"
"
&
[Inmate Records: All Forms]!DLNumber AS Expr4, [Inmate Records: All
Forms].PhoneNumber, [FirstName] & " " & [LastName] AS Expr1, [Inmate
Records:
All Forms].NameSuffix, [Inmate Records: All Forms].MiddleName, [Inmate
Records: All Forms].[SSNY-N], [Inmate Records: All Forms].InmateSSN,
[Inmate
Records: All Forms].PrimaryAddy, [PrimaryCity] & "," & [PrimaryState]
& "
"
& [PrimaryZipCode] AS Expr2, [Inmate Records: All Forms].PrimaryCity,
[Inmate
Records: All Forms].PrimaryState, [Inmate Records: All
Forms].PrimaryZipCode,
Charges.[Charge Name] AS Charge0, Charges.[Statute Number] AS
Statute0,
IIf([Charges.Charge Name] Is Null,"No Charges",[Charges.Charge Name] &
"
"
&
[Statute0]) AS [Charge0 Name and Statute], Charges_1.[Charge Name] AS
Charge1, Charges_1.[Statute Number] AS Statute1, IIf([Charges 2] Is
Null,"
",[Charges_1.Charge Name] & " , " & [Statute1]) AS [Charge 1 Name and
Statute], Charges_2.[Charge Name] AS Charge2, Charges_2.[Statute
Number]
AS
Statute2, IIf([Charges 3] Is Null," ",[Charges_2.Charge Name] & " , "
&
[Statute2]) AS [Charge 2 Name and Statute], Charges_3.[Charge Name] AS
Charge3, Charges_3.[Statute Number] AS Statute3, Charges_4.[Charge
Name]
AS
Charge4, Charges_4.[Statute Number] AS Statute4, [Booking ID (BID):
All
Items].[Where Arrested], Locations.[City Name], Locations.[Zip Code],
[Radio
ID].[Radio ID], [Radio ID].[Full Name], [Radio ID].[Job Title],
[Arrest
Reports: All Forms].Narrative2, [Arrest Reports: All
Forms].ReportedDateTime,
[Arrest Reports: All Forms].Name, [Booking ID (BID): All Items].[How
Discharged], [Booking ID (BID): All Items].[Discharge Date], [Booking
ID
(BID): All Items]![Discharge Date]-[Booking ID (BID): All Items]![Date
Commited] AS Expr3, [Dispatch Log: All Items].[Event Subject],
[Dispatch
Log:
All Items].[Type of Call], [Dispatch Log: All Items].[Description of
Call],
[Dispatch Log: All Items].[Created By], [Dispatch Log: All
Items].Date,
[Dispatch Log: All Items].Time, [Dispatch Log: All Items].[Radio ID],
[Evidence Forms: All Documents].Name AS [Evidence File Name],
IIf([Evidence
Forms: All Documents.Name] Is Null,"No Evidence File on
Record",[Evidence
File Name]) AS [Evidence File Name if True], "" AS [Narrative
Section],
[Arrest Reports: All Forms].NarrativeBuilderText AS [Narrative Builder
NARRATIVE TEXT], Replace([Arrest Reports: All
Forms]!NarrativeBuilderText,Chr(10),Chr(13) & Chr(10)) AS [Narrative
Builder
REPLACE]
FROM (((((([Arrest Reports: All Forms] LEFT JOIN (((((([Booking ID
(BID):
All Items] LEFT JOIN [Inmate Records: All Forms] ON [Booking ID (BID):
All
Items].[Inmate Record (ISSN)] = [Inmate Records: All Forms].ID) LEFT
JOIN
Charges ON [Booking ID (BID): All Items].Charges = Charges.ID) LEFT
JOIN
Charges AS Charges_1 ON [Booking ID (BID): All Items].[Charges 2] =
Charges_1.ID) LEFT JOIN Charges AS Charges_2 ON [Booking ID (BID): All
Items].[Charges 3] = Charges_2.ID) LEFT JOIN Locations ON [Booking ID
(BID):
All Items].[Location ID] = Locations.ID) LEFT JOIN [Radio ID] ON
[Booking
ID
(BID): All Items].[Arresting Officer] = [Radio ID].ID) ON [Arrest
Reports:
All Forms].Person1Bid = [Booking ID (BID): All Items].ID) LEFT JOIN
[Dispatch
Log: All Items] ON [Booking ID (BID): All Items].ID = [Dispatch Log:
All
Items].[BID Reference]) LEFT JOIN Charges AS Charges_3 ON [Booking ID
(BID):
All Items].[Charges 4] = Charges_3.ID) LEFT JOIN Charges AS Charges_4
ON
[Booking ID (BID): All Items].[Charges 5] = Charges_4.ID) LEFT JOIN
Charges
AS Charges_5 ON [Booking ID (BID): All Items].[Charges 6] =
Charges_5.ID)
LEFT JOIN Charges AS Charges_6 ON [Booking ID (BID): All
Items].[Charges
7] =
Charges_6.ID) LEFT JOIN [Evidence Forms: All Documents] ON [Booking ID
(BID):
All Items].ID = [Evidence Forms: All Documents].[BID Reference]
WHERE ((([Arrest Reports: All Forms].Person1Bid)=[BID]));


Again, thank you for your experience in this situation!

Blaze


:

Hmm.. I don't have experience with SharePoint, but let's see the SQL
statement that you're using and let's see what we might suggest...

--

Ken Snell
<MS ACCESS MVP>

I have a Query that runs and it grabs information from about 7-10
tables
(sharepoint based linked tables). This query is a somewhat simple
query
that
has quite a few IIF statements. The name is called "Arrest Report
Generator". This then creates a report called "Arrest Report
Generator".

The Report runs without issue and works fine asking a basic question
to
pick
the right report out of a table. It then populates the report
correctly
running the Query "Arrest Report Generator".

Now if I run the Query from outside the Design, it gives me the
error
""The
Microsoft Jet database engine could not find the object ". Make
sure
the
object exists and that you spell its name and the path name
correctly.
(Error
3011)"

I am trying to edit the Query and when I click the "!" to temp run
the
Query, it asks the basic question and then gives an error stating:

"The Microsoft Jet database engine could not find the object 'Arrest
Report
Generator'. Make sure the object exists and that you spell its name
and
the
path name correctly. (Error 3011)"

Do you still need SQL Syntax?

Blaze



:

Define "won't run by itself" vs. "the report runs fine".... what
are
you
running as a query? what is the query's relationship to the report?
what
is
the query's SQL statement? etc. need more details, please....

--

Ken Snell
<MS ACCESS MVP>


I have a Report that runs fine, but the query will not "run" by
itself.

Any ideas out there.

Thanks,

Blaze

 

[Ken Snell [MVP]]

I was wondering why you were joining each copy of the Charges table through
another copy in your query, but figured it was working for you.

What you need to do is change the joins so that the original Charges table
and all the copies are joining on the same table ("Booking ID (BID): All
Items") and not on each other. With your setup, if any one of the
"in-between" copies of the Charges table does not have a matching record,
all the copies beyond it are not going to be linked in the query and no
records from them will be shown.


--

Ken Snell
<MS ACCESS MVP>

That is correct. I try through the Access/Query section to run the query,
as
I am still in development, and get this error. If I run the Query via the
Report, it works.

Now I have new developments in the issue that may help someone that
understands this a little better...

I decided to recreate the query using another query name. Because I am
developing, I had to do something for the time being, incase this can't be
resolved (gosh, I hope it can!)

I setup the query with a simple get name from inmate record, get charges.
Since I knew the last issue was caused by the charges table, somehow. I
get
the same error. Now if I reduce the query to only show Charge 1 & 2 /
Statute 1 & 2, it runs, but I don't get any data in my query. I do have
it
setup to filter by a BID number, which is my unique identifier for all new
cases. When I run the report on a BID with only 1 Charge, and the Query
only
references 1 charge, it works fine, without issue. If I have a report
with
only 1 charge and Query 2 charges, it gives me no data. I think this may
be
part of the issue, but I can't understand why it would care if there
weren't
other charges.

So this brings up the question... Since reports have a possible 6 charges
they can put on a report, and most of the time it will be 1 or 2 charges,
how
do I deal with that? Is this a situation where I would setup it's own
query
to pull and deal with the missing charges?

Thanks again!

Blaze

Well, this is most puzzling.

I am not sure what to ask next... I don't know if it has anything to do
with
using SharePoint.

I don't see any functions being called that might have the object
reference
that the query seeks; I don't see any references to that object in the
query; I don't see any parameters that might be seeking that object for
data.

Just to confirm my previous understanding, you see the error when you try
to
run the query from the database window, and not from any form, right?

--

Ken Snell
<MS ACCESS MVP>

Charges is a linked table via Sharepoint.

Blaze

:

One query can use another query as a "table" -- just as if it existed
as
a
permanent table. The query will run the other query in order to get
its
records and use them as data source.

OK - post the SQL statement of Charges query. I'm betting that what
we're
seeking is in there....

--

Ken Snell
<MS ACCESS MVP>


Charges is just another Query. I didn't know I could use a Query to
run a
Query. That's an interesting addition to the utility.

Sorry for the wrong SQL Statement.

Blaze

****

Ahhh.. this is a different query than the one you posted on
8/31/2005.

OK - you said that, if you remove the "Charges" portion of the
query,
it
works without giving you the error. So, is Charges a table, or is it
a
query? If it's a query, what is its SQL statement? Somewhere in the
Charges
portion, the query thinks it needs to use some reference to that
Arrest
Report Generator object.
--

Ken Snell
<MS ACCESS MVP>



Well, I thought I posted the correct thing. Here's what I did. In
the
Query
that I'm having issues with, I clicked "Design View".

From there I click the View, SQL View, thus showing the SQL Query.
Here
it
is again. Thank you for hanging in there, because it makes
absolutly
NO
sense to me how this can happen. But as I am sure you are aware
of,
it
is
near impossible to develop if you can run the query to verify
whether
it
works or not.

SELECT [Booking ID (BID): All Items].ID, [Arrest Reports: All
Forms].Person1Bid, [Booking ID (BID): All Items].[Full Name (First
Last)],
[Inmate Records: All Forms].DOB, [Inmate Records: All
Forms]!DLState &
"
"
&
[Inmate Records: All Forms]!DLNumber AS Expr4, [Inmate Records: All
Forms].PhoneNumber, [FirstName] & " " & [LastName] AS Expr1,
[Inmate
Records:
All Forms].NameSuffix, [Inmate Records: All Forms].MiddleName,
[Inmate
Records: All Forms].[SSNY-N], [Inmate Records: All
Forms].InmateSSN,
[Inmate
Records: All Forms].PrimaryAddy, [PrimaryCity] & "," &
[PrimaryState]
& "
"
& [PrimaryZipCode] AS Expr2, [Inmate Records: All
Forms].PrimaryCity,
[Inmate
Records: All Forms].PrimaryState, [Inmate Records: All
Forms].PrimaryZipCode,
Charges.[Charge Name] AS Charge0, Charges.[Statute Number] AS
Statute0,
IIf([Charges.Charge Name] Is Null,"No Charges",[Charges.Charge
Name] &
"
"
&
[Statute0]) AS [Charge0 Name and Statute], Charges_1.[Charge Name]
AS
Charge1, Charges_1.[Statute Number] AS Statute1, IIf([Charges 2] Is
Null,"
",[Charges_1.Charge Name] & " , " & [Statute1]) AS [Charge 1 Name
and
Statute], Charges_2.[Charge Name] AS Charge2, Charges_2.[Statute
Number]
AS
Statute2, IIf([Charges 3] Is Null," ",[Charges_2.Charge Name] & " ,
"
&
[Statute2]) AS [Charge 2 Name and Statute], Charges_3.[Charge Name]
AS
Charge3, Charges_3.[Statute Number] AS Statute3, Charges_4.[Charge
Name]
AS
Charge4, Charges_4.[Statute Number] AS Statute4, [Booking ID (BID):
All
Items].[Where Arrested], Locations.[City Name], Locations.[Zip
Code],
[Radio
ID].[Radio ID], [Radio ID].[Full Name], [Radio ID].[Job Title],
[Arrest
Reports: All Forms].Narrative2, [Arrest Reports: All
Forms].ReportedDateTime,
[Arrest Reports: All Forms].Name, [Booking ID (BID): All
Items].[How
Discharged], [Booking ID (BID): All Items].[Discharge Date],
[Booking
ID
(BID): All Items]![Discharge Date]-[Booking ID (BID): All
Items]![Date
Commited] AS Expr3, [Dispatch Log: All Items].[Event Subject],
[Dispatch
Log:
All Items].[Type of Call], [Dispatch Log: All Items].[Description
of
Call],
[Dispatch Log: All Items].[Created By], [Dispatch Log: All
Items].Date,
[Dispatch Log: All Items].Time, [Dispatch Log: All Items].[Radio
ID],
[Evidence Forms: All Documents].Name AS [Evidence File Name],
IIf([Evidence
Forms: All Documents.Name] Is Null,"No Evidence File on
Record",[Evidence
File Name]) AS [Evidence File Name if True], "" AS [Narrative
Section],
[Arrest Reports: All Forms].NarrativeBuilderText AS [Narrative
Builder
NARRATIVE TEXT], Replace([Arrest Reports: All
Forms]!NarrativeBuilderText,Chr(10),Chr(13) & Chr(10)) AS
[Narrative
Builder
REPLACE]
FROM (((((([Arrest Reports: All Forms] LEFT JOIN (((((([Booking ID
(BID):
All Items] LEFT JOIN [Inmate Records: All Forms] ON [Booking ID
(BID):
All
Items].[Inmate Record (ISSN)] = [Inmate Records: All Forms].ID)
LEFT
JOIN
Charges ON [Booking ID (BID): All Items].Charges = Charges.ID) LEFT
JOIN
Charges AS Charges_1 ON [Booking ID (BID): All Items].[Charges 2] =
Charges_1.ID) LEFT JOIN Charges AS Charges_2 ON [Booking ID (BID):
All
Items].[Charges 3] = Charges_2.ID) LEFT JOIN Locations ON [Booking
ID
(BID):
All Items].[Location ID] = Locations.ID) LEFT JOIN [Radio ID] ON
[Booking
ID
(BID): All Items].[Arresting Officer] = [Radio ID].ID) ON [Arrest
Reports:
All Forms].Person1Bid = [Booking ID (BID): All Items].ID) LEFT JOIN
[Dispatch
Log: All Items] ON [Booking ID (BID): All Items].ID = [Dispatch
Log:
All
Items].[BID Reference]) LEFT JOIN Charges AS Charges_3 ON [Booking
ID
(BID):
All Items].[Charges 4] = Charges_3.ID) LEFT JOIN Charges AS
Charges_4
ON
[Booking ID (BID): All Items].[Charges 5] = Charges_4.ID) LEFT JOIN
Charges
AS Charges_5 ON [Booking ID (BID): All Items].[Charges 6] =
Charges_5.ID)
LEFT JOIN Charges AS Charges_6 ON [Booking ID (BID): All
Items].[Charges
7] =
Charges_6.ID) LEFT JOIN [Evidence Forms: All Documents] ON [Booking
ID
(BID):
All Items].ID = [Evidence Forms: All Documents].[BID Reference]
WHERE ((([Arrest Reports: All Forms].Person1Bid)=[BID]));


Again, thank you for your experience in this situation!

Blaze


:

Hmm.. I don't have experience with SharePoint, but let's see the
SQL
statement that you're using and let's see what we might suggest...

--

Ken Snell
<MS ACCESS MVP>

I have a Query that runs and it grabs information from about 7-10
tables
(sharepoint based linked tables). This query is a somewhat
simple
query
that
has quite a few IIF statements. The name is called "Arrest
Report
Generator". This then creates a report called "Arrest Report
Generator".

The Report runs without issue and works fine asking a basic
question
to
pick
the right report out of a table. It then populates the report
correctly
running the Query "Arrest Report Generator".

Now if I run the Query from outside the Design, it gives me the
error
""The
Microsoft Jet database engine could not find the object ". Make
sure
the
object exists and that you spell its name and the path name
correctly.
(Error
3011)"

I am trying to edit the Query and when I click the "!" to temp
run
the
Query, it asks the basic question and then gives an error
stating:

"The Microsoft Jet database engine could not find the object
'Arrest
Report
Generator'. Make sure the object exists and that you spell its
name
and
the
path name correctly. (Error 3011)"

Do you still need SQL Syntax?

Blaze



:

Define "won't run by itself" vs. "the report runs fine".... what
are
you
running as a query? what is the query's relationship to the
report?
what
is
the query's SQL statement? etc. need more details, please....

--

Ken Snell
<MS ACCESS MVP>


I have a Report that runs fine, but the query will not "run" by
itself.

Any ideas out there.

Thanks,

Blaze

 

[Blaze]

I see. I saw the "Join" command in the Query, but I have no clue either, how
it got there, or what it does. This was done by access via a "drag n drop"
from the table. I don't understand what you are saying about "joining on the
same table". Do I get rid of the Joins? Do I need them? Are they there
because I have more than 1 copy of a table on the record? Would the JOINS
slow down my Query response?

Thanks and sorry about my stupidity,

Blaze

I was wondering why you were joining each copy of the Charges table through
another copy in your query, but figured it was working for you.

What you need to do is change the joins so that the original Charges table
and all the copies are joining on the same table ("Booking ID (BID): All
Items") and not on each other. With your setup, if any one of the
"in-between" copies of the Charges table does not have a matching record,
all the copies beyond it are not going to be linked in the query and no
records from them will be shown.


--

Ken Snell
<MS ACCESS MVP>

That is correct. I try through the Access/Query section to run the query,
as
I am still in development, and get this error. If I run the Query via the
Report, it works.

Now I have new developments in the issue that may help someone that
understands this a little better...

I decided to recreate the query using another query name. Because I am
developing, I had to do something for the time being, incase this can't be
resolved (gosh, I hope it can!)

I setup the query with a simple get name from inmate record, get charges.
Since I knew the last issue was caused by the charges table, somehow. I
get
the same error. Now if I reduce the query to only show Charge 1 & 2 /
Statute 1 & 2, it runs, but I don't get any data in my query. I do have
it
setup to filter by a BID number, which is my unique identifier for all new
cases. When I run the report on a BID with only 1 Charge, and the Query
only
references 1 charge, it works fine, without issue. If I have a report
with
only 1 charge and Query 2 charges, it gives me no data. I think this may
be
part of the issue, but I can't understand why it would care if there
weren't
other charges.

So this brings up the question... Since reports have a possible 6 charges
they can put on a report, and most of the time it will be 1 or 2 charges,
how
do I deal with that? Is this a situation where I would setup it's own
query
to pull and deal with the missing charges?

Thanks again!

Blaze

Well, this is most puzzling.

I am not sure what to ask next... I don't know if it has anything to do
with
using SharePoint.

I don't see any functions being called that might have the object
reference
that the query seeks; I don't see any references to that object in the
query; I don't see any parameters that might be seeking that object for
data.

Just to confirm my previous understanding, you see the error when you try
to
run the query from the database window, and not from any form, right?

--

Ken Snell
<MS ACCESS MVP>


Charges is a linked table via Sharepoint.

Blaze

:

One query can use another query as a "table" -- just as if it existed
as
a
permanent table. The query will run the other query in order to get
its
records and use them as data source.

OK - post the SQL statement of Charges query. I'm betting that what
we're
seeking is in there....

--

Ken Snell
<MS ACCESS MVP>


Charges is just another Query. I didn't know I could use a Query to
run a
Query. That's an interesting addition to the utility.

Sorry for the wrong SQL Statement.

Blaze

****

Ahhh.. this is a different query than the one you posted on
8/31/2005.

OK - you said that, if you remove the "Charges" portion of the
query,
it
works without giving you the error. So, is Charges a table, or is it
a
query? If it's a query, what is its SQL statement? Somewhere in the
Charges
portion, the query thinks it needs to use some reference to that
Arrest
Report Generator object.
--

Ken Snell
<MS ACCESS MVP>



Well, I thought I posted the correct thing. Here's what I did. In
the
Query
that I'm having issues with, I clicked "Design View".

From there I click the View, SQL View, thus showing the SQL Query.
Here
it
is again. Thank you for hanging in there, because it makes
absolutly
NO
sense to me how this can happen. But as I am sure you are aware
of,
it
is
near impossible to develop if you can run the query to verify
whether
it
works or not.

SELECT [Booking ID (BID): All Items].ID, [Arrest Reports: All
Forms].Person1Bid, [Booking ID (BID): All Items].[Full Name (First
Last)],
[Inmate Records: All Forms].DOB, [Inmate Records: All
Forms]!DLState &
"
"
&
[Inmate Records: All Forms]!DLNumber AS Expr4, [Inmate Records: All
Forms].PhoneNumber, [FirstName] & " " & [LastName] AS Expr1,
[Inmate
Records:
All Forms].NameSuffix, [Inmate Records: All Forms].MiddleName,
[Inmate
Records: All Forms].[SSNY-N], [Inmate Records: All
Forms].InmateSSN,
[Inmate
Records: All Forms].PrimaryAddy, [PrimaryCity] & "," &
[PrimaryState]
& "
"
& [PrimaryZipCode] AS Expr2, [Inmate Records: All
Forms].PrimaryCity,
[Inmate
Records: All Forms].PrimaryState, [Inmate Records: All
Forms].PrimaryZipCode,
Charges.[Charge Name] AS Charge0, Charges.[Statute Number] AS
Statute0,
IIf([Charges.Charge Name] Is Null,"No Charges",[Charges.Charge
Name] &
"
"
&
[Statute0]) AS [Charge0 Name and Statute], Charges_1.[Charge Name]
AS
Charge1, Charges_1.[Statute Number] AS Statute1, IIf([Charges 2] Is
Null,"
",[Charges_1.Charge Name] & " , " & [Statute1]) AS [Charge 1 Name
and
Statute], Charges_2.[Charge Name] AS Charge2, Charges_2.[Statute
Number]
AS
Statute2, IIf([Charges 3] Is Null," ",[Charges_2.Charge Name] & " ,
"
&
[Statute2]) AS [Charge 2 Name and Statute], Charges_3.[Charge Name]
AS
Charge3, Charges_3.[Statute Number] AS Statute3, Charges_4.[Charge
Name]
AS
Charge4, Charges_4.[Statute Number] AS Statute4, [Booking ID (BID):
All
Items].[Where Arrested], Locations.[City Name], Locations.[Zip
Code],
[Radio
ID].[Radio ID], [Radio ID].[Full Name], [Radio ID].[Job Title],
[Arrest
Reports: All Forms].Narrative2, [Arrest Reports: All
Forms].ReportedDateTime,
[Arrest Reports: All Forms].Name, [Booking ID (BID): All
Items].[How
Discharged], [Booking ID (BID): All Items].[Discharge Date],
[Booking
ID
(BID): All Items]![Discharge Date]-[Booking ID (BID): All
Items]![Date
Commited] AS Expr3, [Dispatch Log: All Items].[Event Subject],
[Dispatch
Log:
All Items].[Type of Call], [Dispatch Log: All Items].[Description
of
Call],
[Dispatch Log: All Items].[Created By], [Dispatch Log: All
Items].Date,
[Dispatch Log: All Items].Time, [Dispatch Log: All Items].[Radio
ID],
[Evidence Forms: All Documents].Name AS [Evidence File Name],
IIf([Evidence
Forms: All Documents.Name] Is Null,"No Evidence File on
Record",[Evidence
File Name]) AS [Evidence File Name if True], "" AS [Narrative
Section],
[Arrest Reports: All Forms].NarrativeBuilderText AS [Narrative
Builder
NARRATIVE TEXT], Replace([Arrest Reports: All
Forms]!NarrativeBuilderText,Chr(10),Chr(13) & Chr(10)) AS
[Narrative
Builder
REPLACE]
FROM (((((([Arrest Reports: All Forms] LEFT JOIN (((((([Booking ID
(BID):
All Items] LEFT JOIN [Inmate Records: All Forms] ON [Booking ID
(BID):
All
Items].[Inmate Record (ISSN)] = [Inmate Records: All Forms].ID)
LEFT
JOIN
Charges ON [Booking ID (BID): All Items].Charges = Charges.ID) LEFT
JOIN
Charges AS Charges_1 ON [Booking ID (BID): All Items].[Charges 2] =
Charges_1.ID) LEFT JOIN Charges AS Charges_2 ON [Booking ID (BID):
All
Items].[Charges 3] = Charges_2.ID) LEFT JOIN Locations ON [Booking
ID
(BID):
All Items].[Location ID] = Locations.ID) LEFT JOIN [Radio ID] ON
[Booking
ID
(BID): All Items].[Arresting Officer] = [Radio ID].ID) ON [Arrest
Reports:
All Forms].Person1Bid = [Booking ID (BID): All Items].ID) LEFT JOIN
[Dispatch
Log: All Items] ON [Booking ID (BID): All Items].ID = [Dispatch
Log:
All
Items].[BID Reference]) LEFT JOIN Charges AS Charges_3 ON [Booking
ID
(BID):
All Items].[Charges 4] = Charges_3.ID) LEFT JOIN Charges AS
Charges_4
ON
[Booking ID (BID): All Items].[Charges 5] = Charges_4.ID) LEFT JOIN
Charges
AS Charges_5 ON [Booking ID (BID): All Items].[Charges 6] =
Charges_5.ID)
LEFT JOIN Charges AS Charges_6 ON [Booking ID (BID): All
Items].[Charges
7] =
Charges_6.ID) LEFT JOIN [Evidence Forms: All Documents] ON [Booking
ID
(BID):
All Items].ID = [Evidence Forms: All Documents].[BID Reference]
WHERE ((([Arrest Reports: All Forms].Person1Bid)=[BID]));


Again, thank you for your experience in this situation!

Blaze

 

[Ken Snell [MVP]]

Your SQL statement is pretty "monstrous" so I'm really hesitant about trying
to rewrite it in text.

When you view the query in the query's design view, what you'll see are a
number of "lines" between the copies of the Charges table. What you need to
do is to delete those lines between the Charges tables, and instead create a
join between each copy back to the "Charges" field in the "Booking ID (BID):
All Items" table.

Joins can slow a query, but they are the way you find related records for
the query to display / select -- very integral to a query's functionality.

--

Ken Snell
<MS ACCESS MVP>

I see. I saw the "Join" command in the Query, but I have no clue either,
how
it got there, or what it does. This was done by access via a "drag n
drop"
from the table. I don't understand what you are saying about "joining on
the
same table". Do I get rid of the Joins? Do I need them? Are they there
because I have more than 1 copy of a table on the record? Would the JOINS
slow down my Query response?

Thanks and sorry about my stupidity,

Blaze

I was wondering why you were joining each copy of the Charges table
through
another copy in your query, but figured it was working for you.

What you need to do is change the joins so that the original Charges
table
and all the copies are joining on the same table ("Booking ID (BID): All
Items") and not on each other. With your setup, if any one of the
"in-between" copies of the Charges table does not have a matching record,
all the copies beyond it are not going to be linked in the query and no
records from them will be shown.


--

Ken Snell
<MS ACCESS MVP>

That is correct. I try through the Access/Query section to run the
query,
as
I am still in development, and get this error. If I run the Query via
the
Report, it works.

Now I have new developments in the issue that may help someone that
understands this a little better...

I decided to recreate the query using another query name. Because I am
developing, I had to do something for the time being, incase this can't
be
resolved (gosh, I hope it can!)

I setup the query with a simple get name from inmate record, get
charges.
Since I knew the last issue was caused by the charges table, somehow.
I
get
the same error. Now if I reduce the query to only show Charge 1 & 2 /
Statute 1 & 2, it runs, but I don't get any data in my query. I do
have
it
setup to filter by a BID number, which is my unique identifier for all
new
cases. When I run the report on a BID with only 1 Charge, and the
Query
only
references 1 charge, it works fine, without issue. If I have a report
with
only 1 charge and Query 2 charges, it gives me no data. I think this
may
be
part of the issue, but I can't understand why it would care if there
weren't
other charges.

So this brings up the question... Since reports have a possible 6
charges
they can put on a report, and most of the time it will be 1 or 2
charges,
how
do I deal with that? Is this a situation where I would setup it's own
query
to pull and deal with the missing charges?

Thanks again!

Blaze

:

Well, this is most puzzling.

I am not sure what to ask next... I don't know if it has anything to
do
with
using SharePoint.

I don't see any functions being called that might have the object
reference
that the query seeks; I don't see any references to that object in the
query; I don't see any parameters that might be seeking that object
for
data.

Just to confirm my previous understanding, you see the error when you
try
to
run the query from the database window, and not from any form, right?

--

Ken Snell
<MS ACCESS MVP>


Charges is a linked table via Sharepoint.

Blaze

:

One query can use another query as a "table" -- just as if it
existed
as
a
permanent table. The query will run the other query in order to get
its
records and use them as data source.

OK - post the SQL statement of Charges query. I'm betting that what
we're
seeking is in there....

--

Ken Snell
<MS ACCESS MVP>


Charges is just another Query. I didn't know I could use a Query
to
run a
Query. That's an interesting addition to the utility.

Sorry for the wrong SQL Statement.

Blaze

****

Ahhh.. this is a different query than the one you posted on
8/31/2005.

OK - you said that, if you remove the "Charges" portion of the
query,
it
works without giving you the error. So, is Charges a table, or is
it
a
query? If it's a query, what is its SQL statement? Somewhere in
the
Charges
portion, the query thinks it needs to use some reference to that
Arrest
Report Generator object.
--

Ken Snell
<MS ACCESS MVP>



Well, I thought I posted the correct thing. Here's what I did.
In
the
Query
that I'm having issues with, I clicked "Design View".

From there I click the View, SQL View, thus showing the SQL
Query.
Here
it
is again. Thank you for hanging in there, because it makes
absolutly
NO
sense to me how this can happen. But as I am sure you are aware
of,
it
is
near impossible to develop if you can run the query to verify
whether
it
works or not.

SELECT [Booking ID (BID): All Items].ID, [Arrest Reports: All
Forms].Person1Bid, [Booking ID (BID): All Items].[Full Name
(First
Last)],
[Inmate Records: All Forms].DOB, [Inmate Records: All
Forms]!DLState &
"
"
&
[Inmate Records: All Forms]!DLNumber AS Expr4, [Inmate Records:
All
Forms].PhoneNumber, [FirstName] & " " & [LastName] AS Expr1,
[Inmate
Records:
All Forms].NameSuffix, [Inmate Records: All Forms].MiddleName,
[Inmate
Records: All Forms].[SSNY-N], [Inmate Records: All
Forms].InmateSSN,
[Inmate
Records: All Forms].PrimaryAddy, [PrimaryCity] & "," &
[PrimaryState]
& "
"
& [PrimaryZipCode] AS Expr2, [Inmate Records: All
Forms].PrimaryCity,
[Inmate
Records: All Forms].PrimaryState, [Inmate Records: All
Forms].PrimaryZipCode,
Charges.[Charge Name] AS Charge0, Charges.[Statute Number] AS
Statute0,
IIf([Charges.Charge Name] Is Null,"No Charges",[Charges.Charge
Name] &
"
"
&
[Statute0]) AS [Charge0 Name and Statute], Charges_1.[Charge
Name]
AS
Charge1, Charges_1.[Statute Number] AS Statute1, IIf([Charges 2]
Is
Null,"
",[Charges_1.Charge Name] & " , " & [Statute1]) AS [Charge 1
Name
and
Statute], Charges_2.[Charge Name] AS Charge2, Charges_2.[Statute
Number]
AS
Statute2, IIf([Charges 3] Is Null," ",[Charges_2.Charge Name] &
" ,
"
&
[Statute2]) AS [Charge 2 Name and Statute], Charges_3.[Charge
Name]
AS
Charge3, Charges_3.[Statute Number] AS Statute3,
Charges_4.[Charge
Name]
AS
Charge4, Charges_4.[Statute Number] AS Statute4, [Booking ID
(BID):
All
Items].[Where Arrested], Locations.[City Name], Locations.[Zip
Code],
[Radio
ID].[Radio ID], [Radio ID].[Full Name], [Radio ID].[Job Title],
[Arrest
Reports: All Forms].Narrative2, [Arrest Reports: All
Forms].ReportedDateTime,
[Arrest Reports: All Forms].Name, [Booking ID (BID): All
Items].[How
Discharged], [Booking ID (BID): All Items].[Discharge Date],
[Booking
ID
(BID): All Items]![Discharge Date]-[Booking ID (BID): All
Items]![Date
Commited] AS Expr3, [Dispatch Log: All Items].[Event Subject],
[Dispatch
Log:
All Items].[Type of Call], [Dispatch Log: All
Items].[Description
of
Call],
[Dispatch Log: All Items].[Created By], [Dispatch Log: All
Items].Date,
[Dispatch Log: All Items].Time, [Dispatch Log: All Items].[Radio
ID],
[Evidence Forms: All Documents].Name AS [Evidence File Name],
IIf([Evidence
Forms: All Documents.Name] Is Null,"No Evidence File on
Record",[Evidence
File Name]) AS [Evidence File Name if True], "" AS [Narrative
Section],
[Arrest Reports: All Forms].NarrativeBuilderText AS [Narrative
Builder
NARRATIVE TEXT], Replace([Arrest Reports: All
Forms]!NarrativeBuilderText,Chr(10),Chr(13) & Chr(10)) AS
[Narrative
Builder
REPLACE]
FROM (((((([Arrest Reports: All Forms] LEFT JOIN (((((([Booking
ID
(BID):
All Items] LEFT JOIN [Inmate Records: All Forms] ON [Booking ID
(BID):
All
Items].[Inmate Record (ISSN)] = [Inmate Records: All Forms].ID)
LEFT
JOIN
Charges ON [Booking ID (BID): All Items].Charges = Charges.ID)
LEFT
JOIN
Charges AS Charges_1 ON [Booking ID (BID): All Items].[Charges
2] =
Charges_1.ID) LEFT JOIN Charges AS Charges_2 ON [Booking ID
(BID):
All
Items].[Charges 3] = Charges_2.ID) LEFT JOIN Locations ON
[Booking
ID
(BID):
All Items].[Location ID] = Locations.ID) LEFT JOIN [Radio ID] ON
[Booking
ID
(BID): All Items].[Arresting Officer] = [Radio ID].ID) ON
[Arrest
Reports:
All Forms].Person1Bid = [Booking ID (BID): All Items].ID) LEFT
JOIN
[Dispatch
Log: All Items] ON [Booking ID (BID): All Items].ID = [Dispatch
Log:
All
Items].[BID Reference]) LEFT JOIN Charges AS Charges_3 ON
[Booking
ID
(BID):
All Items].[Charges 4] = Charges_3.ID) LEFT JOIN Charges AS
Charges_4
ON
[Booking ID (BID): All Items].[Charges 5] = Charges_4.ID) LEFT
JOIN
Charges
AS Charges_5 ON [Booking ID (BID): All Items].[Charges 6] =
Charges_5.ID)
LEFT JOIN Charges AS Charges_6 ON [Booking ID (BID): All
Items].[Charges
7] =
Charges_6.ID) LEFT JOIN [Evidence Forms: All Documents] ON
[Booking
ID
(BID):
All Items].ID = [Evidence Forms: All Documents].[BID Reference]
WHERE ((([Arrest Reports: All Forms].Person1Bid)=[BID]));


Again, thank you for your experience in this situation!

Blaze

 

[Blaze]

So just to make sure that I understand what you mean...

Instead of dragging from the "charges" table to the "BID" table, I need to
draw the lines from the "BID" table to the "Charges" table?

Do I still use a "type 2" join?

Sorry, I'm somewhat a beginner at this stuff, but everything I am doing has
gone so well until this little hickup..

Blaze

Your SQL statement is pretty "monstrous" so I'm really hesitant about trying
to rewrite it in text.

When you view the query in the query's design view, what you'll see are a
number of "lines" between the copies of the Charges table. What you need to
do is to delete those lines between the Charges tables, and instead create a
join between each copy back to the "Charges" field in the "Booking ID (BID):
All Items" table.

Joins can slow a query, but they are the way you find related records for
the query to display / select -- very integral to a query's functionality.

--

Ken Snell
<MS ACCESS MVP>

I see. I saw the "Join" command in the Query, but I have no clue either,
how
it got there, or what it does. This was done by access via a "drag n
drop"
from the table. I don't understand what you are saying about "joining on
the
same table". Do I get rid of the Joins? Do I need them? Are they there
because I have more than 1 copy of a table on the record? Would the JOINS
slow down my Query response?

Thanks and sorry about my stupidity,

Blaze

I was wondering why you were joining each copy of the Charges table
through
another copy in your query, but figured it was working for you.

What you need to do is change the joins so that the original Charges
table
and all the copies are joining on the same table ("Booking ID (BID): All
Items") and not on each other. With your setup, if any one of the
"in-between" copies of the Charges table does not have a matching record,
all the copies beyond it are not going to be linked in the query and no
records from them will be shown.


--

Ken Snell
<MS ACCESS MVP>

That is correct. I try through the Access/Query section to run the
query,
as
I am still in development, and get this error. If I run the Query via
the
Report, it works.

Now I have new developments in the issue that may help someone that
understands this a little better...

I decided to recreate the query using another query name. Because I am
developing, I had to do something for the time being, incase this can't
be
resolved (gosh, I hope it can!)

I setup the query with a simple get name from inmate record, get
charges.
Since I knew the last issue was caused by the charges table, somehow.
I
get
the same error. Now if I reduce the query to only show Charge 1 & 2 /
Statute 1 & 2, it runs, but I don't get any data in my query. I do
have
it
setup to filter by a BID number, which is my unique identifier for all
new
cases. When I run the report on a BID with only 1 Charge, and the
Query
only
references 1 charge, it works fine, without issue. If I have a report
with
only 1 charge and Query 2 charges, it gives me no data. I think this
may
be
part of the issue, but I can't understand why it would care if there
weren't
other charges.

So this brings up the question... Since reports have a possible 6
charges
they can put on a report, and most of the time it will be 1 or 2
charges,
how
do I deal with that? Is this a situation where I would setup it's own
query
to pull and deal with the missing charges?

Thanks again!

Blaze

:

Well, this is most puzzling.

I am not sure what to ask next... I don't know if it has anything to
do
with
using SharePoint.

I don't see any functions being called that might have the object
reference
that the query seeks; I don't see any references to that object in the
query; I don't see any parameters that might be seeking that object
for
data.

Just to confirm my previous understanding, you see the error when you
try
to
run the query from the database window, and not from any form, right?

--

Ken Snell
<MS ACCESS MVP>


Charges is a linked table via Sharepoint.

Blaze

:

One query can use another query as a "table" -- just as if it
existed
as
a
permanent table. The query will run the other query in order to get
its
records and use them as data source.

OK - post the SQL statement of Charges query. I'm betting that what
we're
seeking is in there....

--

Ken Snell
<MS ACCESS MVP>


Charges is just another Query. I didn't know I could use a Query
to
run a
Query. That's an interesting addition to the utility.

Sorry for the wrong SQL Statement.

Blaze

****

Ahhh.. this is a different query than the one you posted on
8/31/2005.

OK - you said that, if you remove the "Charges" portion of the
query,
it
works without giving you the error. So, is Charges a table, or is
it
a
query? If it's a query, what is its SQL statement? Somewhere in
the
Charges
portion, the query thinks it needs to use some reference to that
Arrest
Report Generator object.
--

Ken Snell
<MS ACCESS MVP>



Well, I thought I posted the correct thing. Here's what I did.
In
the
Query
that I'm having issues with, I clicked "Design View".

From there I click the View, SQL View, thus showing the SQL
Query.
Here
it
is again. Thank you for hanging in there, because it makes
absolutly
NO
sense to me how this can happen. But as I am sure you are aware
of,
it
is
near impossible to develop if you can run the query to verify
whether
it
works or not.

SELECT [Booking ID (BID): All Items].ID, [Arrest Reports: All
Forms].Person1Bid, [Booking ID (BID): All Items].[Full Name
(First
Last)],
[Inmate Records: All Forms].DOB, [Inmate Records: All
Forms]!DLState &
"
"
&
[Inmate Records: All Forms]!DLNumber AS Expr4, [Inmate Records:
All
Forms].PhoneNumber, [FirstName] & " " & [LastName] AS Expr1,
[Inmate
Records:
All Forms].NameSuffix, [Inmate Records: All Forms].MiddleName,
[Inmate
Records: All Forms].[SSNY-N], [Inmate Records: All
Forms].InmateSSN,
[Inmate
Records: All Forms].PrimaryAddy, [PrimaryCity] & "," &
[PrimaryState]
& "
"
& [PrimaryZipCode] AS Expr2, [Inmate Records: All
Forms].PrimaryCity,
[Inmate
Records: All Forms].PrimaryState, [Inmate Records: All
Forms].PrimaryZipCode,
Charges.[Charge Name] AS Charge0, Charges.[Statute Number] AS
Statute0,
IIf([Charges.Charge Name] Is Null,"No Charges",[Charges.Charge
Name] &
"
"
&
[Statute0]) AS [Charge0 Name and Statute], Charges_1.[Charge
Name]
AS
Charge1, Charges_1.[Statute Number] AS Statute1, IIf([Charges 2]
Is
Null,"
",[Charges_1.Charge Name] & " , " & [Statute1]) AS [Charge 1
Name
and
Statute], Charges_2.[Charge Name] AS Charge2, Charges_2.[Statute
Number]
AS
Statute2, IIf([Charges 3] Is Null," ",[Charges_2.Charge Name] &
" ,
"
&
[Statute2]) AS [Charge 2 Name and Statute], Charges_3.[Charge
Name]
AS
Charge3, Charges_3.[Statute Number] AS Statute3,
Charges_4.[Charge
Name]
AS
Charge4, Charges_4.[Statute Number] AS Statute4, [Booking ID
(BID):
All
Items].[Where Arrested], Locations.[City Name], Locations.[Zip
Code],
[Radio
ID].[Radio ID], [Radio ID].[Full Name], [Radio ID].[Job Title],
[Arrest
Reports: All Forms].Narrative2, [Arrest Reports: All
Forms].ReportedDateTime,
[Arrest Reports: All Forms].Name, [Booking ID (BID): All
Items].[How
Discharged], [Booking ID (BID): All Items].[Discharge Date],
[Booking
ID
(BID): All Items]![Discharge Date]-[Booking ID (BID): All
Items]![Date
Commited] AS Expr3, [Dispatch Log: All Items].[Event Subject],
[Dispatch
Log:
All Items].[Type of Call], [Dispatch Log: All

 

[Ken Snell [MVP]]

My eyes are failing me....my apologies. I have reread your query and I see
that the joins are indeed the way I suggested that they should be. Ignore my
note about redoing the joins.

Your description about what happens when you leave out some of the charges
suggests that the query's joins are not what you need/want, but I must admit
that I cannot "see" the full setup in my mind's eye, and am not able to
offer specific advice.

I'd suggest that I look at the database in person but I doubt that the
database will work when it's separated from your SharePoint tables, so that
is not likely going to work.

I don't know what else to suggest from this vantage point, I'm sorry.
--

Ken Snell
<MS ACCESS MVP>

So just to make sure that I understand what you mean...

Instead of dragging from the "charges" table to the "BID" table, I need to
draw the lines from the "BID" table to the "Charges" table?

Do I still use a "type 2" join?

Sorry, I'm somewhat a beginner at this stuff, but everything I am doing
has
gone so well until this little hickup..

Blaze

Your SQL statement is pretty "monstrous" so I'm really hesitant about
trying
to rewrite it in text.

When you view the query in the query's design view, what you'll see are a
number of "lines" between the copies of the Charges table. What you need
to
do is to delete those lines between the Charges tables, and instead
create a
join between each copy back to the "Charges" field in the "Booking ID
(BID):
All Items" table.

Joins can slow a query, but they are the way you find related records for
the query to display / select -- very integral to a query's
functionality.

--

Ken Snell
<MS ACCESS MVP>

I see. I saw the "Join" command in the Query, but I have no clue
either,
how
it got there, or what it does. This was done by access via a "drag n
drop"
from the table. I don't understand what you are saying about "joining
on
the
same table". Do I get rid of the Joins? Do I need them? Are they
there
because I have more than 1 copy of a table on the record? Would the
JOINS
slow down my Query response?

Thanks and sorry about my stupidity,

Blaze


:

I was wondering why you were joining each copy of the Charges table
through
another copy in your query, but figured it was working for you.

What you need to do is change the joins so that the original Charges
table
and all the copies are joining on the same table ("Booking ID (BID):
All
Items") and not on each other. With your setup, if any one of the
"in-between" copies of the Charges table does not have a matching
record,
all the copies beyond it are not going to be linked in the query and
no
records from them will be shown.


--

Ken Snell
<MS ACCESS MVP>

That is correct. I try through the Access/Query section to run the
query,
as
I am still in development, and get this error. If I run the Query
via
the
Report, it works.

Now I have new developments in the issue that may help someone that
understands this a little better...

I decided to recreate the query using another query name. Because I
am
developing, I had to do something for the time being, incase this
can't
be
resolved (gosh, I hope it can!)

I setup the query with a simple get name from inmate record, get
charges.
Since I knew the last issue was caused by the charges table,
somehow.
I
get
the same error. Now if I reduce the query to only show Charge 1 & 2
/
Statute 1 & 2, it runs, but I don't get any data in my query. I do
have
it
setup to filter by a BID number, which is my unique identifier for
all
new
cases. When I run the report on a BID with only 1 Charge, and the
Query
only
references 1 charge, it works fine, without issue. If I have a
report
with
only 1 charge and Query 2 charges, it gives me no data. I think
this
may
be
part of the issue, but I can't understand why it would care if there
weren't
other charges.

So this brings up the question... Since reports have a possible 6
charges
they can put on a report, and most of the time it will be 1 or 2
charges,
how
do I deal with that? Is this a situation where I would setup it's
own
query
to pull and deal with the missing charges?

Thanks again!

Blaze

:

Well, this is most puzzling.

I am not sure what to ask next... I don't know if it has anything
to
do
with
using SharePoint.

I don't see any functions being called that might have the object
reference
that the query seeks; I don't see any references to that object in
the
query; I don't see any parameters that might be seeking that object
for
data.

Just to confirm my previous understanding, you see the error when
you
try
to
run the query from the database window, and not from any form,
right?

--

Ken Snell
<MS ACCESS MVP>


Charges is a linked table via Sharepoint.

Blaze

:

One query can use another query as a "table" -- just as if it
existed
as
a
permanent table. The query will run the other query in order to
get
its
records and use them as data source.

OK - post the SQL statement of Charges query. I'm betting that
what
we're
seeking is in there....

--

Ken Snell
<MS ACCESS MVP>


Charges is just another Query. I didn't know I could use a
Query
to
run a
Query. That's an interesting addition to the utility.

Sorry for the wrong SQL Statement.

Blaze

****

Ahhh.. this is a different query than the one you posted on
8/31/2005.

OK - you said that, if you remove the "Charges" portion of the
query,
it
works without giving you the error. So, is Charges a table, or
is
it
a
query? If it's a query, what is its SQL statement? Somewhere
in
the
Charges
portion, the query thinks it needs to use some reference to
that
Arrest
Report Generator object.
--

Ken Snell
<MS ACCESS MVP>



Well, I thought I posted the correct thing. Here's what I
did.
In
the
Query
that I'm having issues with, I clicked "Design View".

From there I click the View, SQL View, thus showing the SQL
Query.
Here
it
is again. Thank you for hanging in there, because it makes
absolutly
NO
sense to me how this can happen. But as I am sure you are
aware
of,
it
is
near impossible to develop if you can run the query to verify
whether
it
works or not.

SELECT [Booking ID (BID): All Items].ID, [Arrest Reports: All
Forms].Person1Bid, [Booking ID (BID): All Items].[Full Name
(First
Last)],
[Inmate Records: All Forms].DOB, [Inmate Records: All
Forms]!DLState &
"
"
&
[Inmate Records: All Forms]!DLNumber AS Expr4, [Inmate
Records:
All
Forms].PhoneNumber, [FirstName] & " " & [LastName] AS Expr1,
[Inmate
Records:
All Forms].NameSuffix, [Inmate Records: All
Forms].MiddleName,
[Inmate
Records: All Forms].[SSNY-N], [Inmate Records: All
Forms].InmateSSN,
[Inmate
Records: All Forms].PrimaryAddy, [PrimaryCity] & "," &
[PrimaryState]
& "
"
& [PrimaryZipCode] AS Expr2, [Inmate Records: All
Forms].PrimaryCity,
[Inmate
Records: All Forms].PrimaryState, [Inmate Records: All
Forms].PrimaryZipCode,
Charges.[Charge Name] AS Charge0, Charges.[Statute Number] AS
Statute0,
IIf([Charges.Charge Name] Is Null,"No
Charges",[Charges.Charge
Name] &
"
"
&
[Statute0]) AS [Charge0 Name and Statute], Charges_1.[Charge
Name]
AS
Charge1, Charges_1.[Statute Number] AS Statute1, IIf([Charges
2]
Is
Null,"
",[Charges_1.Charge Name] & " , " & [Statute1]) AS [Charge 1
Name
and
Statute], Charges_2.[Charge Name] AS Charge2,
Charges_2.[Statute
Number]
AS
Statute2, IIf([Charges 3] Is Null," ",[Charges_2.Charge Name]
&
" ,
"
&
[Statute2]) AS [Charge 2 Name and Statute], Charges_3.[Charge
Name]
AS
Charge3, Charges_3.[Statute Number] AS Statute3,
Charges_4.[Charge
Name]
AS
Charge4, Charges_4.[Statute Number] AS Statute4, [Booking ID
(BID):
All
Items].[Where Arrested], Locations.[City Name],
Locations.[Zip
Code],
[Radio
ID].[Radio ID], [Radio ID].[Full Name], [Radio ID].[Job
Title],
[Arrest
Reports: All Forms].Narrative2, [Arrest Reports: All
Forms].ReportedDateTime,
[Arrest Reports: All Forms].Name, [Booking ID (BID): All
Items].[How
Discharged], [Booking ID (BID): All Items].[Discharge Date],
[Booking
ID
(BID): All Items]![Discharge Date]-[Booking ID (BID): All
Items]![Date
Commited] AS Expr3, [Dispatch Log: All Items].[Event
Subject],
[Dispatch
Log:
All Items].[Type of Call], [Dispatch Log: All

 

[Blaze]

Yeah, this works really slick in this scenario. I just can't understand what
the Charges has to do with it, if there is more than 2 charges, it just
doesn't work. I will get an arrest with all 6 charges filled out and see
what happens. I will keep this thread posted on any progress, as I hope that
it helps someone to read what has worked and not worked. Do you know if
Access has a limit on the number of "threads" to a certain table before it
fails?

I must suggest that you try to use Access to report against sharepoint data.
With your expertise in Access, I'm sure there isn't much you couldn't do.
The data entry part goes through the web-interface into SQL. From there you
access it via linked tables. Only problem is you can't exactly write to
sharepoint unless you use SOAP (Only read about it, never have tried it).

This project is my first time to actually sit down and use Access to any
good. I created a few little DB's, that I never used, to keep track of
server updates. Now it is a breeze with others entering the data when they
do it, and me having reports to tell me what they did.

This particular instance does a whole different job where I am trying to
develop an application around the Microsoft Office Package w/ Infopath+Access
and Sharepoint Services. So far it has been absolutly awesome, but with my
lack of the useability side of Access, it has been a strugle to develop on a
web-service and learn a DB program. Also, since I have 0, absolutly "0",
knowlege on programming, it has been quite interesting.

I'm sure you'll see some more posts from me, so I hope you can input your
thoughts again!

Thanks for your time and efforts!

Blaze

My eyes are failing me....my apologies. I have reread your query and I see
that the joins are indeed the way I suggested that they should be. Ignore my
note about redoing the joins.

Your description about what happens when you leave out some of the charges
suggests that the query's joins are not what you need/want, but I must admit
that I cannot "see" the full setup in my mind's eye, and am not able to
offer specific advice.

I'd suggest that I look at the database in person but I doubt that the
database will work when it's separated from your SharePoint tables, so that
is not likely going to work.

I don't know what else to suggest from this vantage point, I'm sorry.
--

Ken Snell
<MS ACCESS MVP>

So just to make sure that I understand what you mean...

Instead of dragging from the "charges" table to the "BID" table, I need to
draw the lines from the "BID" table to the "Charges" table?

Do I still use a "type 2" join?

Sorry, I'm somewhat a beginner at this stuff, but everything I am doing
has
gone so well until this little hickup..

Blaze

Your SQL statement is pretty "monstrous" so I'm really hesitant about
trying
to rewrite it in text.

When you view the query in the query's design view, what you'll see are a
number of "lines" between the copies of the Charges table. What you need
to
do is to delete those lines between the Charges tables, and instead
create a
join between each copy back to the "Charges" field in the "Booking ID
(BID):
All Items" table.

Joins can slow a query, but they are the way you find related records for
the query to display / select -- very integral to a query's
functionality.

--

Ken Snell
<MS ACCESS MVP>



I see. I saw the "Join" command in the Query, but I have no clue
either,
how
it got there, or what it does. This was done by access via a "drag n
drop"
from the table. I don't understand what you are saying about "joining
on
the
same table". Do I get rid of the Joins? Do I need them? Are they
there
because I have more than 1 copy of a table on the record? Would the
JOINS
slow down my Query response?

Thanks and sorry about my stupidity,

Blaze


:

I was wondering why you were joining each copy of the Charges table
through
another copy in your query, but figured it was working for you.

What you need to do is change the joins so that the original Charges
table
and all the copies are joining on the same table ("Booking ID (BID):
All
Items") and not on each other. With your setup, if any one of the
"in-between" copies of the Charges table does not have a matching
record,
all the copies beyond it are not going to be linked in the query and
no
records from them will be shown.


--

Ken Snell
<MS ACCESS MVP>

That is correct. I try through the Access/Query section to run the
query,
as
I am still in development, and get this error. If I run the Query
via
the
Report, it works.

Now I have new developments in the issue that may help someone that
understands this a little better...

I decided to recreate the query using another query name. Because I
am
developing, I had to do something for the time being, incase this
can't
be
resolved (gosh, I hope it can!)

I setup the query with a simple get name from inmate record, get
charges.
Since I knew the last issue was caused by the charges table,
somehow.
I
get
the same error. Now if I reduce the query to only show Charge 1 & 2
/
Statute 1 & 2, it runs, but I don't get any data in my query. I do
have
it
setup to filter by a BID number, which is my unique identifier for
all
new
cases. When I run the report on a BID with only 1 Charge, and the
Query
only
references 1 charge, it works fine, without issue. If I have a
report
with
only 1 charge and Query 2 charges, it gives me no data. I think
this
may
be
part of the issue, but I can't understand why it would care if there
weren't
other charges.

So this brings up the question... Since reports have a possible 6
charges
they can put on a report, and most of the time it will be 1 or 2
charges,
how
do I deal with that? Is this a situation where I would setup it's
own
query
to pull and deal with the missing charges?

Thanks again!

Blaze

:

Well, this is most puzzling.

I am not sure what to ask next... I don't know if it has anything
to
do
with
using SharePoint.

I don't see any functions being called that might have the object
reference
that the query seeks; I don't see any references to that object in
the
query; I don't see any parameters that might be seeking that object
for
data.

Just to confirm my previous understanding, you see the error when
you
try
to
run the query from the database window, and not from any form,
right?

--

Ken Snell
<MS ACCESS MVP>


Charges is a linked table via Sharepoint.

Blaze

:

One query can use another query as a "table" -- just as if it
existed
as
a
permanent table. The query will run the other query in order to
get
its
records and use them as data source.

OK - post the SQL statement of Charges query. I'm betting that
what
we're
seeking is in there....

--

Ken Snell
<MS ACCESS MVP>


Charges is just another Query. I didn't know I could use a
Query
to
run a
Query. That's an interesting addition to the utility.

Sorry for the wrong SQL Statement.

Blaze

****

Ahhh.. this is a different query than the one you posted on
8/31/2005.

OK - you said that, if you remove the "Charges" portion of the
query,
it
works without giving you the error. So, is Charges a table, or
is
it
a
query? If it's a query, what is its SQL statement? Somewhere
in
the
Charges
portion, the query thinks it needs to use some reference to
that
Arrest
Report Generator object.
--

Ken Snell
<MS ACCESS MVP>



Well, I thought I posted the correct thing. Here's what I
did.
In
the
Query
that I'm having issues with, I clicked "Design View".

From there I click the View, SQL View, thus showing the SQL
Query.
Here
it
is again. Thank you for hanging in there, because it makes
absolutly
NO
sense to me how this can happen. But as I am sure you are
aware
of,
it
is
near impossible to develop if you can run the query to verify
whether
it
works or not.

SELECT [Booking ID (BID): All Items].ID, [Arrest Reports: All
Forms].Person1Bid, [Booking ID (BID): All Items].[Full Name
(First
Last)],
[Inmate Records: All Forms].DOB, [Inmate Records: All
Forms]!DLState &

 

[Ken Snell [MVP]]

I believe that there is a limit to the number of joins, but you're nowhere
near it.

I get interest from potential clients now and then for web-based ACCESS,
which I've done in Data Access Pages to a small degree. I am looking forward
to more opportunities to use SharePoint, etc. in the future.

Sorry I wasn't able to get you a solution in this instance.