Solved

Error message when attempting to run query

Posted on 2016-11-14
14
28 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
Comment Utility
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
Comment Utility
Its not running and throwing an error on the expression "ANDDATEVALUE"
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:seamus9909
Comment Utility
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
Comment Utility
Yes please. It sounds weird.

/gustav
0
 

Author Comment

by:seamus9909
Comment Utility
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
Comment Utility
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
Comment Utility
So Gustav

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

Author Comment

by:seamus9909
Comment Utility
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
Comment Utility
Yes, right away.
Here are the records for September 2012.

/gustav
myexcel.xlsx
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

762 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

6 Experts available now in Live!

Get 1:1 Help Now