473,890 Members | 1,697 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Quarter to Date conversion

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.
Dec 13 '07 #1
2 5422
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(R ight([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.********@gma il.comwrote in message
news:d7******** *************** ***********@e6g 2000prf.googleg roups.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.
Dec 13 '07 #2
p.********@gmai l.com wrote in
news:d77231e9-d736-4a3b-b00e-5a0f52814e85
@e6g2000prf.goo glegroups.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(qu arterfield,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

Dec 13 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
14528
by: Matt | last post by:
Hello, I have a query that I would like to schedule in DTS. The criteria of this query checks for records in the table that are within the current quarter. Here is what I have. WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005' I would like to dynamically generate the Quarter End and Quarter Beginning dates within my where clause based on the date that DTWS
1
4063
by: Terencetrent | last post by:
I have created a query that examines qarterly sales for 5 regions in the country. The query contains data for the past 6 quarters for each region and calculates the perecentage of total sales for the quarter fo each region. To help enhance the report I would love to include a comparison to of current sales to previous quarter sales and a comparison of current sales to sales a year ago. I am having a devil of a time creating a query to...
8
11891
by: Dominique Vandensteen | last post by:
I have a datetime and want to format it to "quarter year" so 20 december 2003 should give: "4-2003" is this possible? I don't find a format character for quarter :-( Dominique
3
2864
by: RD | last post by:
Say Company's financial year starts October First and say we are now March 17th. How do you determine which quarter of the Financial year - not the actual year - March 17th is in. Thanks for any help, Bob
2
11782
by: JohnC | last post by:
This fantastic expression was posted by Duane Hookom. I have no idea how it works but it displays the fiscal year and quarter for FY starting on October 1. =Format$(DateAdd("q",1,),"\Qq yyyy",0,0) It displays the first quarter for FY 2007 as Q1 2007. However I would like to display this as 2007 Q1. I couldn't get this to work.
1
2089
by: mnoland | last post by:
I relay need some help I am looking to find every quarter between 2 dates here is an example of what i am trying to do data table client sbegin send 499 1-1-2000 6-1-2001 499 12-12-05 12-21-05 499 10-5-06 12-15-06 499 12-1-06 12-10-06
6
16984
by: =?Utf-8?B?bWFydGluMQ==?= | last post by:
All, I try to get specific date's quarter value, for eaxmple quarter value is 1 for 02/04/2008, anyone can help this out? Thanks Martin
2
7659
benchpolo
by: benchpolo | last post by:
First Day of the QUARTER select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) Question: How do I get the last DAY of the QUARTER? For example: 1st Quarter is 01/01/2008 to 03/31/2008. I am having difficulties finding the last day of the quarter.
4
11986
by: gimme_this_gimme_that | last post by:
Is there a way to get the last day of the previous business quarter from DB2? For 10/21/2008 the day would be 9/30/2008. Thanks.
0
9976
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9815
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11215
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10802
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10451
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9618
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8008
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
4665
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4259
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.