searching for dates

[cocoonfx]

I have to database where people enter information and some times miss entering in a field value called [Scanned Invoice] the db automatically gives every entry a date.

The first part was find out which jobs where missing a [scanned invoice] 24hrs ago by making a query [scanned invoice] Is Null [Date Entered] =date()-1 which works fine.

I then realised that sometimes people access the system on a saturday and as the system used globally and different countries have weekends on different days and theres the issues of every country having different back holidays. So i think that rules out networking days.

Is there away where i can get the query to check if the previous day had and entry and keep going back until it finds the next entry?

I tried an iif statement but i am new to Access and i could not get it to work.

I have tried iif(date()="0",date()-1) that seems to work but i can not get it to work any futher? HELP????

 

[RV]

How 'bout tackling this issue at its roots?
In fact you're saying that [Scanned Invoice] is a required field.
So define it as required on your form or use a Before Update on the control preventing is from being null and requiring the user to enter the date.
If no date is entered, your user won't be able to continue.

Do a search on the forum, you're talking 'bout a well known issue

RV

 

[cocoonfx]

thank you

Unfortunately this is for only we customer so setting scann ivoice to complusary would cause a lot of problems policatlly within my organisation.

 

[cocoonfx]

The reason for just looking at one date is that you could have over 2000 enteried per date as over 24000 people have access to the corporate database at one time.