Can't do Index in Access?

[Joe_Hunt via AccessMonster.com]

I suspect I'll need to do this in VBA, but I've never programmed in VBA in
Access before, and it seems to be different than Excel. I have a rather
complicated worksheet in Excel that I'm trying to move into Access. I'm stuck
on one piece (I've spent a full day on it now) and I would appreciate any
guidance anyone could give. In Excel the formula looks at a column of dates
and compares it to the first of the month date entered by the user, usually
me, and performs one calculation if the date in the column is prior to the
date entered, and pulls a number from an Index if it's greater than or equal
to that date. The values in the "Index" change monthly. On the form I'm
setting up in Access apparently I can't create an index, and from what I've
been able to find out I'll need to use VBA with If-Then-Else statements. I
have tried over and over again with no success.

It's dificult to explain, but what I need is for what is in "Month Index 1"
to be in a current month textbox, "Month Index 2" in the next month, on down
to Month Index 36 if there's enough months in the probable life of the
vehicle. Next month Month Index 1 will be there, and Month Index 2 will move
down one month, etc.

I need this to look at an Option Button "Frame" with two buttons (2008 and
2009 selections, with option 2008 value of 1 and 2009 value of 2) and a model
year TextBox (2008 or 2009 will be displayed depending on other textboxes,
currently "MODEL_YEAR"), and the entered date (currently Text410). This
formula in a textBox works if it's prior to the date entered: IIF([Frame462]
=1 AND [MODEL_YEAR]="2008" AND [Text415]=1, [Text236]-[Text460], I just stuck
whatever in the else part to insure the other part worked). The textbox
numbers vary depending on which one the formula is going into.

With modification depending on the month (this would be April 1, 2009, and is
Month Index 2 since the date in cell B1 is March 1, 2009), If it helps the
Excel formula I've been using is: =IF($C$6="2008",IF(F26<B$1,G25-$I$5,IF
(F27>=B$1,(INDEX(C$11:C$46,DATEDIF(B$1-DAY(B$1)+1,F27-DAY(F27)+1,"m"))))),IF
(F26<B$1,G25-$I$5,IF(F27>=B$1,(INDEX(D$11$46,DATEDIF(B$1-DAY(B$1)+1,F27-DAY
(F27)+1,"m"))))))

I realize this is a little vague, but I hope someone can help me with this
somehow. I've been working on this form for way too long. Thank you.

 

[John W. Vinson]

I suspect I'll need to do this in VBA, but I've never programmed in VBA in
Access before, and it seems to be different than Excel.

I have a rather
complicated worksheet in Excel that I'm trying to move into Access.

Excel is a spreadsheet. Access is a relational database programming
environment. It is emphatically NOT "Excel on steroids" and requires different
approaches and a different mindset!

I'm stuck
on one piece (I've spent a full day on it now) and I would appreciate any
guidance anyone could give. In Excel the formula looks at a column of dates
and compares it to the first of the month date entered by the user, usually
me, and performs one calculation if the date in the column is prior to the
date entered, and pulls a number from an Index if it's greater than or equal
to that date. The values in the "Index" change monthly. On the form I'm
setting up in Access apparently I can't create an index, and from what I've
been able to find out I'll need to use VBA with If-Then-Else statements. I
have tried over and over again with no success.

It's clear that what you mean by an Index is different than the meaning of the
term in Access. I would guess that you would want a Table with 36 rows, and
use the DateDiff function to find which row of this table should be joined.

It's dificult to explain, but what I need is for what is in "Month Index 1"
to be in a current month textbox, "Month Index 2" in the next month, on down
to Month Index 36 if there's enough months in the probable life of the
vehicle. Next month Month Index 1 will be there, and Month Index 2 will move
down one month, etc.

Assuming you have a table with fields UseMonth (values 1 to 36), Model_Year,
and whatever other values you need, you could use either DLookUp or a Query
joining

DateDiff("m", [SomeDateField], Date())

to the UseMonth to find the appropriate row. Your business logic may be
different so I'm not certain this is what you want!

I need this to look at an Option Button "Frame" with two buttons (2008 and
2009 selections, with option 2008 value of 1 and 2009 value of 2) and a model
year TextBox (2008 or 2009 will be displayed depending on other textboxes,
currently "MODEL_YEAR"), and the entered date (currently Text410). This
formula in a textBox works if it's prior to the date entered: IIF([Frame462]
=1 AND [MODEL_YEAR]="2008" AND [Text415]=1, [Text236]-[Text460], I just stuck
whatever in the else part to insure the other part worked). The textbox
numbers vary depending on which one the formula is going into.

With modification depending on the month (this would be April 1, 2009, and is
Month Index 2 since the date in cell B1 is March 1, 2009), If it helps the
Excel formula I've been using is: =IF($C$6="2008",IF(F26<B$1,G25-$I$5,IF
(F27>=B$1,(INDEX(C$11:C$46,DATEDIF(B$1-DAY(B$1)+1,F27-DAY(F27)+1,"m"))))),IF
(F26<B$1,G25-$I$5,IF(F27>=B$1,(INDEX(D$11$46,DATEDIF(B$1-DAY(B$1)+1,F27-DAY
(F27)+1,"m"))))))

I realize this is a little vague, but I hope someone can help me with this
somehow. I've been working on this form for way too long. Thank you.

You're trying to jam Access into an Excel-shaped box. It won't fit. You'll
need to *rethink the entire structure* of your application if it's as complex
as it appears to be! Trying to treat Access as a flawed implementation of
Excel will just get you massive headaches. I am getting a headache myself just
trying to understand your formula, and I don't understand your business logic,
so I can't really suggest how to start this restructuring.

 

[Tom van Stiphout]

On Wed, 25 Feb 2009 19:46:17 -0700, John W. Vinson

Very well said! The OP would be wise to hire competent help at least
in the design stages of the application.

-Tom.
Microsoft Access MVP

 

[Joe_Hunt via AccessMonster.com]

Okay...well without the stupidity of Tom's comment (exactly what are you an
MVP of anyway?), I appreciate your input. I'm trying to learn Access and have
no wish to fit it inside an Excel shaped box, I just want it to work and find
I learn as much from things that don't work as things that do. I figured when
I posed the question it was poorly put, but needed to start somewhere. I will
indeed rethink the entire structure. Thanks for your answer John. It's a
start.

I suspect I'll need to do this in VBA, but I've never programmed in VBA in
Access before, and it seems to be different than Excel.

I have a rather
complicated worksheet in Excel that I'm trying to move into Access.

Excel is a spreadsheet. Access is a relational database programming
environment. It is emphatically NOT "Excel on steroids" and requires different
approaches and a different mindset!

I'm stuck
on one piece (I've spent a full day on it now) and I would appreciate any

[quoted text clipped - 6 lines]

been able to find out I'll need to use VBA with If-Then-Else statements. I
have tried over and over again with no success.

It's clear that what you mean by an Index is different than the meaning of the
term in Access. I would guess that you would want a Table with 36 rows, and
use the DateDiff function to find which row of this table should be joined.

It's dificult to explain, but what I need is for what is in "Month Index 1"
to be in a current month textbox, "Month Index 2" in the next month, on down
to Month Index 36 if there's enough months in the probable life of the
vehicle. Next month Month Index 1 will be there, and Month Index 2 will move
down one month, etc.

Assuming you have a table with fields UseMonth (values 1 to 36), Model_Year,
and whatever other values you need, you could use either DLookUp or a Query
joining

DateDiff("m", [SomeDateField], Date())

to the UseMonth to find the appropriate row. Your business logic may be
different so I'm not certain this is what you want!

I need this to look at an Option Button "Frame" with two buttons (2008 and
2009 selections, with option 2008 value of 1 and 2009 value of 2) and a model

[quoted text clipped - 14 lines]

I realize this is a little vague, but I hope someone can help me with this
somehow. I've been working on this form for way too long. Thank you.

You're trying to jam Access into an Excel-shaped box. It won't fit. You'll
need to *rethink the entire structure* of your application if it's as complex
as it appears to be! Trying to treat Access as a flawed implementation of
Excel will just get you massive headaches. I am getting a headache myself just
trying to understand your formula, and I don't understand your business logic,
so I can't really suggest how to start this restructuring.

 

[dymondjack]

Okay...well without the stupidity of Tom's comment (exactly what are you an

MVP of anyway?),

What Tom was trying to say, I think, was that what you are trying to do here
cannot be done in Access (or at least should not be). Actually, he wasn't
even saying that, he was noting the fact that John had done a good job in
explaining why.

As far as the comment about seeking competent help in the design stage, I
would say he was doing you a favor in the suggestion. No offense, but it is
fairly obvious that you don't yet have a full understanding of how access is
designed to work (judging by what you are trying to accomplish through your
post, anyway). So, if that's the case, having someone give you a hand
getting things up and rolling is likely to save you very many hours of
frustration and headaches.

Situations like this are very common. People come into Access with a good
background in exel, and while the two may seem similar, they are actually
very different from each other. While a lot of people don't realize that,
they try and try and try to apply all this excel knowledge to access, and it
generally doesn't work.

So anyway, I would argue that Tom's comment wasn't stupid. My two cents
anyway. I think it was good advice.

A couple of months ago I got in somewhat of an argument with an MVP, and
realized when it was said and done, that he was right, and not I. They don't
give those MVP award things away on streetcorners... there's probably a good
reason he has one.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Okay...well without the stupidity of Tom's comment (exactly what are you an
MVP of anyway?), I appreciate your input. I'm trying to learn Access and have
no wish to fit it inside an Excel shaped box, I just want it to work and find
I learn as much from things that don't work as things that do. I figured when
I posed the question it was poorly put, but needed to start somewhere. I will
indeed rethink the entire structure. Thanks for your answer John. It's a
start.

I suspect I'll need to do this in VBA, but I've never programmed in VBA in
Access before, and it seems to be different than Excel.

I have a rather
complicated worksheet in Excel that I'm trying to move into Access.

Excel is a spreadsheet. Access is a relational database programming
environment. It is emphatically NOT "Excel on steroids" and requires different
approaches and a different mindset!

I'm stuck
on one piece (I've spent a full day on it now) and I would appreciate any

[quoted text clipped - 6 lines]

been able to find out I'll need to use VBA with If-Then-Else statements. I
have tried over and over again with no success.

It's clear that what you mean by an Index is different than the meaning of the
term in Access. I would guess that you would want a Table with 36 rows, and
use the DateDiff function to find which row of this table should be joined.

It's dificult to explain, but what I need is for what is in "Month Index 1"
to be in a current month textbox, "Month Index 2" in the next month, on down
to Month Index 36 if there's enough months in the probable life of the
vehicle. Next month Month Index 1 will be there, and Month Index 2 will move
down one month, etc.

Assuming you have a table with fields UseMonth (values 1 to 36), Model_Year,
and whatever other values you need, you could use either DLookUp or a Query
joining

DateDiff("m", [SomeDateField], Date())

to the UseMonth to find the appropriate row. Your business logic may be
different so I'm not certain this is what you want!

I need this to look at an Option Button "Frame" with two buttons (2008 and
2009 selections, with option 2008 value of 1 and 2009 value of 2) and a model

[quoted text clipped - 14 lines]

I realize this is a little vague, but I hope someone can help me with this
somehow. I've been working on this form for way too long. Thank you.

You're trying to jam Access into an Excel-shaped box. It won't fit. You'll
need to *rethink the entire structure* of your application if it's as complex
as it appears to be! Trying to treat Access as a flawed implementation of
Excel will just get you massive headaches. I am getting a headache myself just
trying to understand your formula, and I don't understand your business logic,
so I can't really suggest how to start this restructuring.

 

[John W. Vinson]

I'm trying to learn Access and have
no wish to fit it inside an Excel shaped box, I just want it to work and find
I learn as much from things that don't work as things that do. I figured when
I posed the question it was poorly put, but needed to start somewhere. I will
indeed rethink the entire structure. Thanks for your answer John. It's a
start.

Here's some more steppingstones: Crystal's videos and tutorials have gotten a
lot of positive comments.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

Crystal's video tutorials for Access on YouTube:
http://www.youtube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Tom van Stiphout]

On Thu, 26 Feb 2009 20:21:43 GMT, "Joe_Hunt via AccessMonster.com"

Sorry if you feel that way. Sometimes it is hard to gauge from an
initial email what the poster's intent is. I took your post as someone
who had to solve a business problem right away, but did not know how
to think in relational concepts. Hiring someone for a day or two to
work that out can be a real time saver and quality saver. Typically
designing the database is the hardest part to grasp, and creating the
queries, forms, and reports on top of that is relatively
straightforward.

-Tom.
Microsoft Access MVP

Okay...well without the stupidity of Tom's comment (exactly what are you an
MVP of anyway?), I appreciate your input. I'm trying to learn Access and have
no wish to fit it inside an Excel shaped box, I just want it to work and find
I learn as much from things that don't work as things that do. I figured when
I posed the question it was poorly put, but needed to start somewhere. I will
indeed rethink the entire structure. Thanks for your answer John. It's a
start.

I suspect I'll need to do this in VBA, but I've never programmed in VBA in
Access before, and it seems to be different than Excel.

I have a rather
complicated worksheet in Excel that I'm trying to move into Access.

Excel is a spreadsheet. Access is a relational database programming
environment. It is emphatically NOT "Excel on steroids" and requires different
approaches and a different mindset!

I'm stuck
on one piece (I've spent a full day on it now) and I would appreciate any

[quoted text clipped - 6 lines]

been able to find out I'll need to use VBA with If-Then-Else statements. I
have tried over and over again with no success.

It's clear that what you mean by an Index is different than the meaning of the
term in Access. I would guess that you would want a Table with 36 rows, and
use the DateDiff function to find which row of this table should be joined.

It's dificult to explain, but what I need is for what is in "Month Index 1"
to be in a current month textbox, "Month Index 2" in the next month, on down
to Month Index 36 if there's enough months in the probable life of the
vehicle. Next month Month Index 1 will be there, and Month Index 2 will move
down one month, etc.

Assuming you have a table with fields UseMonth (values 1 to 36), Model_Year,
and whatever other values you need, you could use either DLookUp or a Query
joining

DateDiff("m", [SomeDateField], Date())

to the UseMonth to find the appropriate row. Your business logic may be
different so I'm not certain this is what you want!

I need this to look at an Option Button "Frame" with two buttons (2008 and
2009 selections, with option 2008 value of 1 and 2009 value of 2) and a model

[quoted text clipped - 14 lines]

I realize this is a little vague, but I hope someone can help me with this
somehow. I've been working on this form for way too long. Thank you.

You're trying to jam Access into an Excel-shaped box. It won't fit. You'll
need to *rethink the entire structure* of your application if it's as complex
as it appears to be! Trying to treat Access as a flawed implementation of
Excel will just get you massive headaches. I am getting a headache myself just
trying to understand your formula, and I don't understand your business logic,
so I can't really suggest how to start this restructuring.

 

[David W. Fenton]

Okay...well without the stupidity of Tom's comment

Great way to introduce yourself to the newsgroup! Yes, indeed,
insult one of the best posters in the newsgroup who has been trying
to help you.

You can be VERY WRONG without being aware of that fact, and
sometimes it takes a slap in the face to bring people around to an
awareness of how fundamental their misunderstanding is.

You should be thanking Tom for taking the time to reinforce a
message that is for your own good.

If not, then I doubt you're going to get much help around here. I
certainly have you on my list of possible lunatics because of the
reaction quoted above.