Make-Table overload

[Uvuriel03]

I have a table filled with entries of inventory items that I need to filter. I only want to use the entries before and/or on a certain date. I need it filtered to be able to calculate our inventory count as of a particular day, and simply hiding the records after that day won't work: I need them to be skipped completely.

In order to do this, I tried making a make-table query with a parameter of <=[Date], and this works sometimes. I say sometimes because it seems that if the date is not terribly recent, the table is made just fine. If the date IS too recent, I get a "Numeric Field Overflow" error. My thought is that maybe there is too much information to be put into the new table, but I'm not sure if I'm right, or how to fix it, either way.

Any thoughts? Or alternative routes?

 

[DCrake]

Simple Software Solutions

in your select query underneath the field that holds the date in question in the criteria section use one of the following
= Date() - Today only
=#01/01/2008# - equal to a specific date
< Date() - Less than a today
<=Date() - Less than or equal to today
<#01/01/2008# - less than 1st Jan 2008
<=#01/01/2008# - less than or equal to 1st Jan 2008
Between #01/01/2007# And #01/01/2008# - Between two dates
> Date() -Greater then Today
>= Date() - Equal to or greater than Today

CodeMaster:

 

[Uvuriel03]

I don't think you quite understand my question. =P I need to be able to chose the date, so it needs to be a parameter. I have that part correct.

What I'm trying to figure out is if anybody knows if there is such thing as too much information for a make-table to hold, since I get this overflow error with recent dates.

 

[neileg]

You don't need to make a table, just use a parameter select query.

If you have a field called [Date] this may be causing the error. Date is the name of a function in Access and using it as an object name can cause all sorts of problems.

 

[Uvuriel03]

It's not called [Date], I have a Date In and a Date Out, just to avoid that problem. I'll keep dinking with it and post my results.

 

[Banana]

Out of curiosity, what's your most oldest date stored in this table, and how many records do you have, then finally, how big is your database (after compact & repair)?

I want to verify that

1) There's no epoch failure though this is very unlikely- I think Access handles pre-1970 dates just fine, and I doubt we have any pre-1900 dates, no?

2) The make-table doesn't exceed Access's limitation of 2 GB file size among other specifications.

 

[Uvuriel03]

1) The oldest dates we have are from 2006.

2) There are two tables I need to filter. The "Product In" table currently has
3,202 records and the "Product Out" table currently has 2,913 records.

The total size after I did Compact & Repair (I *think* I did it right) is 37.5 MB.


Thanks for the help! If you have any other questions, just ask. =)

 

[Banana]

All right.

Let's try and debug the query.

For bad queries...

If you're getting errors that seems entirely random or unrelated to what you are encountering in QBE, it usually helps to start by deleting one column at a time, executing the query and see if it raises the same error, and if so, repeat the process until error no longer raises, then you know that the problem was with the previous column you just removed from the query.

Alternatively, you can start out with a simple query, then add one column or criteria or function at a time to ensure that everything is correct.

(from the second link on my signature.

 

[Uvuriel03]

I only had to go 3 columns over before I figured out which column is the problem.

I have a column for the location of the product. It is made up of a 3 or 4 number row, a letter, and another number. For example, 2003A1, or 412C3.

Not every product actually has a location, so some of these fields do not have any data in them. I can only assume that this is what is causing the problem.

In the linked table, the Position field is set as Text, not required, and yes to allow zero length.

If you can't think of anything right off the bat, I believe I could leave this field out entirely for the purposes of this particular report.

 

[Banana]

Ok, if you want to return all products, whether they have a location or not, try and join from all products rather than default inner join. To change that, just click on the line joining the two tables then choose either #2 or #3 on the Join dialog where it will join all products, whether there are no location or not.

 

[neileg]

1) There's no epoch failure though this is very unlikely- I think Access handles pre-1970 dates just fine, and I doubt we have any pre-1900 dates, no?

Access handles pre-1900 dates fine. They're simply stored as negative numbers.

 

[Banana]

Neil, thanks for that tidbit. I was a bit skeptical because I know that Excel acts weird with 1901 and 1900 dates.