query for tag renewal

[Todd]

I was mistaken on my explanation previously. The field in table1 is for
tagexpiration (for when the tag is due renewal), not when the tag was
purchased.--
Todd

 

[BruceM]

Build a query based on the table. Set the criteria in the date field:
Between Date() And DateAdd("m",1,Date())
This will filter for just those records where the expiration date is between
today and a month from today. See Help for more information about DateAdd.
You could use the query as the Record Source for a form or report.

 

[Todd]

Thanks for the info. It did work but my problem is that for each month
separate records are entered on each vehicle. When the query shows the
results it will show expiration dates from the previous years where the tag
had expired before. Any suggestions on how to only search for the most
recent tag expiration date?

 

[BruceM]

I don't understand your question. You have a date field (I will call it
ExpDate). When you make a query based on the table containing ExpDate, and
you put the criteria I provided into ExpDate, the query will return only
those records with an expiration date (that is, the date in the ExpDate
field) that is between today and a month from today.
Is ExpDate a calculated field?

 

[Todd]

I created the query and in the tagexpiration field criteria I inserted the
following:

Between [tagexpiration] And DateAdd("m",1,[tagexpiration])

The above criteria gave me all tagexpirations in the table.

I'm assuming in the criteria you gave me I was suppose to enter the field
name where Date() was listed. Apparently, this was not correct?

When I entered the criteria as you provided I didn't get any records, I made
sure there were records that had the tagexpiration date for at least 03/2007
and also 04/2007 but still no records returned.

The tagexpiration date is not a calculated field.

 

[BruceM]

Yes, I expect it would return all records, since you are comparing the field
to itself. I had intended for you to use the literal code I posted. Date()
is a function that returns the current date. DateAdd adds a specified date
interval to a date function or field (it can also be used to subtract a date
interval). By adding one month to today, March 12 you are getting the date
April 12. Therefore the criteria when you run the query today is:
Between March 12, 2007 and April 12, 2007
You will see all records with an expiration date in that range.

I created the query and in the tagexpiration field criteria I inserted the
following:

Between [tagexpiration] And DateAdd("m",1,[tagexpiration])

The above criteria gave me all tagexpirations in the table.

I'm assuming in the criteria you gave me I was suppose to enter the field
name where Date() was listed. Apparently, this was not correct?

When I entered the criteria as you provided I didn't get any records, I
made
sure there were records that had the tagexpiration date for at least
03/2007
and also 04/2007 but still no records returned.

The tagexpiration date is not a calculated field.

--
Todd

I don't understand your question. You have a date field (I will call it
ExpDate). When you make a query based on the table containing ExpDate,
and
you put the criteria I provided into ExpDate, the query will return only
those records with an expiration date (that is, the date in the ExpDate
field) that is between today and a month from today.
Is ExpDate a calculated field?

 

[Todd]

I thought maybe the date format I had for tagexpiration field (mm/yyyy) was
the issue but after I changed that to a mm/dd/yyyy format and changed a
couple records to the new date format I entered the criteria you gave me:

Between date() And DateAdd("m",1,Date())

It still returned no records.
--
Todd

Yes, I expect it would return all records, since you are comparing the field
to itself. I had intended for you to use the literal code I posted. Date()
is a function that returns the current date. DateAdd adds a specified date
interval to a date function or field (it can also be used to subtract a date
interval). By adding one month to today, March 12 you are getting the date
April 12. Therefore the criteria when you run the query today is:
Between March 12, 2007 and April 12, 2007
You will see all records with an expiration date in that range.

I created the query and in the tagexpiration field criteria I inserted the
following:

Between [tagexpiration] And DateAdd("m",1,[tagexpiration])

The above criteria gave me all tagexpirations in the table.

I'm assuming in the criteria you gave me I was suppose to enter the field
name where Date() was listed. Apparently, this was not correct?

When I entered the criteria as you provided I didn't get any records, I
made
sure there were records that had the tagexpiration date for at least
03/2007
and also 04/2007 but still no records returned.

The tagexpiration date is not a calculated field.

--
Todd

I don't understand your question. You have a date field (I will call it
ExpDate). When you make a query based on the table containing ExpDate,
and
you put the criteria I provided into ExpDate, the query will return only
those records with an expiration date (that is, the date in the ExpDate
field) that is between today and a month from today.
Is ExpDate a calculated field?

Thanks for the info. It did work but my problem is that for each month
separate records are entered on each vehicle. When the query shows the
results it will show expiration dates from the previous years where the
tag
had expired before. Any suggestions on how to only search for the most
recent tag expiration date?
--
Todd


:

Build a query based on the table. Set the criteria in the date field:
Between Date() And DateAdd("m",1,Date())
This will filter for just those records where the expiration date is
between
today and a month from today. See Help for more information about
DateAdd.
You could use the query as the Record Source for a form or report.

I was mistaken on my explanation previously. The field in table1 is
for
tagexpiration (for when the tag is due renewal), not when the tag
was
purchased.--
Todd

 

[BruceM]

Is it a date field, or is it a text field that is made to look like a date?
If it is a date field, the criteria should work.

Create a new table with a date field. Define the field as a date/time
field. Add a few records with a range of dates. Create a query based on
the table, and try the criteria. Does it work as expected?

I thought maybe the date format I had for tagexpiration field (mm/yyyy) was
the issue but after I changed that to a mm/dd/yyyy format and changed a
couple records to the new date format I entered the criteria you gave me:

Between date() And DateAdd("m",1,Date())

It still returned no records.
--
Todd

Yes, I expect it would return all records, since you are comparing the
field
to itself. I had intended for you to use the literal code I posted.
Date()
is a function that returns the current date. DateAdd adds a specified
date
interval to a date function or field (it can also be used to subtract a
date
interval). By adding one month to today, March 12 you are getting the
date
April 12. Therefore the criteria when you run the query today is:
Between March 12, 2007 and April 12, 2007
You will see all records with an expiration date in that range.

I created the query and in the tagexpiration field criteria I inserted
the
following:

Between [tagexpiration] And DateAdd("m",1,[tagexpiration])

The above criteria gave me all tagexpirations in the table.

I'm assuming in the criteria you gave me I was suppose to enter the
field
name where Date() was listed. Apparently, this was not correct?

When I entered the criteria as you provided I didn't get any records, I
made
sure there were records that had the tagexpiration date for at least
03/2007
and also 04/2007 but still no records returned.

The tagexpiration date is not a calculated field.

--
Todd


:

I don't understand your question. You have a date field (I will call
it
ExpDate). When you make a query based on the table containing
ExpDate,
and
you put the criteria I provided into ExpDate, the query will return
only
those records with an expiration date (that is, the date in the
ExpDate
field) that is between today and a month from today.
Is ExpDate a calculated field?

Thanks for the info. It did work but my problem is that for each
month
separate records are entered on each vehicle. When the query shows
the
results it will show expiration dates from the previous years where
the
tag
had expired before. Any suggestions on how to only search for the
most
recent tag expiration date?
--
Todd


:

Build a query based on the table. Set the criteria in the date
field:
Between Date() And DateAdd("m",1,Date())
This will filter for just those records where the expiration date
is
between
today and a month from today. See Help for more information about
DateAdd.
You could use the query as the Record Source for a form or report.

I was mistaken on my explanation previously. The field in table1
is
for
tagexpiration (for when the tag is due renewal), not when the tag
was
purchased.--
Todd

 

[Todd]

Mystery solved. For some reason the tagexpired field was text and not date.
Thanks and sorry for the trouble.
--
Todd

Is it a date field, or is it a text field that is made to look like a date?
If it is a date field, the criteria should work.

Create a new table with a date field. Define the field as a date/time
field. Add a few records with a range of dates. Create a query based on
the table, and try the criteria. Does it work as expected?

I thought maybe the date format I had for tagexpiration field (mm/yyyy) was
the issue but after I changed that to a mm/dd/yyyy format and changed a
couple records to the new date format I entered the criteria you gave me:

Between date() And DateAdd("m",1,Date())

It still returned no records.
--
Todd

Yes, I expect it would return all records, since you are comparing the
field
to itself. I had intended for you to use the literal code I posted.
Date()
is a function that returns the current date. DateAdd adds a specified
date
interval to a date function or field (it can also be used to subtract a
date
interval). By adding one month to today, March 12 you are getting the
date
April 12. Therefore the criteria when you run the query today is:
Between March 12, 2007 and April 12, 2007
You will see all records with an expiration date in that range.

I created the query and in the tagexpiration field criteria I inserted
the
following:

Between [tagexpiration] And DateAdd("m",1,[tagexpiration])

The above criteria gave me all tagexpirations in the table.

I'm assuming in the criteria you gave me I was suppose to enter the
field
name where Date() was listed. Apparently, this was not correct?

When I entered the criteria as you provided I didn't get any records, I
made
sure there were records that had the tagexpiration date for at least
03/2007
and also 04/2007 but still no records returned.

The tagexpiration date is not a calculated field.

--
Todd


:

I don't understand your question. You have a date field (I will call
it
ExpDate). When you make a query based on the table containing
ExpDate,
and
you put the criteria I provided into ExpDate, the query will return
only
those records with an expiration date (that is, the date in the
ExpDate
field) that is between today and a month from today.
Is ExpDate a calculated field?

Thanks for the info. It did work but my problem is that for each
month
separate records are entered on each vehicle. When the query shows
the
results it will show expiration dates from the previous years where
the
tag
had expired before. Any suggestions on how to only search for the
most
recent tag expiration date?
--
Todd


:

Build a query based on the table. Set the criteria in the date
field:
Between Date() And DateAdd("m",1,Date())
This will filter for just those records where the expiration date
is
between
today and a month from today. See Help for more information about
DateAdd.
You could use the query as the Record Source for a form or report.

I was mistaken on my explanation previously. The field in table1
is
for
tagexpiration (for when the tag is due renewal), not when the tag
was
purchased.--
Todd

 

[BruceM]

No trouble at all. Glad to hear you got it working.

Mystery solved. For some reason the tagexpired field was text and not
date.
Thanks and sorry for the trouble.
--
Todd

Is it a date field, or is it a text field that is made to look like a
date?
If it is a date field, the criteria should work.

Create a new table with a date field. Define the field as a date/time
field. Add a few records with a range of dates. Create a query based on
the table, and try the criteria. Does it work as expected?

I thought maybe the date format I had for tagexpiration field (mm/yyyy)
was
the issue but after I changed that to a mm/dd/yyyy format and changed a
couple records to the new date format I entered the criteria you gave
me:

Between date() And DateAdd("m",1,Date())

It still returned no records.
--
Todd


:

Yes, I expect it would return all records, since you are comparing the
field
to itself. I had intended for you to use the literal code I posted.
Date()
is a function that returns the current date. DateAdd adds a specified
date
interval to a date function or field (it can also be used to subtract
a
date
interval). By adding one month to today, March 12 you are getting the
date
April 12. Therefore the criteria when you run the query today is:
Between March 12, 2007 and April 12, 2007
You will see all records with an expiration date in that range.

I created the query and in the tagexpiration field criteria I
inserted
the
following:

Between [tagexpiration] And DateAdd("m",1,[tagexpiration])

The above criteria gave me all tagexpirations in the table.

I'm assuming in the criteria you gave me I was suppose to enter the
field
name where Date() was listed. Apparently, this was not correct?

When I entered the criteria as you provided I didn't get any
records, I
made
sure there were records that had the tagexpiration date for at least
03/2007
and also 04/2007 but still no records returned.

The tagexpiration date is not a calculated field.

--
Todd


:

I don't understand your question. You have a date field (I will
call
it
ExpDate). When you make a query based on the table containing
ExpDate,
and
you put the criteria I provided into ExpDate, the query will return
only
those records with an expiration date (that is, the date in the
ExpDate
field) that is between today and a month from today.
Is ExpDate a calculated field?

Thanks for the info. It did work but my problem is that for each
month
separate records are entered on each vehicle. When the query
shows
the
results it will show expiration dates from the previous years
where
the
tag
had expired before. Any suggestions on how to only search for
the
most
recent tag expiration date?
--
Todd


:

Build a query based on the table. Set the criteria in the date
field:
Between Date() And DateAdd("m",1,Date())
This will filter for just those records where the expiration
date
is
between
today and a month from today. See Help for more information
about
DateAdd.
You could use the query as the Record Source for a form or
report.

I was mistaken on my explanation previously. The field in
table1
is
for
tagexpiration (for when the tag is due renewal), not when the
tag
was
purchased.--
Todd