Sep 21 2006, 01:43 PM
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?
Sep 21 2006, 01:48 PM
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.
Sep 21 2006, 01:49 PM
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.
Sep 21 2006, 01:50 PM
I like your solution better than mine!
Nice to see you lurking and I trust all is well with you!!
Sep 21 2006, 01:54 PM
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.
Sep 21 2006, 02:03 PM
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?
Sep 21 2006, 02:10 PM
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!!!
Sep 21 2006, 02:20 PM
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.
Sep 21 2006, 02:27 PM
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....
Sep 21 2006, 02:40 PM
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.
Sep 21 2006, 02:47 PM
Try using the input mask wizard on the text boxes...
Sep 21 2006, 03:02 PM
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...
Sep 21 2006, 03:32 PM
Thank you guys!
Sep 21 2006, 03:38 PM
You are very welcome!
The rest of the guys and Jack
Sep 21 2006, 03:50 PM
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.
Sep 21 2006, 04:11 PM
If Format(YourDateControl, "ddd") = "Mon" Then
..subtract two days here
..subtract 1 day here
Pure air code but should get you started...
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here