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
http://I.Am/DougSteele
(no e-mails, please!)
<(E-Mail Removed)> wrote in message
news:032aaffa-30e8-424f-985f-(E-Mail Removed)...
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.
Similar Threads
