Filtering Dates

[Guest]

Hello,

I have created a lease database that contains lease and rent information
(rent and sales information is entered every month by date such as 5/1/2007).
Each lease has a different start and end date which constitutes the lease
year. We pay percentage rent based on the lease year. All dates are in
mm/dd/yyyy format.

I need to figure out a way to print a report for each lease based on the
lease year. Example: If the lease start date is 4/1/1999 I need to show
rent and sales information for 4/1/2006 through 3/31/07.

Is there a simple way to do this? Right now, I have created a query which
asks for the start and end dates. Since we have close to 150 leases, this is
very cumbersome.

HELP???

Thanks.

 

[John W. Vinson]

Hello,

I have created a lease database that contains lease and rent information
(rent and sales information is entered every month by date such as 5/1/2007).
Each lease has a different start and end date which constitutes the lease
year. We pay percentage rent based on the lease year. All dates are in
mm/dd/yyyy format.

I need to figure out a way to print a report for each lease based on the
lease year. Example: If the lease start date is 4/1/1999 I need to show
rent and sales information for 4/1/2006 through 3/31/07.

Is there a simple way to do this? Right now, I have created a query which
asks for the start and end dates. Since we have close to 150 leases, this is
very cumbersome.

Use the DateSerial function:

= DateSerial(Year(Date()), Month([LeaseDate]), 1) AND < DateSerial(Year(Date())+1, Month([LeaseDate], 1)

will get the records from the lease month in this current year through the end
of the previous month next year.

John W. Vinson [MVP]

 

[Guest]

Sorry if I seem a little "dense", but WHERE/HOW do I use this?

Hello,

I have created a lease database that contains lease and rent information
(rent and sales information is entered every month by date such as 5/1/2007).
Each lease has a different start and end date which constitutes the lease
year. We pay percentage rent based on the lease year. All dates are in
mm/dd/yyyy format.

I need to figure out a way to print a report for each lease based on the
lease year. Example: If the lease start date is 4/1/1999 I need to show
rent and sales information for 4/1/2006 through 3/31/07.

Is there a simple way to do this? Right now, I have created a query which
asks for the start and end dates. Since we have close to 150 leases, this is
very cumbersome.

Use the DateSerial function:

= DateSerial(Year(Date()), Month([LeaseDate]), 1) AND < DateSerial(Year(Date())+1, Month([LeaseDate], 1)

will get the records from the lease month in this current year through the end
of the previous month next year.

John W. Vinson [MVP]

 

[John W. Vinson]

Sorry...! that was unclear.

Use it as a Criterion on the date field in ther "rent and sales information"
table.

Sorry if I seem a little "dense", but WHERE/HOW do I use this?

Hello,

I have created a lease database that contains lease and rent information
(rent and sales information is entered every month by date such as 5/1/2007).
Each lease has a different start and end date which constitutes the lease
year. We pay percentage rent based on the lease year. All dates are in
mm/dd/yyyy format.

I need to figure out a way to print a report for each lease based on the
lease year. Example: If the lease start date is 4/1/1999 I need to show
rent and sales information for 4/1/2006 through 3/31/07.

Is there a simple way to do this? Right now, I have created a query which
asks for the start and end dates. Since we have close to 150 leases, this is
very cumbersome.

Use the DateSerial function:

= DateSerial(Year(Date()), Month([LeaseDate]), 1) AND < DateSerial(Year(Date())+1, Month([LeaseDate], 1)

will get the records from the lease month in this current year through the end
of the previous month next year.

John W. Vinson [MVP]

John W. Vinson [MVP]

 

[Guest]

John,

I put this expression:
= DateSerial(Year(Date()), Month([PmtDate]), 1) AND
DateSerial(Year(Date())+1, Month([PmtDate], 1)

in as a criterion in the date field of my query and received the following
error:

"The expression you entered has a function containing the wrong number of
arguments."

Any suggestions?

Sorry...! that was unclear.

Use it as a Criterion on the date field in ther "rent and sales information"
table.

Sorry if I seem a little "dense", but WHERE/HOW do I use this?

Hello,

I have created a lease database that contains lease and rent information
(rent and sales information is entered every month by date such as 5/1/2007).
Each lease has a different start and end date which constitutes the lease
year. We pay percentage rent based on the lease year. All dates are in
mm/dd/yyyy format.

I need to figure out a way to print a report for each lease based on the
lease year. Example: If the lease start date is 4/1/1999 I need to show
rent and sales information for 4/1/2006 through 3/31/07.

Is there a simple way to do this? Right now, I have created a query which
asks for the start and end dates. Since we have close to 150 leases, this is
very cumbersome.

Use the DateSerial function:

= DateSerial(Year(Date()), Month([LeaseDate]), 1) AND < DateSerial(Year(Date())+1, Month([LeaseDate], 1)

will get the records from the lease month in this current year through the end
of the previous month next year.

John W. Vinson [MVP]

John W. Vinson [MVP]

 

[John W. Vinson]

John,

I put this expression:
= DateSerial(Year(Date()), Month([PmtDate]), 1) AND
DateSerial(Year(Date())+1, Month([PmtDate], 1)

in as a criterion in the date field of my query and received the following
error:

"The expression you entered has a function containing the wrong number of
arguments."

Any suggestions?

I think the newsreader must have eaten a couple of > characters. The correct
criterion is:

= DateSerial(Year(Date()), Month([LeaseDate]), 1) AND <

DateSerial(Year(Date())+1, Month([LeaseDate], 1)

That is, Greater Than or Equal to (this year's lease anniversary) And Less
Than (next year's lease anniversary)


John W. Vinson [MVP]

 

[Guest]

John,

I think we have confirmed that I AM dense! I can't get this to work. I'm
not really sure how it should be applied. Here are the fields in my query:

StoreID, SalesDate, Sales, LeaseDate.

I've created a report which lists sales as follows:

March'06 $9999
April'06 $9999

The sales are listed by lease year. In the case, the lease (start) date
(month) was March. Do I create a separate query which will determine the
lease year based on the lease start date and somehow link it to the sales
query?

Oh, I'm so confused.

Thanks for your patience.

John,

I put this expression:
= DateSerial(Year(Date()), Month([PmtDate]), 1) AND
DateSerial(Year(Date())+1, Month([PmtDate], 1)

in as a criterion in the date field of my query and received the following
error:

"The expression you entered has a function containing the wrong number of
arguments."

Any suggestions?

I think the newsreader must have eaten a couple of > characters. The correct
criterion is:

= DateSerial(Year(Date()), Month([LeaseDate]), 1) AND <

DateSerial(Year(Date())+1, Month([LeaseDate], 1)

That is, Greater Than or Equal to (this year's lease anniversary) And Less
Than (next year's lease anniversary)


John W. Vinson [MVP]