469,366 Members | 2,236 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

convert given date to first day of the given month

I have dates of service for several people that range all over each month.
ie: patient had dates of service of: 7/3/2006, 7/24/2006 and 7/25/2006. I
need to create a new field via a query that will convert each of the records
of these service dates to the first date of that month, with results showing:
7/1/2006, 7/1/2006, 7/1/2006. How would you place an expression on a query
that will convert any given date to the first day of the month for that given
month, no matter what that date might be?

Thanks,

Perry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1

Jan 5 '07 #1
4 37818
Hi, Perry.
How would you place an expression on a query
that will convert any given date to the first day of the month for that given
month, no matter what that date might be?
Use the DateSerial( ) method. Try this example:

SELECT SomeDate, DateSerial(Year(SomeDate), Month(SomeDate), 1) AS FirstOfMonth
FROM tblStuff;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"perryclisbee via AccessMonster.com" <u9916@uwewrote in message
news:6bd8ac98fd7a6@uwe...
>I have dates of service for several people that range all over each month.
ie: patient had dates of service of: 7/3/2006, 7/24/2006 and 7/25/2006. I
need to create a new field via a query that will convert each of the records
of these service dates to the first date of that month, with results showing:
7/1/2006, 7/1/2006, 7/1/2006. How would you place an expression on a query
that will convert any given date to the first day of the month for that given
month, no matter what that date might be?

Thanks,

Perry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1

Jan 5 '07 #2

"perryclisbee via AccessMonster.com" <u9916@uwewrote in message
news:6bd8ac98fd7a6@uwe...
I have dates of service for several people that range all over each month.
ie: patient had dates of service of: 7/3/2006, 7/24/2006 and 7/25/2006. I
need to create a new field via a query that will convert each of the
records
of these service dates to the first date of that month, with results
showing:
7/1/2006, 7/1/2006, 7/1/2006. How would you place an expression on a
query
that will convert any given date to the first day of the month for that
given
month, no matter what that date might be?

Thanks,

Perry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1
datevalue(month([Datefield])&"/1/"&year([DateField))
Jan 5 '07 #3
datevalue(month([Datefield])&"/1/"&year([DateField))

I wouldn't rely on using this to determine the first. If the system's
date format is set to dd/mm/yy, then all this will do is give January
1-12th as a date. I'd stick to the DateSerial function.

Chris Nebinger

paii, Ron wrote:
"perryclisbee via AccessMonster.com" <u9916@uwewrote in message
news:6bd8ac98fd7a6@uwe...
I have dates of service for several people that range all over each month.
ie: patient had dates of service of: 7/3/2006, 7/24/2006 and 7/25/2006. I
need to create a new field via a query that will convert each of the
records
of these service dates to the first date of that month, with results
showing:
7/1/2006, 7/1/2006, 7/1/2006. How would you place an expression on a
query
that will convert any given date to the first day of the month for that
given
month, no matter what that date might be?

Thanks,

Perry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1

datevalue(month([Datefield])&"/1/"&year([DateField))
Jan 5 '07 #4
Hi, Perry.

Another alternative is to format the date with only month and year, then
convert that string into a date, but I suspect string operations would be
slower than DateSerial( ). Try:

SELECT SomeDate, CDate(Format$(SomeDate,"mmm yyyy")) AS FirstOfMonth
FROM tblStuff;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AMwrote in
message news:LJ******************************@adelphia.com ...
Hi, Perry.
>How would you place an expression on a query
that will convert any given date to the first day of the month for that
given
month, no matter what that date might be?

Use the DateSerial( ) method. Try this example:

SELECT SomeDate, DateSerial(Year(SomeDate), Month(SomeDate), 1) AS
FirstOfMonth
FROM tblStuff;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"perryclisbee via AccessMonster.com" <u9916@uwewrote in message
news:6bd8ac98fd7a6@uwe...
>>I have dates of service for several people that range all over each month.
ie: patient had dates of service of: 7/3/2006, 7/24/2006 and 7/25/2006.
I
need to create a new field via a query that will convert each of the
records
of these service dates to the first date of that month, with results
showing:
7/1/2006, 7/1/2006, 7/1/2006. How would you place an expression on a
query
that will convert any given date to the first day of the month for that
given
month, no matter what that date might be?

Thanks,

Perry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1


Jan 7 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

19 posts views Thread by Lauren Quantrell | last post: by
4 posts views Thread by Richard Hollenbeck | last post: by
1 post views Thread by manning_news | last post: by
1 post views Thread by Sam | last post: by
1 post views Thread by davelist | last post: by
6 posts views Thread by =?Utf-8?B?UGF1bA==?= | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.