B
Blaze
I have a Report that runs fine, but the query will not "run" by itself.
Any ideas out there.
Thanks,
Blaze
Any ideas out there.
Thanks,
Blaze
K
Ken Snell [MVP]
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....
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....
B
Blaze
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
(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
K
Ken Snell [MVP]
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...
statement that you're using and let's see what we might suggest...
B
Blaze
Here ya go
SELECT "" AS [Payment Information Section], [Civil / Warrant Payment
Processor: All Items].ID AS [Payment ID], [Civil / Warrant Payment Processor:
All Items].[Receipt To] AS [Payment RECEIPT TO], [Civil / Warrant Payment
Processor: All Items].[Date Applied] AS [Payment DATE APPLIED], [Civil /
Warrant Payment Processor: All Items].[Transaction Type] AS [Payment
TRANSACTION TYPE], [Civil / Warrant Payment Processor: All Items].[Check #],
IIf([Civil / Warrant Payment Processor: All Items]![Check #] Is
Null,"Cash","Check Number" & " - " & [Civil / Warrant Payment Processor: All
Items]![Check #]) AS [Payment CHECK NUMBER if True], IIf([Payment TRANSACTION
TYPE]="Credit",[Payment CHECK NUMBER if True]) AS [Payment AMOUNT if Credit],
[Civil / Warrant Payment Processor: All Items].Amount AS [Amount Applied],
IIf([Payment TRANSACTION TYPE]="Credit",-1,1) AS [Amount CREDIT DEBIT],
[Amount Applied]*[Amount CREDIT DEBIT] AS [Apply CREDIT DEBIT AMOUNT], "" AS
[Payment Document Details Section], [Civil / Warrant Processor: All Forms].ID
AS [Payment Document APPLIED TO], [Civil / Warrant Processor: All
Forms].CWDocumentID AS [Payment Document ID], IIf([Civil / Warrant Processor:
All Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffName,Lawyers![Full Name]) AS [Payment Document PLAINTIFF or
LAWYER NAME if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffAddress,Lawyers!Address) AS [Payment Document PLAINTIFF
LAWYER ADDY if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffCity & ", " & [Civil / Warrant Processor: All
Forms]!PlaintiffState & " " & [Civil / Warrant Processor: All
Forms]!PlaintiffZip,Lawyers!City & ", " & Lawyers!State & " " &
Lawyers![Postal Code]) AS [Payment Document PLAINTIFF LAWYER CITYSTZIP if
True], IIf([Payment Document PLAINTIFF LAWYER CITYSTZIP if True]=",
","",[Payment Document PLAINTIFF LAWYER CITYSTZIP if True]) AS [Payment
Document CITYSTZIP Fixer], "" AS [Payment Mileage Section], [Civil / Warrant
Processor: All Forms].ServicesbyRadioID AS [Payment Mileage SERVED BY],
[Civil / Warrant Processor: All Forms].ServicesMiles, ".50" AS [Calculate
Mileage Cost], IIf([Payment TRANSACTION
TYPE]="Credit","",[ServicesMiles]*[Calculate Mileage Cost]) AS [Mileage if
NEEDED], "" AS [Document Reguarding Section], [Civil / Warrant Processor: All
Forms].PlaintiffName AS [Document Reguarding PLAINTIFF NAME], [Civil /
Warrant Processor: All Forms]!RespondentFirstName & " " & [Civil / Warrant
Processor: All Forms]!RespondentLastName AS [Document Reguarding RESPONDENT
NAME], [Civil / Warrant Processor: All Forms]!RespondentBusinessName AS
[Document Reguarding RESPONDENT BUSINESSS NAME]
FROM ([Civil / Warrant Payment Processor: All Items] LEFT JOIN [Civil /
Warrant Processor: All Forms] ON [Civil / Warrant Payment Processor: All
Items].[Applied To] = [Civil / Warrant Processor: All Forms].ID) LEFT JOIN
Lawyers ON [Civil / Warrant Processor: All Forms].AttorneyReference =
Lawyers.[Full Name]
WHERE ((([Civil / Warrant Processor: All Forms].ID)>=[From SEQ] And ([Civil
/ Warrant Processor: All Forms].ID)<=[To SEQ])) OR ((([Civil / Warrant
Processor: All Forms].CWDocumentID)=[CW ID]));
As far as I know, there are not relationships built.
Blaze
SELECT "" AS [Payment Information Section], [Civil / Warrant Payment
Processor: All Items].ID AS [Payment ID], [Civil / Warrant Payment Processor:
All Items].[Receipt To] AS [Payment RECEIPT TO], [Civil / Warrant Payment
Processor: All Items].[Date Applied] AS [Payment DATE APPLIED], [Civil /
Warrant Payment Processor: All Items].[Transaction Type] AS [Payment
TRANSACTION TYPE], [Civil / Warrant Payment Processor: All Items].[Check #],
IIf([Civil / Warrant Payment Processor: All Items]![Check #] Is
Null,"Cash","Check Number" & " - " & [Civil / Warrant Payment Processor: All
Items]![Check #]) AS [Payment CHECK NUMBER if True], IIf([Payment TRANSACTION
TYPE]="Credit",[Payment CHECK NUMBER if True]) AS [Payment AMOUNT if Credit],
[Civil / Warrant Payment Processor: All Items].Amount AS [Amount Applied],
IIf([Payment TRANSACTION TYPE]="Credit",-1,1) AS [Amount CREDIT DEBIT],
[Amount Applied]*[Amount CREDIT DEBIT] AS [Apply CREDIT DEBIT AMOUNT], "" AS
[Payment Document Details Section], [Civil / Warrant Processor: All Forms].ID
AS [Payment Document APPLIED TO], [Civil / Warrant Processor: All
Forms].CWDocumentID AS [Payment Document ID], IIf([Civil / Warrant Processor:
All Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffName,Lawyers![Full Name]) AS [Payment Document PLAINTIFF or
LAWYER NAME if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffAddress,Lawyers!Address) AS [Payment Document PLAINTIFF
LAWYER ADDY if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffCity & ", " & [Civil / Warrant Processor: All
Forms]!PlaintiffState & " " & [Civil / Warrant Processor: All
Forms]!PlaintiffZip,Lawyers!City & ", " & Lawyers!State & " " &
Lawyers![Postal Code]) AS [Payment Document PLAINTIFF LAWYER CITYSTZIP if
True], IIf([Payment Document PLAINTIFF LAWYER CITYSTZIP if True]=",
","",[Payment Document PLAINTIFF LAWYER CITYSTZIP if True]) AS [Payment
Document CITYSTZIP Fixer], "" AS [Payment Mileage Section], [Civil / Warrant
Processor: All Forms].ServicesbyRadioID AS [Payment Mileage SERVED BY],
[Civil / Warrant Processor: All Forms].ServicesMiles, ".50" AS [Calculate
Mileage Cost], IIf([Payment TRANSACTION
TYPE]="Credit","",[ServicesMiles]*[Calculate Mileage Cost]) AS [Mileage if
NEEDED], "" AS [Document Reguarding Section], [Civil / Warrant Processor: All
Forms].PlaintiffName AS [Document Reguarding PLAINTIFF NAME], [Civil /
Warrant Processor: All Forms]!RespondentFirstName & " " & [Civil / Warrant
Processor: All Forms]!RespondentLastName AS [Document Reguarding RESPONDENT
NAME], [Civil / Warrant Processor: All Forms]!RespondentBusinessName AS
[Document Reguarding RESPONDENT BUSINESSS NAME]
FROM ([Civil / Warrant Payment Processor: All Items] LEFT JOIN [Civil /
Warrant Processor: All Forms] ON [Civil / Warrant Payment Processor: All
Items].[Applied To] = [Civil / Warrant Processor: All Forms].ID) LEFT JOIN
Lawyers ON [Civil / Warrant Processor: All Forms].AttorneyReference =
Lawyers.[Full Name]
WHERE ((([Civil / Warrant Processor: All Forms].ID)>=[From SEQ] And ([Civil
/ Warrant Processor: All Forms].ID)<=[To SEQ])) OR ((([Civil / Warrant
Processor: All Forms].CWDocumentID)=[CW ID]));
As far as I know, there are not relationships built.
Blaze
K
Ken Snell [MVP]
By chance, is [Civil / Warrant Payment Processor: All Items] and/or [Civil /
Warrant Processor: All Forms] a query that looks up a value from a control
on a form named "Arrest Report Generator" (especially if you're using a
DLookup or other domain function where the control is the source of a value
for the criterion expression in that funcion)? If yes, this form must be
open in order to run the query.
Also, replace the ! characters between table/query name and the field name
with a . character.
--
Ken Snell
<MS ACCESS MVP>
Warrant Processor: All Forms] a query that looks up a value from a control
on a form named "Arrest Report Generator" (especially if you're using a
DLookup or other domain function where the control is the source of a value
for the criterion expression in that funcion)? If yes, this form must be
open in order to run the query.
Also, replace the ! characters between table/query name and the field name
with a . character.
--
Ken Snell
<MS ACCESS MVP>
Blaze said:Here ya go
SELECT "" AS [Payment Information Section], [Civil / Warrant Payment
Processor: All Items].ID AS [Payment ID], [Civil / Warrant Payment
Processor:
All Items].[Receipt To] AS [Payment RECEIPT TO], [Civil / Warrant Payment
Processor: All Items].[Date Applied] AS [Payment DATE APPLIED], [Civil /
Warrant Payment Processor: All Items].[Transaction Type] AS [Payment
TRANSACTION TYPE], [Civil / Warrant Payment Processor: All Items].[Check
#],
IIf([Civil / Warrant Payment Processor: All Items]![Check #] Is
Null,"Cash","Check Number" & " - " & [Civil / Warrant Payment Processor:
All
Items]![Check #]) AS [Payment CHECK NUMBER if True], IIf([Payment
TRANSACTION
TYPE]="Credit",[Payment CHECK NUMBER if True]) AS [Payment AMOUNT if
Credit],
[Civil / Warrant Payment Processor: All Items].Amount AS [Amount Applied],
IIf([Payment TRANSACTION TYPE]="Credit",-1,1) AS [Amount CREDIT DEBIT],
[Amount Applied]*[Amount CREDIT DEBIT] AS [Apply CREDIT DEBIT AMOUNT], ""
AS
[Payment Document Details Section], [Civil / Warrant Processor: All
Forms].ID
AS [Payment Document APPLIED TO], [Civil / Warrant Processor: All
Forms].CWDocumentID AS [Payment Document ID], IIf([Civil / Warrant
Processor:
All Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffName,Lawyers![Full Name]) AS [Payment Document PLAINTIFF
or
LAWYER NAME if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffAddress,Lawyers!Address) AS [Payment Document PLAINTIFF
LAWYER ADDY if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffCity & ", " & [Civil / Warrant Processor: All
Forms]!PlaintiffState & " " & [Civil / Warrant Processor: All
Forms]!PlaintiffZip,Lawyers!City & ", " & Lawyers!State & " " &
Lawyers![Postal Code]) AS [Payment Document PLAINTIFF LAWYER CITYSTZIP if
True], IIf([Payment Document PLAINTIFF LAWYER CITYSTZIP if True]=",
","",[Payment Document PLAINTIFF LAWYER CITYSTZIP if True]) AS [Payment
Document CITYSTZIP Fixer], "" AS [Payment Mileage Section], [Civil /
Warrant
Processor: All Forms].ServicesbyRadioID AS [Payment Mileage SERVED BY],
[Civil / Warrant Processor: All Forms].ServicesMiles, ".50" AS [Calculate
Mileage Cost], IIf([Payment TRANSACTION
TYPE]="Credit","",[ServicesMiles]*[Calculate Mileage Cost]) AS [Mileage if
NEEDED], "" AS [Document Reguarding Section], [Civil / Warrant Processor:
All
Forms].PlaintiffName AS [Document Reguarding PLAINTIFF NAME], [Civil /
Warrant Processor: All Forms]!RespondentFirstName & " " & [Civil / Warrant
Processor: All Forms]!RespondentLastName AS [Document Reguarding
RESPONDENT
NAME], [Civil / Warrant Processor: All Forms]!RespondentBusinessName AS
[Document Reguarding RESPONDENT BUSINESSS NAME]
FROM ([Civil / Warrant Payment Processor: All Items] LEFT JOIN [Civil /
Warrant Processor: All Forms] ON [Civil / Warrant Payment Processor: All
Items].[Applied To] = [Civil / Warrant Processor: All Forms].ID) LEFT JOIN
Lawyers ON [Civil / Warrant Processor: All Forms].AttorneyReference =
Lawyers.[Full Name]
WHERE ((([Civil / Warrant Processor: All Forms].ID)>=[From SEQ] And
([Civil
/ Warrant Processor: All Forms].ID)<=[To SEQ])) OR ((([Civil / Warrant
Processor: All Forms].CWDocumentID)=[CW ID]));
As far as I know, there are not relationships built.
Blaze
Ken Snell said:
B
Blaze
No, the only thing I do with forms, is create my switchboard. Otherwise,
it's either a query or a table. I have no data in my db, just a bunch of
linked tables to the sharpoint lists that run the data entry side of
everything.
What is the difference between the []! and [].
Thanks for the input!
Blaze
it's either a query or a table. I have no data in my db, just a bunch of
linked tables to the sharpoint lists that run the data entry side of
everything.
What is the difference between the []! and [].
Thanks for the input!
Blaze
Ken Snell said:By chance, is [Civil / Warrant Payment Processor: All Items] and/or [Civil /
Warrant Processor: All Forms] a query that looks up a value from a control
on a form named "Arrest Report Generator" (especially if you're using a
DLookup or other domain function where the control is the source of a value
for the criterion expression in that funcion)? If yes, this form must be
open in order to run the query.
Also, replace the ! characters between table/query name and the field name
with a . character.
--
Ken Snell
<MS ACCESS MVP>
Blaze said:Here ya go
SELECT "" AS [Payment Information Section], [Civil / Warrant Payment
Processor: All Items].ID AS [Payment ID], [Civil / Warrant Payment
Processor:
All Items].[Receipt To] AS [Payment RECEIPT TO], [Civil / Warrant Payment
Processor: All Items].[Date Applied] AS [Payment DATE APPLIED], [Civil /
Warrant Payment Processor: All Items].[Transaction Type] AS [Payment
TRANSACTION TYPE], [Civil / Warrant Payment Processor: All Items].[Check
#],
IIf([Civil / Warrant Payment Processor: All Items]![Check #] Is
Null,"Cash","Check Number" & " - " & [Civil / Warrant Payment Processor:
All
Items]![Check #]) AS [Payment CHECK NUMBER if True], IIf([Payment
TRANSACTION
TYPE]="Credit",[Payment CHECK NUMBER if True]) AS [Payment AMOUNT if
Credit],
[Civil / Warrant Payment Processor: All Items].Amount AS [Amount Applied],
IIf([Payment TRANSACTION TYPE]="Credit",-1,1) AS [Amount CREDIT DEBIT],
[Amount Applied]*[Amount CREDIT DEBIT] AS [Apply CREDIT DEBIT AMOUNT], ""
AS
[Payment Document Details Section], [Civil / Warrant Processor: All
Forms].ID
AS [Payment Document APPLIED TO], [Civil / Warrant Processor: All
Forms].CWDocumentID AS [Payment Document ID], IIf([Civil / Warrant
Processor:
All Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffName,Lawyers![Full Name]) AS [Payment Document PLAINTIFF
or
LAWYER NAME if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffAddress,Lawyers!Address) AS [Payment Document PLAINTIFF
LAWYER ADDY if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffCity & ", " & [Civil / Warrant Processor: All
Forms]!PlaintiffState & " " & [Civil / Warrant Processor: All
Forms]!PlaintiffZip,Lawyers!City & ", " & Lawyers!State & " " &
Lawyers![Postal Code]) AS [Payment Document PLAINTIFF LAWYER CITYSTZIP if
True], IIf([Payment Document PLAINTIFF LAWYER CITYSTZIP if True]=",
","",[Payment Document PLAINTIFF LAWYER CITYSTZIP if True]) AS [Payment
Document CITYSTZIP Fixer], "" AS [Payment Mileage Section], [Civil /
Warrant
Processor: All Forms].ServicesbyRadioID AS [Payment Mileage SERVED BY],
[Civil / Warrant Processor: All Forms].ServicesMiles, ".50" AS [Calculate
Mileage Cost], IIf([Payment TRANSACTION
TYPE]="Credit","",[ServicesMiles]*[Calculate Mileage Cost]) AS [Mileage if
NEEDED], "" AS [Document Reguarding Section], [Civil / Warrant Processor:
All
Forms].PlaintiffName AS [Document Reguarding PLAINTIFF NAME], [Civil /
Warrant Processor: All Forms]!RespondentFirstName & " " & [Civil / Warrant
Processor: All Forms]!RespondentLastName AS [Document Reguarding
RESPONDENT
NAME], [Civil / Warrant Processor: All Forms]!RespondentBusinessName AS
[Document Reguarding RESPONDENT BUSINESSS NAME]
FROM ([Civil / Warrant Payment Processor: All Items] LEFT JOIN [Civil /
Warrant Processor: All Forms] ON [Civil / Warrant Payment Processor: All
Items].[Applied To] = [Civil / Warrant Processor: All Forms].ID) LEFT JOIN
Lawyers ON [Civil / Warrant Processor: All Forms].AttorneyReference =
Lawyers.[Full Name]
WHERE ((([Civil / Warrant Processor: All Forms].ID)>=[From SEQ] And
([Civil
/ Warrant Processor: All Forms].ID)<=[To SEQ])) OR ((([Civil / Warrant
Processor: All Forms].CWDocumentID)=[CW ID]));
As far as I know, there are not relationships built.
Blaze
Ken Snell said:
K
Ken Snell [MVP]
So Civil / Warrant Payment Processor: All Items] and [Civil / Warrant
Processor: All Forms] are not queries? They are tables?
What is Arrest Report Generator? Is that a form that enables the running of
the report?
Tables and queries do not use the bang operator to separate table/query name
and field name; they use a dot operator. The bang operator is used in VBA
code (and related programming usages) to refer to a collection that is
associated with an object. For example, you would use it to refer to a
control on a form:
Forms!FormName!ControlName
--
Ken Snell
<MS ACCESS MVP>
Processor: All Forms] are not queries? They are tables?
What is Arrest Report Generator? Is that a form that enables the running of
the report?
Tables and queries do not use the bang operator to separate table/query name
and field name; they use a dot operator. The bang operator is used in VBA
code (and related programming usages) to refer to a collection that is
associated with an object. For example, you would use it to refer to a
control on a form:
Forms!FormName!ControlName
--
Ken Snell
<MS ACCESS MVP>
Blaze said:No, the only thing I do with forms, is create my switchboard. Otherwise,
it's either a query or a table. I have no data in my db, just a bunch of
linked tables to the sharpoint lists that run the data entry side of
everything.
What is the difference between the []! and [].
Thanks for the input!
Blaze
Ken Snell said:By chance, is [Civil / Warrant Payment Processor: All Items] and/or
[Civil /
Warrant Processor: All Forms] a query that looks up a value from a
control
on a form named "Arrest Report Generator" (especially if you're using a
DLookup or other domain function where the control is the source of a
value
for the criterion expression in that funcion)? If yes, this form must be
open in order to run the query.
Also, replace the ! characters between table/query name and the field
name
with a . character.
--
Ken Snell
<MS ACCESS MVP>
Blaze said:Here ya go
SELECT "" AS [Payment Information Section], [Civil / Warrant Payment
Processor: All Items].ID AS [Payment ID], [Civil / Warrant Payment
Processor:
All Items].[Receipt To] AS [Payment RECEIPT TO], [Civil / Warrant
Payment
Processor: All Items].[Date Applied] AS [Payment DATE APPLIED], [Civil
/
Warrant Payment Processor: All Items].[Transaction Type] AS [Payment
TRANSACTION TYPE], [Civil / Warrant Payment Processor: All
Items].[Check
#],
IIf([Civil / Warrant Payment Processor: All Items]![Check #] Is
Null,"Cash","Check Number" & " - " & [Civil / Warrant Payment
Processor:
All
Items]![Check #]) AS [Payment CHECK NUMBER if True], IIf([Payment
TRANSACTION
TYPE]="Credit",[Payment CHECK NUMBER if True]) AS [Payment AMOUNT if
Credit],
[Civil / Warrant Payment Processor: All Items].Amount AS [Amount
Applied],
IIf([Payment TRANSACTION TYPE]="Credit",-1,1) AS [Amount CREDIT DEBIT],
[Amount Applied]*[Amount CREDIT DEBIT] AS [Apply CREDIT DEBIT AMOUNT],
""
AS
[Payment Document Details Section], [Civil / Warrant Processor: All
Forms].ID
AS [Payment Document APPLIED TO], [Civil / Warrant Processor: All
Forms].CWDocumentID AS [Payment Document ID], IIf([Civil / Warrant
Processor:
All Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffName,Lawyers![Full Name]) AS [Payment Document
PLAINTIFF
or
LAWYER NAME if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffAddress,Lawyers!Address) AS [Payment Document PLAINTIFF
LAWYER ADDY if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffCity & ", " & [Civil / Warrant Processor: All
Forms]!PlaintiffState & " " & [Civil / Warrant Processor: All
Forms]!PlaintiffZip,Lawyers!City & ", " & Lawyers!State & " " &
Lawyers![Postal Code]) AS [Payment Document PLAINTIFF LAWYER CITYSTZIP
if
True], IIf([Payment Document PLAINTIFF LAWYER CITYSTZIP if True]=",
","",[Payment Document PLAINTIFF LAWYER CITYSTZIP if True]) AS [Payment
Document CITYSTZIP Fixer], "" AS [Payment Mileage Section], [Civil /
Warrant
Processor: All Forms].ServicesbyRadioID AS [Payment Mileage SERVED BY],
[Civil / Warrant Processor: All Forms].ServicesMiles, ".50" AS
[Calculate
Mileage Cost], IIf([Payment TRANSACTION
TYPE]="Credit","",[ServicesMiles]*[Calculate Mileage Cost]) AS [Mileage
if
NEEDED], "" AS [Document Reguarding Section], [Civil / Warrant
Processor:
All
Forms].PlaintiffName AS [Document Reguarding PLAINTIFF NAME], [Civil /
Warrant Processor: All Forms]!RespondentFirstName & " " & [Civil /
Warrant
Processor: All Forms]!RespondentLastName AS [Document Reguarding
RESPONDENT
NAME], [Civil / Warrant Processor: All Forms]!RespondentBusinessName AS
[Document Reguarding RESPONDENT BUSINESSS NAME]
FROM ([Civil / Warrant Payment Processor: All Items] LEFT JOIN [Civil /
Warrant Processor: All Forms] ON [Civil / Warrant Payment Processor:
All
Items].[Applied To] = [Civil / Warrant Processor: All Forms].ID) LEFT
JOIN
Lawyers ON [Civil / Warrant Processor: All Forms].AttorneyReference =
Lawyers.[Full Name]
WHERE ((([Civil / Warrant Processor: All Forms].ID)>=[From SEQ] And
([Civil
/ Warrant Processor: All Forms].ID)<=[To SEQ])) OR ((([Civil / Warrant
Processor: All Forms].CWDocumentID)=[CW ID]));
As far as I know, there are not relationships built.
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
B
Blaze
Well, that is correct, except the are "linked tables", so they are somewhat
queries. Otherwise, I just use data from the linked tables, which link to
sharepoint. The Arrest Report Generator, Query, that I am having issues with
is the query, the Arrest Report Generator, Report, is working from.
I have found that the issue is somewhere in the charges section, but
deleteing them is the only solution I have found. I have even moved the
linking from the sharepoint list called Booking ID(BID), to the Arrest Report
Form, published via Infopath to Sharepoint Document Library.
Any ideas here?
Thanks,
Blaze
queries. Otherwise, I just use data from the linked tables, which link to
sharepoint. The Arrest Report Generator, Query, that I am having issues with
is the query, the Arrest Report Generator, Report, is working from.
I have found that the issue is somewhere in the charges section, but
deleteing them is the only solution I have found. I have even moved the
linking from the sharepoint list called Booking ID(BID), to the Arrest Report
Form, published via Infopath to Sharepoint Document Library.
Any ideas here?
Thanks,
Blaze
Ken Snell said:So Civil / Warrant Payment Processor: All Items] and [Civil / Warrant
Processor: All Forms] are not queries? They are tables?
What is Arrest Report Generator? Is that a form that enables the running of
the report?
Tables and queries do not use the bang operator to separate table/query name
and field name; they use a dot operator. The bang operator is used in VBA
code (and related programming usages) to refer to a collection that is
associated with an object. For example, you would use it to refer to a
control on a form:
Forms!FormName!ControlName
--
Ken Snell
<MS ACCESS MVP>
Blaze said:No, the only thing I do with forms, is create my switchboard. Otherwise,
it's either a query or a table. I have no data in my db, just a bunch of
linked tables to the sharpoint lists that run the data entry side of
everything.
What is the difference between the []! and [].
Thanks for the input!
Blaze
Ken Snell said:By chance, is [Civil / Warrant Payment Processor: All Items] and/or
[Civil /
Warrant Processor: All Forms] a query that looks up a value from a
control
on a form named "Arrest Report Generator" (especially if you're using a
DLookup or other domain function where the control is the source of a
value
for the criterion expression in that funcion)? If yes, this form must be
open in order to run the query.
Also, replace the ! characters between table/query name and the field
name
with a . character.
--
Ken Snell
<MS ACCESS MVP>
Here ya go
SELECT "" AS [Payment Information Section], [Civil / Warrant Payment
Processor: All Items].ID AS [Payment ID], [Civil / Warrant Payment
Processor:
All Items].[Receipt To] AS [Payment RECEIPT TO], [Civil / Warrant
Payment
Processor: All Items].[Date Applied] AS [Payment DATE APPLIED], [Civil
/
Warrant Payment Processor: All Items].[Transaction Type] AS [Payment
TRANSACTION TYPE], [Civil / Warrant Payment Processor: All
Items].[Check
#],
IIf([Civil / Warrant Payment Processor: All Items]![Check #] Is
Null,"Cash","Check Number" & " - " & [Civil / Warrant Payment
Processor:
All
Items]![Check #]) AS [Payment CHECK NUMBER if True], IIf([Payment
TRANSACTION
TYPE]="Credit",[Payment CHECK NUMBER if True]) AS [Payment AMOUNT if
Credit],
[Civil / Warrant Payment Processor: All Items].Amount AS [Amount
Applied],
IIf([Payment TRANSACTION TYPE]="Credit",-1,1) AS [Amount CREDIT DEBIT],
[Amount Applied]*[Amount CREDIT DEBIT] AS [Apply CREDIT DEBIT AMOUNT],
""
AS
[Payment Document Details Section], [Civil / Warrant Processor: All
Forms].ID
AS [Payment Document APPLIED TO], [Civil / Warrant Processor: All
Forms].CWDocumentID AS [Payment Document ID], IIf([Civil / Warrant
Processor:
All Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffName,Lawyers![Full Name]) AS [Payment Document
PLAINTIFF
or
LAWYER NAME if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffAddress,Lawyers!Address) AS [Payment Document PLAINTIFF
LAWYER ADDY if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffCity & ", " & [Civil / Warrant Processor: All
Forms]!PlaintiffState & " " & [Civil / Warrant Processor: All
Forms]!PlaintiffZip,Lawyers!City & ", " & Lawyers!State & " " &
Lawyers![Postal Code]) AS [Payment Document PLAINTIFF LAWYER CITYSTZIP
if
True], IIf([Payment Document PLAINTIFF LAWYER CITYSTZIP if True]=",
","",[Payment Document PLAINTIFF LAWYER CITYSTZIP if True]) AS [Payment
Document CITYSTZIP Fixer], "" AS [Payment Mileage Section], [Civil /
Warrant
Processor: All Forms].ServicesbyRadioID AS [Payment Mileage SERVED BY],
[Civil / Warrant Processor: All Forms].ServicesMiles, ".50" AS
[Calculate
Mileage Cost], IIf([Payment TRANSACTION
TYPE]="Credit","",[ServicesMiles]*[Calculate Mileage Cost]) AS [Mileage
if
NEEDED], "" AS [Document Reguarding Section], [Civil / Warrant
Processor:
All
Forms].PlaintiffName AS [Document Reguarding PLAINTIFF NAME], [Civil /
Warrant Processor: All Forms]!RespondentFirstName & " " & [Civil /
Warrant
Processor: All Forms]!RespondentLastName AS [Document Reguarding
RESPONDENT
NAME], [Civil / Warrant Processor: All Forms]!RespondentBusinessName AS
[Document Reguarding RESPONDENT BUSINESSS NAME]
FROM ([Civil / Warrant Payment Processor: All Items] LEFT JOIN [Civil /
Warrant Processor: All Forms] ON [Civil / Warrant Payment Processor:
All
Items].[Applied To] = [Civil / Warrant Processor: All Forms].ID) LEFT
JOIN
Lawyers ON [Civil / Warrant Processor: All Forms].AttorneyReference =
Lawyers.[Full Name]
WHERE ((([Civil / Warrant Processor: All Forms].ID)>=[From SEQ] And
([Civil
/ Warrant Processor: All Forms].ID)<=[To SEQ])) OR ((([Civil / Warrant
Processor: All Forms].CWDocumentID)=[CW ID]));
As far as I know, there are not relationships built.
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
B
Blaze
I have also noticed that when I remove ALL of the charges, the query runs
very quickly(less than 2 seconds).... Otherwise, the query takes around 10-15
seconds?
Any clues there?
Blaze
very quickly(less than 2 seconds).... Otherwise, the query takes around 10-15
seconds?
Any clues there?
Blaze
Ken Snell said:So Civil / Warrant Payment Processor: All Items] and [Civil / Warrant
Processor: All Forms] are not queries? They are tables?
What is Arrest Report Generator? Is that a form that enables the running of
the report?
Tables and queries do not use the bang operator to separate table/query name
and field name; they use a dot operator. The bang operator is used in VBA
code (and related programming usages) to refer to a collection that is
associated with an object. For example, you would use it to refer to a
control on a form:
Forms!FormName!ControlName
--
Ken Snell
<MS ACCESS MVP>
Blaze said:No, the only thing I do with forms, is create my switchboard. Otherwise,
it's either a query or a table. I have no data in my db, just a bunch of
linked tables to the sharpoint lists that run the data entry side of
everything.
What is the difference between the []! and [].
Thanks for the input!
Blaze
Ken Snell said:By chance, is [Civil / Warrant Payment Processor: All Items] and/or
[Civil /
Warrant Processor: All Forms] a query that looks up a value from a
control
on a form named "Arrest Report Generator" (especially if you're using a
DLookup or other domain function where the control is the source of a
value
for the criterion expression in that funcion)? If yes, this form must be
open in order to run the query.
Also, replace the ! characters between table/query name and the field
name
with a . character.
--
Ken Snell
<MS ACCESS MVP>
Here ya go
SELECT "" AS [Payment Information Section], [Civil / Warrant Payment
Processor: All Items].ID AS [Payment ID], [Civil / Warrant Payment
Processor:
All Items].[Receipt To] AS [Payment RECEIPT TO], [Civil / Warrant
Payment
Processor: All Items].[Date Applied] AS [Payment DATE APPLIED], [Civil
/
Warrant Payment Processor: All Items].[Transaction Type] AS [Payment
TRANSACTION TYPE], [Civil / Warrant Payment Processor: All
Items].[Check
#],
IIf([Civil / Warrant Payment Processor: All Items]![Check #] Is
Null,"Cash","Check Number" & " - " & [Civil / Warrant Payment
Processor:
All
Items]![Check #]) AS [Payment CHECK NUMBER if True], IIf([Payment
TRANSACTION
TYPE]="Credit",[Payment CHECK NUMBER if True]) AS [Payment AMOUNT if
Credit],
[Civil / Warrant Payment Processor: All Items].Amount AS [Amount
Applied],
IIf([Payment TRANSACTION TYPE]="Credit",-1,1) AS [Amount CREDIT DEBIT],
[Amount Applied]*[Amount CREDIT DEBIT] AS [Apply CREDIT DEBIT AMOUNT],
""
AS
[Payment Document Details Section], [Civil / Warrant Processor: All
Forms].ID
AS [Payment Document APPLIED TO], [Civil / Warrant Processor: All
Forms].CWDocumentID AS [Payment Document ID], IIf([Civil / Warrant
Processor:
All Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffName,Lawyers![Full Name]) AS [Payment Document
PLAINTIFF
or
LAWYER NAME if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffAddress,Lawyers!Address) AS [Payment Document PLAINTIFF
LAWYER ADDY if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffCity & ", " & [Civil / Warrant Processor: All
Forms]!PlaintiffState & " " & [Civil / Warrant Processor: All
Forms]!PlaintiffZip,Lawyers!City & ", " & Lawyers!State & " " &
Lawyers![Postal Code]) AS [Payment Document PLAINTIFF LAWYER CITYSTZIP
if
True], IIf([Payment Document PLAINTIFF LAWYER CITYSTZIP if True]=",
","",[Payment Document PLAINTIFF LAWYER CITYSTZIP if True]) AS [Payment
Document CITYSTZIP Fixer], "" AS [Payment Mileage Section], [Civil /
Warrant
Processor: All Forms].ServicesbyRadioID AS [Payment Mileage SERVED BY],
[Civil / Warrant Processor: All Forms].ServicesMiles, ".50" AS
[Calculate
Mileage Cost], IIf([Payment TRANSACTION
TYPE]="Credit","",[ServicesMiles]*[Calculate Mileage Cost]) AS [Mileage
if
NEEDED], "" AS [Document Reguarding Section], [Civil / Warrant
Processor:
All
Forms].PlaintiffName AS [Document Reguarding PLAINTIFF NAME], [Civil /
Warrant Processor: All Forms]!RespondentFirstName & " " & [Civil /
Warrant
Processor: All Forms]!RespondentLastName AS [Document Reguarding
RESPONDENT
NAME], [Civil / Warrant Processor: All Forms]!RespondentBusinessName AS
[Document Reguarding RESPONDENT BUSINESSS NAME]
FROM ([Civil / Warrant Payment Processor: All Items] LEFT JOIN [Civil /
Warrant Processor: All Forms] ON [Civil / Warrant Payment Processor:
All
Items].[Applied To] = [Civil / Warrant Processor: All Forms].ID) LEFT
JOIN
Lawyers ON [Civil / Warrant Processor: All Forms].AttorneyReference =
Lawyers.[Full Name]
WHERE ((([Civil / Warrant Processor: All Forms].ID)>=[From SEQ] And
([Civil
/ Warrant Processor: All Forms].ID)<=[To SEQ])) OR ((([Civil / Warrant
Processor: All Forms].CWDocumentID)=[CW ID]));
As far as I know, there are not relationships built.
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
K
Ken Snell [MVP]
Blaze -
I do not know your database setup, so the use of terms "charges" and such is
not clear to me. I don't know what are "charges" in your query or report.
Thus, I cannot guess why removing them might make the query run faster nor
why the error no longer appears. However, if the "charges" involve
calculations (especially any domain functions or public user-defined
functions), that can make a query run very slowly. Also, how you do the
joins, whether you use subqueries, and where you do the filtering of records
(better if the filtering is done in the "lowest level" queries -- those that
are at the bottom of the "nesting" order) can influence the time needed to
run a query.
As I've noted, the error when you run the query by itself is because the
query is looking for an object ("Arrest Report Generator") that is not
available to it at that time, but that object is available to the query when
you run it as part of generating the report. I don't see any reference to
this object in the SQL statement that you posted, which is why I asked if
the "tables' were other queries that are perhaps seeking that object for
their calculations.
Does the report have any programming in it? How do you you "launch" the
report? Do you start the report by clicking a button on a form? Do you start
the report by opening it directly from the database window?
--
Ken Snell
<MS ACCESS MVP>
I do not know your database setup, so the use of terms "charges" and such is
not clear to me. I don't know what are "charges" in your query or report.
Thus, I cannot guess why removing them might make the query run faster nor
why the error no longer appears. However, if the "charges" involve
calculations (especially any domain functions or public user-defined
functions), that can make a query run very slowly. Also, how you do the
joins, whether you use subqueries, and where you do the filtering of records
(better if the filtering is done in the "lowest level" queries -- those that
are at the bottom of the "nesting" order) can influence the time needed to
run a query.
As I've noted, the error when you run the query by itself is because the
query is looking for an object ("Arrest Report Generator") that is not
available to it at that time, but that object is available to the query when
you run it as part of generating the report. I don't see any reference to
this object in the SQL statement that you posted, which is why I asked if
the "tables' were other queries that are perhaps seeking that object for
their calculations.
Does the report have any programming in it? How do you you "launch" the
report? Do you start the report by clicking a button on a form? Do you start
the report by opening it directly from the database window?
--
Ken Snell
<MS ACCESS MVP>
Blaze said:Well, that is correct, except the are "linked tables", so they are
somewhat
queries. Otherwise, I just use data from the linked tables, which link to
sharepoint. The Arrest Report Generator, Query, that I am having issues
with
is the query, the Arrest Report Generator, Report, is working from.
I have found that the issue is somewhere in the charges section, but
deleteing them is the only solution I have found. I have even moved the
linking from the sharepoint list called Booking ID(BID), to the Arrest
Report
Form, published via Infopath to Sharepoint Document Library.
Any ideas here?
Thanks,
Blaze
Ken Snell said:So Civil / Warrant Payment Processor: All Items] and [Civil / Warrant
Processor: All Forms] are not queries? They are tables?
What is Arrest Report Generator? Is that a form that enables the running
of
the report?
Tables and queries do not use the bang operator to separate table/query
name
and field name; they use a dot operator. The bang operator is used in VBA
code (and related programming usages) to refer to a collection that is
associated with an object. For example, you would use it to refer to a
control on a form:
Forms!FormName!ControlName
--
Ken Snell
<MS ACCESS MVP>
Blaze said:No, the only thing I do with forms, is create my switchboard.
Otherwise,
it's either a query or a table. I have no data in my db, just a bunch
of
linked tables to the sharpoint lists that run the data entry side of
everything.
What is the difference between the []! and [].
Thanks for the input!
Blaze
:
By chance, is [Civil / Warrant Payment Processor: All Items] and/or
[Civil /
Warrant Processor: All Forms] a query that looks up a value from a
control
on a form named "Arrest Report Generator" (especially if you're using
a
DLookup or other domain function where the control is the source of a
value
for the criterion expression in that funcion)? If yes, this form must
be
open in order to run the query.
Also, replace the ! characters between table/query name and the field
name
with a . character.
--
Ken Snell
<MS ACCESS MVP>
Here ya go
SELECT "" AS [Payment Information Section], [Civil / Warrant Payment
Processor: All Items].ID AS [Payment ID], [Civil / Warrant Payment
Processor:
All Items].[Receipt To] AS [Payment RECEIPT TO], [Civil / Warrant
Payment
Processor: All Items].[Date Applied] AS [Payment DATE APPLIED],
[Civil
/
Warrant Payment Processor: All Items].[Transaction Type] AS [Payment
TRANSACTION TYPE], [Civil / Warrant Payment Processor: All
Items].[Check
#],
IIf([Civil / Warrant Payment Processor: All Items]![Check #] Is
Null,"Cash","Check Number" & " - " & [Civil / Warrant Payment
Processor:
All
Items]![Check #]) AS [Payment CHECK NUMBER if True], IIf([Payment
TRANSACTION
TYPE]="Credit",[Payment CHECK NUMBER if True]) AS [Payment AMOUNT if
Credit],
[Civil / Warrant Payment Processor: All Items].Amount AS [Amount
Applied],
IIf([Payment TRANSACTION TYPE]="Credit",-1,1) AS [Amount CREDIT
DEBIT],
[Amount Applied]*[Amount CREDIT DEBIT] AS [Apply CREDIT DEBIT
AMOUNT],
""
AS
[Payment Document Details Section], [Civil / Warrant Processor: All
Forms].ID
AS [Payment Document APPLIED TO], [Civil / Warrant Processor: All
Forms].CWDocumentID AS [Payment Document ID], IIf([Civil / Warrant
Processor:
All Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffName,Lawyers![Full Name]) AS [Payment Document
PLAINTIFF
or
LAWYER NAME if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffAddress,Lawyers!Address) AS [Payment Document
PLAINTIFF
LAWYER ADDY if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffCity & ", " & [Civil / Warrant Processor: All
Forms]!PlaintiffState & " " & [Civil / Warrant Processor: All
Forms]!PlaintiffZip,Lawyers!City & ", " & Lawyers!State & " " &
Lawyers![Postal Code]) AS [Payment Document PLAINTIFF LAWYER
CITYSTZIP
if
True], IIf([Payment Document PLAINTIFF LAWYER CITYSTZIP if True]=",
","",[Payment Document PLAINTIFF LAWYER CITYSTZIP if True]) AS
[Payment
Document CITYSTZIP Fixer], "" AS [Payment Mileage Section], [Civil /
Warrant
Processor: All Forms].ServicesbyRadioID AS [Payment Mileage SERVED
BY],
[Civil / Warrant Processor: All Forms].ServicesMiles, ".50" AS
[Calculate
Mileage Cost], IIf([Payment TRANSACTION
TYPE]="Credit","",[ServicesMiles]*[Calculate Mileage Cost]) AS
[Mileage
if
NEEDED], "" AS [Document Reguarding Section], [Civil / Warrant
Processor:
All
Forms].PlaintiffName AS [Document Reguarding PLAINTIFF NAME], [Civil
/
Warrant Processor: All Forms]!RespondentFirstName & " " & [Civil /
Warrant
Processor: All Forms]!RespondentLastName AS [Document Reguarding
RESPONDENT
NAME], [Civil / Warrant Processor: All Forms]!RespondentBusinessName
AS
[Document Reguarding RESPONDENT BUSINESSS NAME]
FROM ([Civil / Warrant Payment Processor: All Items] LEFT JOIN
[Civil /
Warrant Processor: All Forms] ON [Civil / Warrant Payment Processor:
All
Items].[Applied To] = [Civil / Warrant Processor: All Forms].ID)
LEFT
JOIN
Lawyers ON [Civil / Warrant Processor: All Forms].AttorneyReference
=
Lawyers.[Full Name]
WHERE ((([Civil / Warrant Processor: All Forms].ID)>=[From SEQ] And
([Civil
/ Warrant Processor: All Forms].ID)<=[To SEQ])) OR ((([Civil /
Warrant
Processor: All Forms].CWDocumentID)=[CW ID]));
As far as I know, there are not relationships built.
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
B
Blaze
As far as I know there is nothing special I am doing. The Charges is just a
list, that contains 4 things...
1. Charge Name - Common Name for the Charge as they refer to it when
charging inmates.
2. Statute Number - The statute for the particular charge. This allow to
pick a name and get the statute also.
3. UCN Number - This is the Federal number for each charge. Used to input
warrants and other things.
4. LMS Number - This is used soley to be backwards compatible to their old
system.
Now there is nothing "special" about my queries as far as I know. I
actually know very little about programming or access, I am actually learning
on this project I am working on.
The way that the reports are launched is through the "Switchboard". As far
as I can tell, the Switchboard creats a form to build the switchboard.
Otherwise, for testing purposes, I am running both the queries and the
reports from inside the Access DB.
I don't think I have anything "exciting" with my queries. The only thing I
am doing with my queries on the charges, is combining the [Charge Name] & "/
" & [Statute]
Any ideas on what could be going on? I have another query doing the same
thing and this really make development hard to do.
Thanks for the input!
Blaze
list, that contains 4 things...
1. Charge Name - Common Name for the Charge as they refer to it when
charging inmates.
2. Statute Number - The statute for the particular charge. This allow to
pick a name and get the statute also.
3. UCN Number - This is the Federal number for each charge. Used to input
warrants and other things.
4. LMS Number - This is used soley to be backwards compatible to their old
system.
Now there is nothing "special" about my queries as far as I know. I
actually know very little about programming or access, I am actually learning
on this project I am working on.
The way that the reports are launched is through the "Switchboard". As far
as I can tell, the Switchboard creats a form to build the switchboard.
Otherwise, for testing purposes, I am running both the queries and the
reports from inside the Access DB.
I don't think I have anything "exciting" with my queries. The only thing I
am doing with my queries on the charges, is combining the [Charge Name] & "/
" & [Statute]
Any ideas on what could be going on? I have another query doing the same
thing and this really make development hard to do.
Thanks for the input!
Blaze
Ken Snell said:Blaze -
I do not know your database setup, so the use of terms "charges" and such is
not clear to me. I don't know what are "charges" in your query or report.
Thus, I cannot guess why removing them might make the query run faster nor
why the error no longer appears. However, if the "charges" involve
calculations (especially any domain functions or public user-defined
functions), that can make a query run very slowly. Also, how you do the
joins, whether you use subqueries, and where you do the filtering of records
(better if the filtering is done in the "lowest level" queries -- those that
are at the bottom of the "nesting" order) can influence the time needed to
run a query.
As I've noted, the error when you run the query by itself is because the
query is looking for an object ("Arrest Report Generator") that is not
available to it at that time, but that object is available to the query when
you run it as part of generating the report. I don't see any reference to
this object in the SQL statement that you posted, which is why I asked if
the "tables' were other queries that are perhaps seeking that object for
their calculations.
Does the report have any programming in it? How do you you "launch" the
report? Do you start the report by clicking a button on a form? Do you start
the report by opening it directly from the database window?
--
Ken Snell
<MS ACCESS MVP>
Blaze said:Well, that is correct, except the are "linked tables", so they are
somewhat
queries. Otherwise, I just use data from the linked tables, which link to
sharepoint. The Arrest Report Generator, Query, that I am having issues
with
is the query, the Arrest Report Generator, Report, is working from.
I have found that the issue is somewhere in the charges section, but
deleteing them is the only solution I have found. I have even moved the
linking from the sharepoint list called Booking ID(BID), to the Arrest
Report
Form, published via Infopath to Sharepoint Document Library.
Any ideas here?
Thanks,
Blaze
Ken Snell said:So Civil / Warrant Payment Processor: All Items] and [Civil / Warrant
Processor: All Forms] are not queries? They are tables?
What is Arrest Report Generator? Is that a form that enables the running
of
the report?
Tables and queries do not use the bang operator to separate table/query
name
and field name; they use a dot operator. The bang operator is used in VBA
code (and related programming usages) to refer to a collection that is
associated with an object. For example, you would use it to refer to a
control on a form:
Forms!FormName!ControlName
--
Ken Snell
<MS ACCESS MVP>
No, the only thing I do with forms, is create my switchboard.
Otherwise,
it's either a query or a table. I have no data in my db, just a bunch
of
linked tables to the sharpoint lists that run the data entry side of
everything.
What is the difference between the []! and [].
Thanks for the input!
Blaze
:
By chance, is [Civil / Warrant Payment Processor: All Items] and/or
[Civil /
Warrant Processor: All Forms] a query that looks up a value from a
control
on a form named "Arrest Report Generator" (especially if you're using
a
DLookup or other domain function where the control is the source of a
value
for the criterion expression in that funcion)? If yes, this form must
be
open in order to run the query.
Also, replace the ! characters between table/query name and the field
name
with a . character.
--
Ken Snell
<MS ACCESS MVP>
Here ya go
SELECT "" AS [Payment Information Section], [Civil / Warrant Payment
Processor: All Items].ID AS [Payment ID], [Civil / Warrant Payment
Processor:
All Items].[Receipt To] AS [Payment RECEIPT TO], [Civil / Warrant
Payment
Processor: All Items].[Date Applied] AS [Payment DATE APPLIED],
[Civil
/
Warrant Payment Processor: All Items].[Transaction Type] AS [Payment
TRANSACTION TYPE], [Civil / Warrant Payment Processor: All
Items].[Check
#],
IIf([Civil / Warrant Payment Processor: All Items]![Check #] Is
Null,"Cash","Check Number" & " - " & [Civil / Warrant Payment
Processor:
All
Items]![Check #]) AS [Payment CHECK NUMBER if True], IIf([Payment
TRANSACTION
TYPE]="Credit",[Payment CHECK NUMBER if True]) AS [Payment AMOUNT if
Credit],
[Civil / Warrant Payment Processor: All Items].Amount AS [Amount
Applied],
IIf([Payment TRANSACTION TYPE]="Credit",-1,1) AS [Amount CREDIT
DEBIT],
[Amount Applied]*[Amount CREDIT DEBIT] AS [Apply CREDIT DEBIT
AMOUNT],
""
AS
[Payment Document Details Section], [Civil / Warrant Processor: All
Forms].ID
AS [Payment Document APPLIED TO], [Civil / Warrant Processor: All
Forms].CWDocumentID AS [Payment Document ID], IIf([Civil / Warrant
Processor:
All Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffName,Lawyers![Full Name]) AS [Payment Document
PLAINTIFF
or
LAWYER NAME if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffAddress,Lawyers!Address) AS [Payment Document
PLAINTIFF
LAWYER ADDY if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffCity & ", " & [Civil / Warrant Processor: All
Forms]!PlaintiffState & " " & [Civil / Warrant Processor: All
Forms]!PlaintiffZip,Lawyers!City & ", " & Lawyers!State & " " &
Lawyers![Postal Code]) AS [Payment Document PLAINTIFF LAWYER
CITYSTZIP
if
True], IIf([Payment Document PLAINTIFF LAWYER CITYSTZIP if True]=",
","",[Payment Document PLAINTIFF LAWYER CITYSTZIP if True]) AS
[Payment
Document CITYSTZIP Fixer], "" AS [Payment Mileage Section], [Civil /
Warrant
Processor: All Forms].ServicesbyRadioID AS [Payment Mileage SERVED
BY],
[Civil / Warrant Processor: All Forms].ServicesMiles, ".50" AS
[Calculate
Mileage Cost], IIf([Payment TRANSACTION
TYPE]="Credit","",[ServicesMiles]*[Calculate Mileage Cost]) AS
[Mileage
if
NEEDED], "" AS [Document Reguarding Section], [Civil / Warrant
Processor:
All
Forms].PlaintiffName AS [Document Reguarding PLAINTIFF NAME], [Civil
/
Warrant Processor: All Forms]!RespondentFirstName & " " & [Civil /
Warrant
Processor: All Forms]!RespondentLastName AS [Document Reguarding
RESPONDENT
NAME], [Civil / Warrant Processor: All Forms]!RespondentBusinessName
AS
[Document Reguarding RESPONDENT BUSINESSS NAME]
FROM ([Civil / Warrant Payment Processor: All Items] LEFT JOIN
[Civil /
Warrant Processor: All Forms] ON [Civil / Warrant Payment Processor:
All
Items].[Applied To] = [Civil / Warrant Processor: All Forms].ID)
LEFT
JOIN
Lawyers ON [Civil / Warrant Processor: All Forms].AttorneyReference
=
Lawyers.[Full Name]
WHERE ((([Civil / Warrant Processor: All Forms].ID)>=[From SEQ] And
([Civil
/ Warrant Processor: All Forms].ID)<=[To SEQ])) OR ((([Civil /
Warrant
Processor: All Forms].CWDocumentID)=[CW ID]));
As far as I know, there are not relationships built.
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....
K
Ken Snell [MVP]
Relate to us exactly the steps you go through to run the report. Include
which form(s) you open, which button(s) you click, what those buttons do,
etc. Somewhere, the query is using that object and we need to identify what
it is.
--
Ken Snell
<MS ACCESS MVP>
which form(s) you open, which button(s) you click, what those buttons do,
etc. Somewhere, the query is using that object and we need to identify what
it is.
--
Ken Snell
<MS ACCESS MVP>
Blaze said:As far as I know there is nothing special I am doing. The Charges is just
a
list, that contains 4 things...
1. Charge Name - Common Name for the Charge as they refer to it when
charging inmates.
2. Statute Number - The statute for the particular charge. This allow to
pick a name and get the statute also.
3. UCN Number - This is the Federal number for each charge. Used to
input
warrants and other things.
4. LMS Number - This is used soley to be backwards compatible to their
old
system.
Now there is nothing "special" about my queries as far as I know. I
actually know very little about programming or access, I am actually
learning
on this project I am working on.
The way that the reports are launched is through the "Switchboard". As
far
as I can tell, the Switchboard creats a form to build the switchboard.
Otherwise, for testing purposes, I am running both the queries and the
reports from inside the Access DB.
I don't think I have anything "exciting" with my queries. The only thing
I
am doing with my queries on the charges, is combining the [Charge Name] &
"/
" & [Statute]
Any ideas on what could be going on? I have another query doing the same
thing and this really make development hard to do.
Thanks for the input!
Blaze
Ken Snell said:Blaze -
I do not know your database setup, so the use of terms "charges" and such
is
not clear to me. I don't know what are "charges" in your query or report.
Thus, I cannot guess why removing them might make the query run faster
nor
why the error no longer appears. However, if the "charges" involve
calculations (especially any domain functions or public user-defined
functions), that can make a query run very slowly. Also, how you do the
joins, whether you use subqueries, and where you do the filtering of
records
(better if the filtering is done in the "lowest level" queries -- those
that
are at the bottom of the "nesting" order) can influence the time needed
to
run a query.
As I've noted, the error when you run the query by itself is because the
query is looking for an object ("Arrest Report Generator") that is not
available to it at that time, but that object is available to the query
when
you run it as part of generating the report. I don't see any reference to
this object in the SQL statement that you posted, which is why I asked if
the "tables' were other queries that are perhaps seeking that object for
their calculations.
Does the report have any programming in it? How do you you "launch" the
report? Do you start the report by clicking a button on a form? Do you
start
the report by opening it directly from the database window?
--
Ken Snell
<MS ACCESS MVP>
Blaze said:Well, that is correct, except the are "linked tables", so they are
somewhat
queries. Otherwise, I just use data from the linked tables, which link
to
sharepoint. The Arrest Report Generator, Query, that I am having
issues
with
is the query, the Arrest Report Generator, Report, is working from.
I have found that the issue is somewhere in the charges section, but
deleteing them is the only solution I have found. I have even moved
the
linking from the sharepoint list called Booking ID(BID), to the Arrest
Report
Form, published via Infopath to Sharepoint Document Library.
Any ideas here?
Thanks,
Blaze
:
So Civil / Warrant Payment Processor: All Items] and [Civil / Warrant
Processor: All Forms] are not queries? They are tables?
What is Arrest Report Generator? Is that a form that enables the
running
of
the report?
Tables and queries do not use the bang operator to separate
table/query
name
and field name; they use a dot operator. The bang operator is used in
VBA
code (and related programming usages) to refer to a collection that is
associated with an object. For example, you would use it to refer to a
control on a form:
Forms!FormName!ControlName
--
Ken Snell
<MS ACCESS MVP>
No, the only thing I do with forms, is create my switchboard.
Otherwise,
it's either a query or a table. I have no data in my db, just a
bunch
of
linked tables to the sharpoint lists that run the data entry side of
everything.
What is the difference between the []! and [].
Thanks for the input!
Blaze
:
By chance, is [Civil / Warrant Payment Processor: All Items] and/or
[Civil /
Warrant Processor: All Forms] a query that looks up a value from a
control
on a form named "Arrest Report Generator" (especially if you're
using
a
DLookup or other domain function where the control is the source of
a
value
for the criterion expression in that funcion)? If yes, this form
must
be
open in order to run the query.
Also, replace the ! characters between table/query name and the
field
name
with a . character.
--
Ken Snell
<MS ACCESS MVP>
Here ya go
SELECT "" AS [Payment Information Section], [Civil / Warrant
Payment
Processor: All Items].ID AS [Payment ID], [Civil / Warrant
Payment
Processor:
All Items].[Receipt To] AS [Payment RECEIPT TO], [Civil / Warrant
Payment
Processor: All Items].[Date Applied] AS [Payment DATE APPLIED],
[Civil
/
Warrant Payment Processor: All Items].[Transaction Type] AS
[Payment
TRANSACTION TYPE], [Civil / Warrant Payment Processor: All
Items].[Check
#],
IIf([Civil / Warrant Payment Processor: All Items]![Check #] Is
Null,"Cash","Check Number" & " - " & [Civil / Warrant Payment
Processor:
All
Items]![Check #]) AS [Payment CHECK NUMBER if True], IIf([Payment
TRANSACTION
TYPE]="Credit",[Payment CHECK NUMBER if True]) AS [Payment AMOUNT
if
Credit],
[Civil / Warrant Payment Processor: All Items].Amount AS [Amount
Applied],
IIf([Payment TRANSACTION TYPE]="Credit",-1,1) AS [Amount CREDIT
DEBIT],
[Amount Applied]*[Amount CREDIT DEBIT] AS [Apply CREDIT DEBIT
AMOUNT],
""
AS
[Payment Document Details Section], [Civil / Warrant Processor:
All
Forms].ID
AS [Payment Document APPLIED TO], [Civil / Warrant Processor: All
Forms].CWDocumentID AS [Payment Document ID], IIf([Civil /
Warrant
Processor:
All Forms]!AttorneyReference Is Null,[Civil / Warrant Processor:
All
Forms]!PlaintiffName,Lawyers![Full Name]) AS [Payment Document
PLAINTIFF
or
LAWYER NAME if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffAddress,Lawyers!Address) AS [Payment Document
PLAINTIFF
LAWYER ADDY if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffCity & ", " & [Civil / Warrant Processor: All
Forms]!PlaintiffState & " " & [Civil / Warrant Processor: All
Forms]!PlaintiffZip,Lawyers!City & ", " & Lawyers!State & " " &
Lawyers![Postal Code]) AS [Payment Document PLAINTIFF LAWYER
CITYSTZIP
if
True], IIf([Payment Document PLAINTIFF LAWYER CITYSTZIP if
True]=",
","",[Payment Document PLAINTIFF LAWYER CITYSTZIP if True]) AS
[Payment
Document CITYSTZIP Fixer], "" AS [Payment Mileage Section],
[Civil /
Warrant
Processor: All Forms].ServicesbyRadioID AS [Payment Mileage
SERVED
BY],
[Civil / Warrant Processor: All Forms].ServicesMiles, ".50" AS
[Calculate
Mileage Cost], IIf([Payment TRANSACTION
TYPE]="Credit","",[ServicesMiles]*[Calculate Mileage Cost]) AS
[Mileage
if
NEEDED], "" AS [Document Reguarding Section], [Civil / Warrant
Processor:
All
Forms].PlaintiffName AS [Document Reguarding PLAINTIFF NAME],
[Civil
/
Warrant Processor: All Forms]!RespondentFirstName & " " & [Civil
/
Warrant
Processor: All Forms]!RespondentLastName AS [Document Reguarding
RESPONDENT
NAME], [Civil / Warrant Processor: All
Forms]!RespondentBusinessName
AS
[Document Reguarding RESPONDENT BUSINESSS NAME]
FROM ([Civil / Warrant Payment Processor: All Items] LEFT JOIN
[Civil /
Warrant Processor: All Forms] ON [Civil / Warrant Payment
Processor:
All
Items].[Applied To] = [Civil / Warrant Processor: All Forms].ID)
LEFT
JOIN
Lawyers ON [Civil / Warrant Processor: All
Forms].AttorneyReference
=
Lawyers.[Full Name]
WHERE ((([Civil / Warrant Processor: All Forms].ID)>=[From SEQ]
And
([Civil
/ Warrant Processor: All Forms].ID)<=[To SEQ])) OR ((([Civil /
Warrant
Processor: All Forms].CWDocumentID)=[CW ID]));
As far as I know, there are not relationships built.
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....
B
Blaze
Oh boy... This should be interesting Here we go...
I use a switchboard to launch the reports in the report section of the DB.
There are many, many, many reports in here that all work fine. Each report
is actually tied to it's own query, usually named the exact same thing.
(mosty because I'm not creative enough to think of different names) From
there each query grabs information and does whatever the query does with that
information. I use that direct query to "pump" data into the report. For
example in this issue:
I will use the (Q/R) to identify Query or Report since my naming scheme
SUCKS
I have a Query name "Arrest Report Generator" (Q)
it feeds the Query information to a report called
"Arrest Report Generator" (R). I use almost all of the direct query results
to populate the report. (except for a few IIF statements to identify the type
of arrest in the report header)
How this happens, as far as I can tell:
I used the Switchboard Manager to create a switchboard to launch these
different reports. I built sections for each department to run their own
reports. From there each section has several report "buttons"(made by the
manager wizard). Each button directly launches a report from the report
section of the DB. I use the Startup section to automatically launch the
switchboard so that the users only see the switchboard and no DB tables.
There is no security other than the security built into the web-service.
I hope this explains my method of launching reports.
Thanks!
Blaze
Here we go...I use a switchboard to launch the reports in the report section of the DB.
There are many, many, many reports in here that all work fine. Each report
is actually tied to it's own query, usually named the exact same thing.
(mosty because I'm not creative enough to think of different names) From
there each query grabs information and does whatever the query does with that
information. I use that direct query to "pump" data into the report. For
example in this issue:
I will use the (Q/R) to identify Query or Report since my naming scheme
SUCKS
I have a Query name "Arrest Report Generator" (Q)
it feeds the Query information to a report called
"Arrest Report Generator" (R). I use almost all of the direct query results
to populate the report. (except for a few IIF statements to identify the type
of arrest in the report header)
How this happens, as far as I can tell:
I used the Switchboard Manager to create a switchboard to launch these
different reports. I built sections for each department to run their own
reports. From there each section has several report "buttons"(made by the
manager wizard). Each button directly launches a report from the report
section of the DB. I use the Startup section to automatically launch the
switchboard so that the users only see the switchboard and no DB tables.
There is no security other than the security built into the web-service.
I hope this explains my method of launching reports.
Thanks!
Blaze
Ken Snell said:Relate to us exactly the steps you go through to run the report. Include
which form(s) you open, which button(s) you click, what those buttons do,
etc. Somewhere, the query is using that object and we need to identify what
it is.
--
Ken Snell
<MS ACCESS MVP>
Blaze said:As far as I know there is nothing special I am doing. The Charges is just
a
list, that contains 4 things...
1. Charge Name - Common Name for the Charge as they refer to it when
charging inmates.
2. Statute Number - The statute for the particular charge. This allow to
pick a name and get the statute also.
3. UCN Number - This is the Federal number for each charge. Used to
input
warrants and other things.
4. LMS Number - This is used soley to be backwards compatible to their
old
system.
Now there is nothing "special" about my queries as far as I know. I
actually know very little about programming or access, I am actually
learning
on this project I am working on.
The way that the reports are launched is through the "Switchboard". As
far
as I can tell, the Switchboard creats a form to build the switchboard.
Otherwise, for testing purposes, I am running both the queries and the
reports from inside the Access DB.
I don't think I have anything "exciting" with my queries. The only thing
I
am doing with my queries on the charges, is combining the [Charge Name] &
"/
" & [Statute]
Any ideas on what could be going on? I have another query doing the same
thing and this really make development hard to do.
Thanks for the input!
Blaze
Ken Snell said:Blaze -
I do not know your database setup, so the use of terms "charges" and such
is
not clear to me. I don't know what are "charges" in your query or report.
Thus, I cannot guess why removing them might make the query run faster
nor
why the error no longer appears. However, if the "charges" involve
calculations (especially any domain functions or public user-defined
functions), that can make a query run very slowly. Also, how you do the
joins, whether you use subqueries, and where you do the filtering of
records
(better if the filtering is done in the "lowest level" queries -- those
that
are at the bottom of the "nesting" order) can influence the time needed
to
run a query.
As I've noted, the error when you run the query by itself is because the
query is looking for an object ("Arrest Report Generator") that is not
available to it at that time, but that object is available to the query
when
you run it as part of generating the report. I don't see any reference to
this object in the SQL statement that you posted, which is why I asked if
the "tables' were other queries that are perhaps seeking that object for
their calculations.
Does the report have any programming in it? How do you you "launch" the
report? Do you start the report by clicking a button on a form? Do you
start
the report by opening it directly from the database window?
--
Ken Snell
<MS ACCESS MVP>
Well, that is correct, except the are "linked tables", so they are
somewhat
queries. Otherwise, I just use data from the linked tables, which link
to
sharepoint. The Arrest Report Generator, Query, that I am having
issues
with
is the query, the Arrest Report Generator, Report, is working from.
I have found that the issue is somewhere in the charges section, but
deleteing them is the only solution I have found. I have even moved
the
linking from the sharepoint list called Booking ID(BID), to the Arrest
Report
Form, published via Infopath to Sharepoint Document Library.
Any ideas here?
Thanks,
Blaze
:
So Civil / Warrant Payment Processor: All Items] and [Civil / Warrant
Processor: All Forms] are not queries? They are tables?
What is Arrest Report Generator? Is that a form that enables the
running
of
the report?
Tables and queries do not use the bang operator to separate
table/query
name
and field name; they use a dot operator. The bang operator is used in
VBA
code (and related programming usages) to refer to a collection that is
associated with an object. For example, you would use it to refer to a
control on a form:
Forms!FormName!ControlName
--
Ken Snell
<MS ACCESS MVP>
No, the only thing I do with forms, is create my switchboard.
Otherwise,
it's either a query or a table. I have no data in my db, just a
bunch
of
linked tables to the sharpoint lists that run the data entry side of
everything.
What is the difference between the []! and [].
Thanks for the input!
Blaze
:
By chance, is [Civil / Warrant Payment Processor: All Items] and/or
[Civil /
Warrant Processor: All Forms] a query that looks up a value from a
control
on a form named "Arrest Report Generator" (especially if you're
using
a
DLookup or other domain function where the control is the source of
a
value
for the criterion expression in that funcion)? If yes, this form
must
be
open in order to run the query.
Also, replace the ! characters between table/query name and the
field
name
with a . character.
--
Ken Snell
<MS ACCESS MVP>
Here ya go
SELECT "" AS [Payment Information Section], [Civil / Warrant
Payment
Processor: All Items].ID AS [Payment ID], [Civil / Warrant
Payment
Processor:
All Items].[Receipt To] AS [Payment RECEIPT TO], [Civil / Warrant
Payment
Processor: All Items].[Date Applied] AS [Payment DATE APPLIED],
[Civil
/
Warrant Payment Processor: All Items].[Transaction Type] AS
[Payment
TRANSACTION TYPE], [Civil / Warrant Payment Processor: All
Items].[Check
#],
IIf([Civil / Warrant Payment Processor: All Items]![Check #] Is
Null,"Cash","Check Number" & " - " & [Civil / Warrant Payment
Processor:
All
Items]![Check #]) AS [Payment CHECK NUMBER if True], IIf([Payment
TRANSACTION
TYPE]="Credit",[Payment CHECK NUMBER if True]) AS [Payment AMOUNT
if
Credit],
[Civil / Warrant Payment Processor: All Items].Amount AS [Amount
Applied],
IIf([Payment TRANSACTION TYPE]="Credit",-1,1) AS [Amount CREDIT
DEBIT],
[Amount Applied]*[Amount CREDIT DEBIT] AS [Apply CREDIT DEBIT
AMOUNT],
""
AS
[Payment Document Details Section], [Civil / Warrant Processor:
All
Forms].ID
AS [Payment Document APPLIED TO], [Civil / Warrant Processor: All
Forms].CWDocumentID AS [Payment Document ID], IIf([Civil /
Warrant
Processor:
All Forms]!AttorneyReference Is Null,[Civil / Warrant Processor:
All
Forms]!PlaintiffName,Lawyers![Full Name]) AS [Payment Document
PLAINTIFF
or
LAWYER NAME if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffAddress,Lawyers!Address) AS [Payment Document
PLAINTIFF
LAWYER ADDY if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor: All
Forms]!PlaintiffCity & ", " & [Civil / Warrant Processor: All
Forms]!PlaintiffState & " " & [Civil / Warrant Processor: All
Forms]!PlaintiffZip,Lawyers!City & ", " & Lawyers!State & " " &
Lawyers![Postal Code]) AS [Payment Document PLAINTIFF LAWYER
CITYSTZIP
if
True], IIf([Payment Document PLAINTIFF LAWYER CITYSTZIP if
True]=",
","",[Payment Document PLAINTIFF LAWYER CITYSTZIP if True]) AS
[Payment
Document CITYSTZIP Fixer], "" AS [Payment Mileage Section],
[Civil /
Warrant
Processor: All Forms].ServicesbyRadioID AS [Payment Mileage
SERVED
BY],
[Civil / Warrant Processor: All Forms].ServicesMiles, ".50" AS
[Calculate
Mileage Cost], IIf([Payment TRANSACTION
TYPE]="Credit","",[ServicesMiles]*[Calculate Mileage Cost]) AS
[Mileage
if
NEEDED], "" AS [Document Reguarding Section], [Civil / Warrant
Processor:
All
Forms].PlaintiffName AS [Document Reguarding PLAINTIFF NAME],
[Civil
/
Warrant Processor: All Forms]!RespondentFirstName & " " & [Civil
/
Warrant
Processor: All Forms]!RespondentLastName AS [Document Reguarding
RESPONDENT
NAME], [Civil / Warrant Processor: All
Forms]!RespondentBusinessName
AS
[Document Reguarding RESPONDENT BUSINESSS NAME]
FROM ([Civil / Warrant Payment Processor: All Items] LEFT JOIN
[Civil /
Warrant Processor: All Forms] ON [Civil / Warrant Payment
Processor:
All
Items].[Applied To] = [Civil / Warrant Processor: All Forms].ID)
LEFT
JOIN
Lawyers ON [Civil / Warrant Processor: All
Forms].AttorneyReference
=
Lawyers.[Full Name]
WHERE ((([Civil / Warrant Processor: All Forms].ID)>=[From SEQ]
And
([Civil
/ Warrant Processor: All Forms].ID)<=[To SEQ])) OR ((([Civil /
Warrant
Processor: All Forms].CWDocumentID)=[CW ID]));
K
Ken Snell [MVP]
I'm not seeing anything in the query that you posted nor in the process
description that you listed that would appear to give rise to the error that
you're seeing. Nonetheless, that error clearly says that some part of your
query is looking for an object named Arrest Report Generator, and until we
find a place in the query where there is a reference to it in some way,
we're not going to be able to solve your problem.
You said in a previous post that you're combing "Charge Name" and "Statute"
together, but the query that you'd posted does not contain such a
"calculation".
I don't mean to be a "pest", but are you sure that the query you posted
contains all the SQL statement? And that that query is not "calling" other
queries that are being used as "tables" in the query?
--
Ken Snell
<MS ACCESS MVP>
description that you listed that would appear to give rise to the error that
you're seeing. Nonetheless, that error clearly says that some part of your
query is looking for an object named Arrest Report Generator, and until we
find a place in the query where there is a reference to it in some way,
we're not going to be able to solve your problem.
You said in a previous post that you're combing "Charge Name" and "Statute"
together, but the query that you'd posted does not contain such a
"calculation".
I don't mean to be a "pest", but are you sure that the query you posted
contains all the SQL statement? And that that query is not "calling" other
queries that are being used as "tables" in the query?
--
Ken Snell
<MS ACCESS MVP>
Blaze said:Oh boy... This should be interesting
I use a switchboard to launch the reports in the report section of the DB.
There are many, many, many reports in here that all work fine. Each
report
is actually tied to it's own query, usually named the exact same thing.
(mosty because I'm not creative enough to think of different names) From
there each query grabs information and does whatever the query does with
that
information. I use that direct query to "pump" data into the report. For
example in this issue:
I will use the (Q/R) to identify Query or Report since my naming scheme
SUCKS
I have a Query name "Arrest Report Generator" (Q)
it feeds the Query information to a report called
"Arrest Report Generator" (R). I use almost all of the direct query
results
to populate the report. (except for a few IIF statements to identify the
type
of arrest in the report header)
How this happens, as far as I can tell:
I used the Switchboard Manager to create a switchboard to launch these
different reports. I built sections for each department to run their own
reports. From there each section has several report "buttons"(made by the
manager wizard). Each button directly launches a report from the report
section of the DB. I use the Startup section to automatically launch the
switchboard so that the users only see the switchboard and no DB tables.
There is no security other than the security built into the web-service.
I hope this explains my method of launching reports.
Thanks!
Blaze
Ken Snell said:Relate to us exactly the steps you go through to run the report. Include
which form(s) you open, which button(s) you click, what those buttons do,
etc. Somewhere, the query is using that object and we need to identify
what
it is.
--
Ken Snell
<MS ACCESS MVP>
Blaze said:As far as I know there is nothing special I am doing. The Charges is
just
a
list, that contains 4 things...
1. Charge Name - Common Name for the Charge as they refer to it when
charging inmates.
2. Statute Number - The statute for the particular charge. This allow
to
pick a name and get the statute also.
3. UCN Number - This is the Federal number for each charge. Used to
input
warrants and other things.
4. LMS Number - This is used soley to be backwards compatible to their
old
system.
Now there is nothing "special" about my queries as far as I know. I
actually know very little about programming or access, I am actually
learning
on this project I am working on.
The way that the reports are launched is through the "Switchboard". As
far
as I can tell, the Switchboard creats a form to build the switchboard.
Otherwise, for testing purposes, I am running both the queries and the
reports from inside the Access DB.
I don't think I have anything "exciting" with my queries. The only
thing
I
am doing with my queries on the charges, is combining the [Charge Name]
&
"/
" & [Statute]
Any ideas on what could be going on? I have another query doing the
same
thing and this really make development hard to do.
Thanks for the input!
Blaze
:
Blaze -
I do not know your database setup, so the use of terms "charges" and
such
is
not clear to me. I don't know what are "charges" in your query or
report.
Thus, I cannot guess why removing them might make the query run faster
nor
why the error no longer appears. However, if the "charges" involve
calculations (especially any domain functions or public user-defined
functions), that can make a query run very slowly. Also, how you do
the
joins, whether you use subqueries, and where you do the filtering of
records
(better if the filtering is done in the "lowest level" queries --
those
that
are at the bottom of the "nesting" order) can influence the time
needed
to
run a query.
As I've noted, the error when you run the query by itself is because
the
query is looking for an object ("Arrest Report Generator") that is not
available to it at that time, but that object is available to the
query
when
you run it as part of generating the report. I don't see any reference
to
this object in the SQL statement that you posted, which is why I asked
if
the "tables' were other queries that are perhaps seeking that object
for
their calculations.
Does the report have any programming in it? How do you you "launch"
the
report? Do you start the report by clicking a button on a form? Do you
start
the report by opening it directly from the database window?
--
Ken Snell
<MS ACCESS MVP>
Well, that is correct, except the are "linked tables", so they are
somewhat
queries. Otherwise, I just use data from the linked tables, which
link
to
sharepoint. The Arrest Report Generator, Query, that I am having
issues
with
is the query, the Arrest Report Generator, Report, is working from.
I have found that the issue is somewhere in the charges section, but
deleteing them is the only solution I have found. I have even moved
the
linking from the sharepoint list called Booking ID(BID), to the
Arrest
Report
Form, published via Infopath to Sharepoint Document Library.
Any ideas here?
Thanks,
Blaze
:
So Civil / Warrant Payment Processor: All Items] and [Civil /
Warrant
Processor: All Forms] are not queries? They are tables?
What is Arrest Report Generator? Is that a form that enables the
running
of
the report?
Tables and queries do not use the bang operator to separate
table/query
name
and field name; they use a dot operator. The bang operator is used
in
VBA
code (and related programming usages) to refer to a collection that
is
associated with an object. For example, you would use it to refer
to a
control on a form:
Forms!FormName!ControlName
--
Ken Snell
<MS ACCESS MVP>
No, the only thing I do with forms, is create my switchboard.
Otherwise,
it's either a query or a table. I have no data in my db, just a
bunch
of
linked tables to the sharpoint lists that run the data entry side
of
everything.
What is the difference between the []! and [].
Thanks for the input!
Blaze
:
By chance, is [Civil / Warrant Payment Processor: All Items]
and/or
[Civil /
Warrant Processor: All Forms] a query that looks up a value from
a
control
on a form named "Arrest Report Generator" (especially if you're
using
a
DLookup or other domain function where the control is the source
of
a
value
for the criterion expression in that funcion)? If yes, this form
must
be
open in order to run the query.
Also, replace the ! characters between table/query name and the
field
name
with a . character.
--
Ken Snell
<MS ACCESS MVP>
Here ya go
SELECT "" AS [Payment Information Section], [Civil / Warrant
Payment
Processor: All Items].ID AS [Payment ID], [Civil / Warrant
Payment
Processor:
All Items].[Receipt To] AS [Payment RECEIPT TO], [Civil /
Warrant
Payment
Processor: All Items].[Date Applied] AS [Payment DATE
APPLIED],
[Civil
/
Warrant Payment Processor: All Items].[Transaction Type] AS
[Payment
TRANSACTION TYPE], [Civil / Warrant Payment Processor: All
Items].[Check
#],
IIf([Civil / Warrant Payment Processor: All Items]![Check #]
Is
Null,"Cash","Check Number" & " - " & [Civil / Warrant Payment
Processor:
All
Items]![Check #]) AS [Payment CHECK NUMBER if True],
IIf([Payment
TRANSACTION
TYPE]="Credit",[Payment CHECK NUMBER if True]) AS [Payment
AMOUNT
if
Credit],
[Civil / Warrant Payment Processor: All Items].Amount AS
[Amount
Applied],
IIf([Payment TRANSACTION TYPE]="Credit",-1,1) AS [Amount
CREDIT
DEBIT],
[Amount Applied]*[Amount CREDIT DEBIT] AS [Apply CREDIT DEBIT
AMOUNT],
""
AS
[Payment Document Details Section], [Civil / Warrant
Processor:
All
Forms].ID
AS [Payment Document APPLIED TO], [Civil / Warrant Processor:
All
Forms].CWDocumentID AS [Payment Document ID], IIf([Civil /
Warrant
Processor:
All Forms]!AttorneyReference Is Null,[Civil / Warrant
Processor:
All
Forms]!PlaintiffName,Lawyers![Full Name]) AS [Payment Document
PLAINTIFF
or
LAWYER NAME if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor:
All
Forms]!PlaintiffAddress,Lawyers!Address) AS [Payment Document
PLAINTIFF
LAWYER ADDY if True], IIf([Civil / Warrant Processor: All
Forms]!AttorneyReference Is Null,[Civil / Warrant Processor:
All
Forms]!PlaintiffCity & ", " & [Civil / Warrant Processor: All
Forms]!PlaintiffState & " " & [Civil / Warrant Processor: All
Forms]!PlaintiffZip,Lawyers!City & ", " & Lawyers!State & " "
&
Lawyers![Postal Code]) AS [Payment Document PLAINTIFF LAWYER
CITYSTZIP
if
True], IIf([Payment Document PLAINTIFF LAWYER CITYSTZIP if
True]=",
","",[Payment Document PLAINTIFF LAWYER CITYSTZIP if True]) AS
[Payment
Document CITYSTZIP Fixer], "" AS [Payment Mileage Section],
[Civil /
Warrant
Processor: All Forms].ServicesbyRadioID AS [Payment Mileage
SERVED
BY],
[Civil / Warrant Processor: All Forms].ServicesMiles, ".50" AS
[Calculate
Mileage Cost], IIf([Payment TRANSACTION
TYPE]="Credit","",[ServicesMiles]*[Calculate Mileage Cost]) AS
[Mileage
if
NEEDED], "" AS [Document Reguarding Section], [Civil / Warrant
Processor:
All
Forms].PlaintiffName AS [Document Reguarding PLAINTIFF NAME],
[Civil
/
Warrant Processor: All Forms]!RespondentFirstName & " " &
[Civil
/
Warrant
Processor: All Forms]!RespondentLastName AS [Document
Reguarding
RESPONDENT
NAME], [Civil / Warrant Processor: All
Forms]!RespondentBusinessName
AS
[Document Reguarding RESPONDENT BUSINESSS NAME]
FROM ([Civil / Warrant Payment Processor: All Items] LEFT JOIN
[Civil /
Warrant Processor: All Forms] ON [Civil / Warrant Payment
Processor:
All
Items].[Applied To] = [Civil / Warrant Processor: All
Forms].ID)
LEFT
JOIN
Lawyers ON [Civil / Warrant Processor: All
Forms].AttorneyReference
=
Lawyers.[Full Name]
WHERE ((([Civil / Warrant Processor: All Forms].ID)>=[From
SEQ]
And
([Civil
/ Warrant Processor: All Forms].ID)<=[To SEQ])) OR ((([Civil /
Warrant
Processor: All Forms].CWDocumentID)=[CW ID]));
B
Blaze
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
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 said:I'm not seeing anything in the query that you posted nor in the process
description that you listed that would appear to give rise to the error that
you're seeing. Nonetheless, that error clearly says that some part of your
query is looking for an object named Arrest Report Generator, and until we
find a place in the query where there is a reference to it in some way,
we're not going to be able to solve your problem.
You said in a previous post that you're combing "Charge Name" and "Statute"
together, but the query that you'd posted does not contain such a
"calculation".
I don't mean to be a "pest", but are you sure that the query you posted
contains all the SQL statement? And that that query is not "calling" other
queries that are being used as "tables" in the query?
--
Ken Snell
<MS ACCESS MVP>
Blaze said:Oh boy... This should be interesting
I use a switchboard to launch the reports in the report section of the DB.
There are many, many, many reports in here that all work fine. Each
report
is actually tied to it's own query, usually named the exact same thing.
(mosty because I'm not creative enough to think of different names) From
there each query grabs information and does whatever the query does with
that
information. I use that direct query to "pump" data into the report. For
example in this issue:
I will use the (Q/R) to identify Query or Report since my naming scheme
SUCKS
I have a Query name "Arrest Report Generator" (Q)
it feeds the Query information to a report called
"Arrest Report Generator" (R). I use almost all of the direct query
results
to populate the report. (except for a few IIF statements to identify the
type
of arrest in the report header)
How this happens, as far as I can tell:
I used the Switchboard Manager to create a switchboard to launch these
different reports. I built sections for each department to run their own
reports. From there each section has several report "buttons"(made by the
manager wizard). Each button directly launches a report from the report
section of the DB. I use the Startup section to automatically launch the
switchboard so that the users only see the switchboard and no DB tables.
There is no security other than the security built into the web-service.
I hope this explains my method of launching reports.
Thanks!
Blaze
Ken Snell said:Relate to us exactly the steps you go through to run the report. Include
which form(s) you open, which button(s) you click, what those buttons do,
etc. Somewhere, the query is using that object and we need to identify
what
it is.
--
Ken Snell
<MS ACCESS MVP>
As far as I know there is nothing special I am doing. The Charges is
just
a
list, that contains 4 things...
1. Charge Name - Common Name for the Charge as they refer to it when
charging inmates.
2. Statute Number - The statute for the particular charge. This allow
to
pick a name and get the statute also.
3. UCN Number - This is the Federal number for each charge. Used to
input
warrants and other things.
4. LMS Number - This is used soley to be backwards compatible to their
old
system.
Now there is nothing "special" about my queries as far as I know. I
actually know very little about programming or access, I am actually
learning
on this project I am working on.
The way that the reports are launched is through the "Switchboard". As
far
as I can tell, the Switchboard creats a form to build the switchboard.
Otherwise, for testing purposes, I am running both the queries and the
reports from inside the Access DB.
I don't think I have anything "exciting" with my queries. The only
thing
I
am doing with my queries on the charges, is combining the [Charge Name]
&
"/
" & [Statute]
Any ideas on what could be going on? I have another query doing the
same
thing and this really make development hard to do.
Thanks for the input!
Blaze
:
Blaze -
I do not know your database setup, so the use of terms "charges" and
such
is
not clear to me. I don't know what are "charges" in your query or
report.
Thus, I cannot guess why removing them might make the query run faster
nor
why the error no longer appears. However, if the "charges" involve
calculations (especially any domain functions or public user-defined
functions), that can make a query run very slowly. Also, how you do
the
joins, whether you use subqueries, and where you do the filtering of
records
(better if the filtering is done in the "lowest level" queries --
those
that
are at the bottom of the "nesting" order) can influence the time
needed
to
run a query.
As I've noted, the error when you run the query by itself is because
the
query is looking for an object ("Arrest Report Generator") that is not
available to it at that time, but that object is available to the
query
when
you run it as part of generating the report. I don't see any reference
to
this object in the SQL statement that you posted, which is why I asked
if
the "tables' were other queries that are perhaps seeking that object
for
their calculations.
Does the report have any programming in it? How do you you "launch"
the
report? Do you start the report by clicking a button on a form? Do you
start
the report by opening it directly from the database window?
--
Ken Snell
<MS ACCESS MVP>
Well, that is correct, except the are "linked tables", so they are
somewhat
queries. Otherwise, I just use data from the linked tables, which
link
to
sharepoint. The Arrest Report Generator, Query, that I am having
issues
with
is the query, the Arrest Report Generator, Report, is working from.
I have found that the issue is somewhere in the charges section, but
deleteing them is the only solution I have found. I have even moved
the
linking from the sharepoint list called Booking ID(BID), to the
Arrest
Report
Form, published via Infopath to Sharepoint Document Library.
Any ideas here?
Thanks,
Blaze
:
So Civil / Warrant Payment Processor: All Items] and [Civil /
Warrant
Processor: All Forms] are not queries? They are tables?
What is Arrest Report Generator? Is that a form that enables the
running
of
the report?
Tables and queries do not use the bang operator to separate
table/query
name
and field name; they use a dot operator. The bang operator is used
in
VBA
code (and related programming usages) to refer to a collection that
is
associated with an object. For example, you would use it to refer
to a
control on a form:
Forms!FormName!ControlName
--
Ken Snell
<MS ACCESS MVP>
No, the only thing I do with forms, is create my switchboard.
Otherwise,
it's either a query or a table. I have no data in my db, just a
bunch
of
linked tables to the sharpoint lists that run the data entry side
of
everything.
What is the difference between the []! and [].
Thanks for the input!
Blaze
:
By chance, is [Civil / Warrant Payment Processor: All Items]
and/or
[Civil /
Warrant Processor: All Forms] a query that looks up a value from
a
control
on a form named "Arrest Report Generator" (especially if you're
using
a
DLookup or other domain function where the control is the source
of
a
value
for the criterion expression in that funcion)? If yes, this form
must
be
open in order to run the query.
Also, replace the ! characters between table/query name and the
field
name
with a . character.
--
Ken Snell
<MS ACCESS MVP>
Here ya go
SELECT "" AS [Payment Information Section], [Civil / Warrant
Payment
Processor: All Items].ID AS [Payment ID], [Civil / Warrant
Payment
Processor:
All Items].[Receipt To] AS [Payment RECEIPT TO], [Civil /
Warrant
Payment
Processor: All Items].[Date Applied] AS [Payment DATE
APPLIED],
[Civil
/
Warrant Payment Processor: All Items].[Transaction Type] AS
[Payment
TRANSACTION TYPE], [Civil / Warrant Payment Processor: All
K
Ken Snell [MVP]
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>
< snipped >
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>
Blaze said: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 said:I'm not seeing anything in the query that you posted nor in the process
description that you listed that would appear to give rise to the error
that
you're seeing. Nonetheless, that error clearly says that some part of
your
query is looking for an object named Arrest Report Generator, and until
we
find a place in the query where there is a reference to it in some way,
we're not going to be able to solve your problem.
You said in a previous post that you're combing "Charge Name" and
"Statute"
together, but the query that you'd posted does not contain such a
"calculation".
I don't mean to be a "pest", but are you sure that the query you posted
contains all the SQL statement? And that that query is not "calling"
other
queries that are being used as "tables" in the query?
--
Ken Snell
<MS ACCESS MVP>
Blaze said:Oh boy... This should be interesting
I use a switchboard to launch the reports in the report section of the
DB.
There are many, many, many reports in here that all work fine. Each
report
is actually tied to it's own query, usually named the exact same thing.
(mosty because I'm not creative enough to think of different names)
From
there each query grabs information and does whatever the query does
with
that
information. I use that direct query to "pump" data into the report.
For
example in this issue:
I will use the (Q/R) to identify Query or Report since my naming scheme
SUCKS
I have a Query name "Arrest Report Generator" (Q)
it feeds the Query information to a report called
"Arrest Report Generator" (R). I use almost all of the direct query
results
to populate the report. (except for a few IIF statements to identify
the
type
of arrest in the report header)
How this happens, as far as I can tell:
I used the Switchboard Manager to create a switchboard to launch these
different reports. I built sections for each department to run their
own
reports. From there each section has several report "buttons"(made by
the
manager wizard). Each button directly launches a report from the
report
section of the DB. I use the Startup section to automatically launch
the
switchboard so that the users only see the switchboard and no DB
tables.
There is no security other than the security built into the
web-service.
I hope this explains my method of launching reports.
Thanks!
Blaze
:
Relate to us exactly the steps you go through to run the report.
Include
which form(s) you open, which button(s) you click, what those buttons
do,
etc. Somewhere, the query is using that object and we need to identify
what
it is.
--
Ken Snell
<MS ACCESS MVP>
As far as I know there is nothing special I am doing. The Charges
is
just
a
list, that contains 4 things...
1. Charge Name - Common Name for the Charge as they refer to it
when
charging inmates.
2. Statute Number - The statute for the particular charge. This
allow
to
pick a name and get the statute also.
3. UCN Number - This is the Federal number for each charge. Used
to
input
warrants and other things.
4. LMS Number - This is used soley to be backwards compatible to
their
old
system.
Now there is nothing "special" about my queries as far as I know. I
actually know very little about programming or access, I am actually
learning
on this project I am working on.
The way that the reports are launched is through the "Switchboard".
As
far
as I can tell, the Switchboard creats a form to build the
switchboard.
Otherwise, for testing purposes, I am running both the queries and
the
reports from inside the Access DB.
I don't think I have anything "exciting" with my queries. The only
thing
I
am doing with my queries on the charges, is combining the [Charge
Name]
&
"/
" & [Statute]
Any ideas on what could be going on? I have another query doing the
same
thing and this really make development hard to do.
Thanks for the input!
Blaze
< snipped >
B
Blaze
I am going to start a new thread with our last 3 posts, back where the SQL
Statement was incorrect.
Blaze
Statement was incorrect.
Blaze
Ken Snell said: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>
Blaze said: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 said:I'm not seeing anything in the query that you posted nor in the process
description that you listed that would appear to give rise to the error
that
you're seeing. Nonetheless, that error clearly says that some part of
your
query is looking for an object named Arrest Report Generator, and until
we
find a place in the query where there is a reference to it in some way,
we're not going to be able to solve your problem.
You said in a previous post that you're combing "Charge Name" and
"Statute"
together, but the query that you'd posted does not contain such a
"calculation".
I don't mean to be a "pest", but are you sure that the query you posted
contains all the SQL statement? And that that query is not "calling"
other
queries that are being used as "tables" in the query?
--
Ken Snell
<MS ACCESS MVP>
Oh boy... This should be interesting
I use a switchboard to launch the reports in the report section of the
DB.
There are many, many, many reports in here that all work fine. Each
report
is actually tied to it's own query, usually named the exact same thing.
(mosty because I'm not creative enough to think of different names)
From
there each query grabs information and does whatever the query does
with
that
information. I use that direct query to "pump" data into the report.
For
example in this issue:
I will use the (Q/R) to identify Query or Report since my naming scheme
SUCKS
I have a Query name "Arrest Report Generator" (Q)
it feeds the Query information to a report called
"Arrest Report Generator" (R). I use almost all of the direct query
results
to populate the report. (except for a few IIF statements to identify
the
type
of arrest in the report header)
How this happens, as far as I can tell:
I used the Switchboard Manager to create a switchboard to launch these
different reports. I built sections for each department to run their
own
reports. From there each section has several report "buttons"(made by
the
manager wizard). Each button directly launches a report from the
report
section of the DB. I use the Startup section to automatically launch
the
switchboard so that the users only see the switchboard and no DB
tables.
There is no security other than the security built into the
web-service.
I hope this explains my method of launching reports.
Thanks!
Blaze
:
Relate to us exactly the steps you go through to run the report.
Include
which form(s) you open, which button(s) you click, what those buttons
do,
etc. Somewhere, the query is using that object and we need to identify
what
it is.
--
Ken Snell
<MS ACCESS MVP>
As far as I know there is nothing special I am doing. The Charges
is
just
a
list, that contains 4 things...
1. Charge Name - Common Name for the Charge as they refer to it
when
charging inmates.
2. Statute Number - The statute for the particular charge. This
allow
to
pick a name and get the statute also.
3. UCN Number - This is the Federal number for each charge. Used
to
input
warrants and other things.
4. LMS Number - This is used soley to be backwards compatible to
their
old
system.
Now there is nothing "special" about my queries as far as I know. I
actually know very little about programming or access, I am actually
learning
on this project I am working on.
The way that the reports are launched is through the "Switchboard".
As
far
as I can tell, the Switchboard creats a form to build the
switchboard.
Otherwise, for testing purposes, I am running both the queries and
the
reports from inside the Access DB.
I don't think I have anything "exciting" with my queries. The only
thing
I
am doing with my queries on the charges, is combining the [Charge
Name]
&
"/
" & [Statute]
Any ideas on what could be going on? I have another query doing the
same
thing and this really make development hard to do.
Thanks for the input!
Blaze
< snipped >
B
Blaze
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>
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>
Blaze said:
K
Ken Snell [MVP]
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>
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>
Blaze said: