I need to only display the last 30 days of records 1

[RichardPrewitt]

OK two problems about the same.

Problem #1
-------------

I need a query that will display only dates < 30 days old. Please help me. The query is going to be used on a report. I have the dates entered into fields in the table two different ways. One in format ##/##/#### and another way in 3 seperate fields to do calculations on. Because in VB I had to find when the task has to be completed again and do alot of math for each possiblity

I have a table that is keeping track of every task ever completed and they only want a report genereated that lists just the last 30 days. So the statement that I need would be hopefully in Access so I can just use the query from Access for an Access report.

Problem #2
-------------

Ok another wall that I am hitting is similar to that one.

I am needing to find the oldest of the tasks that are not completed yet and list them descending and return two dates.
I already have them listing descending. I have a field in the table that supplies [lastcomplete] date. I need to come up with a query that based on that date will return the first date(oldest) to the most current date. Now since the newest date can be irrelevant by saying return all dates with incomplete tasks. I just need to also return the oldest date and the newest date in the set. (This being from the one field [lastcomplete]

Another thing that might be need to know is that all of the tasks are incomplete becuase in VB when they finish a task I take the data from this table and put it into a history table and just change the [lastcomplete] date with the date that it was completed and such. They also just want to the last 30 days worth, so maybe something like taking the date in that field and comparing it to [date] (computer generated current date) and if less than 30 days old? Please supply code or examples if you could. I'm just new to programming and this is my first real project using SQL that differs from:

SELECT table1.*, table2.* FROM table2 INNER JOIN table1 ON table2.commonfield = table1.commonfield

So ya I've defiently got alot to learn with SQL.

Any help would be greatly appreciated.

This makes what . . . three things related that I am having problems with lol. O the joys of learning something new in the wonderful world of programming.

Thanks in advance,
Richard

 

[ArtieChoke]

For #1, if the date is stored as a datetime value, rather than text, you can use the datediff function in VB or SQL (not sure if Access provides it, but I suspect it has something like it). Check the date in the table against the current date, ie (VB syntax)
if datediff("d",db_date,date) < 30 then ...

I don't remember which date goes first to get a positive difference - you might have to swap 'em around.

For #2, to get the oldest/newest date, do something like: select min(lastcomplete) as oldest, max(lastcomplete) as newest


"I think we're all Bozos on this bus!" - Firesign Theatre

 

[johnwm]

Note that there are quite considerable differences between JetSQL (as used in Access) and most of the 'standard' dialects of SQL. There is an often overlooked resource installed with Access. It's the JetSQL help file, default location is "C:\Program Files\Common Files\Microsoft Shared\Office10\1033\jetsql40.chm"

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[RichardPrewitt]

Thanks guys, I'll look up that help file and take a gander. But (trying to get this done as soon as possible) if anyone could supply MS Access SQL(JetSQL) code for this problem, I will give you my first born (jk)


You know how it goes, they are trying to save money, so they let some programmers go or forced early retirement and now they are taking me(from IT/Networking) and go, "Hey I heard you knew a little about this" Um ya I took a few classes in college and they said good enough, we want this done in two weeks. (and of course no extra pay and longer hours) SO its like "Um . . . ok".

I'm on the verge of finishing this project and hit quite a few road blocks along the way.

Thanks to everyone that's helped or lead me in the right direction.

- Richard

 

[RichardPrewitt]

Ok problem number 2 is the one that I really need to solve becuase it will answer both at the same time.

I can list the dates oldest to newest by doing a sort. no problem there but after the descending sort then I need just the last 30 days worth from the sort.

Thanks,
Richard

 

[johnwm]

Look up the TOP clause in the reference given.

As regards full code solutions, check out the forum guidelines at faq222-2244. You'll also find guidelines to forum etiquette

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[RichardPrewitt]

In regards to that last post, I am just wanting an example of syntax that will work becuase if I do not know what syntax to search for in the help file then help files do no good. If I want to compare dates and I've never used the "TOP" clause before then searching the help file for compare date or compare values is not going to cut it. I am not asking for someone to supply the full source code, just a syntax example like "[variable] + 1 = [variable] is a good counter", not "Look up adding in the dictionary".

I have 37 pages of VB code for this project and I just need one SQL statement that will bring up records between two determined dates.

I appreciate you trying to help by telling me to look it up, but going to the forums is after I already spent many hours trying to look it up. I have 4 VB books and 3 Access books next to me as well as the complete MSDN library running in the background while I work and none of those sources make any reference to compareing dates to get all records falling between those two dates (And yes I looked up the BETWEEN clause).

 

[zemp]

To get all records from the lat 30 days, for a specific date field, you might try an SQL 'WHERE' clause at the end of your statement. Something similar to,

... WHERE [mydateField] >= (Date - 30)

To get the earliest and latest date in the set you might need to look into the 'Min' and 'Max' aggregate functions. However I an not sure if this will work with date types or what the syntax would be. For specific SQL syntax for Access queries and SQL for the jet engine try forum701.

zemp

 

[RichardPrewitt]

Thanks Zemp you are awesome.