Add date days in form text field

[lmatias]

Hello everyone

Could you help me?

I can not display consecutive date days in a text field.

I have two form fields:
DayStart (field calculated with the day of the month from a field of type data - "DateStart")
DayEnd (field calculated with the day of the month from a date field - "DateEnd")

These fields only exist only in the form. The content does not come from any table.

I want to get a text field, in the form, with the following content (assuming DayStart = 2 and DayEnd = 5):
'02', '03', '04', '05'

In case the start and end dates are the same (for example DayStart = 2 and DayEnd = 2) the result must be:
'02' (not '02', '02')

It is necessary to include the prime/quotation (') before and after each day and separated by comma.

Thank you
LM

 

[Minty]

You'll need to write some VBA to achieve this and call it in the after update event of the DateEnd text box. To be honest you don't need the DayStart and DayEnd boxes.

Assuming your result text box is called txtTest and your start and end dates controls are called txtDateTo and txtDateFrom then this should work;

Private Sub txtDateTo_AfterUpdate()

    Dim iDays       As Integer
    Dim iStart      As Integer
    Dim iEnd        As Integer
    Dim iCount      As Integer
    Dim sResult     As String
    
    iDays = DateDiff("d", Me.txDateFrom, Me.txtDateTo)
    iStart = Day(Me.txDateFrom)
    iEnd = Day(Me.txtDateTo)
    
    iCount = 0
    
    While iCount <= iDays
        sResult = sResult & "'" & Format(iStart, "00") & "',"
        iStart = iStart + 1
        If iStart > iEnd Then iCount = iDays
        iCount = iCount + 1
    Wend
  [COLOR="SeaGreen"]  'Remove the last comma[/COLOR]
    sResult = Left(sResult, (Len(sResult) - 1))
    
    Me.txtTest = sResult
    
End Sub

Oh and welcome to the forum!

 

[Gasman]

Damn,
A lot quicker than me.:banghead:


This was my attempt

Sub ConcatDate()
Dim dtStart As Date, dtEnd As Date
Dim lngDay As Integer, lngDiff As Integer
Dim strText As String
dtStart = #5/16/2018#
dtEnd = #5/20/2018#

lngDiff = DateDiff("d", dtStart, dtEnd)
For lngDay = 0 To DateDiff("d", dtStart, dtEnd)
strText = strText & "'" & Day(dtStart + lngDay) & "', "
    Debug.Print strText
Next
strText = Left(strText, Len(strText) - 2)
Debug.Print strText
End Sub

 

[Minty]

@Gasman - Yours is more verbose, I was trying to make mine really VBA for beginners

 

[lmatias]

I like your proposal too but how do I call the result to a text field?

Thanks

Damn,
A lot quicker than me.:banghead:


This was my attempt

Sub ConcatDate()
Dim dtStart As Date, dtEnd As Date
Dim lngDay As Integer, lngDiff As Integer
Dim strText As String
dtStart = #5/16/2018#
dtEnd = #5/20/2018#

lngDiff = DateDiff("d", dtStart, dtEnd)
For lngDay = 0 To DateDiff("d", dtStart, dtEnd)
strText = strText & "'" & Day(dtStart + lngDay) & "', "
    Debug.Print strText
Next
strText = Left(strText, Len(strText) - 2)
Debug.Print strText
End Sub

 

[Minty]

If you read my explanation, you will see my code sets an unbound text box called txtTest to the value.

Is that not what you wanted to do?

 

[Gasman]

You assign the text box to the value returned by the code.

In Minty's example he has already given you a dummy example named control called TxtTest
Mine was just an example, but similar to Minty's it would be

Me.TxtTest = strText

Where you put the code is up to you.

You could make it a function and pass the two dates in each time, but one step at a time. However you need to understand the code to really be able to use it.
Also how big is the textbox going to be?, what if the dates are 3 months apart?