extract value from a query field

  • Thread starter Thread starter helloImJean
  • Start date Start date
H

helloImJean

Hi,

I'm new to access and VBA and here's what I'm trying to do:

I have created a query with columns A, B and C. C should be showing the
result of a comparison between a field value on the same row in column A, and
all other field values on the other rows in column B. I want to use a
function as the "expression" for column C, but don't know how to get the
field values into the function. tried GoCmd, but didn't work...

I'm using Access 2003. Thank you very much for the help!

Best,
Jean
 
If you use another row, you need another reference to the same table, like:

SELECT whatever

FROM table AS a , table AS b

WHERE b.someField = a.someOtherField


note that the condition, here in the WHERE clause, should be able to
identify which (other) row, "b", you need, based on the assumption that the
actual row is represented by "a".



SELECT a.quantity- b.quantity
FROM table AS a INNER JOIN table AS b
ON b.someField = a.someOtherField


as example, subtract the quantity field from these two records. I also have
put the condition in the FROM clause, rather than in the WHERE clause.



Vanderghast, Access MVP
 
Hi, Jean.

Generally, the way to handle this is to pass some values to the function.
In my example below, I've passed fnComparison the value of [FieldC] and the
[ID] value of that record. I need to pass it the value from the field, in
order to do the comparison, and I need to pass it the ID of the current
record, so it will not perform the comparison against that record.

Comparison:fnComparison([FieldC], [ID])

Public Function fnComparison(ValueToCompare as variant,
CurrentRecordID as long) as string

dim strSQL as String
dim rs as DAO.Recordset

strSQL = "SELECT [FieldB] FROM tblYourTable " _
& "WHERE [ID] <> " & CurrentRecordID
set rs = currentdb.openrecordset(strsql,,dbfailonerror)

While not rs.eof
'do some comparison thing inside the loop

rs.movenext
Wend
rs.close
set rs = nothing

fnComparison = "Some value"
End Function

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Hi Dale,

It worked! Thank you very much!

Jean


Dale Fye said:
Hi, Jean.

Generally, the way to handle this is to pass some values to the function.
In my example below, I've passed fnComparison the value of [FieldC] and the
[ID] value of that record. I need to pass it the value from the field, in
order to do the comparison, and I need to pass it the ID of the current
record, so it will not perform the comparison against that record.

Comparison:fnComparison([FieldC], [ID])

Public Function fnComparison(ValueToCompare as variant,
CurrentRecordID as long) as string

dim strSQL as String
dim rs as DAO.Recordset

strSQL = "SELECT [FieldB] FROM tblYourTable " _
& "WHERE [ID] <> " & CurrentRecordID
set rs = currentdb.openrecordset(strsql,,dbfailonerror)

While not rs.eof
'do some comparison thing inside the loop

rs.movenext
Wend
rs.close
set rs = nothing

fnComparison = "Some value"
End Function

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



helloImJean said:
Hi,

I'm new to access and VBA and here's what I'm trying to do:

I have created a query with columns A, B and C. C should be showing the
result of a comparison between a field value on the same row in column A, and
all other field values on the other rows in column B. I want to use a
function as the "expression" for column C, but don't know how to get the
field values into the function. tried GoCmd, but didn't work...

I'm using Access 2003. Thank you very much for the help!

Best,
Jean
Click to expand...
Click to expand...
 
Back
Top