How do I refer to date control in SQL?

[Guest]

My report runs once a year and lists all the addresses which were added since
the previous run. Right now, the RecordSource SQL says

WHERE [AddDate]>#6/21/2005#

I have to update that filter after each year's run. To simplify things for
me, I would like to add a hidden TextBox formatted as a Short Date which I
manually update (in design mode) after each run, but I don't know how to make
the SQL filter refer to the TextBox. How do I do that?

Or, is there a better way altogether to approach this?

Thanks!

 

[Marshall Barton]

My report runs once a year and lists all the addresses which were added since
the previous run. Right now, the RecordSource SQL says

WHERE [AddDate]>#6/21/2005#

I have to update that filter after each year's run. To simplify things for
me, I would like to add a hidden TextBox formatted as a Short Date which I
manually update (in design mode) after each run, but I don't know how to make
the SQL filter refer to the TextBox. How do I do that?

You refer to the text box in the query's criteria this way:

WHERE [AddDate] > Forms!theform.thetextbox

But what's the benefit in changing a form's design instead
of changing the query's design?

Personally, I would make the form's text box visible and
type in the date whenever I want to run the report.

 

[OfficeDev18 via AccessMonster.com]

I like the idea of the invisible text box. Keep the text box unbound. In your
VBA, after your report is run for the year, add the following line:

Me.TextBoxName.Value=Date()

In your SQL, as Marshall says, just change it to read

WHERE [AddDate] > Forms!theform.TextBoxName

That frees you from making any manual entries at all.

Hope this helps,

Sam

My report runs once a year and lists all the addresses which were added since
the previous run. Right now, the RecordSource SQL says

[quoted text clipped - 5 lines]

manually update (in design mode) after each run, but I don't know how to make
the SQL filter refer to the TextBox. How do I do that?

You refer to the text box in the query's criteria this way:

WHERE [AddDate] > Forms!theform.thetextbox

But what's the benefit in changing a form's design instead
of changing the query's design?

Personally, I would make the form's text box visible and
type in the date whenever I want to run the report.

 

[OfficeDev18 via AccessMonster.com]

Forget it. I must have been on Mars when I wrote that!

Sam

I like the idea of the invisible text box. Keep the text box unbound. In your
VBA, after your report is run for the year, add the following line:

Me.TextBoxName.Value=Date()

In your SQL, as Marshall says, just change it to read

WHERE [AddDate] > Forms!theform.TextBoxName

That frees you from making any manual entries at all.

Hope this helps,

Sam
[quoted text clipped - 11 lines]

Personally, I would make the form's text box visible and
type in the date whenever I want to run the report.

 

[Guest]

Maybe I don't have the best approach. Here's the situation: the report is
manually run on an inexact date. So, I may run it in June this year and July
next year, etc. The idea is to list all new addresses added since the last
run (on that uncertain date.) Basically, I need for the report to know when
it last ran. I know I can create a one-field table to hold the date and
programatically update that each time, but that seems like adding clutter to
my long table list. But it could be "cleaner" for the user running it from
my switchboard interface. What would you do?

--
Dave

My report runs once a year and lists all the addresses which were added since
the previous run. Right now, the RecordSource SQL says

WHERE [AddDate]>#6/21/2005#

I have to update that filter after each year's run. To simplify things for
me, I would like to add a hidden TextBox formatted as a Short Date which I
manually update (in design mode) after each run, but I don't know how to make
the SQL filter refer to the TextBox. How do I do that?

You refer to the text box in the query's criteria this way:

WHERE [AddDate] > Forms!theform.thetextbox

But what's the benefit in changing a form's design instead
of changing the query's design?

Personally, I would make the form's text box visible and
type in the date whenever I want to run the report.

 

[Marshall Barton]

Maybe I don't have the best approach. Here's the situation: the report is
manually run on an inexact date. So, I may run it in June this year and July
next year, etc. The idea is to list all new addresses added since the last
run (on that uncertain date.) Basically, I need for the report to know when
it last ran. I know I can create a one-field table to hold the date and
programatically update that each time, but that seems like adding clutter to
my long table list. But it could be "cleaner" for the user running it from
my switchboard interface. What would you do?

That's different than what I thought you were doing.

If you are the only one running the report it probably
doesn't matter too much how you do it.

If somebody else is running it, then I would go with an
additional date field in the addresses table. Then, the
report can be generated by filtering the records to those
with a Null in that field. The report can use its Close
event to execute an UPDATE query to set the records with
Null in that field to Date(). If for one reason or another,
you should ever need to rerun the report, it would just be a
matter of filtering on the date that the report was run.

 

[Guest]

Thanks, Marsh. After thinking about it, I will go with the additional table.
I'll be retiring next year, and if I can save the new guy the additional
nuisance of having to fiddle with an unfamiliar report, I'll tolerate having
a one-field table for the job.