Date condition for autoexec macro to only run once a day (1 Viewer)

Hi Everyone! First post here, I am relatively new to access. I started a new job a year and a half ago and have been using access daily but now I am getting more into the development side of things. I am running access 2007 with the database saved as .mdb (2000 version) and I have come accross a recent issue that I cannot figure out. :banghead:

I am trying to create an autoexec macro with a condition to only run once a day when I start up my database. I looked into doing it via VBA but came across the autoexec option looking through some other forums. Here is what I have so far.

I have created a table "tblUpdated" with one field "DateUpdated".
I have also created a query "tblUpdated_Append_Today" with the "tblUpdated" table and one column - Field: Expr1: Date() / Append to: DateUpdated

The macro "autoexec" is as follows:
1: Action:set warnings / Arguments:No
2: Condition: DCount("[DateUpdated]","[tblUpdated]","[DateUpdated] = #" & Date()& "#")=0 / Action: StopMacro
3:Action: MsgBox / Arguments: Got it?, Yes, None,
4:Action: OpenQuery / Arguments: tblUpdated_Append_today, Datasheet, Edit

What I want to have happen is when the database opens for the first time it counts the number of dates matching today's date, if there are none then the DCount() formula should equal 0 and then (0=0) and the macro will stop. And then it will add today's date to the table so if I open the database again, the DCount Formula will equal 1 and then 1=0 will keep the stop macro from running. and continue with the message (which is just a test dummy macro).

I have messed around with the formula trying to get it to work (using quotes ' instead of # etc. and it doesn't seem to work, I'm wondering if it has to do with the field type in the table? (I also tried "*" at the beginning of the DCount and got this error: Syntax error (missing operator) in query expression '[DateUpdated # = 9/21/2016#'.

You do realize that Date() returns a DateTime value with the time set to midnight, right?

Not sure if I did realize this, but if the table field is set to format date mm/dd/yyyy would that make it work for the append?

I do not think so. Format is simply a display effect. Try extracting just the date from each element of the compare.

"Try extracting just the date from each element of the compare."

Any advice on how exactly to do that? (sorry I'm a newb)

The DateValue() function can extract just the date from a DateTime value/field.

You could also use the Format() function on each element to just return the date.

What I ended up doing to resolve this issue was put a date field on the form that updates when the big macro runs. I then created a macro that opens that form and then has a condition of [Forms]![My date field]<Date() to run the "big macro" (with all the queries). The "big macro" runs and updates the date field so if the database is opened again the condition to run the big macro is not met and does not run. Feels so good to finally have a solution! Hope this helps anyone else with the similar issue!

Outstanding! Thanks for posting back with your success and solution.

Did you want to mark this thread as Solved?

Yes! just did this, thanks!!