#6/5/2003# date thing question 2

[MrMajik]

Can the date values between the pound signs be manipulated using variables?

I tried
#6/ & myVariable & 5/2003#
#6/# & myVariable & #5/2003#

Both generate an error message.

Thank you.

Thank you.

MrMajik

Everything should be made as simple as possible, but not simpler
--Albert Einstein

 

[CmPaiva]

Hi,

What do you want to concatenate?

It should be as strings as :

#6/ & myVariable & 5/2003#

should be:

"#6/" & myVariable & "5/2003#"

but what does myVariable contains? sinte it will be appended to the existing date, it will give a non-sence string.

What do you want to pass to your SQL?

Tell me more in order i can help you!

Carlos Paiva

 

[MrMajik]

Hi Carlos;

I have a loop that runs from 1 to lastDayOfTheMonth

For i = 1 To lastDayOfTheMonth
choice = CDate(Str(Month(Now)) & "/" & i & "/" & Str(Year(Now)))
'other code goes here...
Next i

This is a way to get the desired result but it just seems lengthy and messy. I thought manipulating #any date# would be a better way of doing the same thing.

Is it?

Thank you.

MrMajik

Everything should be made as simple as possible, but not simpler
--Albert Einstein

 

[CmPaiva]

Hi, again.

Having a start date of xx/yy/zzzz, by definition of the date type, adding a number X to a date, gives a date with on offset of X days. So you only need to do as:

For i = 1 To lastDayOfTheMonth
choice = Now() + i
'other code goes here...
Next i

I hope this helps,
Carlos

 

[MrMajik]

That is a simple way that unfortunately will not work in this application because the month, day and year can change to any date. Thus the need to be able to change the values within the pound sign.

Any ideas?

Thank you.

MrMajik

Everything should be made as simple as possible, but not simpler
--Albert Einstein

 

[CmPaiva]

I'm not realy understanding the problem. You want to loop throught days, months or Years? Is this gonna be used in sql? (#..your date..# pound signs seems to say so)

Anyway, you could always use the Format() function to do the job as:

format(...your date...,"\#mm-dd-yyyy\#&quot

Sorry my misunderstanding of your problem.
Please explicit it better...

Carlos

 

[benlinkknilneb]

MrMajik,

Why can't you feed the function a Starting Date #m/1/y# and use

CurDate = DateAdd(d,1,CurDate) to cycle through it?

Ben

 

[CCLINT]

Here are 3 ways:

TheDate = Date
iDay = 15 'Day - use in the loop


?Format$(DateSerial(Year(TheDate), Month(TheDate), iDay), "\#mm\/dd\/yyyy\#&quot

Print Format$(TheDate, "\#mm\/" & iDay & "\/yyyy\#&quot

Print Format$(TheDate, "\#mm\/" & Format$(iDay, "00&quot & "\/yyyy\#&quot

 

[MrMajik]

Here is a more descriptive wording of what my goal is with this:

I am developing a calendar using the MSFlexGrid to display months. It is doing this just fine right now.

Now I want to color grid cells different colors to visually show:

Paydays (every other Thursday Green)
Weekends (Gray)
Legal Holidays (Yellow)

Other dates that the user can add if they choose.

So as I am looping through this loop:

For i = 1 To lastDayOfTheMonth
choice = CDate(Str(Month(Now)) & "/" & i & "/" & Str(Year(Now)))
'other code goes here...
Next i

I need to be able to get the value of i into a date value to change the grid cellBackground color if necessary.

There are other reasons for this but this should explain why I would like to make #6/5/2003# into something simple to modify with variables.

Thank you.

MrMajik

Everything should be made as simple as possible, but not simpler
--Albert Einstein

 

[CCLINT]

Drop off all of the

\#

in my examples, and surround the statement with CDate(),

or

Just use the DateSerial function:

DateSerial(Year(Date), Month(Date), i)

 

[MrMajik]

Thank you to all who responded with helpful suggestions!

I am looking at
DateSerial(Year(Date), Month(Date), i)
and
CurDate = DateAdd(d,1,CurDate)

Both will simplify the task at hand

Thank you.

MrMajik

Everything should be made as simple as possible, but not simpler
--Albert Einstein

 

[CCLINT]

The DateSerial is better when you are using a loop, as it is faster.

So is this:

Dim CurDate As Date

CurDate = Date + i

For your purpose, which I mis-understood, this last example will work fine and is the fastest method....

(If CurDate is a String variable and not a Date variable, you will have to use CDate(CurDate) + 1