Is there a way to only pull recent data from a access database

[dodgeking3171]

Hey Guys,

So heres what im trying to do. Im using Excel to go through a access database to figure out What parts we sold yesterday, how many, and who the supplier is.

Everything worked great. There is some basic excel formulas to find the supplier and to weed out all of the special order stuff that we do not stock

I wanted to make this update automatically. Before i was just going through and copy pasting the records from yesterday and letting excel go through and make my pivot table.

I thought that i could link a access database, then sort by date to only show yesterday, and away it would go. Problem is excel seems to freeze up when i try to do this. I can fix the freezing problem by getting rid of the formula to find the supplier so that it will sort out the date, But the supplier is a huge item needed to make this function the way i want it too

The database is 440,000 records long. So its huge.

My question is can i only pull data from the last day or week from the access database? Im not sure how to supply my spreed sheet as im sure that would be helpfull for anyone trying to lend a hand.

thanks in advanced

 

[dodgeking3171]

im a bit early but here is the sql

SELECT tblPartCharge.Workorder, tblPartCharge.SectionNo, tblPartCharge.ShopID, tblPartCharge.Partnumber, tblPartCharge.Description, tblPartCharge.Date, tblPartCharge.Qtysold, tblPartCharge.Cost, tblPartCharge.CoreCost, tblPartCharge.Sell, tblPartCharge.Core, tblPartCharge.Hazardous, tblPartCharge.Salestax, tblPartCharge.Posted, tblPartCharge.PO, tblPartCharge.SpecialPricing
FROM `C:\Users\Administrator\Desktop\ServiceData Data.MDB`.tblPartCharge tblPartCharge


should mention that I kind of got it to work with BETWEEN. adds a extra step, but should be usble . i appreciate everyones help

 

[Fazza]

from my first post

'before
SELECT list of fields
FROM your data table

'change it to
SELECT list of fields
FROM your data table
WHERE CLng(Now) - CLng(yourtable.[your date field]) < [?]

So one way is like that. I tested it at the time & it worked fine. Does what you asked for - the last day's data or the last week, by entering 1 or 7. No formulas & no VBA required.

It looks like the date field is tblPartCharge.Date. Then the line to add to your existing SQL is

WHERE CLng(Now) - CLng(tblPartCharge.[Date]) < [?]

If you want some variation on that (such as using BETWEEN), then please change it to suit. The CLng function tries to return a long value - so a whole number. That overcomes the time stamp elements with times of the day, as they are basically chopped off. OK?

 

[dodgeking3171]

I appreciate the help. I also did not know what the clng function did. I will try your method first as it seems it would work better. it currently comes up with the same thing i had before (can not run a query that can not be displayed graphically) however I very much appreciate the directions

 

[Fazza]

it currently comes up with the same thing i had before (can not run a query that can not be displayed graphically)...

Again, just OK to acknowledge. The query can run: the message is something about not being able to show in the GUI within the range of query types the GUI shows.