query to see blank records with no date in them

burrina

New Member
Joined
Jan 26, 2013
Messages
8
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())>
 
Last edited by a moderator:

burrina

New Member
Joined
Jan 26, 2013
Messages
8
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())>
 

Forum statistics

Threads
1,081,693
Messages
5,360,668
Members
400,592
Latest member
katekoz

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top