dates in query...?

[111]

I have a total query with a date field which is giving me problems.

What i need the date field to return is a month and a year ie: "9,2003" ;
"1,2004" etc.
what i'm getting from this is "9,2" : "10, "

The expression i'm using is:
year: DatePart("m",[Date]) & ", " & DatePart("yyyy",[Date])

Can someone show me what I"m doing wrong???

Thanks

 

[Ken Snell]

Try this:

Format([Date], "m,yyyy")

Note: not good idea to use Date as the name of a field, etc. It's a VBA
function and if you forget to put the [ ] around the field name, ACCESS may
become permanently confused.... use DateMY or something.

 

[111]

Thanks that work perfectly.
I was hoping to be able to sort by decending date order. When I select this in
the query i get 9,2003 then 8,2003 ...2,2004 ect.
How can i get these to sort by decending order for the whole date not just the
first digit of the date???

Thanks
Steve

Try this:

Format([Date], "m,yyyy")

Note: not good idea to use Date as the name of a field, etc. It's a VBA
function and if you forget to put the [ ] around the field name, ACCESS may
become permanently confused.... use DateMY or something.

--
Ken Snell
<MS ACCESS MVP>

I have a total query with a date field which is giving me problems.

What i need the date field to return is a month and a year ie: "9,2003" ;
"1,2004" etc.
what i'm getting from this is "9,2" : "10, "

The expression i'm using is:
year: DatePart("m",[Date]) & ", " & DatePart("yyyy",[Date])

Can someone show me what I"m doing wrong???

Thanks

 

[Ken Snell]

Add the [Date] field to the query, mark it to "not" show, and set its order
for Descending.

--
Ken Snell
<MS ACCESS MVP>

Thanks that work perfectly.
I was hoping to be able to sort by decending date order. When I select this in
the query i get 9,2003 then 8,2003 ...2,2004 ect.
How can i get these to sort by decending order for the whole date not just the
first digit of the date???

Thanks
Steve

Try this:

Format([Date], "m,yyyy")

Note: not good idea to use Date as the name of a field, etc. It's a VBA
function and if you forget to put the [ ] around the field name, ACCESS may
become permanently confused.... use DateMY or something.

--
Ken Snell
<MS ACCESS MVP>

I have a total query with a date field which is giving me problems.

What i need the date field to return is a month and a year ie: "9,2003" ;
"1,2004" etc.
what i'm getting from this is "9,2" : "10, "

The expression i'm using is:
year: DatePart("m",[Date]) & ", " & DatePart("yyyy",[Date])

Can someone show me what I"m doing wrong???

Thanks

 

[111]

Thanks works perfectly

Add the [Date] field to the query, mark it to "not" show, and set its order
for Descending.

--
Ken Snell
<MS ACCESS MVP>

Thanks that work perfectly.
I was hoping to be able to sort by decending date order. When I select this in
the query i get 9,2003 then 8,2003 ...2,2004 ect.
How can i get these to sort by decending order for the whole date not just the
first digit of the date???

Thanks
Steve

Try this:

Format([Date], "m,yyyy")

Note: not good idea to use Date as the name of a field, etc. It's a VBA
function and if you forget to put the [ ] around the field name, ACCESS may
become permanently confused.... use DateMY or something.

--
Ken Snell
<MS ACCESS MVP>

I have a total query with a date field which is giving me problems.

What i need the date field to return is a month and a year ie: "9,2003" ;
"1,2004" etc.
what i'm getting from this is "9,2" : "10, "

The expression i'm using is:
year: DatePart("m",[Date]) & ", " & DatePart("yyyy",[Date])

Can someone show me what I"m doing wrong???

Thanks