Solved

In Access, I need a Query to Convert a Date to the First Day of that Date's Month and Year

Posted on 2011-09-12
5
998 Views
Last Modified: 2012-05-12
I am trying to extract the month and year from a mm/dd/yyyy date. I need to Join it later with a Table that has Months and Years (Formatted mmm-yy) ...but the actual date values for those are always the first of the month, e.g., Apr-04 is really 4/1/2004

If I don't have the date values match, the Join finds no records equal and returns nothing.

So...I need to turn a date like 4/25/2004 into 4/1/2004
0
Comment
Question by:Rex85
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36524361
use this to get the first day

firstDay:dateserial(year([dateField]),month([dateField]),1)
0
 

Author Comment

by:Rex85
ID: 36524566
I think I am doing something wrong. I pasted your expression into the Select statement...

SELECT GetWeekEndingDate([Created on]) AS WeekEndingDate, Sum(KO_QN_Data.[DefectQty (ext)]) AS Total_QNs, firstDay:dateserial(year([Created on]),month([Created on]),1) AS First_Day INTO QPR_Interstuhl_QNs_tbl
FROM KO_QN_Data

...and got the following error.

I couldn't save the query.
0-syntax-error.jpg
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 36524639


SELECT GetWeekEndingDate([Created on]) AS WeekEndingDate, Sum(KO_QN_Data.[DefectQty (ext)]) AS Total_QNs,  dateserial(year([Created on]),month([Created on]),1) AS First_Day INTO QPR_Interstuhl_QNs_tbl
FROM KO_QN_Data
0
 

Author Closing Comment

by:Rex85
ID: 36524685
Fantastic! Thank you very much.

Rex
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 36524742
This expression will yield the first day of the current month:

CDate(Month(Date) & "/1/" & Year(Date))

Substitute a Date field or variable for the Date function to get the first day of a specified month
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question