date issue

  • Thread starter Thread starter Stephen
  • Start date Start date
S

Stephen

I have a table that contains a date field, and some
additional numerical value fields. The data in the date
field ranges from 01jan04 to present day.

I want to create a query that sums all the records for
each numerical field per month. Thus providing a total
for each month.

Currently I'm using;
#01/05/2004# And <#01/05/2004# or
Click to expand...
between 01/01/04 and 31/01/04

But my problem is that all months have different amount of
days. Therefore I really want to just difine January or
February. Could someone please help?

Many thanks for your help.

Stephen
 
Stephen

Take a look at Access HELP on the Month() command, as one possibility (the
notion is you'd query where Month = 11, or some such).

Or consider a Totals query, GroupedBy that Month() value.

NOTE! You'll probably also want to restrict the year under consideration,
since January happens every year <g>!
 
Thanks for your comments Jeff, but I've already been
through the help and tried that. I also thought that month
(2) in the criteria would work, but I just get no
results!! I have totalled by group, where, ect but to no
avail.

Do you know of anyway around this?
 
Hi again, I have got this working as ive created a
calculated field; DATE: Month([Datefield])

Thanks jeff and to all. Take care, Stephen
 
Stephen

A word of caution -- Access treats "Date" as a reserved word, and may decide
you mean something other than what you've described. At a minimum, consider
changing that fieldname to MyMonth or some other descriptive text.

Your previous post raised a possible red flag in my mind. I don't
understand what you wanted to have Access do when you used "month(2)" as a
criterion. The Month() function uses a date and returns the month number.
"2" doesn't seem like a date, does it?
 
Hi Jeff,

In my query I entered a calc field with the following
expresion:
InPutMonth: Month([RepoDate])..RepoDate = a field in record

Not sure why/how it worked, but it broke down my data into
seperate months. This was the desired result.

The only issue is that it shows 1 for Jan 2 for Feb ect,
were preferably I'd like to see Jan, Feb ect.

Is this OK, or do you think I could encounter probs at
some future point? Thanks.

Stephen.
 
I'm not familiar with that type of calculation:
InPutMonth: Month([RepoDate])..RepoDate = a field in record
Click to expand...

or are you saying that the expression you entered was
InPutMonth: Month([RepoDate])
Click to expand...

If the latter, that will generate the month (number) of the field named
[RepoDate]. If you want to use month numbers, fine. If you want to see the
month name, you could simply format [RepoDate] to display "mmmm" (the full
text of the month name).

Again, I'll mention that 2003 AND 2004 both have months named "January" --
are you restricting your query by the Year([RepoDate]) as well?
 
Hi Jeff.

Yes, the expression was the latter. I've looked at the
field properties and the only option is numbers,currencies
ect. It does not recognise it as a date field.
I tried the following
InputMonth: Format(Month([RepoDate]),"mmm")
But it gave erroneous results.. jan, dec, jan, dec, ect
when it should have been Jan, Feb, Mar, Apr ect

Could you provide somethink that is more valid?

Thanks for your tip, Ive now restricted by year, I done
this by using InputYear: Year([RepoDate]) with a criteria
2004.

-----Original Message-----
I'm not familiar with that type of calculation:
InPutMonth: Month([RepoDate])..RepoDate = a field
Click to expand...
in record

or are you saying that the expression you entered was
InPutMonth: Month([RepoDate])
Click to expand...

If the latter, that will generate the month (number) of the field named
[RepoDate]. If you want to use month numbers, fine. If you want to see the
month name, you could simply format [RepoDate] to display "mmmm" (the full
text of the month name).

Again, I'll mention that 2003 AND 2004 both have months named "January" --
are you restricting your query by the Year([RepoDate]) as well?

--
Good luck

Jeff Boyce
<Access MVP>

.
Click to expand...
 
Ok, what Jeff was suggesting is that you add another calculated column to DISPLAY.

Field: ShowMonth: FORMAT([RepoDate],"mmm")

Your original calculated field can be retained as the sorting field

Field: SortMonth: Month([RepoDate])
Sort: Ascending

I hope that my jumping in will give you a different perspective on what Jeff has
been saying.
Hi Jeff.

Yes, the expression was the latter. I've looked at the
field properties and the only option is numbers,currencies
ect. It does not recognise it as a date field.
I tried the following
InputMonth: Format(Month([RepoDate]),"mmm")
But it gave erroneous results.. jan, dec, jan, dec, ect
when it should have been Jan, Feb, Mar, Apr ect

Could you provide somethink that is more valid?

Thanks for your tip, Ive now restricted by year, I done
this by using InputYear: Year([RepoDate]) with a criteria
2004.
-----Original Message-----
I'm not familiar with that type of calculation:
InPutMonth: Month([RepoDate])..RepoDate = a field
Click to expand...
in record

or are you saying that the expression you entered was
InPutMonth: Month([RepoDate])
Click to expand...

If the latter, that will generate the month (number) of the field named
[RepoDate]. If you want to use month numbers, fine. If you want to see the
month name, you could simply format [RepoDate] to display "mmmm" (the full
text of the month name).

Again, I'll mention that 2003 AND 2004 both have months named "January" --
are you restricting your query by the Year([RepoDate]) as well?

--
Good luck

Jeff Boyce
<Access MVP>

.
Click to expand...
Click to expand...
 
Back
Top