?
Solved

Error message when attempting to run query

Posted on 2016-11-14
14
Medium Priority
?
57 Views
Last Modified: 2016-11-17
I have no issue when I run this query via a form,  however when I factor in the to and from dates from the form I get a run time 3828.

Here is the syntax from the query.

SELECT 233059986 AS [TAX ID], tblClaims.FileNo, tblClaims.InvoiceSentDate, tblClaims.Fname, tblClaims.Lname, tblClaims.[Date of IME], tblClaims.Specialty, tblregions.[USAA Corresponding Region] AS Region, tblStates.Stateabbr, tblClaims.ServiceID, tblClaims.[$Billed], ' - ' AS TAX, tblClaims.[$Billed] AS TOTAL, Val(Left([Claim#],9)) AS [Member #], Qrylostreportnumber.[LOST REPORT #], 'PIP' AS PIP, Customers.Company
FROM (Customers INNER JOIN ((tblClaims INNER JOIN tblStates ON tblClaims.State = tblStates.ID) INNER JOIN tblregions ON tblStates.Stateabbr = tblregions.[State Code]) ON Customers.ID = tblClaims.[Client - Contact]) INNER JOIN Qrylostreportnumber ON tblClaims.InvoiceID = Qrylostreportnumber.InvoiceID
WHERE (((tblClaims.InvoiceSentDate) Between [forms]![form1]![text63] And [forms]![form1]![text65]) AND ((Customers.Company)='USAA INSURANCE COMPANY'));
0
Comment
Question by:seamus9909
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 41887048
Does this work?

SELECT 233059986 AS [TAX ID], tblClaims.FileNo, tblClaims.InvoiceSentDate, tblClaims.Fname, tblClaims.Lname, tblClaims.[Date of IME], tblClaims.Specialty, tblregions.[USAA Corresponding Region] AS Region, tblStates.Stateabbr, tblClaims.ServiceID, tblClaims.[$Billed], ' - ' AS TAX, tblClaims.[$Billed] AS TOTAL, Val(Left([Claim#],9)) AS [Member #], Qrylostreportnumber.[LOST REPORT #], 'PIP' AS PIP, Customers.Company
FROM (Customers INNER JOIN ((tblClaims INNER JOIN tblStates ON tblClaims.State = tblStates.ID) INNER JOIN tblregions ON tblStates.Stateabbr = tblregions.[State Code]) ON Customers.ID = tblClaims.[Client - Contact]) INNER JOIN Qrylostreportnumber ON tblClaims.InvoiceID = Qrylostreportnumber.InvoiceID
WHERE (((tblClaims.InvoiceSentDate) Between DateValue([forms]![form1]![text63]) AndDateValue([forms]![form1]![text65])) AND ((Customers.Company)='USAA INSURANCE COMPANY'));
0
 

Author Comment

by:seamus9909
ID: 41887234
Its not running and throwing an error on the expression "ANDDATEVALUE"
0
 
LVL 35

Expert Comment

by:Norie
ID: 41887307
There should be a space between AND and DATEVALUE, don't know what happened there.

If you fix that does the query run?

PS What was the original error message?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:John Tsioumpris
ID: 41887362
I think you are missing something....check this
SELECT 233059986 AS [TAX ID],
       tblClaims.FileNo,
       tblClaims.InvoiceSentDate,
       tblClaims.Fname,
       tblClaims.Lname,
       tblClaims.[Date of IME],
       tblClaims.Specialty,
       tblregions.[USAA Corresponding Region] AS Region,
       tblStates.Stateabbr,
       tblClaims.ServiceID,
       tblClaims.[$Billed],
       ' - ' AS TAX,
       tblClaims.[$Billed] AS TOTAL,
       Val(Left([Claim#],9)) AS [Member #],
       Qrylostreportnumber.[LOST REPORT #],
       'PIP' AS PIP,
       Customers.Company
FROM (Customers
      INNER JOIN ((tblClaims
                   INNER JOIN tblStates ON tblClaims.State = tblStates.ID)
                  INNER JOIN tblregions ON tblStates.Stateabbr = tblregions.[State Code]) ON Customers.ID = tblClaims.[Client - Contact])
INNER JOIN Qrylostreportnumber ON tblClaims.InvoiceID = Qrylostreportnumber.InvoiceID
WHERE (((tblClaims.InvoiceSentDate) BETWEEN #" & cdate([forms]![form1]![text63]) & "#" AND #" & cdate([forms]![form1]![text65]) &"#
       AND ((Customers.Company)='USAA INSURANCE COMPANY'));

Open in new window


We need the full info of the Error and probably a cut down sample of your database...
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41887542
You may have to specify the data type of the parameters:
PARAMETERS
    [Forms]![form1]![text63] DateTime,
    [Forms]![form1]![text65] DateTime;
SELECT 
    233059986 AS [TAX ID], 
    tblClaims.FileNo, 
    tblClaims.InvoiceSentDate, 
    tblClaims.Fname, 
    tblClaims.Lname, 
    tblClaims.[Date of IME], 
    tblClaims.Specialty, 
    tblregions.[USAA Corresponding Region] AS Region, 
    tblStates.Stateabbr, 
    tblClaims.ServiceID, 
    tblClaims.[$Billed], ' - ' AS TAX, 
    tblClaims.[$Billed] AS TOTAL, 
    Val(Left([Claim#],9)) AS [Member #], 
    Qrylostreportnumber.[LOST REPORT #], 'PIP' AS PIP, 
    Customers.Company
FROM 
    (Customers 
INNER JOIN 
    ((tblClaims INNER JOIN tblStates 
        ON tblClaims.State = tblStates.ID) 
    INNER JOIN tblregions 
            ON tblStates.Stateabbr = tblregions.[State Code]) 
        ON Customers.ID = tblClaims.[Client - Contact]) 
INNER JOIN 
    Qrylostreportnumber 
        ON tblClaims.InvoiceID = Qrylostreportnumber.InvoiceID
WHERE 
    (((tblClaims.InvoiceSentDate) 
        Between [Forms]![form1]![text63] 
        And [Forms]![form1]![text65]) 
    AND 
        ((Customers.Company)='USAA INSURANCE COMPANY')); 

Open in new window

/gustav
0
 

Author Comment

by:seamus9909
ID: 41888860
all the solutions still result in a 3828 Error.  Can not reference a table with a multi-valued field using an IN clause that refers to another database"
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41889284
The error message is pretty clear. Get rid of those multi-valued fields.

MVFs are intended for non-programming users coming from Excel, and will cause you nothing but trouble - as demonstrated here.

/gustav
0
 

Author Comment

by:seamus9909
ID: 41889553
I don't have any multi value fields in the query at all.    In fact the query will run fine until I introduce the forms start date and end date.   Shoujd I include a cop err of the data Base ?
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41889567
Yes please. It sounds weird.

/gustav
0
 

Author Comment

by:seamus9909
ID: 41889800
I have attached the FE and BE versions of the Database.  The Query in question is qryEbill.   to run the query based on the form input. go to report center and Bottom of form you will see EBILL button.

Thanks for your help.
DMC_Backup_Backup.accdb
DMC_Backup_Backup_be.accdb
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 41889885
It is table Customers which have the MV field Attachements looking up

DATABASE=C:\Test\DMC_Backup_Backup_be.accdb;TABLE=Customers

/gustav
0
 

Author Comment

by:seamus9909
ID: 41890122
So Gustav

Did you delete that field from Customers Table, and try to run the Query?
0
 

Author Comment

by:seamus9909
ID: 41890162
so when I deleted that Column from the table, refreshed the FE I get no results from the query
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 41890191
Yes, right away.
Here are the records for September 2012.

/gustav
myexcel.xlsx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question