query problem

R

richaluft

trying to construct a query field as follows.
Expr1:DLookup("[Value}","tbl1"," Value1= ClientNum And Value2 = ID"),
where values ClienNum and ID are fields of the same query.
When I try to run query, I get err msg of "MSAccess can't find field
ID".
Everything is spelled properly.
Anyone have any idea why this is not working? I'm having no problem
with other Lookup functions of 'single' parameters in the same query.
Richard
 
J

Jeanette Cunningham

Richard,
there is a curly bracket after Value, replace it with a straight bracket.
Value is a reserved word in Access, if you have it as a field name, I
suggest you change it.

Here is an example using 2 criteria, if both ClientNum and ID are numbers,
you could use this way to code the criteria expression.

DCount("*", "[Student Data Entry]", "[Age] = " & [Age] & " AND [1Q] =" &
[1Q])

This example build the string [Age] = 14 AND [1Q] = 1


Jeanette Cunningham
 
R

richaluft

Richard,
there is a curly bracket after Value, replace it with a straight bracket.
Value is a reserved word in Access, if you have it as a field name, I
suggest you change it.

Here is an example using 2 criteria, if both ClientNum and ID are numbers,
you could use this way to code the criteria expression.

DCount("*", "[Student Data Entry]", "[Age] = " & [Age] & " AND [1Q] =" &
[1Q])

This example build the string [Age] = 14 AND [1Q] = 1

Jeanette Cunningham


trying to construct a query field as follows.
Expr1:DLookup("[Value}","tbl1"," Value1= ClientNum And Value2 = ID"),
where values ClienNum and ID are fields of the same query.
When I try to run query, I get err msg of "MSAccess can't find field
ID".
Everything is spelled properly.
Anyone have any idea why this is not working? I'm having no problem
with other Lookup functions of 'single' parameters in the same query.
Richard

Jeanette:
Thanks for reply, but I had already tried that & it didn't work. It is
giving me a value, but it's the incorrect value. (The curly bracket
wqas a typo, and the field name is not really 'value')
Any other suggestions as to what the problem could be.
Note that all other fields in the query (such as IIf fields) parse
with the normal "[ID] = [ID]" structure, not the " & [field]"
structure.
Richard
 
J

Jeanette Cunningham

Sorry, your question was not as I thought when I first read it.
I wouldn't recommend using any of the Domain functions in a query, they make
it slow to run.
You are trying to retrieve a value from the query in an unusual way, I'm not
sure why you would want to do it this way.

If you want the value to use on your form, you would use a DLookup similar
to the one I posted in my last reply and you could set an unbound text box's
control source to the = DCount("*", "[Student Data Entry]", "[Age] = " &
[Age] & " AND [1Q] =" &
[1Q]) (replace with your own control and field names).

If you are trying to use query criteria, usually you would do it like this:
In the design view of the query, in the row criteria for Value1 you would
type ClientNum and for Value2 you would type ID. when you switch to
datasheet view you would see the value you are trying to find, providing
that you have that field in the query.

If you are trying to use the query to use values from controls on an open
form you would syntax like this:
In the criteria row for ClientNum you would put:
Forms!TheFormName!TheControlName and similar thing for ID
Replace the form and controls names with your own names.

Jeanette Cunningham

Richard,
there is a curly bracket after Value, replace it with a straight bracket.
Value is a reserved word in Access, if you have it as a field name, I
suggest you change it.

Here is an example using 2 criteria, if both ClientNum and ID are
numbers,
you could use this way to code the criteria expression.

DCount("*", "[Student Data Entry]", "[Age] = " & [Age] & " AND [1Q] =" &
[1Q])

This example build the string [Age] = 14 AND [1Q] = 1

Jeanette Cunningham


trying to construct a query field as follows.
Expr1:DLookup("[Value}","tbl1"," Value1= ClientNum And Value2 = ID"),
where values ClienNum and ID are fields of the same query.
When I try to run query, I get err msg of "MSAccess can't find field
ID".
Everything is spelled properly.
Anyone have any idea why this is not working? I'm having no problem
with other Lookup functions of 'single' parameters in the same query.
Richard

Jeanette:
Thanks for reply, but I had already tried that & it didn't work. It is
giving me a value, but it's the incorrect value. (The curly bracket
wqas a typo, and the field name is not really 'value')
Any other suggestions as to what the problem could be.
Note that all other fields in the query (such as IIf fields) parse
with the normal "[ID] = [ID]" structure, not the " & [field]"
structure.
Richard
 
R

richaluft

Sorry, your question was not as I thought when I first read it.
I wouldn't recommend using any of the Domain functions in a query, they make
it slow to run.
You are trying to retrieve a value from the query in an unusual way, I'm not
sure why you would want to do it this way.

If you want the value to use on your form, you would use a DLookup similar
to the one I posted in my last reply and you could set an unbound text box's
control source to the = DCount("*", "[Student Data Entry]", "[Age] = " &
[Age] & " AND [1Q] =" &
[1Q]) (replace with your own control and field names).

If you are trying to use query criteria, usually you would do it like this:
In the design view of the query, in the row criteria for Value1 you would
type ClientNum and for Value2 you would type ID. when you switch to
datasheet view you would see the value you are trying to find, providing
that you have that field in the query.

If you are trying to use the query to use values from controls on an open
form you would syntax like this:
In the criteria row for ClientNum you would put:
Forms!TheFormName!TheControlName and similar thing for ID
Replace the form and controls names with your own names.

Jeanette Cunningham


Richard,
there is a curly bracket after Value, replace it with a straight bracket.
Value is a reserved word in Access, if you have it as a field name, I
suggest you change it.
Here is an example using 2 criteria, if both ClientNum and ID are
numbers,
you could use this way to code the criteria expression.
DCount("*", "[Student Data Entry]", "[Age] = " & [Age] & " AND [1Q] =" &
[1Q])
This example build the string [Age] = 14 AND [1Q] = 1
Jeanette Cunningham

trying to construct a query field as follows.
Expr1:DLookup("[Value}","tbl1"," Value1= ClientNum And Value2 = ID"),
where values ClienNum and ID are fields of the same query.
When I try to run query, I get err msg of "MSAccess can't find field
ID".
Everything is spelled properly.
Anyone have any idea why this is not working? I'm having no problem
with other Lookup functions of 'single' parameters in the same query.
Richard
Jeanette:
Thanks for reply, but I had already tried that & it didn't work. It is
giving me a value, but it's the incorrect value. (The curly bracket
wqas a typo, and the field name is not really 'value')
Any other suggestions as to what the problem could be.
Note that all other fields in the query (such as IIf fields) parse
with the normal "[ID] = [ID]" structure, not the " & [field]"
structure.
Richard

Actually I was just trying to use the query builder for a code sql for
what was to be a parameter query. These values were to be obtained as
parameters from a table, but I was trying to simplify the sql parsing.
Thanks again
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top