Bruce, Thank you very much, this was very helpful!!!
:
It is ususally a good idea to state the nature of the problem.
"Something
is off" is vague.
I noticed a few things. There was an extra 0 before the first
instance
of
the Date. Also, use Is Null, not = Is Null. Be aware that Date is a
reserved word in Access, and should not be used for a field name. For
a
listing of reserved words, see:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#ReservedWords
In particular, look at Reserved Words in Microsoft Access and Reserved
Words
in Jet 4.0.
You can get around using a reserved word by enclosing it in brackets,
but
sooner or later you will run into a problem with using reserved words
incorrectly, and it could be difficult to track down.
Having said that, here is your expression, amended.
= IIf( [2oz] ="X" and [Date] < #5/14/2007#,.545,IIf( [2oz] Is Null and
[Date] < #5/14/2007#,.321,IIf( [2oz] = "x" and [Date] >=
#05/14/2007#,.459,IIf(
[2oz] Is Null and [Date] >= #05/14/2007#,.334))))
You can, however, shorten it to something like:
= IIf([2oz] = "X",IIf([Date] < #5/14/07#,.545,.459),IIf([2oz] Is
Null,IIf([Date] < #5/14/07#,.321,.334)))
There are only two choices for the date: before 5/14/07, or else on or
after
5/14/07. You don't need to test for >=, because anything that is not
<
is
automatically >=. If the only two choices are X or null, you can
shorten
it
to:
= IIf([2oz] = "X",IIf([Date] < #5/14/07#,.545,.459),IIf([Date] <
#5/14/07#,.321,.334))
However, if there could be an entry other than X or Null you will need
to
allow for that in the expression:
= IIf([2oz] = "X",IIf([Date] < #5/14/07#,.545,.459),IIf([2oz] Is
Null,IIf([Date] < #5/14/07#,.321,.334),"Other"))
If the expression gets much more involved than that you may do well to
use
VBA, where a wider range of approaches are possible.
Bruce, thanks for your advise! The scope of the project changesd
direction
a
bit. I have another questions, maybe you can help me. I am trying to
create
this IIF statement in a control and something is off, can you take a
look
at
it?
= IIf( [2oz] ="X" and [Date] <0#5/14/2007#,.545,IIf( [2oz] = is Null
and
[Date] <#5/14/2007#,.321,IIf( [2oz] = "x" and [Date]
=#05/14/2007#,.459,IIf(
[2oz] = is Null and [Date] >= #05/14/2007#,.334))))
THANK YOU!
:
Responses inline.
Bruce thank you for all your advise so far, but here is the
problem.
I
have
been working on this project for a while and I really need to
come
up
with
a
form for people to fill out that will store the info that they
fill
in.
I
don't have the luxury any more to learn more termanology, I have
to
show
something for all the time I out in. I leaned access from scratch
and
the
posts that i found here... and I am sure I know maybe 3% of
what's
there,
but
that's not excuse for not getting this project done.
A great deal can be accomplished with just a fraction of the
capabilities
of
Access, but a solid design is essential to using Access properly.
Without
knowing more about how your database is structured or the
real-world
situation it is trying to addess I may not be able to offer many
useful
suggestions. Much can be accomplished with Access, but there is a
substantial learning curve. You may not have enough knowledge of
the
product to get the particular job done. By analogy, having a
learner's
permit, and being familiar with the basics of putting a car into
gear
and
steering it around the neighborhood, does not qualify you to take
part
in
a
road rally. It's not an excuse, it's a fact.
To answer your questions: I ran the compile in VBA and there was
one
problem, I took care of it. When I say Control source = master
Item
#=forms!FrmJob1ID.Column(7), I mean by that the control source in
the
properties sheet for that text box is called master item #.
For what text box? What is Forms!frmJob1ID.Column(7)?
Also, I don't think I would need a "contact" table to be updated
b/c
the
user is selecting from a given list of operators. When they
select
the
operator from the combo box, other text boxes are automatically
populated.
The Contacts table was an example: "If you have a Contacts table
...".
It
is a familiar situation, so I had hoped it would be useful as an
example.
Ex. Combo box Operator. Row souce in properties is called
operator
query
Column 1 shows: Bob, John, Joe, Mike
What is the source for the operator query? Is there an Employee
table
or
something of the sort?
Machine they work on text box: autopopulates 12345 (based on
Operator
query
column 2)
Each operator works on just one machine? Do you mean they operate
the
machine, or is this a shop that repairs machines? "Work on" is a
vague
term. In any case, "machine they work on" is not an attribute of
an
operator, so the information should be stored in a separate table.
If
that
is already the case, and the operator query assembles information
from
several tables, the question remains whether you need to store the
information about machine and shift, or just link to it.
Shift they work, text box autopopulates 1 (based on operator
query
column
3)
That's pretty much the set up for this form. When the user
selects
something
from the combo box(based on row sources and the text boxes based
on
control
sources), other text boxes autopopulate. I hope this makes sense.
I'm sorry to have to tell you it does not make much sense to me. I
am
unfamiliar either with the real-world business situation you are
trying
to
address or the structure of your database. If you can post a
description
of
the tables and relationships it may be possible to come up with
something
more definite.
:
With the VBA editor open, click Debug > Compile ___ to compile
the
code.
If
you have created a situation such as attempting to requery a
field
(rather
than a control), it will highlight the line of code. It also
guards
against
typographical errors, and quite a few other things that will
prevent
the
code from running.
I am not quite sure what to make of something like:
control source = Master Item2 = Forms!FrmJob1!ID.Column(7)
Is MasterItem2 a combo box? If so, Forms!FrmJob1!ID.Column(7)
cannot
be
its
control source. The Control Source is the field to which a
control
is
bound. If you have a Contacts table with fields for FirstName,
LastName,
etc., and you create a form bound to the Contacts table for
entering
and
editing contact information, then information typed into a text
box
with
its
control source set to FirstName will store that information in
the
FirstName
field of the Contacts table.
To elaborate a bit, open a form in design view. Click View >
Properties
(or
double click the small square at the very top left of the form
window).
You
will see a place for the Record Source. If you select a table
or a
query
bound to the table as the Record Source, the form is a bound
form.
That
is,
it is bound to that record source (table or query). The form
becomes
a
sort
of portal to the table.
Next, the controls. A control is a combo box, text box, label,
line,
or
just about anything else on the form. Some controls, such as
text
boxes
and
combo boxes, may be bound to fields in the form's Record Source.
That
is, a
field from the form's Record Source is selected as the control's
Control
Source. Information entered into the bound control (or selected
from
a
combo box list, which accomplishes the same thing as typing into
a
text
box)
will be stored in that field in the form's Record Source.
Back to the code you have provided, this cannot do anything I
can
see:
Control Source=PS Item 2= " " (8)
Another thing I found puzzling was that you said the info needs
to
be
saved
to a new table. Why? It is possible to create a table through
code
and
store information there. I couldn't say exactly how to go about
that,
but I
doubt very much that it is what you need to do.
I think a greater understanding of some of the terminology would
be
of
help.
A good place to start is here:
http://allenbrowne.com/tips.html
In particular, look at the Table Design links in the Tips for
Casual
Users.
On the right side of the page, toward the bottom of the list, is
a
Links
link. That will take you to quite an extensive list of Access
links
for
users of all levels.
Below are my combo boxes:
Combo Category = Row Source = Select Query
Combo Job = Row Source = Query 5 (Main Query)
Cat ID, Cat code, Job Name, Master item, PS item, Master Desc,
Price,
Master
item 2, ps item2, price2, mat2, job cc, contact
Combo ID = row source = query 5, event procedure after update
text box = count
