conditionally setting field values to 0

[geert.van.ransbeeck]

Hello

I have this problem.

I made a query which amongst others contains a datafield
‘Enddate’ (with ex. 01/06/2011) and several other fields as ‘Y 2010’,
‘Y 2011’, ‘Y 2012’, ‘Y 2013’ … All those fields a filled in with data.

What I want to become: I finally want to create a report in which all
fields like ‘Y 2010’, ‘Y2011’ which depass the value of my field
‘Enddate’ are set to zero.

Ex. ‘Enddate’= 01/06/2011; in this case all values for this record in
the fields ‘Y2012’, ‘Y2013’, … must be set to zero. If 'Enddate' =
01/08/2012 all values for this record in the fields ‘Y2013’, ‘Y2014’,
‘Y2015’ … must be set to zero.

So it has to be checked record by record.

I am a beginner in vba. I suppose only vba is appropriate here.

Can anybody help me please?
Thank you.

 

[Douglas J. Steele]

No offense, but your table isn't normalized. Fields like Y2010, Y2011, Y2012
etc are referred to as a repeating group, and are a violation of database
normalization rules. Having them in your table makes your query far more
difficult than it should be!

That having been said, you can easily alter your query so that it returns 0
as appropriate. Rather than

SELECT Field1, Field2, Field3, Enddate,
Y2010, Y2011, Y2012, Y2013, Y2014, Y2015
FROM MyTable

use

SELECT Field1, Field2, Field3, Enddate,
IIf(Year(Enddate) < 2010, 0, MyTable.Y2010) AS Y2010,
IIf(Year(Enddate) < 2011, 0, MyTable.Y2011) AS Y2011,
IIf(Year(Enddate) < 2012, 0, MyTable.Y2012) AS Y2012,
IIf(Year(Enddate) < 2013, 0, MyTable.Y2013) AS Y2013,
IIf(Year(Enddate) < 2014, 0, MyTable.Y2012) AS Y2014,
IIf(Year(Enddate) < 2015, 0, MyTable.Y2013) AS Y2015
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello

I have this problem.

I made a query which amongst others contains a datafield
‘Enddate’ (with ex. 01/06/2011) and several other fields as ‘Y 2010’,
‘Y 2011’, ‘Y 2012’, ‘Y 2013’ … All those fields a filled in with data.

What I want to become: I finally want to create a report in which all
fields like ‘Y 2010’, ‘Y2011’ which depass the value of my field
‘Enddate’ are set to zero.

Ex. ‘Enddate’= 01/06/2011; in this case all values for this record in
the fields ‘Y2012’, ‘Y2013’, … must be set to zero. If 'Enddate' =
01/08/2012 all values for this record in the fields ‘Y2013’, ‘Y2014’,
‘Y2015’ … must be set to zero.

So it has to be checked record by record.

I am a beginner in vba. I suppose only vba is appropriate here.

Can anybody help me please?
Thank you.

 

[geert.van.ransbeeck]

Y2013, Y2013, etc are just example names I gave and (sorry) indeed not
normalised. In my reality they have other names (impact 2009, impact
2010 ....
So that's not the point.

 

[Douglas J. Steele]

So did my suggestion solve your problem or not?