Solved

Error message when attempting to run query

Posted on 2016-11-14
14
43 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

738 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