convert given date to first day of the given month

[perryclisbee via AccessMonster.com]

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

['69 Camaro]

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

[paii, Ron]

"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))

[chris.nebinger]

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))

['69 Camaro]

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