Queries using calculated fields on a form.

[Nosdron]

I would like to use a calculated unbound field from a form on a query. Firstly is it possible and secondly can anyone help me with the syntax.

Angela

 

[SeeJane]

Hi,

Yes, you can do this.

1. Create your calculated field to do whatever you need
2. Create a new query, add the fields you want
3. For the field you where want the calc field as criteria, put the following exprssion in the criteria line:
[Forms]![YourFormName]![CalcFieldName]

- As a side note, if you want to refer to a calc field on a subform, use this variation on the language:
[Forms]![MainFormName]![SubfomName].[Form].[CalcFieldName]

For the query to run, the form needs to be open (if not, Access will give you a dialog asking you to manually enter criteria).

Good luck,
CJ

 

[thornmastr]

Angela

If it has a name you can use it, assuming of course there is some type of match on a table somewhere. Assuming the name of this unbound calculated field is unkfld and when computed it points to the primary key(pk) of some other table; then all you have to do is build the sql statement. Assuming it is a simple select query, something like this will do the trick for you.

Dim strsql as string
Dim rs as recordset

Strsql = “select * from some_table where pk = “ & me.unkfld.value
Set rs = currentdb.openrecordset (strsql, dbopendynaset)

You’ve done it.

This assumes that unkfld is numeric. If it is text, your sql string will incorporate double quotes like this.

Strsql = “select * from some_table where pk = ‘” _
& me.unkfld.value & “’”
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Nosdron]

thornmastr,

What do you mean when it is computed it points to the primary key of some other table ?

 

[thornmastr]

This was an example based on your criteria.
What is it you do not understand or disagree with? Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Nosdron]

I have a unbound calculated field on a form which is not linked to a table. So how do I link this to a table when you discuss matching it to a table and when computed it points to a primary key of some other table.

How do I set this up ?

 

[thornmastr]

As I said, it was an example. Usually you use an unbound control to compute somevalue that points to something. You don't have to. You can use it to do whatever you want to do with it. Thew only point I attempted to make was to answere your question, ie, can you use a unbound variable on a form in a query and the answe is yes. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com