How to write this "between dates" statement properly?

[vavroom]

I've searched the archives, but can't seem to find the answer to my
question.

On a report, I have a date field. I need to determine which of three
date ranges that date falls in, and based on that return a different
value to a textfield.

I can't seem to be able to figure out how to make this happen in the
code behind the report Can someone point me in the right direction?
I've tried playing with Format() and other things, but no matter how I
look at it, it isn't working.

the date field is formatted this way in the table:
dd mmm yyyy hh:nn AM/PM
(and I cannot change the format)

The date ranges are
1- between 31 October and 4 november
2- between 6 november and 10 november
3- between 12 november and 16 november

Writing:
if me.date between #31/10/2006# and #04/11/2006# then
doesn't do the trick at all.

Any ideas?

 

[John Vinson]

I've searched the archives, but can't seem to find the answer to my
question.

On a report, I have a date field.

Umm... no. In a TABLE you have a date field. This field might or might
not be *displayed* on a Report, or more than one report, but it's
certainly not *stored* in a Report.

I need to determine which of three
date ranges that date falls in, and based on that return a different
value to a textfield.

What "value" do you want to display?

I can't seem to be able to figure out how to make this happen in the
code behind the report Can someone point me in the right direction?
I've tried playing with Format() and other things, but no matter how I
look at it, it isn't working.

the date field is formatted this way in the table:
dd mmm yyyy hh:nn AM/PM
(and I cannot change the format)

Again, no. The Format is NOT part of the table, and the date is
actually stored as a Double Float number, a count of days and
fractions of a day since midnight, December 30, 1899. The format
controls how the value is *displayed* but not how it's stored.

The date ranges are
1- between 31 October and 4 november
2- between 6 november and 10 november
3- between 12 november and 16 november

'
Of what year? Any year? The current year? The previous year?

Writing:
if me.date between #31/10/2006# and #04/11/2006# then
doesn't do the trick at all.

Three problems: SQL Queries have a BETWEEN operator, but VBA code does
not; and (the programmers of Access being Americans) all date literals
must be in month-day-year format, or an unambiguous format such as
yyyy-mmm-dd; and Date is a reserved word for the builtin Date()
function, and using it without brackets may very well cause confusion.
Try

If Me.[Date] >= #10/31/2006# AND Me.Date <= #11/4/2006# Then

Or, perhaps, use a SELECT CASE statement:

Select Case Me.[Date]
Case >= #10/31/2006# AND <= #11/4/2006#
<do the first thing>
Case >= #11/6/2006# AND <= #11/10/2006#
<do the second thing>
Case >= #11/12/2006# AND <= #11/16/2006#
<do the third thing>
Case Else
MsgBox "Oops! Date out of range!"
End Select


John W. Vinson[MVP]

 

[tina]

try adding the following code to the Print event procedure of the report
section that the textbox "sits" in, as

Dim dat As Date

dat = Me!DateFieldName

Select Case dat
Case DateSerial(Year(dat), 10, 31) _
To DateSerial(Year(dat), 11, 4)
Me!TextboxControlName = "some value"
Case DateSerial(Year(dat), 11, 6) _
To DateSerial(Year(dat), 11, 10)
Me!TextboxControlName = "another value"
Case DateSerial(Year(dat), 11, 12) _
To DateSerial(Year(dat), 11, 16)
Me!TextboxControlName = "a different value"
Case Else
Me!TextboxControlName = "invalid date"
End Select

replace DateFieldName and TextboxControlName with the correct names of the
date field in the report's RecordSource and the correct name of the textbox
control that will display the value you choose. you should have an action of
some sort in the Case Else section, even if it is to just set the textbox
control's value to a zero-length string ("").

hth

 

[vavroom]

Thanks for the responses.

John, I'm sorry I wasn't precise enough to point out I meant I was
displaying a date in a report when I said "I have a report with a date
field". i'm glad you knew what I meant.

I didn't name the field "Date", I'm inheriting this database and can't
go in and change existing things, sadly. However, adding the brackets
around it made a world of difference.

Thanks a bunch.