Missing the weekends? 1

[tinymind]

Hello All ...

I have a problem concerning a form, a table and some VBA ...

The problem ....

This form has an UserName field and two unbound fields, BeginDate and EndDate. Also on this form I have one button ... This button has to take the UserName from the form and append the records (individualy per day) to a table .... the catch is I dont want the dates added that represent a weekend ... How do I go about this ...

The table is called ... "SubHols"

Any VBA programmers out there that can help out a VB learner ... Thank You ....

Tiny

 

[FoxProProgrammer]

What date do you want to add to the table if BeginDate or EndDate falls on a weekend? Do you want to store the previous Friday, following Monday, or nothing? dz
dzaccess@yahoo.com

 

[tinymind]

Hi DZ ...

BeginDate will NEVER be on the weekend and that is the same for the EndDate.

So if the user enters a BeginDate it will always be Mon-Fri as with the EndDate ...

The only thing is I don't want my DB to record the weekend dates ...

Example:
BeginDate: 18/July/2000
EndDate: 24/July/2002

Total records I want to record are 5 and the entries that are missing are that of the 20/21 July 2002.

I hope that is clear ...

Tiny

 

[FoxProProgrammer]

Just to make sure that I understand the issue...

BeginDate is 18 July 2002
EndDate is 24 July 2002
UserName is TinyMind

You want to add five records to the table:
UserName Date Field1 Field2 FieldN
TinyMind 07/18/2002 ...... ...... ......
TinyMind 07/19/2002 ...... ...... ......
TinyMind 07/22/2002 ...... ...... ......
TinyMind 07/23/2002 ...... ...... ......
TinyMind 07/24/2002 ...... ...... ......

Let me know if this is correct and I'll give you some code examples.
dz
dzaccess@yahoo.com

 

[tinymind]

That is exactly what I need the button to do ...

Cheers DZ

Tiny

 

[FoxProProgrammer]

There might be any number of ways to do this. Here is one:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim incDate As Date

Set db = CurrentDb
Set rs = db.OpenRecordset("yourtablename", dbOpenDynaset)

For incDate = startDate.Value To endDate.Value
If (Weekday(incDate) <> 1) And (Weekday(incDate) <> 7) Then
rs.AddNew
rs!UserName = UserName.Value
rs!thisdate = incDate
rs!Field = ......
rs.Update
End If
Next incDate
rs.Close
db.Close

You will need to modify my code to match your table field names, form field names, table names, etc., as appropriate. I hope that this is what you were looking for.

Best,

dz
dzaccess@yahoo.com

 

[tinymind]

Absolutely Perfect .....

Thans DZ!!!

Saved me a lot of hassle ...

Regards,

Tiny

 

[FoxProProgrammer]

You're welcome, Tiny! I'm glad that it helped. dz
dzaccess@yahoo.com