If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Error when running Query, but not Report
I have a Report that runs fine, but the query will not "run" by itself.
Any ideas out there. Thanks, Blaze |
#2
|
|||
|
|||
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 "Blaze" wrote in message news I have a Report that runs fine, but the query will not "run" by itself. Any ideas out there. Thanks, Blaze |
#3
|
|||
|
|||
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 "Ken Snell [MVP]" wrote: 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 "Blaze" wrote in message news I have a Report that runs fine, but the query will not "run" by itself. Any ideas out there. Thanks, Blaze |
#4
|
|||
|
|||
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 "Blaze" wrote in message ... 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 "Ken Snell [MVP]" wrote: 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 "Blaze" wrote in message news I have a Report that runs fine, but the query will not "run" by itself. Any ideas out there. Thanks, Blaze |
#5
|
|||
|
|||
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 [MVP]" wrote: 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 "Blaze" wrote in message ... 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 "Ken Snell [MVP]" wrote: 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 "Blaze" wrote in message news I have a Report that runs fine, but the query will not "run" by itself. Any ideas out there. Thanks, Blaze |
#6
|
|||
|
|||
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" wrote in message ... 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 [MVP]" wrote: 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 "Blaze" wrote in message ... 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 "Ken Snell [MVP]" wrote: 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 "Blaze" wrote in message news I have a Report that runs fine, but the query will not "run" by itself. Any ideas out there. Thanks, Blaze |
#7
|
|||
|
|||
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 [MVP]" wrote: 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" wrote in message ... 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 [MVP]" wrote: 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 "Blaze" wrote in message ... 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 "Ken Snell [MVP]" wrote: 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 "Blaze" wrote in message news I have a Report that runs fine, but the query will not "run" by itself. Any ideas out there. Thanks, Blaze |
#8
|
|||
|
|||
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" wrote in message ... 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 [MVP]" wrote: 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" wrote in message ... 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 [MVP]" wrote: 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 "Blaze" wrote in message ... 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 "Ken Snell [MVP]" wrote: 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 "Blaze" wrote in message news I have a Report that runs fine, but the query will not "run" by itself. Any ideas out there. Thanks, Blaze |
#9
|
|||
|
|||
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 [MVP]" wrote: 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" wrote in message ... 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 [MVP]" wrote: 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" wrote in message ... 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 [MVP]" wrote: 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 "Blaze" wrote in message ... 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 "Ken Snell [MVP]" wrote: 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 "Blaze" wrote in message news I have a Report that runs fine, but the query will not "run" by itself. Any ideas out there. Thanks, Blaze |
#10
|
|||
|
|||
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 "Ken Snell [MVP]" wrote: 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" wrote in message ... 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 [MVP]" wrote: 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" wrote in message ... 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 [MVP]" wrote: 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 "Blaze" wrote in message ... 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 "Ken Snell [MVP]" wrote: 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 "Blaze" wrote in message news I have a Report that runs fine, but the query will not "run" by itself. Any ideas out there. Thanks, Blaze |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
To Sharkbyte and all: Calculate a total values in group level | Ally | General Discussion | 6 | June 13th, 2005 08:16 PM |
help needed | jkendrick75 | Running & Setting Up Queries | 19 | March 17th, 2005 02:37 PM |
incorrect sums in report using 2 tables | jkendrick75 | Setting Up & Running Reports | 22 | December 13th, 2004 02:19 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |