Hi,

I just want to know if the next thing is possible or not and if yes how...

Let's say I have a form called 'frmDataInput' This form is unbound. On this form I have 2 combo boxes linked to a calendar control and they are called 'Begindatum' (startdate) and 'Einddatum' (enddate). I also have several combo boxes to insert an artikel number called 'Artikelnummer'.

What I had so far is that the data always had to be added on a monday for every record that is been created with the next code (only displaying the part that is important here, there is more code on the button...):


Private Sub BijkomendeInvoer_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sArt As String
Dim iStart, iEnd As Date

iStart = Me.Begindatum
iEnd = Me.Einddatum

'check artikel and insert data if not empty
If Not IsNull(Me.Artikelnummer) Or Me.Artikelnummer <> "" Then
    sArt = Me.Artikelnummer
    'Open the table
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblTestBuyBA01")
    'add all records here
    Do Until iStart = iEnd + 7
        rst.AddNew
        rst("Datum") = iStart
        rst("Artikelnummer") = sArt
        rst.Update
        iStart = iStart + 7
    Loop
        rst.Close
        Set rst = Nothing
        Set db = Nothing
End If

End Sub

This code is working fine, but what I should obtain know is that instead of the record being added on every monday, the record should be added every 5 days.

So if the first time it is entered is a monday, the next time should be the first saturday following this monday.
If the first time it is entered is a friday, the next time should be the next thursday (and not the wednesday).

In other words, the 'iStart = iStart + 7' in the code should be something like 'iStart = iStart + 5' except that when there is a sunday in between the 'iStart' and the 'iStart + 5' it should become 'iStart = iStart + 6'.

I don't know if this is clear for you or not. Basically what I would like is that the record is added every 5 days in between the given startdate and enddate (the last time on the enddate if the enddate equals the iStart = iStart + 5, otherwise the last input has to stop on the closest data that is BEFORE the enddate), but that when the date is a sunday it skips this sunday and automatically adds 1 more day.

Is this possible or not?

I am assuming there is no data entry on Sunday.

Dim iW, iDay As Integer

iStart = Me.Begindatum
iEnd = Me.Einddatum

iW = DatePart("w", iStart) 'returns day of week number 1 - 7

If iW <= 2 Then 'Sunday, Monday
   iDay = 5
Else 'Tuesday, Wednesday, Thursday, Friday, Saturday
   iDay = 6
End If


'check artikel and insert data if not empty
If Not IsNull(Me.Artikelnummer) Or Me.Artikelnummer <> "" Then
    sArt = Me.Artikelnummer
    'Open the table
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblTestBuyBA01")
    'add all records here
    Do Until iStart = iEnd + iDay
        rst.AddNew
        rst("Datum") = iStart
        rst("Artikelnummer") = sArt
        rst.Update
        iStart = iStart + iDay
    Loop
        rst.Close
        Set rst = Nothing
        Set db = Nothing
End If

Anyway, I am not sure how the iStart + 7 is working. Shouldn't that be DateAdd("d", iStart)? Or is the + 7 a short hand notation that VBA recognizes? New one on me.

I think this will do what you want.

mmcdonal

This seems to work fine. Thanks.

I have a second problem on the same topic. The records are added the way I want in the table, but I also have a query based on this table to retrieve data I need.

What I have to do is get the data based on the date again, but this works in the opposite way of the vba code.

I'm using Iif to filter the data, but I'm not getting any results...

What I need to have is that according to the day of the week I need to receive the data where the date is equal or before today's date and the date is bigger or equal to today's date minus 4 days. But when there is a sunday between these 2 days it should become minus 5 days.

This is the function:

IIf(DatePart("w";Date();1;2)=7;([tblTestBuyBA01].[Datum])>=(Date()-4) And ([tblTestBuyBA01].[Datum])<=Date();([tblTestBuyBA01].[Datum])>=(Date()-5) And ([tblTestBuyBA01].[Datum])<=Date())

When I run the query based on this function I don't get any results, but there are some data in the table...

Here is the full query's sql if this can help...

SELECT TOP 10 tblTestBuyBA01.Datum, tblTestBuyBA01.Artikelnummer, tblTestBuyBA01.Ptag, tblTestBuyBA01.Volledig, tblTestBuyBA01.Kwaliteiten, tblTestBuyBA01.Koopinstructies, tblTestBuyBA01.Voorradig, tblTestBuyBA01.Invoerdatum, tblArtikelenBA01.Artikelnaam
FROM tblArtikelenBA01 RIGHT JOIN tblTestBuyBA01 ON tblArtikelenBA01.Artikelnummer = tblTestBuyBA01.Artikelnummer
WHERE (((tblTestBuyBA01.Datum)=IIf(DatePart("w",Date(),1 ,2)=7,([tblTestBuyBA01].[Datum])>=(Date()-4) And ([tblTestBuyBA01].[Datum])<=Date(),([tblTestBuyBA01].[Datum])>=(Date()-5) And ([tblTestBuyBA01].[Datum])<=Date())) AND ((tblTestBuyBA01.Invoerdatum)=Date() Or (tblTestBuyBA01.Invoerdatum) Is Null));

What could be the problem here?