Using dates for months

[Rudi Groenewald]

In our production line we have targets for our machines, which changes
monthly.

In a table I have 3 coloumns: Machine, date, target.

this has a relationship with a query that runs a report daily. In the report
/ query, the date changes constantly as days go on, eg: 01/05/2004,
02/05/2004, for each day's report, but now the targets has to included in
this report everyday and has to show the monthly targets.

When I capture the monthly target as follows:
8066301 : April 2004 : 60
Then access will autoconvert the date to 01/05/2004 or 01 April 2004
whichever format is used, leading to the targets being read into the report
only on the 1st of april 2004, and not the rest of the month.

What do I do so that my targets gets read the whole month until the next
targets are set?

Thanks in advance

[Rudi Groenewald]

Hi all again...

I have tried to insert the following in the daily report query:

format(dbo.dc_machinehours_MAIN.Date_Stamp, [#01#/mm/yyyy])

Meaning, it should take that date and just give the first day of that month,
then I can link my table to that field, but for some reason it gives me that
format is not a recognised function name

Please help, thanks a lot again in advance

"Rudi Groenewald" <tj**@tjoppie.co.za> wrote in message
news:c7**********@ctb-nnrp2.saix.net...

In our production line we have targets for our machines, which changes
monthly.

In a table I have 3 coloumns: Machine, date, target.

this has a relationship with a query that runs a report daily. In the report / query, the date changes constantly as days go on, eg: 01/05/2004,
02/05/2004, for each day's report, but now the targets has to included in
this report everyday and has to show the monthly targets.

When I capture the monthly target as follows:
8066301 : April 2004 : 60
Then access will autoconvert the date to 01/05/2004 or 01 April 2004
whichever format is used, leading to the targets being read into the report only on the 1st of april 2004, and not the rest of the month.

What do I do so that my targets gets read the whole month until the next
targets are set?

Thanks in advance

[Bob Quintal]

"Rudi Groenewald" <tj**@tjoppie.co.za> wrote in
news:c7**********@ctb-nnrp2.saix.net:

Hi all again...

I have tried to insert the following in the daily report
query:

format(dbo.dc_machinehours_MAIN.Date_Stamp, [#01#/mm/yyyy])

Meaning, it should take that date and just give the first day
of that month, then I can link my table to that field, but for
some reason it gives me that format is not a recognised
function name

Please help, thanks a lot again in advance

"Rudi Groenewald" <tj**@tjoppie.co.za> wrote in message
news:c7**********@ctb-nnrp2.saix.net...

In our production line we have targets for our machines,
which changes monthly.

In a table I have 3 coloumns: Machine, date, target.

this has a relationship with a query that runs a report
daily. In the

report

/ query, the date changes constantly as days go on, eg:
01/05/2004, 02/05/2004, for each day's report, but now the
targets has to included in this report everyday and has to
show the monthly targets.

When I capture the monthly target as follows:
8066301 : April 2004 : 60
Then access will autoconvert the date to 01/05/2004 or 01
April 2004 whichever format is used, leading to the targets
being read into the

report

only on the 1st of april 2004, and not the rest of the month.

What do I do so that my targets gets read the whole month
until the next targets are set?

Thanks in advance

Hi, to answer the second question first,
[#01#/mm/yyyy] should be "#01#/mm/yyyy".
(wrong delimiters)

As to your first question,

I much prefer the expression

period: year([mydatefield])*100+month([mydatefield])