Quarter to Date conversion

[p.smachylo]

Hello,
I am relatively new to Microsoft Access and databases in general, and
have a problem - one which I think may necessitate some VBA code (or
maybe just modification of the criteria section of the query.
I have a table with several columns being imported into a query which
will eventually be used in a report. One of the fields in the
database is called 'Predicted Completion Date' and is in the format:
Q# Year (formatted as text). Some examples are: Q1 2003, Q4 2007, Q3
2004 etc. Now I need to compare this date with another date, in
"normal" date format which comes from another table, but will be
located in the same query. What I'm needing to do is to convert the Q#
part to a date, and then tack on the year to it. What I'm hoping to do
is make:
Q1 = January 1
Q2 = April 1
Q3 = July 1
Q4 = October 1
and then add on whatever the year is to the end.
For example, Q2 2006 = April 1 2006
Would anyone know how I would go about accomplishing this?

Thank you!
-r.s.

[Allen Browne]

The month is:
3 * (Val(Nz(Mid([Predicted Completion Date],2,1),"")) - 1) + 1

The year is:
Nz(Right([Predicted Completion Date],4), 1900)

The date is therefore something like this (one line):
DateSerial(Nz(Right([Predicted Completion Date],4), 1900),
3 * (Val(Nz(Mid([Predicted Completion Date],2,1),"")) - 1) + 1,
1)

It would probably be easier and more efficient to store the quarter as a
date rather than text.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<p.********@gmail.comwrote in message
news:d7**********************************@e6g2000p rf.googlegroups.com...

Hello,
I am relatively new to Microsoft Access and databases in general, and
have a problem - one which I think may necessitate some VBA code (or
maybe just modification of the criteria section of the query.
I have a table with several columns being imported into a query which
will eventually be used in a report. One of the fields in the
database is called 'Predicted Completion Date' and is in the format:
Q# Year (formatted as text). Some examples are: Q1 2003, Q4 2007, Q3
2004 etc. Now I need to compare this date with another date, in
"normal" date format which comes from another table, but will be
located in the same query. What I'm needing to do is to convert the Q#
part to a date, and then tack on the year to it. What I'm hoping to do
is make:
Q1 = January 1
Q2 = April 1
Q3 = July 1
Q4 = October 1
and then add on whatever the year is to the end.
For example, Q2 2006 = April 1 2006
Would anyone know how I would go about accomplishing this?

Thank you!
-r.s.

[Bob Quintal]

p.********@gmail.com wrote in
news:d77231e9-d736-4a3b-b00e-5a0f52814e85
@e6g2000prf.googlegroups.com
:

Hello,
I am relatively new to Microsoft Access and databases in general,
and have a problem - one which I think may necessitate some VBA
code (or maybe just modification of the criteria section of the
query. I have a table with several columns being imported into a
query which will eventually be used in a report. One of the
fields in the database is called 'Predicted Completion Date' and
is in the format: Q# Year (formatted as text). Some examples are:
Q1 2003, Q4 2007, Q3 2004 etc. Now I need to compare this date
with another date, in "normal" date format which comes from
another table, but will be located in the same query. What I'm
needing to do is to convert the Q# part to a date, and then tack
on the year to it. What I'm hoping to do is make:
Q1 = January 1
Q2 = April 1
Q3 = July 1
Q4 = October 1
and then add on whatever the year is to the end.
For example, Q2 2006 = April 1 2006
Would anyone know how I would go about accomplishing this?

Thank you!
-r.s.

It would be much simpler to do the conversion the other way, in that

"Q" & datepart("q", somedate) & " " & datepart("yyyy",somedate)

will return the proper string for comparison, but if you cannot
compare that way, you can try

dateSerial(mid(quarterfield,4,4),((val(mid(quarter field,2,1)-1)*3)+
1,1)

All on one line

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com