creating a date expression in access

[reb0101]

This one is a tough one, at least for me.
I have an app I’m working on that will require the users to confirm a product once a month.
There are a couple of hundred products or in this case, records.
Once a month they open an access form and will click on a calendar icon.
A calendar comes up and they click on the date they are confirming the supply of that product.
The date format is as follows: 08-Jan-07
They have to go in and confirm the product at least once a month. It doesn’t matter when in the month they do it, just so long as once a month they confirm the product.
Once they fill out that date, as well as other data it is exported as an excel spreadsheet.
I need to figure out a query or some other way to scan all the records and ‘ignore’ every record that has a date for the previous month input.
On Jan 01 it will scan all records and ignore any records that have any date from December filled in.
If no date for December is input it will ‘flag’ it and mark it as delinquent.
This is the hard part I am having a hard time figuring it. I’ve never been too good with the ‘Now’ function in Access.
On December 01 when it is run it will not only have to flag any records that do not have a November date, but any that are blank (no date at all ) or any previous date before November.
In some cases they get by for 2-3 months without being input.
I just can’t figure out how to set this up and ‘carry’ it month to month.
Jan capturing all records that were not input with a Dec date, Feb capturing all records that were not input with a Jan date, etc., etc.
Any and all help will be very much appreciated.

[GolferGuy]

If I understand you correctly, during any month, you want to flag records that were not CONFIRMED last month. So, if the CONFIRM date is any time before the 1st day of last month (including empty dates) you want to flag that record as delinquent. Here is the formula for the criteria:

Is Null OR DateSerial(Year(DateAdd("m", -1, Date)), Month(DateAdd("m", -1, Date)), 1)

That whole formula would go into the criteria row for the field with the confirm date.
HTH,

[reb0101]

Thanks GolferGuy,
I think (hope) I’m almost there.
This is what I have;
There is a total of 240 records in the table. I went in and stripped out all sign dates.
I created the query and this is what I have in the criteria block for capturing the range of dates.

Between [Forms]![EXPORT_HR]![hrsign] And [Forms]![EXPORT_HR]![transmit]

EXPORT_HR is the name of the form with the calendar icon to insert the dates.
hrsign is the name of the field corresponding to the actual field in the table, which is
DATE_HR-SIGN.
The transmit is the date field they will plug in for the day they are transmitting the data.
They are supposed to send every Friday so they would put in that date.

Having the table completely empty of any dates, I went in and put the following dates in the first 12 records. 04-Dec-06 for 6 records, and 04_Nov-06 for the next 6 records.

(6) 04-Dec-06
(6) 04-Nov-06

I then went to the bottom of the table, and the last 6 records I put in

02-Jan-07
03-Jan-07
04-Jan-07
05-Jan-07
06-Jan-07
07-Jan-07

I then opened the EXPORT_HR form and for the hrsign date I put in 01-Jan-07 and for the transmit date I put in 15-Jan-07.
The excel spreadsheet opened and returned these records;

02-Jan-07
03-Jan-07
04-Jan-07
05-Jan-07
06-Jan-07
07-Jan-07

So it’s working, just doing the exact opposite of what I want it to do. I want it to ‘ignore’ the dates between 01-Jan-07 and 15-Jan-07 and capture all the rest, approximately 234.
so what I need to do (and have tried but my syntax is off) is for it to basically say
“Is Null or Not Equal to Between [Forms]![EXPORT_HR]![hrsign] And [Forms]![EXPORT_HR]![transmit]

That way, if it worked, and correct me if I’m wrong, would capture all that are null and any that are NOT falling in the range that is ‘captured’ from the ‘from and to’ above.

It works quite well with the: Between [Forms]![EXPORT_HR]![hrsign] And [Forms]![EXPORT_HR]![transmit]

but I just don’t know how to do a Is Null or not equal to in front of it without it bombing out.
any ideas?

[GolferGuy]

Between Date1 and DateTwo is just another way of saying >= Date1 AND <= DateTwo. So, armed with that knowledge, here is what we now want:
Is Null OR <Date1.
If you really would like any dates that are greater than DateTwo, then you would use this:
Is Null OR <Date1 OR > DateTwo
Hope this helps,
Vic