Solved

Error message when attempting to run query

Posted on 2016-11-14
14
45 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 34

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 34

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
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!

 
LVL 17

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 50

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 50

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 50

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 50

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 50

Accepted Solution

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

/gustav
myexcel.xlsx
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 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