Running a query with dates from another table (1 Viewer)

[ericryd]

Hi,

I'm running a query that needs to pull dates from another table as its criteria.

As in, I have 2 fields in another table: [start] and [end]

I go into the query and on the date criteria: "Between [date range]![startdate] And [date range]![enddate]

I run this, and it never ends...Access usually freezes. I checked the file and it looks fine, formats are good...no issues that I can see.

If I type the dates manually, 'between ##### and #####', the same dates listed as startdate & enddate.....it runs fine and quick.

I did another test in which I queried off the date range table and had it list out all dates in between in a new table. Then I linked my main query to this table that listed all of the dates in range...Same issue

I'm not understanding why if I manually type dates it takes 10 seconds it seems, but if I do it with fields from another table (even though the table only has 2 fields and 1 row)....it crashes.

help?

 

[HiTechCoach]

I would like to suggest that you post eh SQL for the query that uses the tagle to get the date range.

Woudl you please post the SQL?

 

[ericryd]

SELECT DISTINCT
dbo_AS400_SerialNumber.PO_Number AS [PO#]
, dbo_AS400_SerialNumber.Part AS EDC
, dbo_AS400_SerialNumber.ReceivedNumber AS Recd
, dbo_AS400_POHEADER.Supplier AS Vendor
, dbo_AS400_SerialNumber.DateCreated AS [Date]
INTO [New SN]
FROM maxdate2
, dbo_AS400_POHEADER
INNER JOIN (dbo_AS400_SerialNumber INNER JOIN dbo_AS400_PartsTable
ON dbo_AS400_SerialNumber.Part = dbo_AS400_PartsTable.Part)
ON dbo_AS400_PMP02_POHEADER.OrderNumber = dbo_AS400_SerialNumber.PONumber
WHERE (((dbo_AS400_POHEADER.SUPPLIER) In ("Supp1","Supp2","Supp3"))
AND ((dbo_AS400_SerialNumber.DateCreated) Between [maxdate2]![startdate] And [maxdate2]![enddate])
AND ((dbo_AS400_PartsTable.MFGCode)
In ("MFG1","MFG2","MFG3")));

i changed a few names as not sure if my work wants me giving away structure information. Here is some additional info. This only happens when I run the compare against as400 tables, vs local tables it works fine. Someone said possibly that the query is trying to download the entire as400 table first, which causes the crash?

 

[namliam]

Assuming MaxDate2 only contains 1 record this syntax looks OK... The problem will probably be in "joining" a local table with multiple AS400 tables and very likely yes, access is retrieving the full AS400 tables.

You will probably have to go with the "hard coded" dates, where you use some code to write in the dates instead of using a table...

 

[ericryd]

Thanks for the reply. Someone else mentioned that I could try utilizing vb code to do it...think this would work?

 

[namliam]

You will probably have to go with the "hard coded" dates, where you use some code to write in the dates instead of using a table...

WHich is what I already suggested, yes that will work!

 

[DCrake]

You may try looking at this Link to improve performance and programming.

David