query to see blank records with no date in them

[burrina]

I saw a post of yours from a long time ago about this and find myself in the same spot. I will post screenshots and explain as best I can of what I am after. My db is a Check Register db with a scheduler to export the data to the registry table. problem is of course that some events get missed and dont get exported or posted to the check registry. The same as it is in Quicken or Quickbooks if your familiar with them. So I need to see all of my missed scheduled events and decide what to do with them.
This database accomplishes several tasks:
1. Allows user to schedule an event and then export it into the Check Register.
2. Data is stored in the table tblEvent.
3. Some events are reoccurring and some are not.
4. Some are daily, weekly, monthly, quarterly and yearly.

Design:
This would take much explaining to get to everything.What I am after is getting how many times the event has been missed for every EventID and then asking the user if they want to enter it into the Check Register or Can the Event! It is date sensitive and can't be an exact desired result since there are too many variables such as weekends, holidays, etc... I have tried with my queries to get the desired result but have to stop since my brain is fried. Two things tell me it was entered into the check register, 1. a check number (ChkNo) and also (enter) has been checked.

1. EventStart is date the event was first entered.
2. nextschddte is the next scheduled date when event is supposed to be entered.
3. dteentered is the date when the event was last entered.
4. missev was this a missed event yes/no.
5. missdte on what date was event missed.
To enter an event you:enter an eventstart date, a Bank, Type of transaction, Payee, Freq Amount, Debit or Credit, check number, next scheduled date, THAT'S IT. When the event is entered into the check register then dteentered gets a date, and enter is checked. IF the event is missed then missed event is checked and missed date gets a date. THE GOAL: Is to get a list of all missed events and allow the user an opportunity to either enter it or dismiss it the same way as it is done in Quicken or Quickbooks.
I can get the number of missed events but not the actual dates they were missed. Here is my sql for a missed event:
SELECT tblEvent.EventID, tblEvent.Payee, tblEvent.FrequencyAmnt, tblEvent.EventStart, tblEvent.dteentered, tblEvent.enter, tblEvent.ChkNo, tblEvent.nextschddte, DateDiff("d",[nextschddte],Date()) AS Days, DateDiff("d",[EventStart],Date()) AS Missed
FROM tblEvent
WHERE (((tblEvent.enter)=True) AND ((tblEvent.nextschddte)<date()) ((DateDiff(?d?,[nextschddte],Date()))="" AND="">0));


Cross-posted here: http://www.accessforums.net/queries/count-how-many-days-since-what-date-31801.html</date())>

 

[burrina]

I saw a post of yours from a long time ago about this and find myself in the same spot. I will post screenshots and explain as best I can of what I am after. My db is a Check Register db with a scheduler to export the data to the registry table. problem is of course that some events get missed and dont get exported or posted to the check registry. The same as it is in Quicken or Quickbooks if your familiar with them. So I need to see all of my missed scheduled events and decide what to do with them.
This database accomplishes several tasks:
1. Allows user to schedule an event and then export it into the Check Register.
2. Data is stored in the table tblEvent.
3. Some events are reoccurring and some are not.
4. Some are daily, weekly, monthly, quarterly and yearly.

Design:
This would take much explaining to get to everything.What I am after is getting how many times the event has been missed for every EventID and then asking the user if they want to enter it into the Check Register or Can the Event! It is date sensitive and can't be an exact desired result since there are too many variables such as weekends, holidays, etc... I have tried with my queries to get the desired result but have to stop since my brain is fried. Two things tell me it was entered into the check register, 1. a check number (ChkNo) and also (enter) has been checked.

1. EventStart is date the event was first entered.
2. nextschddte is the next scheduled date when event is supposed to be entered.
3. dteentered is the date when the event was last entered.
4. missev was this a missed event yes/no.
5. missdte on what date was event missed.
To enter an event you:enter an eventstart date, a Bank, Type of transaction, Payee, Freq Amount, Debit or Credit, check number, next scheduled date, THAT'S IT. When the event is entered into the check register then dteentered gets a date, and enter is checked. IF the event is missed then missed event is checked and missed date gets a date. THE GOAL: Is to get a list of all missed events and allow the user an opportunity to either enter it or dismiss it the same way as it is done in Quicken or Quickbooks.
I can get the number of missed events but not the actual dates they were missed. Here is my sql for a missed event:
SELECT tblEvent.EventID, tblEvent.Payee, tblEvent.FrequencyAmnt, tblEvent.EventStart, tblEvent.dteentered, tblEvent.enter, tblEvent.ChkNo, tblEvent.nextschddte, DateDiff("d",[nextschddte],Date()) AS Days, DateDiff("d",[EventStart],Date()) AS Missed
FROM tblEvent
WHERE (((tblEvent.enter)=True) AND ((tblEvent.nextschddte)<date()) ((datediff(?d?,[nextschddte],date()))="" and="">0));


SOLVED
</date())>