records with current year

[aubrey]

Hello,

I have a field, "EXPIRATION DATE," which already has a expression attached
to it, "DateSerial(Year([DATE CERTIFIED])+3,12,31)." The expression basically
saids that the date from the DATE CERTIFIED field add three years and make
the defauld month and day 12/31.

I now need to pull a report for all the records that expires in the current
year.

My question is two part, can I add a criteria to the current "EXPRIATION
DATE" field, which already has an expression and what would that criteria
look like?

I hope my question is clear, any help would be greatly appreciated.

Thanks,

Aubrey

 

[Jerry Whittle]

Sure. The criteria would be:

Year(Date()) +3

 

[aubrey]

it dosen't work its giving me an error message "Data type mismatch in
criteria expression."

Sure. The criteria would be:

Year(Date()) +3
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,

I have a field, "EXPIRATION DATE," which already has a expression attached
to it, "DateSerial(Year([DATE CERTIFIED])+3,12,31)." The expression basically
saids that the date from the DATE CERTIFIED field add three years and make
the defauld month and day 12/31.

I now need to pull a report for all the records that expires in the current
year.

My question is two part, can I add a criteria to the current "EXPRIATION
DATE" field, which already has an expression and what would that criteria
look like?

I hope my question is clear, any help would be greatly appreciated.

Thanks,

Aubrey

 

[Jerry Whittle]

Whoops! I blew that one. Try this:

In the Field put the following:

TheYear: Year([DATE CERTIFIED])

In the criteria put:

Year(Date))

If you don't want to see it returned, remove the checkmark.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

it dosen't work its giving me an error message "Data type mismatch in
criteria expression."

Sure. The criteria would be:

Year(Date()) +3
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,

I have a field, "EXPIRATION DATE," which already has a expression attached
to it, "DateSerial(Year([DATE CERTIFIED])+3,12,31)." The expression basically
saids that the date from the DATE CERTIFIED field add three years and make
the defauld month and day 12/31.

I now need to pull a report for all the records that expires in the current
year.

My question is two part, can I add a criteria to the current "EXPRIATION
DATE" field, which already has an expression and what would that criteria
look like?

I hope my question is clear, any help would be greatly appreciated.

Thanks,

Aubrey

 

[aubrey]

Thanks alot Jerry.

Aubrey

Whoops! I blew that one. Try this:

In the Field put the following:

TheYear: Year([DATE CERTIFIED])

In the criteria put:

Year(Date))

If you don't want to see it returned, remove the checkmark.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

it dosen't work its giving me an error message "Data type mismatch in
criteria expression."

Sure. The criteria would be:

Year(Date()) +3
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hello,

I have a field, "EXPIRATION DATE," which already has a expression attached
to it, "DateSerial(Year([DATE CERTIFIED])+3,12,31)." The expression basically
saids that the date from the DATE CERTIFIED field add three years and make
the defauld month and day 12/31.

I now need to pull a report for all the records that expires in the current
year.

My question is two part, can I add a criteria to the current "EXPRIATION
DATE" field, which already has an expression and what would that criteria
look like?

I hope my question is clear, any help would be greatly appreciated.

Thanks,

Aubrey

 

[aubrey]

Okay, I taught I had it but I don't.

Remember I need this report to pull records that expire in the current year
which is three years in advance of the DATE CERTIFIED field, which is also
coded in the EXPIRATION DATE field.

I tried to substitute TheYear: Year([DATE CERTIFIED]), instead putting
EXPIRATION DATE in the brackets, but then I get no returns. I think it's
because this is no original date in the EXPIRATION DATE field, that date is
generated automatically with a DateSerial expression.

I feel like I'm almost there, there's just one piece to the puzzle missing.

 

[Jerry Whittle]

In the criteria put:

Year(Date()) +3

or, if I have the dates backwards, maybe

Year(Date()) -3

 

[Rick Brandt]

Hello,

I have a field, "EXPIRATION DATE," which already has a expression
attached to it, "DateSerial(Year([DATE CERTIFIED])+3,12,31)." The
expression basically saids that the date from the DATE CERTIFIED
field add three years and make the defauld month and day 12/31.

I now need to pull a report for all the records that expires in the
current year.

My question is two part, can I add a criteria to the current
"EXPRIATION DATE" field, which already has an expression and what
would that criteria look like?

I hope my question is clear, any help would be greatly appreciated.

Thanks,

Aubrey

WHERE [DATE CERTIFIED] >= DateSerial(Year(Date())-3,1,1)
AND [DATE CERTIFIED] < DateSerial(Year(Date())-2,1,1)

Don't apply criteria to an expression if you can avoid it.

 

[aubrey]

Hello,

I have a field, "EXPIRATION DATE," which already has a expression
attached to it, "DateSerial(Year([DATE CERTIFIED])+3,12,31)." The
expression basically saids that the date from the DATE CERTIFIED
field add three years and make the defauld month and day 12/31.

I now need to pull a report for all the records that expires in the
current year.

My question is two part, can I add a criteria to the current
"EXPRIATION DATE" field, which already has an expression and what
would that criteria look like?

I hope my question is clear, any help would be greatly appreciated.

Thanks,

Aubrey

WHERE [DATE CERTIFIED] >= DateSerial(Year(Date())-3,1,1)
AND [DATE CERTIFIED] < DateSerial(Year(Date())-2,1,1)

Don't apply criteria to an expression if you can avoid it.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


sorry for the ignorance, but where should i be entering that expression

 

[Lainni]

Try this in the criteria row for the Expiration Date field

Year([Expiration Date])=Year(Date())

If I understand your query correctly, that should work

Hello,

I have a field, "EXPIRATION DATE," which already has a expression
attached to it, "DateSerial(Year([DATE CERTIFIED])+3,12,31)." The
expression basically saids that the date from the DATE CERTIFIED
field add three years and make the defauld month and day 12/31.

I now need to pull a report for all the records that expires in the
current year.

My question is two part, can I add a criteria to the current
"EXPRIATION DATE" field, which already has an expression and what
would that criteria look like?

I hope my question is clear, any help would be greatly appreciated.

Thanks,

Aubrey

WHERE [DATE CERTIFIED] >= DateSerial(Year(Date())-3,1,1)
AND [DATE CERTIFIED] < DateSerial(Year(Date())-2,1,1)

Don't apply criteria to an expression if you can avoid it.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


sorry for the ignorance, but where should i be entering that expression

 

[Rick Brandt]

WHERE [DATE CERTIFIED] >= DateSerial(Year(Date())-3,1,1)
AND [DATE CERTIFIED] < DateSerial(Year(Date())-2,1,1)

Don't apply criteria to an expression if you can avoid it.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


sorry for the ignorance, but where should i be entering that
expression

Under the field [DATE CERTIFIED] in the query designer enter in the criteria
row...

 

[aubrey]

WORKS GREAT.

Thanks alot.

WHERE [DATE CERTIFIED] >= DateSerial(Year(Date())-3,1,1)
AND [DATE CERTIFIED] < DateSerial(Year(Date())-2,1,1)

Don't apply criteria to an expression if you can avoid it.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


sorry for the ignorance, but where should i be entering that
expression

Under the field [DATE CERTIFIED] in the query designer enter in the criteria
row...

= DateSerial(Year(Date())-3,1,1) AND < DateSerial(Year(Date())-2,1,1)