extract value from a query field

[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

 

[Michel Walsh]

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

 

[Dale Fye]

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]

Hi Dale,

It worked! Thank you very much!

Jean

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,

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