RE: Query stopped working after entry 615 in table

Ok, had another thought...

What if I build a Macro that did the same thing as what I'm trying to do
with the Query? There wouldn't be all the joins that seem to be messing it
up, right? And, I'm not using it to enter information, just pull the
information I need to export somewhere else.

So, here's what I have so far....

Macro Name: Find Jenish Invoice
Action: Open Table
Table Name: Tracking
View: Data***
Data Mode: Read Only
Action: FindRecord
Find What: Q26K03
Match: Whole Field
Match Case: No
Search: All
Search As Formatted: Yes
Only Current Field: No
Find First: Yes

This will find the record that I want - it works.

Here - I need something that will write the record that was found to a table
that can be exported...

Next,

Macro Name: Prt J Order
Action: Transfer Spread***
Transfer Type: Export
Spreadhseet Type: Microsoft Excel 8-10
Table Name: Previous Invoices For Tracking
File Name: \\<blah blah blah>
Has Field Name: Yes
Range: <Blank>
Action: RunApp
Command Line: "C:\<blah blah blah>"

This works, although I will have to change the table name once this is
operating, but the basics are there.

Now...
1) Can I make the "Find What" criteria user input? If so, how?
2) How do I "write" (for want of a better word) that particular Record to a
table that can be used in the exporting?

I can see what needs to be done, I'm not sure how to get it there.

Jennifer


"KARL DEWEY" wrote:

The problem is not in the table size.

How many records does Jenish Invoice Query pull? How many should it pull?
How many records does Previous Invoices for Tracking pull? How many should
it pull?

Try this to see how many records --
SELECT [Jenish Invoice Query].[Customer ID], Tracking.ProCadInv,
Tracking.JenishInv, Tracking.[Customer ID]
FROM Customers INNER JOIN Tracking ON Customers.CustomerID =
Tracking.[Customer ID]
WHERE Customers.CustomerID=[Enter Customer ID for your Invoice];



"ProCad Jen" wrote:

ok - so, I tried splitting the Tracking Table, which the two Queries are
built off of, into separate tables for each year. So, all the Q26's
together, Q25's together, etc. Theoretically, if the inability to pick up
the entries after Q26K02 is due to the size of the Tracking Table, then this
would disappear when it was moved into a table that was much smaller. It
still doesn't work. Does this program have something against alpha-numeric
strings with K in it?! I'm running out of ideas.

Jennifer

"KARL DEWEY" wrote:

They are joined in Previous Invoices for Tracking. That is why the second
prompt for invoice number.

"ProCad Jen" wrote:

There aren't joins actually.

There will only be one entry with this specific invoice number. The Jenish
Invoice Query brings up the data for that specific entry.

The Previous Invoices for Tracking query is based on the Customer ID entered
for this Invoice. It produces all the previous invoices under that Customer
ID.

The two queries are not joined. They run independently. When the Macro is
activated, it runs the Jenish Invoice Query and asks for the Invoice
criteria. Then, it runs the Previous Invoices for Tracking query, and asks
for the Invoice criteria again.

So, when the macro is activated, you have to enter the Invoice criteria in
two separate criteria boxes. I couldn't figure out how to make the Previous
Invoices for Tracking query run based on the results of the Jenish Invoice
query so that you only have to enter the criteria once.


"KARL DEWEY" wrote:

I am not following everything but you may have too many JOINs.

What is the Previous Invoices for Tracking: Select Query doing for you
that Jenish Invoice Query: Select Query will not give you in the results?

How many records will have the one invoice number "Q26K03" related?

"ProCad Jen" wrote:

Hi Karl,

When I run both the Jenish Invoice Query and the Previous Invoice Tracking
Query, it asks for the Invoice number. The Invoice number is typed in as
alpha-numberic in one string as follows:

Q - Jenish Invoice
26 - Year (2006)
K - November (A-L representing the Months)
03 - The third invoice of the month.

So, Q26K03 is entered as the answer to the query.

I have run both queries independent of the Macro and have found that the
invoices prior to this one, and none after, including this one, will come up
when you request it.

Is there a limitation somewhere, either in the query search parameters or in
the database defaults which might be limiting it? so that it's not looking
past a certain point maybe? The way the information is being entered has not
changed one iota - and it's not the "K" in the input criteria - the 01 and 02
invoice will show, both in the import and when you run the query separately.

Jennifer



"KARL DEWEY" wrote:

You did not mention Excel spread*** the first time.

If you run the queries alone is the data displayed?

Build a little, test a little -- you have to isolate the problem.

"ProCad Jen" wrote:

The Printing Macro Runs the following two queries and then exports the
information to an Excel spread***. The information is not showing up on
the spread*** if it is after entry 615 in the table. Here are the queries
involved in the process:

Previous Invoices for Tracking: Select Query

SELECT [Jenish Invoice Query].[Customer ID], Tracking.ProCadInv,
Tracking.JenishInv, Tracking.[Customer ID]
FROM (Customers INNER JOIN [Jenish Invoice Query] ON Customers.CustomerID =
[Jenish Invoice Query].[Customer ID]) INNER JOIN Tracking ON
Customers.CustomerID = Tracking.[Customer ID];

Jenish Invoice Query: Select Query

SELECT Tracking.[Customer ID], Tracking.OrderDate, Tracking.PurchaseOrderNo,
Tracking.ProCadInv, Tracking.OrderModNo, Tracking.ShippingMethod,
Tracking.Site, Tracking.[Plan No], [House Plans].sq_ft, [House
Plans].Description, Tracking.NoSets, Tracking.[Material List],
Tracking.AdditCopies, Tracking.Vellums, Tracking.MIRev, Tracking.ReadRev,
Tracking.ModVel, Tracking.EfileRev, Tracking.DetModNo, Tracking.Sheetsperset,
Tracking.Elevation, Tracking.Feature, Tracking.SilentFloor, Tracking.Framing,
Tracking.RenderingMat, Tracking.Rendering, Tracking.[Extra Costs],
Tracking.[Decrip Extra Costs], Tracking.RevisionsCost, Tracking.Retainer,
Customers.CompanyName, Customers.ContactFirstName, Customers.ContactLastName,
Customers.ContactFirstName1, Customers.ContactLastName1, Customers.Address1,
Customers.Address2, Customers.City, Customers.StateOrProvince,
Customers.PostalCode, Customers.PhoneNumber, Customers.CellNumber,
Customers.Disc, Tracking.JenishInv, Customers.FaxNumber, Tracking.Ecoblock
FROM Customers INNER JOIN ([House Plans] INNER JOIN Tracking ON [House
Plans].Plan = Tracking.[Plan No]) ON Customers.CustomerID =
Tracking.[Customer ID]
WHERE (((Tracking.ProCadInv)=[ProCad Invoice No?]));

Thanks for looking at this Karl.

Jennifer


"KARL DEWEY" wrote:

Do you have any criteria for the query?

Post your SQL.

"ProCad Jen" wrote:

Hi,
I am having trouble with a query/macro that has stopped working. I have 618
records in the table, and it won't pick up any data after about the 615th
entry. The table is allowing more information to be put into it, but when
you run the macro/query combination to pull the information, it won't find it.

I have checked the talbe and even re-entered the information but to no
avail. I'm at a loss. This database is imperative to the workings of my
office so I need to solve this asap.

If you can help, please let me know. I will provide any information you
need in order to solve this dilema.

Thank you.

Jennifer

.

Loading