Creating Jobs using scheduler in Access

[twychopen22]

Doc,

query 2 - using (DateDiff of today vs. last auto update in days) vs. interval, update the mark on the items due for an update.

I was just wondering how you would get the last auto update in days value to show. I think that your way is probably the best but I am not grasping the complete idea.

I hae used DatePart and can manipulate that to show next month etc. but I don't think that this is the best way to go about what I am trying to accomplish. What would happen after 12, some code would have to reset the month to -11 and the year to +1. Not sure how to do that. Any suggestions would be helpful.

 

[twychopen22]

Doc, I am still having problems with this. I have been working at this for a while trying different things and can't get it. I know exactly what I want to do for the first part but cant get it exactly.

DateAdd('d',-1(next month/1/year)

which would give me the last day of the month (even if its feb.).
If it is this date then run query.

Then I want to be able to look at the last update day and do the same thing over. I am confused here and also do not know how to add a year after december.

 

[The_Doc_Man]

OK, I'll address a couple of issues.

Your initial description WAS a bit scattered, though I was able to read between the lines on it.

You need two tables because one holds the incurred expenses and the other holds the schedule of expenses. The incurred expenses have a history. That is, old occurrences reside there. The scheduled expense table is never historical. If you stop having an expense, you take it out of the scheduled expense table.

You use the content of the scheduled expense table to drive the append queries that will update the incurred expense table. THAT's why you need two tables.

When you add the expenses, the date on which the append query updates the incurred expense table goes into the incurred expense table as "expense occurred on this date". As a SECOND action, another query should also update the scheduled expense table to show what items were appended on the current date. In other words, "action was taken on this date."

ATTENTION PURISTS: There are many ways to do this including a query to decide the most recent update of a particular expense. In this case I'm a pragmatist. Update the date of the last incursion of this particular expense. Keep it simple!

This is a case where I suggested many queries in sequence as part of a "divide and conquer" strategy for your problem.

Your schedule table must contain all the fields you want to appear in the incurred expense table plus a yes/no flag, a schedule frequency in days, and a date of last use/activation for this expense.

Make a query that updates the yes/no flag based on comparing today's date against the DateAdd (in days) of the date of last use and the frequency. If today is "older" (greater), it is time. Update the flag.

Now go through another query that selects for the updated flags only. Append your expenses as needed.

Now go through another query selecting for updated flags to update today's date in the "last activated" slot.

Last, go through another query and reset (to NO) the "due today" flags.

Make a macro that does these steps in this particular order.

Just run the macro.

 

[twychopen22]

Your schedule table must contain all the fields you want to appear in the incurred expense table plus a yes/no flag, a schedule frequency in days, and a date of last use/activation for this expense.

I have that Monthly, Quarterly, SemiAnnually, Annually thing. I want to be able to select that and havethat as the schedule frequency in days. I understand what youare saying. I agree but can't have it frequency in days because if it is 30 days and february has 28 It will not be addes as a feb. expense. I want it to do this:

IIF([FixedExpneseOccurrance]="monthly", DateAdd('m';(next month/1/2006))-1,0

I can't get the next month part. How do I show it next month, and also after the month = 12 change month to 1 for december to january.
After I get that then:

IIf (Date() >= [DateAdd]),[yes/no]=yes,[yes/no]=no

Then I can say

IIf([DateAdd]=yes),add expense AND [LastActivation]=Date() And [Yes/No]=No, 0

Except I don't knwo what to put in for "add expense" how do I do that in the query designer?

Then I need to Add:

 DateAdd('m';(next month/1/2006))-1 + [LastActivation]

(not sure how to do this one)

Then I can say

IIf(Date()>=Result of (DateAdd('m';(next month/1/2006))-1 + [LastActivation]), [Yes/No]=Yes, [Yes/No]=No

(not sure how to do this one)

That would cover it.

 

[The_Doc_Man]

You are working too hard.

DateAdd doesn't NEED to have anything done to it to automagically get the date right.

DateAdd( "d", nnn, date ) gives you a date.

If you have a FIELD called DATEADD, change its name right away. Using a reserved word is going to zap you.

The WHERE clause for the query that marks the Yes/No field should be something like ...

WHERE ( DateAdd( "d", [SchedDays], [LastDateUpd] ) > Date() ) and ....

The IIF is not constructive in this context.

Read up on UPDATE queries. You can even do this one via the query-by-example grid. Put your update query to store a constant Yes in the [DueToday] field or whatever you call it, in the first qbe grid. Put your criteria clauses in the next several columns. One of them can contain the WHERE expression (minus "WHERE") I showed you earlier. Later queries can use criteria for that same field =Yes.

 

[twychopen22]

Nevermind about what I had I figured both of those out so far. Thanks
Oh, sorry also for all this work on your part. I am obviously a newbie at this and really, really appreciate your patience!
A quick question. I have my first query like i want. It shows the original entry date and then the date a month down the road. should I start my second query here or do this

WHERE ( DateAdd( "d", [SchedDays], [LastDateUpd] ) > Date() ) and ....

In the first one?

 

[The_Doc_Man]

The way I am thinking, you use the first query to set the Yes/No field for every scheduled expense that is due or overdue. After that, your WHERE clauses become simply a matter of finding the scheduled expenses for which your "due" flag is True. So you only need the DateAdd in the first query that sets the "due" flag.

Until the final query that resets ALL "due" flags because you are done, of course.

 

[twychopen22]

I have yes/no default to no (using 1 less query) because it is not due on the entry date. If (nextactivation) is >= (today) then yes/no = yes,0

I know u said don't use the if but I don't really know much about the where...Now on to what my question was b4 edit.

Please let me know if this is heading in the right direction.
I have got the first one that shows the date entered (Date)and shows a month down the road (NextActivation) and shows if it is due today with a y/n (DueToday).
Next I have a 2nd query based off the first query with pretty much the same stuff but with today's date as an added field. (Today)
In (today) criteria I need to put the where statement correct?

 

[twychopen22]

(IIf(("NextActivation")>=("Today"),(("DueToday")="yes"),(("DueToday")="No")))

If I understand u correctly need this turned into a Where clause? I am looking it up right now. This does run but nothing is shown in the query.

 

[The_Doc_Man]

This does run but nothing is shown in the query.

You need to run an UPDATE query, not a SELECT query. Your answer makes me think you are running the wrong kind of query. Look up UPDATE queries in your Access Help. We are having a "communication disconnect" somewhere and I can't parse it out from my end.

 

[twychopen22]

I have not really messed with the query since thursday, I am working on it now...The reason I wanted to do just a normal query at first is to test to make sure that the query would draw the right records. If it did, I would just change it into an update query. You are right with what you are saying.

 

[twychopen22]

I am posting my whole database to hopefully make it more clear what I am doing. Under queries, Query1 and Query 3 are the ones that we are dealing with. The first one selects all the "monthly" expenses and the 3rd one is the one I am trying to have update. I will make it update from the fixedexpensetbl to the FixedExpenseReoccurringtbl

 

[The_Doc_Man]

Unfortunately, my site's rules prevent me from downloading anything from an overseas site. I can't look at it for you. Someone else might, but I'm not allowed. (Side effect of working for the U.S. Government.)

 

[twychopen22]

Ok, I have it somewhat working. The first query shows -1 if due and 0 if it is not due. The field is a Yes/no but it is not returning yes or no, it is returning those values. I guess I could use these values to continue making the queries but I would rather stick with yes not. Also, it is asking for a parameter whenever I start the query. as long as I don't enter anything it runs. How do I get just the field name (nextactivation) in the IIf statement?
DueToday: IIf(([NextActivation])=Date(),Yes,No)

 

[twychopen22]

I have it updating now, still with the problems from last post though, I need to tell it to reset to "no" now and updatet (LastActivation) to today (or whatever the day it was updated)

 

[The_Doc_Man]

Don't sweat 0/-1 for no/yes (respectively). That occurs because a yes/no field is a DERIVED type, not a NATIVE data type.

Yes/No is actually just a BYTE integer to which special formatting rules have been defined. Just like the Access Date/Time field is a DOUBLE to which special rules have been applied. So when you see 0/-1 you are looking at the underlying field. But don't worry, Access understands the two choices correctly and will do the right thing.

 

[twychopen22]

Got it! Thanks. Now, I am reading (just bought access 2002 Developer's handbook set) and looking at update queries but I am not getting too far. I have 3 of 5 (i think 5) queries done. I have the append query that adds the records to the 2nd table "FixedExpenseReoccurranceTbl". I need the query to update two fields: Yes/No and LastActivation

Here is what I havebut don't know if I am even in the ballpark.

UPDATE FixedExpenseReoccurranceTbl
SET FixedExpenseReocurranceTbl.Date, 
FixedExpenseReocurranceTbl.FixedExpense, 
FixedExpenseReocurranceTbl.Amount, 
FixedExpenseReocurranceTbl.FixedExpenseOccurrance, 
FixedExpenseReocurranceTbl.Notes, 
FixedExpenseReocurranceTbl.ID, 
FixedExpenseReocurranceTbl.LastActivation, 
IIf("Yes","No","No") AS DueToday, FixedExpenseReocurranceTbl.NextActivation
FROM Query02;

 

[twychopen22]

HAHAHAHAHA I finally got it to work! All queries are up and running. Geez thanks so much for your help!!!!