calculated field question

[Guest]

I have a query which I pass a criteria to: "contractID=" & Me.ContractID

How can I access this value in a calculated field within the query without
referencing it as a field on the form? For clarity, I want to create a
calculated field equal to 1 or 0 depending on whether it matched the
ContractID that was passed in as the Criteria expression...??

 

[Jeff Boyce]

Why do you believe you need to do it that way?

One possibility would be to convert the query to its SQL statement and pass
the criteria in via the (dynamic) SQL statement, in code.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tom Ellison]

Dear chem:

You could use a calculated column something like this:

IIf(contactID = [Forms]![FormName]![ContractID], 1, 0)

In the above, replace FormName with the actual name of your form.

I understand you want to reference the value of the form's recordsource
currently selected row. Is that correct?

Tom Ellison

 

[Guest]

I am trying to come up with a good way to list all contractIDs but order them
so that the contractID passed in the criteria displays at the top of the list
- as this is most likely the ones they want - but not necessarily

Thx

 

[Guest]

Tom,

Yes that would work if my query was used by only one specific form (ie
referring to a fieldname) but I use this query in several places that don't
pass a criteria or have that field on a form........is there a way to do an
IIF to check on whether the field exists on the form first?

Someting like
IIF(Not IsNull([Forms]![FormName]![ContractID], IIf(contactID =
[Forms]![FormName]![ContractID], 1, 0),0)

Dear chem:

You could use a calculated column something like this:

IIf(contactID = [Forms]![FormName]![ContractID], 1, 0)

In the above, replace FormName with the actual name of your form.

I understand you want to reference the value of the form's recordsource
currently selected row. Is that correct?

Tom Ellison

I have a query which I pass a criteria to: "contractID=" & Me.ContractID

How can I access this value in a calculated field within the query without
referencing it as a field on the form? For clarity, I want to create a
calculated field equal to 1 or 0 depending on whether it matched the
ContractID that was passed in as the Criteria expression...??

 

[Tom Ellison]

Dear Chem:

I expect you will need a separate version of the query for each form. One
problem with your suggestion is that there may be 2 forms open that use the
query. If the query used the wrong one, you'd have some very funny results.

Tom Ellison

Tom,

Yes that would work if my query was used by only one specific form (ie
referring to a fieldname) but I use this query in several places that
don't
pass a criteria or have that field on a form........is there a way to do
an
IIF to check on whether the field exists on the form first?

Someting like
IIF(Not IsNull([Forms]![FormName]![ContractID], IIf(contactID =
[Forms]![FormName]![ContractID], 1, 0),0)

Dear chem:

You could use a calculated column something like this:

IIf(contactID = [Forms]![FormName]![ContractID], 1, 0)

In the above, replace FormName with the actual name of your form.

I understand you want to reference the value of the form's recordsource
currently selected row. Is that correct?

Tom Ellison

I have a query which I pass a criteria to: "contractID=" &
Me.ContractID

How can I access this value in a calculated field within the query
without
referencing it as a field on the form? For clarity, I want to create
a
calculated field equal to 1 or 0 depending on whether it matched the
ContractID that was passed in as the Criteria expression...??