CODEWITHSUNDEEP
ms-accessvbams-access-2003
Coding Enthusiast
Coding Enthusiast

Reputation: 3933

How to create a date using strings in vba?

I want to run a for-loop from sometime to some specific time. Let's say from the first day of the year to the last day:

I am given the year and I need to add the month and the day to it:

I am trying to concatenate into a full date string and the compiler keeps throwing errors: 

dim dt as Date 
dim rs as recordset 
set rs = currentdb.openRecordset("select max(date) as dan from Atbl")

for dt = #1/1/year(now)# to #2/2/Year(rs!dan)#
    msgbox dt
Next

any help is appreciated. or any hint is welcome

Upvotes: 2

Views: 446

Answers (2)

HansUp
HansUp

Reputation: 97101

DateSerial should make this easier.

Give it values for year, month, and day in that order, and it will give you back the corresponding date as a Date/Time value.

for dt = DateSerial(Year(Date), 1, 1) to DateSerial(rs!dan, 2, 2)
    msgbox dt
Next

Upvotes: 5

Coding Enthusiast
Coding Enthusiast

Reputation: 3933

Nevermind, I found a solution quite fast: This works fine

dim dt as Date 
dim rs as recordset 
set rs = currentdb.openRecordset("select max(date) as dan from Atbl")

'save the first day as string first
Dim firstDay As String
firstDay = "1/1/" & Year(Now)
'convert to date
Dim firstDate As Date
firstDate = CDate(firstDay)

'save as string
Dim lastDay as string 
lastDay = "2/2/" & Year(rs!dan)

'convert to date
Dim lastDate As Date
lastDate = CDate(lastDay)

'works fine in here
for dt = firstDate to lastDate
    msgbox dt
next

Upvotes: 1

Related Questions