SQL for If, Then in a Query?

[Guest]

I need to create a calculated field in a query that looks at the 'Complete
Date' and returns the quarter the deal completed. Example, if the complete
date is 2/15/05, then it completed in Q1 and the query should return 1 in
that record. Similarly if the date is 6/3/05, then 2 (the year is irrelevant
for this field). The powers that be can't look at the date and calculate the
quarter in their head so I have to do it for them in this report.

I was thinking an SQL subquery would work but I haven't a clue how to write
that.

I can code the data input form and populate the table driving the above
query but, I prefer to have the calculation in the Query because it will
catch any data changes/updates in the table (which are unlikely but may
happen). Am I barking up the wrong tree with the SQL? And if not, anyone
know how to write that?

 

[John Spencer]

Use the DatePart function
DatePart{"q",[Complete Date])

Or use the format function
Format([Complete Date],"q")

Or use the format property of a control bound to the Complete Date

 

[Guest]

You should be able to used the following as a calculated field in your query:

Quarter: DatePart("q",[Complete Date])

 

[Guest]

And here I was banging my head against the wall. Works perfectly. Thanks

You should be able to used the following as a calculated field in your query:

Quarter: DatePart("q",[Complete Date])

I need to create a calculated field in a query that looks at the 'Complete
Date' and returns the quarter the deal completed. Example, if the complete
date is 2/15/05, then it completed in Q1 and the query should return 1 in
that record. Similarly if the date is 6/3/05, then 2 (the year is irrelevant
for this field). The powers that be can't look at the date and calculate the
quarter in their head so I have to do it for them in this report.

I was thinking an SQL subquery would work but I haven't a clue how to write
that.

I can code the data input form and populate the table driving the above
query but, I prefer to have the calculation in the Query because it will
catch any data changes/updates in the table (which are unlikely but may
happen). Am I barking up the wrong tree with the SQL? And if not, anyone
know how to write that?

 

[Guest]

This is much easier than I thought, thanks

Use the DatePart function
DatePart{"q",[Complete Date])

Or use the format function
Format([Complete Date],"q")

Or use the format property of a control bound to the Complete Date

I need to create a calculated field in a query that looks at the 'Complete
Date' and returns the quarter the deal completed. Example, if the
complete
date is 2/15/05, then it completed in Q1 and the query should return 1 in
that record. Similarly if the date is 6/3/05, then 2 (the year is
irrelevant
for this field). The powers that be can't look at the date and calculate
the
quarter in their head so I have to do it for them in this report.

I was thinking an SQL subquery would work but I haven't a clue how to
write
that.

I can code the data input form and populate the table driving the above
query but, I prefer to have the calculation in the Query because it will
catch any data changes/updates in the table (which are unlikely but may
happen). Am I barking up the wrong tree with the SQL? And if not, anyone
know how to write that?

 

[Marshall Barton]

I need to create a calculated field in a query that looks at the 'Complete
Date' and returns the quarter the deal completed. Example, if the complete
date is 2/15/05, then it completed in Q1 and the query should return 1 in
that record. Similarly if the date is 6/3/05, then 2 (the year is irrelevant
for this field). The powers that be can't look at the date and calculate the
quarter in their head so I have to do it for them in this report.

I was thinking an SQL subquery would work but I haven't a clue how to write
that.

I can code the data input form and populate the table driving the above
query but, I prefer to have the calculation in the Query because it will
catch any data changes/updates in the table (which are unlikely but may
happen). Am I barking up the wrong tree with the SQL? And if not, anyone
know how to write that?

The calculated field can be just:
DatePart("Q", [Complete Date])