How to Set the Criteria Automatically?

  • Thread starter Thread starter Telesphore
  • Start date Start date
T

Telesphore

Time for tax returns of last year.

In a query,
- calculated field is: Format([InscriptionDate],"yy") AS InscriptionYear
- criteria is: WHERE (((Format([InscriptionDate],"yy"))="03")

Is there a way to enter automatically the equivalent of "03" in the
criteria?

Thanks in advance.
 
Keep in mind that Format returns a text value, not a number. If you need a
number, consider using the Year function. But in answer to your question:

WHERE Year([InscriptionDate]) = (Year(Date()) - 1)

The above returns the records that are in the previous year.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Dear Mr Viescas,

I am very honoured to receive this answer to my problem. I want to take this
opportunity to say how I am grateful to you. Building a database is not an
easy task, but when I read your answer to the question: "Oh No! Not Another
Order-Entry Example!", on Chapter 4 of your book, there came the insight and
it works since! Thank you so much.

Now for the current problem, I realized that I must reform the query that is
the motor of the report slip (4" x 8.5") for tax returns of our students.
The federal Canadian Tax Return is asking to put the year and the month with
to digits, so that the term from January 1st 2003 to April 30th 2003 should
be written 03 01 to 03 04.

Thanks again.

John Viescas said:
Keep in mind that Format returns a text value, not a number. If you need a
number, consider using the Year function. But in answer to your question:

WHERE Year([InscriptionDate]) = (Year(Date()) - 1)

The above returns the records that are in the previous year.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Telesphore said:
Time for tax returns of last year.

In a query,
- calculated field is: Format([InscriptionDate],"yy") AS InscriptionYear
- criteria is: WHERE (((Format([InscriptionDate],"yy"))="03")

Is there a way to enter automatically the equivalent of "03" in the
criteria?

Thanks in advance.
Click to expand...
Click to expand...
 
You can use the Year, Month, and Format functions to get what you want:

Format(Year([MyDate]) Mod 100, "00") & " " & Format(Month([MyDate], "00")

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Telesphore said:
Dear Mr Viescas,

I am very honoured to receive this answer to my problem. I want to take this
opportunity to say how I am grateful to you. Building a database is not an
easy task, but when I read your answer to the question: "Oh No! Not Another
Order-Entry Example!", on Chapter 4 of your book, there came the insight and
it works since! Thank you so much.

Now for the current problem, I realized that I must reform the query that is
the motor of the report slip (4" x 8.5") for tax returns of our students.
The federal Canadian Tax Return is asking to put the year and the month with
to digits, so that the term from January 1st 2003 to April 30th 2003 should
be written 03 01 to 03 04.

Thanks again.

John Viescas said:
Keep in mind that Format returns a text value, not a number. If you
Click to expand...
need
a
number, consider using the Year function. But in answer to your question:

WHERE Year([InscriptionDate]) = (Year(Date()) - 1)

The above returns the records that are in the previous year.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Telesphore said:
Time for tax returns of last year.

In a query,
- calculated field is: Format([InscriptionDate],"yy") AS InscriptionYear
- criteria is: WHERE (((Format([InscriptionDate],"yy"))="03")

Is there a way to enter automatically the equivalent of "03" in the
criteria?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top