I currently have Between "09/01/06" And "09/30/06" in the criteria of a query on a field called "lastupdated"

But I don't want to hard code those dates where I would have to change it every month. I want them to enter the date range in and then click a submit button. then on the onclick....of the command button say something like if the range is between this and this open the form with this range.

the lastupdate field has dates that goes all the way back to 1/3/2005. So they could enter 1/15/05-9/22/06. Each day data is enter so the dates would keep increasing.

Do anyone have any suggestions?

Thanks.

in the code behind your button that opens the form--

docmd.openform "FormNameHere",,,"[LastUpdated] between #" & me.txtFrom & "# and #" & me.txtTo & "#"

where txtFrom and txtTo are the names of the text boxes on the form where they enter their date range.

This assumes the lastUpdated field is in Date format.

HTH
Ben

Two controls on a form and a command button. BeginDate and EndDate or whatever you like. In the criteria line of your query:

Between [Forms]![NameOfForm]![BeginDate] And [Forms]![NameOfForm]![EndDate]

The command button runs the query or opens a form based on the query.

hth,
Jack

Ben -

I like your solution better than mine!

Nice to see you lurking and I trust all is well with you!!

Jack

yes, create an unbound form with 2 controls on it to enter the date range. Set the criteria of the column to:

BETWEEN Forms!formname!control1 AND Forms!formname!control2

Add a button to run the report.

I prefer using a form. This way you can check the ranges to make sure they don't enter the end date as the start date or other screwy things.



Edited by: ScottGem on Thu Sep 21 14:55:39 EDT 2006.

Hey--I almost PM'd you yesterday. I have been out of commission for a while from a UA perspective--there's an old curse, which I've heard called both Chinese and Arabic, that says

May you live in interesting times

From a personal and professional perspective, I did indeed live in some interesting times for the first half of the year or so. Trying now to get back to some semblance of normalcy, which of course includes some additional participation here.

How's things in SF?

Ben

Did you have a problem with your back? NONE of my business but it seems like I remember something like that... No need to answer this question, just glad to have you back!!

I have been gone for a few months myself.... Was helping to create some databases and that was taking all my time, but I think I am back to having more free time to create problems here at UA...

All is very well in the Bay Area and I am actually going into The City tomorrow just for the fun of it. I have to do it while the good weather holds as it is no fun to go when the fog is so thick you can't see the Golden Gate Bridge or other cool stuff, like Alcatraz...

Stay well and take care of yourself!!!

Jack

Nope, my issues weren't health related per se--just a totally crazy work life for a while, then my #2 was out a lot 'cause his wife was busy having a difficult pregnancy and had 10 week premature twins, then I became the executor for the estate of a guy who was in all material respects my father....just a really crazy time.

But life is more settled now, so I'm back to my old tricks.

Ben

Ben -

Glad to hear that you didn't have a back problem! Now I wonder who it was that did????

I'm glad to hear that the dust has settled, somewhat, and that we once again have your expertise close to hand!! Welcome back....

Jack

my dates in my filed are formatted to read 09/01/06.

When I try to format the txt boxes from and to to read mm/dd/yy it till defaults to the 2006 at the end and taking the 0 away. How do I prevent this from happening.

or I may be doing something wrong.

Try using the input mask wizard on the text boxes...

Much better to save the full year rather then 06... I would save my data as 09/01/2006 as there can't be any question that it is 2006 and not 1906...

My 3 cents worth...

Jack

Thank you guys!

You are very welcome!

The rest of the guys and Jack

I have one more question.

If i have a query that runs always runs the previous days work, how do I get it to exclude Saturday and Sunday.

in the criteria I put date()-1 which works for today because it shows yesterdays work. But on Monday 9/25/06 I need it to show fridays work 9-22-06 instead of 9-24-06 because the would be no updates on the weekend.

If Format(YourDateControl, "ddd") = "Mon" Then
..subtract two days here
Else
..subtract 1 day here
End if

Pure air code but should get you started...

Jack