vba date handling

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

I wish to test an input date to see whether it falls
between two dates. These dates are constant mm/dd with no
fixed year. I wish the code to work in any year for those
dates.

When I try the coding "[Date1] > #11/15#" it changes
to "[Date1] > #11/15/2004#".

How do I code for month/day only?

Thanks.
Mary
 
First, separate your constant dates into month and day:
StartMonth
StartDay
EndMonth
EndDay
Note: StartMonth and EndMonth need to be 1-12

Between DateSerial(Year[InputDate],StartMonth,StartDay) And _
DateSerial(Year[InputDate],EndMonth,EndDay)
 
Mary,

Microsoft Access does not store a date field in the same format it is
displayed on the screen. The date is actually stored as an 8 bit 64
character integer value.

Try changing the format for the field in the table to display in the format
you would like.

Best regards,

Todd
 
Thanks.

Is this correct?

Between DateSerial(Year[StartDate],11,15) And _
DateSerial(Year[StartDate],12,31)

In my [StartDate] after update event, how would I use
that if correct?

if me.StartDate between . . .

if that's correct, I get compile error and anything I try
fails. What don't I see?

Thanks,
Mary


-----Original Message-----
First, separate your constant dates into month and day:
StartMonth
StartDay
EndMonth
EndDay
Note: StartMonth and EndMonth need to be 1-12

Between DateSerial(Year[InputDate],StartMonth,StartDay) And _
DateSerial(Year[InputDate],EndMonth,EndDay)


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
I wish to test an input date to see whether it falls
between two dates. These dates are constant mm/dd with no
fixed year. I wish the code to work in any year for those
dates.

When I try the coding "[Date1] > #11/15#" it changes
to "[Date1] > #11/15/2004#".

How do I code for month/day only?

Thanks.
Mary
Click to expand...


.
Click to expand...
 
My mistake! There should be parantheses around [Startdate].
Year([StartDate])

My expression was for criteria in a query. Use this expression in the
AfterUpdate event:
If Me!StartDate >= DateSerial(Year([StartDate]),11,15) And _
Me!StartDate <= DateSerial(Year([StartDate]),12,31) Then

BTW, the underline is the symbol for continuation and need a space preceding
it. The above expression is really one long expression. I used the
continuation character to avoid line wrap.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



Mary said:
Thanks.

Is this correct?

Between DateSerial(Year[StartDate],11,15) And _
DateSerial(Year[StartDate],12,31)

In my [StartDate] after update event, how would I use
that if correct?

if me.StartDate between . . .

if that's correct, I get compile error and anything I try
fails. What don't I see?

Thanks,
Mary


-----Original Message-----
First, separate your constant dates into month and day:
StartMonth
StartDay
EndMonth
EndDay
Note: StartMonth and EndMonth need to be 1-12

Between DateSerial(Year[InputDate],StartMonth,StartDay) And _
DateSerial(Year[InputDate],EndMonth,EndDay)


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
I wish to test an input date to see whether it falls
between two dates. These dates are constant mm/dd with no
fixed year. I wish the code to work in any year for those
dates.

When I try the coding "[Date1] > #11/15#" it changes
to "[Date1] > #11/15/2004#".

How do I code for month/day only?

Thanks.
Mary
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks, PC

That worked!

I'm in business now.

BTW I have another post (yesterday) regarding goto
record...can you help?

Thanks,
Mary




-----Original Message-----
My mistake! There should be parantheses around [Startdate].
Year([StartDate])

My expression was for criteria in a query. Use this expression in the
AfterUpdate event:
If Me!StartDate >= DateSerial(Year([StartDate]),11,15) And _
Me!StartDate <= DateSerial(Year([StartDate]),12,31) Then

BTW, the underline is the symbol for continuation and need a space preceding
it. The above expression is really one long expression. I used the
continuation character to avoid line wrap.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



Thanks.

Is this correct?

Between DateSerial(Year[StartDate],11,15) And _
DateSerial(Year[StartDate],12,31)

In my [StartDate] after update event, how would I use
that if correct?

if me.StartDate between . . .

if that's correct, I get compile error and anything I try
fails. What don't I see?

Thanks,
Mary


-----Original Message-----
First, separate your constant dates into month and day:
StartMonth
StartDay
EndMonth
EndDay
Note: StartMonth and EndMonth need to be 1-12

Between DateSerial(Year
Click to expand...
[InputDate],StartMonth,StartDay)
And _
DateSerial(Year[InputDate],EndMonth,EndDay)


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
I wish to test an input date to see whether it falls
between two dates. These dates are constant mm/dd
Click to expand...
with
no
fixed year. I wish the code to work in any year for those
dates.

When I try the coding "[Date1] > #11/15#" it changes
to "[Date1] > #11/15/2004#".

How do I code for month/day only?

Thanks.
Mary


.
Click to expand...
Click to expand...


.
Click to expand...
 
Look back to your previous post; I posted an answer.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Mary said:
Thanks, PC

That worked!

I'm in business now.

BTW I have another post (yesterday) regarding goto
record...can you help?

Thanks,
Mary




-----Original Message-----
My mistake! There should be parantheses around [Startdate].
Year([StartDate])

My expression was for criteria in a query. Use this expression in the
AfterUpdate event:
If Me!StartDate >= DateSerial(Year([StartDate]),11,15) And _
Me!StartDate <= DateSerial(Year([StartDate]),12,31) Then

BTW, the underline is the symbol for continuation and need a space preceding
it. The above expression is really one long expression. I used the
continuation character to avoid line wrap.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



Thanks.

Is this correct?

Between DateSerial(Year[StartDate],11,15) And _
DateSerial(Year[StartDate],12,31)

In my [StartDate] after update event, how would I use
that if correct?

if me.StartDate between . . .

if that's correct, I get compile error and anything I try
fails. What don't I see?

Thanks,
Mary



-----Original Message-----
First, separate your constant dates into month and day:
StartMonth
StartDay
EndMonth
EndDay
Note: StartMonth and EndMonth need to be 1-12

Between DateSerial(Year [InputDate],StartMonth,StartDay)
And _
DateSerial(Year[InputDate],EndMonth,EndDay)


--
PC Datasheet
Your Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
www.pcdatasheet.com
message
I wish to test an input date to see whether it falls
between two dates. These dates are constant mm/dd with
no
fixed year. I wish the code to work in any year for
those
dates.

When I try the coding "[Date1] > #11/15#" it changes
to "[Date1] > #11/15/2004#".

How do I code for month/day only?

Thanks.
Mary


.
Click to expand...


.
Click to expand...
Click to expand...
 
PC thanks. I'm working on that. If I don't get it
working I'll post again, but new, since I won't be here
after this mid-day.

Thanks and Happy Holidays.

-----Original Message-----
Look back to your previous post; I posted an answer.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Thanks, PC

That worked!

I'm in business now.

BTW I have another post (yesterday) regarding goto
record...can you help?

Thanks,
Mary




-----Original Message-----
My mistake! There should be parantheses around [Startdate].
Year([StartDate])

My expression was for criteria in a query. Use this expression in the
AfterUpdate event:
If Me!StartDate >= DateSerial(Year([StartDate]),11,15) And _
Me!StartDate <= DateSerial(Year([StartDate]),12,31) Then

BTW, the underline is the symbol for continuation and need a space preceding
it. The above expression is really one long
Click to expand...
expression.
I used the
continuation character to avoid line wrap.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



Thanks.

Is this correct?

Between DateSerial(Year[StartDate],11,15) And _
DateSerial(Year[StartDate],12,31)

In my [StartDate] after update event, how would I use
that if correct?

if me.StartDate between . . .

if that's correct, I get compile error and anything
Click to expand...
I
try
fails. What don't I see?

Thanks,
Mary



-----Original Message-----
First, separate your constant dates into month and day:
StartMonth
StartDay
EndMonth
EndDay
Note: StartMonth and EndMonth need to be 1-12

Between DateSerial(Year [InputDate],StartMonth,StartDay)
And _
DateSerial(Year[InputDate],EndMonth,EndDay)


--
PC Datasheet
Your Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
www.pcdatasheet.com
message
I wish to test an input date to see whether it falls
between two dates. These dates are constant mm/dd with
no
fixed year. I wish the code to work in any year for
those
dates.

When I try the coding "[Date1] > #11/15#" it changes
to "[Date1] > #11/15/2004#".

How do I code for month/day only?

Thanks.
Mary


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top