Using dates in a dialog form

[retro]

I have a form that is used to specify criteria in a query, namely the date range for a report (much like the Sales By Year Dialog form in NorthWind). There are two text boxes: StartDate and EndDate.

Currently, I have to enter both details manually. I want the report to always list one week at a time, from Monday to Saturday. Is there any way of making it so that the value of EndDate is automatically 5 days ahead of StartDate?

Many thanks in advance to anyone that can help!

*EDIT* forgot to mention - to identify it as a date, I used a format of dd-mmm-yyyy.

 

[___]

Here's a few Date Functions that may help you.

The current month:
DateSerial(Year(Date()), Month(Date()), 1)

The next month:
DateSerial(Year(Date()), Month(Date()) + 1, 1)

The last day of the current month:
DateSerial(Year(Date()), Month(Date()) + 1, 0)

The last day of the next month:
DateSerial(Year(Date()), Month(Date()) + 2, 0)

The first day of the previous month:
DateSerial(Year(Date()), Month(Date())-1,1)

The last day of the previous month:
DateSerial(Year(Date()), Month(Date()),0)

The first day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 1, 1)

The last day of the current quarter:
DateSerial(Year(Date()), Int((Month(Date()) - 1) / 3) * 3 + 4, 0)

The first day of the current week (assuming Sunday = day 1):
Date() - WeekDay(Date()) + 1

The last day of the current week:
Date() - WeekDay(Date()) + 7

The first day of the current week (using settings in Options dialog box):
Date() - WeekDay(Date(), 0) + 1

The last day of the current week:
Date() - WeekDay(Date(), 0) + 7


HTH

 

[retro]

Thanks very much for your quick response

 

[Oldsoftboss]

could you just put in the after update event of the beginning date text box

Me.EndDateTextBox = me.BeginningDateTextBox + 5

And the date functions by Pat Hartman in a working Db

 

[retro]

Thanks for the replies, guys.

The functions worked great, but unfortunately are only of limited use in the project I want them for. I need to be able to select ANY week, either this week or a previous week. Doing this by entering a date would be easiest. So yes, I think the event procedure is the way to go.

I looked at the database attached, and it is very interesting. I think that should have my solution

Many thanks.

 

[___]

http://support.microsoft.com/default.aspx?scid=kb;en-us;210604

 

[Oldsoftboss]

And here is a little calendar thingy that I include in nearly all my Db's these days.

The double click code can be put in any field and the date will be returned.

Dave

 

[Mile-O]

I always prefer calendars over Enter Parameter Value boxes.

 

[Oldsoftboss]

Hopefully that calendar thing will help.

 

[retro]

Wow! THANKS!!! I was thinking about a calender thingy, but thought that might be a bit advanced for me at the moment. I'll certainly give that a look

I have a weird problem. I could view the code of the event procedures in the Hartman db, but when i go back to mine and try to build event > code under the form, it says:

Microsoft Access failed to create the Visual Basic Module '|.'

If your database is on a network drive, check your network connection, then try again.

I think this may be because I had imported a form from another db, but didn't use it so deleted it from mine.

How do i rectify this??

 

[Mile-O]

Q287443

 

[Oldsoftboss]

Very uninformative, Mr Microsoft. Basically says you are in the shit

Teaches you to back up on the hour, but.

 

[retro]

Yup, they're a lot of help there!!

I like the way they tell you how to see the error! As if someone would actually be browsing their pages and think, ooh that sounds fun, let's try it!!!!!!!!

OK, back to the beginning then.....

 

[retro]

To Oldsoftboss (or anyone who knows!!)

How do I get that calendar into my database? I tried exporting the forms, but when you double click the text box, it says:

The expression On Dbl Click you entered as the event property setting produced the following error: Error accessing file. Network connection may have been lost.

Argh not again! I tried importing to a new db with the same result.

The form says that the calendar is an ActiveX Control. I haven't really worked much with ActiveX Controls, but don't I have to have the actual ActiveX Control in my database somewhere??

 

[___]

Goto Insert >> ActiveX Control and select it from the list.

 

[Oldsoftboss]

Bad bad news Your Db is stuffed

I'm sure when you check you can not access the VB window. The only solution is to go back to a recent working copy and start again.

http://www.access-programmers.co.uk...p?s=&threadid=60024&highlight=connection+lost

I'm sure the Microsoft link says that you need to copy the code to a text file, which is not much help if you cant access the VB window.

Dave

 

[retro]

More date woes!

I have sorted my form... almost!

I have a text box called StartDate, which gets the date from the calendar as per the attached sample. I also have a text box called EndDate. This is where my problem is...

EndDate has a criteria of Val(StartDate + 5). It has a Short Date format. However, doing it this way... well although it adds 5 days to the date, it has also become 1900! I have the millennium bug in my Access 2000 database!!!

Hmm, what am I doing wrong? HELP!!!!

Thanks in advance.

 

[retro]

come to think of it, this doesn't do what I want at all! Maybe I can use what was in that previous database......

 

[R6Flyer]

retro,

Your end date text box needs to be Val(StartDate)+5 - notice the positioning of the brackets.

Cheers

Flyer

 

[retro]

Hi,

Thanks for the reply. Unfortunately, that does the same thing (it adds 5 to the day part, but the rest resets to jan 1900)!

I need a function, such as in the UsefulDateFunctions zip that was previously attached, taken from the MS article 210604. It works great in that table, I just need to figure out how to get it to work here!!

what we have is the following:

A function in a module:

Public Function LstDayCurWeek(InDate As Date) As Date
LstDayCurWeek = InDate - Weekday(InDate) + 7
End Function

An After Update Event in the Form:

Private Sub cmbFunction_AfterUpdate()
Me.txtReqDate = Eval (Me.cmbFunction & "(#" & Me.txtBaseDate & "#)")

To explain, the form that is from has 3 essential parts:

Text Box: txtBaseDate
Combo Box: cmbFunction
Text Box: txtReqDate

My form has the following:

Text Box: StartDate
Text Box: EndDate

I want EndDate to be StartDate + 5 days.

Basically, I need to know how to change the Eval to fit. In the test form, the Me.cmbFunction returns LstDayCurWeek, so I would've thought:

Me.EndDate = Eval (LstDayCurWeek & "(#" & Me.StartDate & "#)")

But that throws back an error. I guess it doesn't like me putting in the function name direct.

HAYLP!!