How to create a date using strings in vba?

Question

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

Can you edit your question with the error message?

Fjodr
– Fjodr

2015-06-15 12:41:30 +00:00
Commented Jun 15, 2015 at 12:41 — Fjodr
– Fjodr, Commented Jun 15, 2015 at 12:41

HansUp · Accepted Answer · 2015-06-15 12:48:09Z

5

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

edited Jun 15, 2015 at 12:48

answered Jun 15, 2015 at 12:43

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HansUp Over a year ago

You're welcome. I was uncertain what you had in mind when you said last day of year. That made me think DateSerial(rs!dan, 12, 31) But I decided to just translate what you already had to DateSerial

Coding Enthusiast Over a year ago

No problem. I usually don't ask the exact thing i am looking for, I look for similar stuff so I can figure the rest out. If I ask the exact thing I will get lazy :D

Coding Enthusiast · Accepted Answer · 2015-06-15 12:41:50Z

1

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

answered Jun 15, 2015 at 12:41

Coding Enthusiast

3,9431 gold badge31 silver badges50 bronze badges

2 Comments

Gustav Over a year ago

No, don't go the string route, use DateSerial.

Coding Enthusiast Over a year ago

ok. as you can see above, i accepted the answer with dateserial not mine. and the string route is a possibility even if it is the one that is more efficient @Gustav

Collectives™ on Stack Overflow

How to create a date using strings in vba?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related