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
854 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
  • 2
  • 2
5 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
use this to get the first day

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

Author Comment

by:Rex85
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility


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
Comment Utility
Fantastic! Thank you very much.

Rex
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now