Return a value from a field 1

[mdaniele]

Can someone help me with an expression, for a query criteria, that will return the value of a field called AREA from the active record, (not the active field) in a table called ESTIMATE.

I don't know the correct syntax to specify the value of a field in the current record I would be working in but the idea is something like:

get (active record) AREA (value) from ESTIMATE

I think I could get an SQL statement to work if it's not possible to put such a statement in a query's criteria


MoD

...and when Satan lividly demanded to know why the old man did not exhibit any fear whatsoever of him, the 82 year old man calmly replied "why hell, you ain't so bad, I've been married to your sister for over 50 years"

 

[VBslammer]

"SELECT [AREA] FROM ESTIMATE WHERE [PK]=" & Forms![FormName]![PK]

Replace [FormName] with the name of the form and replace [PK] with the name of the primary key field in table ESTIMATE.

VBSlammer

Unemployed in Houston, Texas

 

[mdaniele]

I'm using the query to retrieve records that need to be updated, the data is never used in a form, it's presented in a report. All I need to do is: get the row, get the field, and use the value from the field in the query criteria. Would I first have to bookmark the record?

...and when Satan lividly demanded to know why the old man did not exhibit any fear whatsoever of him, the 82 year old man calmly replied "why hell, you ain't so bad, I've been married to your sister for over 50 years"

 

[VBslammer]

I'm not following you. Where are you getting the "active record" if you're not using a form?

Are you manipulating a recordset through DAO or ADO using code only?

VBSlammer

Unemployed in Houston, Texas

 

[mdaniele]

To clarify -

I work with tables of raw data (these are project estimates)that are later used in reports. As new data comes in or is developed I'm required to update the old data. Currently the easiest way for me to locate a old record is to query the data using a "like" statement in the DESCRIPTION field in a query of the data. Again, I run the query against the datatable. I may get 200 records based on a partial description, the next easiest way for me to filter down by an AREA.
By active record - I meant the row of data I'm modifying in the table, what my goal is to simply run my query that searches the description (already working) and add another criteria in the AREA field of the query that will "read" which row I'm on, get the value from the AREA field, and automatically use that value into the query criteria for AREA.
Although I don't see this as a difficult question I've posted this same question in several other posts and it seems I haven't been able to communicate my need clearly.

Here it is in a nutshell:
I have a DATATable w/5 fields: ID, AREA, DESC, QNTY, UNIT.
have a SELECTquery w/5 fields: ID, AREA, DESC, QNTY, UNIT.

In the DESC criteria I have: "LIKE "*" & [ ] & "*".


In the AREA criteria I want:
(get and use the value of AREA from the rownumber I'm sitting on in the table)

?? am I just way off on this ?? I can't find any help reference to it, am hoping you might have the answer.

Thanks for any further help you can offer.



...and when Satan lividly demanded to know why the old man did not exhibit any fear whatsoever of him, the 82 year old man calmly replied "why hell, you ain't so bad, I've been married to your sister for over 50 years"

 

[VBslammer]

You really have no way of referencing a table like that.

You need to create a form and set its recordsource property to the ESTIMATE table, and show it in datasheet view.

Once you do that, you can reference the active field in the query's criteria section. For example, say you name the form "frmEstimates":

  Forms![frmEstimates]![AREA]

That's all there is to it!

VBSlammer

Unemployed in Houston, Texas - but accepting stars

as payment for helpful posts