423,851 Members | 2,762 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,851 IT Pros & Developers. It's quick & easy.

convert given date to first day of the given month

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a

"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

P: n/a
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

P: n/a
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.