Using calculated query field in Recordset

[bgcpen]

Is it possible to reference a calculated field in a recordset? Here is the
code i have.
I have a query that searches the email body field and gets a string from
that field. So the only true field in the query is [body]. However, the text
i would like to use in the code is a calculated field. Is there any way to
set a variable to equal a calculated query field? (ie. want to set userName
to equal calculated field string.)

Function RecordSetQuery()
Const strqryName = "servicemom_alerts"
Dim userAlias As String
Dim userName As String
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb() ' Open pointer to current database
Set rs = db.OpenRecordset(strqryName) ' Open recordset on saved query
Do While Not rs.EOF
'Debug.Print (rs![Expr3])
userName = (rs![Expr3])
Call sbSendMessage(userAlias, userName)
'Debug.Print userAlias
rs.MoveNext
Loop
rs.Close
db.Close
End Function

 

[Tim Ferguson]

Is it possible to reference a calculated field in a recordset? Here is
the code i have.

Yes: you base the recordset on a query that does the calculation for you:

jetSQL = "SELECT Body, " & _
"MID(Body, 36, 34) AS SliceOfBody " & _
"FROM servicemom_alerts " & _
"ORDER BY Body ASC;"

Set rst = db.OpenRecordset( _
jetSQL, dbOpenSnapshot, dbForwardOnly)

' etc



There is a limit to the complexity you can reasonably code into a single
SQL expression, but most things are doable with a bit of patience. If you
need to use Instr() and so on, that is fine. If you need to dive into
regular expressions and the like, then you are probably into SQL Server
and stored-procedures territory.

Hope that helps


Tim F

 

[bgcpen]

Hi Tim,

thanks for the response. I tried doing that before, but i think the query
i'm running is too complex.

SELECT servicemom_alerts.Body, LTrim(Mid(Left([Body],InStr([Body],"logged on
to")-2),InStr([Body],"User ENTNBU\")+12)) AS Expr1,
LTrim(Mid(Left([Body],InStr([Body],"logged on to")-2),InStr([Body],"User
ENTERPRISE\")+16)) AS Expr2,
IIf(Len([Expr1])>5,[Expr2],[Expr1]) AS Expr3
FROM servicemom_alerts
WHERE (((servicemom_alerts.Body) Like "*" & [Enter Name] & "*"))

The problem i run into when i try to put that in is the code thinks i'm
trying to close the quotes on the SELECT statement when it reaches the first
"logged on to" instance. Any ideas on how to get this to run? it might be too
complex to run as you stated below.

Thanks.

 

[David C. Holley]

When you open the query, all of the fields are available in the
recordSet calculated or not, HOWEVER if you're STRICKTLY using the
calculated value in a query that is ONLY used in code, its probably more
efficient to remove the calculated values from the query and put the
calculations in the code.

 

[bgcpen]

i am using the calculated field in the report as well.
the problem is that after i open the recordset and try to use the expression
output of the field, (Expr3 in the code posted earlier) i get a run-time
error 3265: Item not found in this collection.

The actual contents of the expression/calculated field is a string found
after using a nesting of mid and instr functions.

am i doing something else wrong?

When you open the query, all of the fields are available in the
recordSet calculated or not, HOWEVER if you're STRICKTLY using the
calculated value in a query that is ONLY used in code, its probably more
efficient to remove the calculated values from the query and put the
calculations in the code.

Is it possible to reference a calculated field in a recordset? Here is the
code i have.
I have a query that searches the email body field and gets a string from
that field. So the only true field in the query is [body]. However, the text
i would like to use in the code is a calculated field. Is there any way to
set a variable to equal a calculated query field? (ie. want to set userName
to equal calculated field string.)

Function RecordSetQuery()
Const strqryName = "servicemom_alerts"
Dim userAlias As String
Dim userName As String
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb() ' Open pointer to current database
Set rs = db.OpenRecordset(strqryName) ' Open recordset on saved query
Do While Not rs.EOF
'Debug.Print (rs![Expr3])
userName = (rs![Expr3])
Call sbSendMessage(userAlias, userName)
'Debug.Print userAlias
rs.MoveNext
Loop
rs.Close
db.Close
End Function

 

[Tim Ferguson]

SELECT servicemom_alerts.Body,
LTrim(Mid(Left([Body],InStr([Body],"logged on
to")-2),InStr([Body],"User ENTNBU\")+12)) AS Expr1,
LTrim(Mid(Left([Body],InStr([Body],"logged on
to")-2),InStr([Body],"User ENTERPRISE\")+16)) AS Expr2,
IIf(Len([Expr1])>5,[Expr2],[Expr1]) AS Expr3
FROM servicemom_alerts
WHERE (((servicemom_alerts.Body) Like "*" & [Enter Name] & "*"))

A bit of cut-and-pasting gets rid of the unwanted(?) columns:

PARAMETERS [Enter Name] STRING;

SELECT
Body,
IIf(Len(LTrim(Mid(Left([Body],InStr([Body],"logged on to")-2),
InStr([Body],"User ENTNBU\")+12)))>5,
[LTrim(Mid(Left([Body],InStr([Body],"logged on to")-2),
InStr([Body],"User ENTERPRISE\")+16))],
[LTrim(Mid(Left([Body],InStr([Body],"logged on to")-2),
InStr([Body],"User ENTNBU\")+12))]) AS Expr3

FROM servicemom_alerts

WHERE Body LIKE "*" & [Enter Name] & "*"

It's certainly a pain to debug and I have not tried to reverse-engineer
it(!), but I cannot easily see why it should be "too complex" for the
database engine. Needs really good comment text and documenting for the
sake of your successors!!

The problem i run into when i try to put that in is the code thinks
i'm trying to close the quotes on the SELECT statement when it reaches
the first "logged on to" instance. Any ideas on how to get this to
run? it might be too complex to run as you stated below.

Two thoughts:
(1) if you can set it up as a querydef, you can simply paste the SQL code
into the SQL window and forget about the quote marks.

(2) if you have to create it in VBA strings, then you need to double up
the internal quote marks vis:

jetSQL = jetSQL & "WHERE Body LIKE ""*"" & [Enter Name] & ""*"";"

and that will be perfectly legal. Oh, and don't forget to do a

MsgBox jetSQL

until you _know_ that you have it working right.

Hope that helps


Tim F