vba date handling

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
 
P

PC Datasheet

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)
 
T

Todd Shillam

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
 
M

Mary

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...
 
P

PC Datasheet

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...
 
M

Mary

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...
 
P

PC Datasheet

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...
 
M

Mary

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding records relating to intervals between two dates 4
Date comparisons 5
How to enter a date in DD-MM-YYYY format 2
Date Time decimal value help please 1
number of Full Months between dates & partial Days calculation 1
VBA word change keys 0
SUMIF between date range 0
printing report with date 2

Top