Query with Dates as Numbers

[forrestapi]

Heres the key points:

-Our AS400 system keeps the date field stored as a number (example of todays date would be 20110330).

-In a query i have two tables. In Table 1, i have a list of "item numbers" with a date column to correspond with each item. Table 2 is a list of all of our sales, and it also includes a date column to correspond with each sale.

-What i want to accomplish is to Pull all sales of items in Table 1 starting with the date listed in Table 1, and go back 90 days.



Now this usually wouldn't be a problem if the date was stored as a date (i could just use the Date()-90), however i am stuck with how to do it with the date being stored as a number and in the format i showed.

Any ideas? I know this may seem confusing, but i can provide any further details if needed. Thanks in advance

[orange]

Just a thought, you could take your numbers (are they really numbers or strings)
and do something like this to make them a Date for calculation.

?cdate(Left("20110330",4)&"/" & mid("20110330",5,2) & "/"& Right("20110330",2))
30/03/2011

Then do your regular Date - 90, get a value...etc.

[ajetrumpet]

forrest,

someone is already helping you here, but I did want to ask if IBM could help you out someway?? AS400 is extremely outdated, but I'm still shocked at how companies still have to use it, or do use it.

But with IBM's computers now beating the jeopardy contestants, I would assume they might have some help or something with the compatibility of these command line db systems, with regard to other db systems out there. Like db2? I think that's IBM's as well.

Just thought I'd throw the idea out there for you. You can't get much smarter than the people at IBM!

[forrestapi]

We have some contacts with IBM, i just usually hate contacting them for something like this. (though i cant say i really understand there thinking in storing a date as a number). Thanks for the suggestion though, i may have to take that route if this doesnt work.

Orange, im currently trying your suggestion. I will report back with the results.

[ls9bg]

Hi
Sorry for intruding. I thought I may as well chip in since I have encountered similar problem today.
We used csv's(with many date fields as dates( Format 30/12/2010) from extracted from systemA and used in our Access database.

After the systemA upgrade the dates in extracts are now a general field( Format 2011033) instead of date.

This is going to lead modifying the queries.
Is there a convenient way around
------------

[ajetrumpet]

We have some contacts with IBM, i just usually hate contacting them for something like this. (though i cant say i really understand there thinking in storing a date as a number). Thanks for the suggestion though, i may have to take that route if this doesnt work.

Orange, im currently trying your suggestion. I will report back with the results.

my guess is simply because date field data types are not allowed in UNIX. I have no idea though. But those systems are so close to binary-based systems, I would be surprised if they even stored text!

And to add again, I would assume that it'd be pretty tough to find someone at IBM that would be interested in helping you. Maybe in a call center. But most of those guys are interested in business consulting to huge companies that use their machines, and can use their wisdom.

Dumping their computer line was probably the best move they ever made. The ironic thing about it was the fact that the Chinese still charge out the wahzooo for that brand, AND they make tons of money from them, but apparently that wasn't enough to please IBM. Unbelievable! Intelligent AND greedy people. I think I could set all of that aside to get into their top-notch secret society. I love em!

[forrestapi]

After some testing, it doesnt seem as though that is going to work (maybe because its still not treating it as a date field?). It seemed as though it was trying to remove the -90 from the year and not day/month. I am going to look at this more in the morning, as the system gets pretty hard hit at the end of the day, and me running query's against it doesn't help any.

[orange]

Make a small test file and get it working before processing volume data.

Do you have some sample records you can zip and post?

You said 90 days right?
Are you familiar with Access or, with the A400 or both?

[forrestapi]

Im working on pulling out some sample data to post and get some help (just been a hectic couple of days, so this got put on the back burner). Thinking last night, i wanted to ask something else.

Lets say in table 1 i include along with the item number, both the start date and end date of the information i want. (use the mid,left,right function to get it in the correct form for what i need to search against the as400 file)

example table 1

Item Start Date End Date
000000 20110101 20110331
111111 20101201 20110228
222222 20101001 20101231

So lets say i want to bounce this against the sales table. Is there any way to have it search for each item with the two dates specified in table 1 for each item inside the sales file (table 2).

Usually this would be accomplished by linking the two tables through the item number and using the table name and field name in the criteria

ex.
Between [Item]![Start Date] & [Item]![End Date]

however i cant do this since this date will be variable depending on the item.

I know that may sound confusing, but if anyone understands it, is there any way to accomplish something like that.

[forrestapi]

bump, no more input? Still been unable to get some sample data, but am working on that.