Dlookup

[nzyui]

Trying to lookup a field from a table called condition this is a separate
table not linked to anything: setup as shown
Code S M L
DG 1 2 3
RI 2 4 5
need a dlookup to give me a single code as in number 1 2 3
all from a form thats entered by user
ie: condition code DG severity L nedd to return 5
cheers

[Mike Preston]

On Mon, 19 Dec 2005 22:32:31 GMT, "nzyui" <u16807@uwe> wrote:

Trying to lookup a field from a table called condition this is a separate
table not linked to anything: setup as shown
Code S M L
DG 1 2 3
RI 2 4 5
need a dlookup to give me a single code as in number 1 2 3
all from a form thats entered by user
ie: condition code DG severity L nedd to return 5

What have you tried that hasn't worked?

mike

[nzyui]

Mike Preston wrote:

Trying to lookup a field from a table called condition this is a separate
table not linked to anything: setup as shown

[quoted text clipped - 4 lines]

all from a form thats entered by user
ie: condition code DG severity L nedd to return 5

What have you tried that hasn't worked?

mike

I can get the results by typing in the numbers manually need to to aquire
these numbers from forms

[Mike Preston]

On Mon, 19 Dec 2005 23:40:43 GMT, "nzyui" <u16807@uwe> wrote:

Mike Preston wrote:

Trying to lookup a field from a table called condition this is a separate
table not linked to anything: setup as shown

[quoted text clipped - 4 lines]

all from a form thats entered by user
ie: condition code DG severity L nedd to return 5

What have you tried that hasn't worked?

mike

I can get the results by typing in the numbers manually need to to aquire
these numbers from forms

Need more information. Be liberal with your keystrokes. Imagine
there are hordes of people who wish to assist you, if only you fed
them enough information so that they could. I know it is tough. But
you must.

mike

[nzyui]

Mike Preston wrote:

Trying to lookup a field from a table called condition this is a separate
table not linked to anything: setup as shown

[quoted text clipped - 8 lines]

I can get the results by typing in the numbers manually need to to aquire
these numbers from forms

Need more information. Be liberal with your keystrokes. Imagine
there are hordes of people who wish to assist you, if only you fed
them enough information so that they could. I know it is tough. But
you must.

mike

OK
i have been able to lookup the condition table putting info in manually using
the following
=DLookUp("[M]","Condition","[code] = 'DG'") the results are fine , but i dont
want to keep doing this manually off course
the info on my subform shows condition code which could be S M L with this
there is a condition code eg: DG RI ETC
with the example above need forms to put the "M" in and code DG from forms
hope this is more explicit for you
thank you

[Mike Preston]

On Tue, 20 Dec 2005 00:18:06 GMT, "nzyui" <u16807@uwe> wrote:

Mike Preston wrote:

>Trying to lookup a field from a table called condition this is a separate
>table not linked to anything: setup as shown[quoted text clipped - 8 lines]

I can get the results by typing in the numbers manually need to to aquire
these numbers from forms

Need more information. Be liberal with your keystrokes. Imagine
there are hordes of people who wish to assist you, if only you fed
them enough information so that they could. I know it is tough. But
you must.

mike

OK
i have been able to lookup the condition table putting info in manually using
the following
=DLookUp("[M]","Condition","[code] = 'DG'") the results are fine , but i dont
want to keep doing this manually off course

I'm not sure I understand. The above "lookup" does exactly what you
want. And while you can make it slightly more efficient using VBA,
for something as trivial as two rows and 3 fields, I think Dlookup
works fine.

But your use of the word "manually" gives me pause. What do you mean
by that? Are you taking the two fields "M" and "CODE" (as input by
the user) and then doing something yourself to run the Dlookup? Like
in the debug window? That would truly be "manually".
the info on my subform shows condition code which could be S M L with this
there is a condition code eg: DG RI ETC
with the example above need forms to put the "M" in and code DG from forms
hope this is more explicit for you

It is more explicit, but not quite explicit enough.

You already have the Dlookup function available to you.

What else are you looking for? Where do you intend to use it?

mike

[nzyui]

Mike Preston wrote:

>>Trying to lookup a field from a table called condition this is a separate
>>table not linked to anything: setup as shown

[quoted text clipped - 14 lines]

=DLookUp("[M]","Condition","[code] = 'DG'") the results are fine , but i dont
want to keep doing this manually off course

I'm not sure I understand. The above "lookup" does exactly what you
want. And while you can make it slightly more efficient using VBA,
for something as trivial as two rows and 3 fields, I think Dlookup
works fine.

But your use of the word "manually" gives me pause. What do you mean
by that? Are you taking the two fields "M" and "CODE" (as input by
the user) and then doing something yourself to run the Dlookup? Like
in the debug window? That would truly be "manually".

the info on my subform shows condition code which could be S M L with this
there is a condition code eg: DG RI ETC
with the example above need forms to put the "M" in and code DG from forms
hope this is more explicit for you

It is more explicit, but not quite explicit enough.

You already have the Dlookup function available to you.

What else are you looking for? Where do you intend to use it?

mike

Yes all i want is the forms to pick up the 2 fields M for example and DG for
example to give me a number from the condition table which transposes into a
field call score

[Mike Preston]

On Tue, 20 Dec 2005 00:55:49 GMT, "nzyui" <u16807@uwe> wrote:

Mike Preston wrote:

>>>Trying to lookup a field from a table called condition this is a separate
>>>table not linked to anything: setup as shown

[quoted text clipped - 14 lines]

=DLookUp("[M]","Condition","[code] = 'DG'") the results are fine , but i dont
want to keep doing this manually off course

I'm not sure I understand. The above "lookup" does exactly what you
want. And while you can make it slightly more efficient using VBA,
for something as trivial as two rows and 3 fields, I think Dlookup
works fine.

But your use of the word "manually" gives me pause. What do you mean
by that? Are you taking the two fields "M" and "CODE" (as input by
the user) and then doing something yourself to run the Dlookup? Like
in the debug window? That would truly be "manually".

the info on my subform shows condition code which could be S M L with this
there is a condition code eg: DG RI ETC
with the example above need forms to put the "M" in and code DG from forms
hope this is more explicit for you

It is more explicit, but not quite explicit enough.

You already have the Dlookup function available to you.

What else are you looking for? Where do you intend to use it?

mike

Yes all i want is the forms to pick up the 2 fields M for example and DG for
example to give me a number from the condition table which transposes into a
field call score

Have you tried putting the Dlookup function in the controlsource of
your score control? Just as you typed it above:

=DLookUp("[M]","Condition","[code] = 'DG'")

Oh, wait.

I think I get it.

Let's assume that you have two controls, the first of which we shall
assume is named txtSize and the second of which is named txtCode. We
shall assume that the user has typed an "M" into txtSize and "DG" into
txtCode.

You want your score control's controlsource to be:

=DLookUp(me.txtSize,"Condition","[code] = '" & txtCode & "')

Try that.

mike

[nzyui]

Mike Preston wrote:

>>>>Trying to lookup a field from a table called condition this is a separate
>>>>table not linked to anything: setup as shown

[quoted text clipped - 27 lines]

example to give me a number from the condition table which transposes into a
field call score

Have you tried putting the Dlookup function in the controlsource of
your score control? Just as you typed it above:

=DLookUp("[M]","Condition","[code] = 'DG'")

Oh, wait.

I think I get it.

Let's assume that you have two controls, the first of which we shall
assume is named txtSize and the second of which is named txtCode. We
shall assume that the user has typed an "M" into txtSize and "DG" into
txtCode.

You want your score control's controlsource to be:

=DLookUp(me.txtSize,"Condition","[code] = '" & txtCode & "')

Try that.

mike

Yes Mike you are on the right track that code did not work i have 2 controls
1 is Condition code the other is Severity these 2 codes make up the dlookup
to put in score ,the return is a number, condition code is txt and Severity
is txt Condition code = DG Severity = M ps i got a #name? error
cheers

[Mike Preston]

On Tue, 20 Dec 2005 01:32:34 GMT, "nzyui" <u16807@uwe> wrote:

Mike Preston wrote:

>>>>>Trying to lookup a field from a table called condition this is a separate
>>>>>table not linked to anything: setup as shown

[quoted text clipped - 27 lines]

example to give me a number from the condition table which transposes into a
field call score

Have you tried putting the Dlookup function in the controlsource of
your score control? Just as you typed it above:

=DLookUp("[M]","Condition","[code] = 'DG'")

Oh, wait.

I think I get it.

Let's assume that you have two controls, the first of which we shall
assume is named txtSize and the second of which is named txtCode. We
shall assume that the user has typed an "M" into txtSize and "DG" into
txtCode.

You want your score control's controlsource to be:

=DLookUp(me.txtSize,"Condition","[code] = '" & txtCode & "')

Try that.

mike

Yes Mike you are on the right track that code did not work i have 2 controls
1 is Condition code the other is Severity these 2 codes make up the dlookup
to put in score ,the return is a number, condition code is txt and Severity
is txt Condition code = DG Severity = M ps i got a #name? error

Show me exactly what you typed and tell me where you put it.

mike

[nzyui]

Mike Preston wrote:

>>>>>>Trying to lookup a field from a table called condition this is a separate
>>>>>>table not linked to anything: setup as shown

[quoted text clipped - 27 lines]

to put in score ,the return is a number, condition code is txt and Severity
is txt Condition code = DG Severity = M ps i got a #name? error

Show me exactly what you typed and tell me where you put it.

mike

=DLookUp(me.Severity,"Condition","[code] = '" & Condition code & "')
this is what i have in the control source of the score control
Steve

[Mike Preston]

On Tue, 20 Dec 2005 02:27:10 GMT, "nzyui" <u16807@uwe> wrote:

Mike Preston wrote:

>>>>>>>Trying to lookup a field from a table called condition this is a separate
>>>>>>>table not linked to anything: setup as shown

[quoted text clipped - 27 lines]

to put in score ,the return is a number, condition code is txt and Severity
is txt Condition code = DG Severity = M ps i got a #name? error

Show me exactly what you typed and tell me where you put it.

mike

=DLookUp(me.Severity,"Condition","[code] = '" & Condition code & "')
this is what i have in the control source of the score control

=DLookUp(me.Severity,"Condition","[code] = '" & me.[Condition code] &
'")

Note that your single and double quotes are reversed, too.

mike

[Wayne Morgan]

"Me." won't work in the Control Source of a control, just in VBA. There is
also a double quote missing at the end.

Modifying the line you have:
=DLookUp([Severity],"Condition","[code] = '" & [Condition code] & "'")

This assumes that the severity is in a control called [Severity] and that
the condition code is in a control called [Condition Code] (without the
brackets in the control name, but they are needed here). Condition would be
the name of the table and Code would be the name of the filtering field in
the Condition table. You will return a result from the field (S, M, or L)
named by the control Severity.

If this gives an error, please post what that error is.

--
Wayne Morgan
MS Access MVP
"Mike Preston" <mb******@pacbell.net.invalid> wrote in message
news:43*****************@news.INDIVIDUAL.NET...

On Tue, 20 Dec 2005 02:27:10 GMT, "nzyui" <u16807@uwe> wrote:

Mike Preston wrote:

>>>>>>>>Trying to lookup a field from a table called condition this is a
>>>>>>>>separate
>>>>>>>>table not linked to anything: setup as shown
[quoted text clipped - 27 lines]
to put in score ,the return is a number, condition code is txt and
Severity
is txt Condition code = DG Severity = M ps i got a #name?
error

Show me exactly what you typed and tell me where you put it.

mike

=DLookUp(me.Severity,"Condition","[code] = '" & Condition code & "')
this is what i have in the control source of the score control

=DLookUp(me.Severity,"Condition","[code] = '" & me.[Condition code] &
'")

Note that your single and double quotes are reversed, too.

mike

[nzyui]

Wayne Morgan wrote:

"Me." won't work in the Control Source of a control, just in VBA. There is
also a double quote missing at the end.

Modifying the line you have:
=DLookUp([Severity],"Condition","[code] = '" & [Condition code] & "'")

This assumes that the severity is in a control called [Severity] and that
the condition code is in a control called [Condition Code] (without the
brackets in the control name, but they are needed here). Condition would be
the name of the table and Code would be the name of the filtering field in
the Condition table. You will return a result from the field (S, M, or L)
named by the control Severity.
Wayne works perfect thanks very much
thanks to mike for his help too
cheers guysIf this gives an error, please post what that error is.

>>>>>>>>>Trying to lookup a field from a table called condition this is a
>>>>>>>>>separate

[quoted text clipped - 17 lines]

mike