referring to a particular cell in query..... basic =(

[liuec]

Hi, I've got a basic question...

On a report i'm building i want to refer to a particular cell in a query,
Let's say the query name is qry1 and I want to refer to the 3rd column and 2nd row. What should the code look like?
I'm also coding the same stuff in VBA, i found this 'Dlookup' function but not sure how to use it...
Can anyone help?

This is what I'm trying and failing...
=[Queries]![qry1]![Column3]

If i use DLookup, what should the code look like?
Dim Value1 as String
Value1 = DLookup([Column3], [Queries]![qry1], cell.A1)?

thanks so much!!

 

[jdraw]

You are thinking Excel. For Access and RDBMS generally, there are not cells.
There are tables which contain fields(columns), and records(rows).

For DLookup help see
http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[plog]

To expand on what jdraw said--there are no cells--there are rows of data--and probably not in the order you expect them to be. This means you have to explicitly identify the row of data you want, then get the value of the field in that row you want.

If you truly do need a Dlookup (which I have a suspicion you may not) it should look like this:

=DLookup("[FieldName]", "DataSource", "CriteriaToIdentifyOneRow")

DataSource is the name of the table or query you want to look into.
FieldName is the name of the field in DataSource whose value you want.
Criteria.... is a condition identifies just the row of data you want.

If you wanted the row whose unique id was 7 the criteria would be:

"[IdField]=7"

 

[liuec]

You are thinking Excel. For Access and RDBMS generally, there are not cells.
There are tables which contain fields(columns), and records(rows).

For DLookup help see
http://www.techonthenet.com/access/functions/domain/dlookup.php

Thanks for the link, what would I use as "Criteria = n" in this case? If I want the 1st row, 2nd column cell value?
The query name is [qry1]..
thank you


Edit: Just saw the 2nd reply, I'll give it a try and post back.
Thanks!!

 

[liuec]

To expand on what jdraw said--there are no cells--there are rows of data--and probably not in the order you expect them to be. This means you have to explicitly identify the row of data you want, then get the value of the field in that row you want.

If you truly do need a Dlookup (which I have a suspicion you may not) it should look like this:

=DLookup("[FieldName]", "DataSource", "CriteriaToIdentifyOneRow")

DataSource is the name of the table or query you want to look into.
FieldName is the name of the field in DataSource whose value you want.
Criteria.... is a condition identifies just the row of data you want.

If you wanted the row whose unique id was 7 the criteria would be:

"[IdField]=7"

Thanks for the detailed explanation, I'm probably not quite familiar enough with access.... After multiple failed attempts, i'm posting the a screen shot of the query here.
This is what i'm trying and still failed....
DLookup("[Column1]", "[Queries]![qry1]", "[Column0]=10")

What would the code look like in this case?
Thanks,

 

[plog]

I think its the second paramater that is failing. Try this:

=DLookup("[Column1]", "qry1", "[Column0]=10")


Is this going on a form or report?

 

[liuec]

To expand on what jdraw said--there are no cells--there are rows of data--and probably not in the order you expect them to be. This means you have to explicitly identify the row of data you want, then get the value of the field in that row you want.

If you truly do need a Dlookup (which I have a suspicion you may not) it should look like this:

=DLookup("[FieldName]", "DataSource", "CriteriaToIdentifyOneRow")

DataSource is the name of the table or query you want to look into.
FieldName is the name of the field in DataSource whose value you want.
Criteria.... is a condition identifies just the row of data you want.

If you wanted the row whose unique id was 7 the criteria would be:

"[IdField]=7"

Coming back to the basics, what if I just want pass on the date value from Column1 on to a report, how would I do that?

 

[plog]

Then you would set the datasource for that report as your query, no Dlookups needed.

I would suggest you use the wizard to build a report using your query as its source. If that doesn't get you exactly what you want it should provide some insight into how the process works.

 

[liuec]

I think its the second paramater that is failing. Try this:

=DLookup("[Column1]", "qry1", "[Column0]=10")


Is this going on a form or report?

Got an error of '2001' and "You canceled the previous operation" message... =(

 

[plog]

Where are you using this Dlookup?

 

[liuec]

Where are you using this Dlookup?

Oh ok, for now i'm testing it in VBA.
so my code looks like this:

Dim Strg1 as String

Strg1 = DLookup("[Column1]", "qry1", "[Column0]=10")

and I check variable Strg1's value so I know if this Dlookup is working.
Ultimately, I'm trying to display this date in the query on a report...
Thanks for you time =)

 

[plog]

Try setting it to a control directly on the report. I'm not entirely certain why you are getting that error.

 

[liuec]

worked it out using another query and a subreport...
Thanks anyways!

Try setting it to a control directly on the report. I'm not entirely certain why you are getting that error.