How do I create an Access VBA Do Until loop?

I'm trying to track items on rent each day.  What I have is Item#, Start Date and End Date. I need to end up with a list of items and the days they are on rent. For Example:
Item 1 went out for rent on 3/1/2015. It was returned on 3/4/2015.
Item 2 went out for rent on 3/1/15. It was returned the same day, 3/1/2015.
I need to end up with a list that looks like this
Item 1, 3/1/15
Item 1, 3/2/15
Item 1, 3/3/15
Item 1, 3/4/15
Item 2, 3/1/15
I can use the DateDiff function + 1 to get the total number of days. I think I need a loop until function
If Start = End, append Item# and Start Date to a table
If Start<> End,  for i through (DateDiff number)
Append Item# and Start +1
Next i
Do Until i= DateDiff number

I think I have the logic, I have no idea where to start with the script.

Also, I have mostly done VB in Excel. I don't know anything about whether or not I need DAO functions.
At some point down the road, I'm going to have to add rental time in hours to this but I want to understand how to do this in days first.
zinoviia660Asked:
Who is Participating?
 
Gustav BrockCIOCommented:
You will not just have a "list" but a useful list, and that could be a query.

1. Create a query which lists a series of numbers:
SELECT DISTINCT 
    [Tens]+[Ones] AS Factor, 
    10*Abs([Deca].[id] Mod 10) AS Tens, 
    Abs([Uno].[id] Mod 10) AS Ones
FROM 
    msysobjects AS Uno, 
    msysobjects AS Deca;

Open in new window

Save this as qdxFactor.

2. Create a query to list the rental days:
SELECT 
    tblRental.Item, 
    DateAdd("d",[Factor],[StartDate]) AS RentalDate
FROM 
    tblRental, 
    qdxFactor
WHERE 
    DateAdd("d",[Factor],[StartDate])<=[EndDate]
ORDER BY 
    tblRental.Item, 
    DateAdd("d",[Factor],[StartDate]);

Open in new window

Output:

Item	RentalDate
Item 1	01-03-2015
Item 1	02-03-2015
Item 1	03-03-2015
Item 1	04-03-2015
Item 2	01-03-2015
Item 3	27-02-2015
Item 3	28-02-2015
Item 3	01-03-2015
Item 3	02-03-2015
Item 3	03-03-2015
Item 3	04-03-2015
Item 3	05-03-2015
Item 3	06-03-2015

Open in new window

/gustav
0
 
Rey Obrero (Capricorn1)Commented:
test this code

Sub ListItems()
Dim rs As DAO.Recordset, vDate As Date, j As Integer
j = 0
Set rs = CurrentDb.OpenRecordset("tblItems")
Do Until rs.EOF
    vDate = rs![Start Date]
    If vDate = rs![End Date] Then
        Debug.Print rs!Item & " " & vDate
        Else
        Do Until vDate = rs![End Date]
            vDate = DateAdd("d", j, rs![Start Date])
            Debug.Print rs!Item & " " & vDate
            j = j + 1
        Loop
    End If
rs.MoveNext
Loop

End Sub
0
 
HuaMin ChenSystem AnalystCommented:
Hi,
Please do in the same way like the following
rst.MoveFirst
strTemp1 = rst!DONOR_CONTACT_ID
rst.MoveNext
'strTemp2 = rst!DONOR_CONTACT_ID

'Do While Not rst!DONOR_CONTACT_ID = rst.EOF
Do While Not rst.EOF
    strTemp2 = rst!DONOR_CONTACT_ID
    If strTemp1 = strTemp2 Then
        MsgBox "Equal"
    Else
        MsgBox "Not equal"
    End If
    strTemp1 = strTemp2
    rst.MoveNext
    'strTemp2 = rst!DONOR_CONTACT_ID
Loop

Open in new window

0
 
hnasrCommented:
"How do I create an Access VBA Do Until loop?"
I assume you are looking for the concept of using "Until" and relating that to "While"

This goes under the topic of Logical Expressions, which creates confusion for many programmers, and it introduces (logic) errors undetected by software,

Assume you have an empty box, identified as B. This is referred to as B=0. It is full when B=5
 
(Box expression is True) DO WHILE (Box expression stays True)
DO WHILE True

(B<=5) DO WHILE (B<=5)
DO WHILE B<=5

    B = 0
    Do While B <= 5
        Debug.Print B
        B = B + 1
    Loop

Open in new window

Processing a recordset (rs) starting from record 1:
(rs.EOF is False, Not rs.EOF is true) DO WHILE (Not rs.EOF stays True)
DO WHILE NOT rs.EOF
    ---


(Box expression is False) DO UNTIL (Box expression evaluates to True)
DO UNTIL True

(B<=5) DO UNTIL (B>5)
DO UNTIL B > 5

    B = 0
    Do Until B > 5
        Debug.Print B
        B = B + 1
    Loop

Open in new window

Processing a recordset (rs) starting from record 1:
(rs.EOF is False) DO UNTIL (rs.EOF  becomes True)
DO UNTIL rs.EOF
    ---
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.