Solved

Error message when attempting to run query

Posted on 2016-11-14
14
35 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 33

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 33

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
 
LVL 13

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 49

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 49

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 49

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 49

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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now