If then else statements

[Nancy]

I am trying to enter a number in a form field then have a
number appear in the next field. If product code equals
1234 then company code equals 300. I have tried to enter
information in the query but can't. How do I write the
statement and where does it go. I am at my wits end.

Thank you,
Nancy

 

[Bas Cost Budde]

I am trying to enter a number in a form field then have a
number appear in the next field. If product code equals
1234 then company code equals 300. I have tried to enter
information in the query but can't. How do I write the
statement and where does it go. I am at my wits end.

Thank you,
Nancy

If you can derive that company code from the product code, you don't
need to store that company code there! John Vinson would say: you are
using a relational database. Use it relationally!

 

[_Bigred]

you would have to open a new query in design view add the table to the query
and then drag the desired field on to the qbe grid (the field you want to
update).

then change it to a "update query"

then on the "update to" line you would put your calculation ....

IIF([table1].[productCODE]=1234,300)

you will have to replace table 1 and productCODE with whatever the proper
table name & field you are updating. Since you didn't provide that type of
information my example/explanation, I did it as a hypothetical example.

You can only have (I believe) 7 IIF statements nested. If you have more
updates then that to do, then you might want to create a update query that
updates 1 table from a different table.

Hope this helps, let me know if it works for you or you need more help.
_Bigred

 

[Steve Schapel]

Nancy,

Can you please clarify what you are trying to do here? What if the
Product Code is not 1234? Is the procedure you are looking for only in
the case of Product Code 1234, and otherwise Company Code is left blank?
Or otherwise Company Code is manually entered? Or otherwise Company
Code is something else, which you also want to automatically "appear"
depending on the Product Code? By the way, are you using a Combobox to
enter the Product Code on the form?

 

[Nancy]

Steve,
My goal is to enter a code 101705 and have the number 300
appear in the next field. I want to contiue with several
more sets of numbers. I have eight sets of numbers, so
one number will have to manually entered. I am using text
boxes in tabular form in form view. I have found the
information to do the if then statements, but I can't get
it to work....If product code is 101705 then company code
is 300...If product code is 101493 then company code is
200 and so on.
Nancy

 

[Steve Schapel]

Nancy,

Thanks for the further clarification.

In this case, I agree with the reply you received from Bas Cost Budde.
Writing the value in one field based on the value entered in another
field is incorrect procedure. The 8 sets of numbers must be recorded in
a table somewhere, either an existing table if appropriate, or a new
table specifically for the purpose. And then to get the other number to
show on the form, you need to do one of three things...
1. Base the form on a query that includes the table that has the lookup
values, so you can include this field directly on the form.
2. Use a multi-column combobox for the entry of the code number, and
then use an expression in the controlsource of an unbound combobox to
display the daya from the second column of the combobox
3. Use a DLookup function in the controlsource of the combobox to
display the corresponding number.

If you need more specific help with any of these, please give more
details of your data.

 

[Nancy]

Can I call you? I will try one of your suggestions. I
told my boss that I need to go to a class. This self-
teaching is for the birds. He laughed.

 

[Steve Schapel]

Nancy,

Access is not a simple programme, and most people find a pretty steep
learning curve to get a grasp of the basics. So you're normal And
your boss is apparently ignorant. A class would be a good idea, but I
have taught many Access courses, and I know that the best you can do
there too is cover the basics, and there is still an on-going need for
learning after the class is over. A good book on Access would be
invaluable. And it looks like your boss is happy to allow you access to
the newsgroups in work time, so that's a great resource. So if you are
allowed to stuff around with the project you are working on at the
moment until you get it right, you will definitely learn a lot in the
process, as a result of which your next database will be a lot smoother.

 

[Nancy]

What book(s) would you recomend for Access 97?

-----Original Message-----
Nancy,

Access is not a simple programme, and most people find a pretty steep
learning curve to get a grasp of the basics. So you're normal And
your boss is apparently ignorant. A class would be a good idea, but I
have taught many Access courses, and I know that the best you can do
there too is cover the basics, and there is still an on- going need for
learning after the class is over. A good book on Access would be
invaluable. And it looks like your boss is happy to allow you access to
the newsgroups in work time, so that's a great resource. So if you are
allowed to stuff around with the project you are working on at the
moment until you get it right, you will definitely learn a lot in the
process, as a result of which your next database will be a lot smoother.

 

[Steve Schapel]

Nancy,

I suggest you post this question as a separate new thread, as some other
people will be able to offer better recommendations than me for general
overview Access 97 book.