Auto Date Detection on Form

[slenish]

Hello all,

I am trying to create a text box that will automatically create a date based on the day of the week.

What im trying to do is have a text box look at if its Sunday - Thursday to take the current date and add 1 to it if its Friday to add 3 or Saturday to add 2.

Im not sure if this is actually possible or not but what I have been trying to do is I have two text boxes. One that is a constant Date() the other that checks for what day its on and then add accordingly. Problem is even with formating the constant Date() txtBox to only "dddd" it does not want to recognize that its a day of the week I either get a type mismatch error or nothing at all.

Any help would be apprciated

thanks so much,

slen

here is an example of what i have been trying

 
txtDate1.Defaultvalue = Date()
if format(txtDate1,"dddd", vbWednesday) then
    txtDate2 = txtDate1 + 1
end if
 

[mshmyob]

To add a certain number of days to a date use the DateAdd function.

Dim dteDate2 as Date
dteDate2 = DateAdd("d",1,Date())
 

cheers,

[slenish]

Thanks for the reply mshmyob,

Ok now with your example how do I make it add the date when its a certain day??

[mshmyob]

Use the Weekday function to determine what day it is. The weekday function will return a number based on the date. ie: 1- for sunday, 2 - Monday etc.

Once you know that then use a Select case to determine how many days to add to the current date as mentioned earlier.

If you need more clarification let me know but try the code first and if you have problems I will correct it for you.

cheers,

[slenish]

Hi mshmyob,

Thanks for the quick reply back. So i looked up the Weekday function and from there I ended up getting it to work the way I wanted it to. Im still not sure if I coded it the right way so im attaching my demo for you to take a look at and see if there is any improvments that could be made.

thank you again for the help could not have gotten this other wise

:D

[mshmyob]

Hello Slenish,

I have looked at it and I can clean this up quite a bit for you. Before I do that please explain again what you are attempting to do. For instance what are the hidden dates for? Also can I just create a simple combo box and dynamically populate it for you?

cheers,

P.S.: Actually explain in detail what you are trying to accomplish because your code is very confusing. Maybe if I understood what you are exactly trying to do in detail I can come up with a much better solution.

[slenish]

Hello mshmyob,

I appreciate you taking at look at this for me.

Let me break it down in more detail for you. The hidden date fields were for the test. Actually that will become just one txtbox that is hidden that the drop down box looks at to see what the date is then adds 1 to the date depending on the day of the week.

The reason behind this is the program is used for a mail processing. The people using it usually process for the next business day but when the weekend comes they process for Monday.

The drop down menu will give you a selection of two dates (i just put more in to test it for each day) one for the current date and one for the next day date.

hope this helps clear up any confusion :D

[mshmyob]

Yes that makes more sense. I have attached a sample database for you. See if this helps.

Basically I created a combo box and a text control on the form. The combo box will be dynamically populated based on the current date. It will hold only two dates - the current date and the next processing date based on the info you gave me in the first post. After selecting a date from the combo box the text box will display which date you chose.

This is the code in the ON LOAD event of the form.

' ------ populate the date combo box ------
 
Dim strRowSource As String
 
strRowSource = "" ' clear out the strRowSource variable
strRowSource = Date
' ------- determine the next available processing date based on the current date ----------
Select Case Date
    Case Weekday(Date) = 6 ' friday
        strRowSource = strRowSource & ";" & DateAdd("d", 3, Date)
    Case Weekday(Date) = 7 ' saturday
        strRowSource = strRowSource & ";" & DateAdd("d", 2, Date)
    Case Else
        strRowSource = strRowSource & ";" & DateAdd("d", 1, Date) ' sunday to thursday  
End Select
' populate the combo box
Me.cboDate.RowSource = strRowSource
 
' ------ stop populating the date combo box ------
 

This is the code in the ON CLICK event of the combo box.

Me.txtDateSelected = Me.cboDate.Value
 

cheers,

[slenish]

Hi mshmyob,

Very nice! I like how you cleaned that up looks a lot better than what I did. Really apprciate you taking the time to do this.

Thank you very much!

[mshmyob]

You're welcome. Good luck with the rest of your project.

cheers,