using recordset findfirst

[new2access123 via AccessMonster.com]

I have created a recordset that is a subset of records from another table
based on age and gender. Three of the columns in the recordset (among others)
are a Score, HighLimit and LowLimit. I need to determin if sngCurlUpCount:
- is greater than the largest value in the HighLimit
- or lower thant the lowest value in the HighLimit .

'This returns my recordset of 4 rows
Dim strSQL As String
'Query the look up table rows for the passed age and gender.
strSQL = "SELECT * " & _
"FROM MECurlUpTable " & _
"WHERE AgeLowLimit < " & [sngAge] & _
" And AgeHighLimit > " & [sngAge] & _
" And Male = " & [bytMale] & ";"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

When i execute this code i see that DMax(rs("HighLimit") and DMin(rs
("HighLimit") return the same value. But I know the high value in the
HighLimit column is 40 and teh low value is 13.
Debug.Print sngCurlUpCount
Debug.Print DMax(rs("HighLimit"), "MECurlUpTable")
Debug.Print DMin(rs("HighLimit"), "MECurlUpTable")

This is the code i wanted to use to return the correct score.
Select Case sngCurlUpCount
Case Is >= DMax(rs("HighLimit"), "MECurlUpTable")
varITAScoreCUC = 4
Case Is <= DMin(rs("HighLimit"), "MECurlUpTable")
varITAScoreCUC = 1
End Select

Should I be using something other than the domain functions or have I messed
up some where else?

Thanks for the help

 

[new2access123 via AccessMonster.com]

Sorry the subject should be Using DMax() and DMin() with a Recordset

I have created a recordset that is a subset of records from another table
based on age and gender. Three of the columns in the recordset (among others)
are a Score, HighLimit and LowLimit. I need to determin if sngCurlUpCount:
- is greater than the largest value in the HighLimit
- or lower thant the lowest value in the HighLimit .

'This returns my recordset of 4 rows
Dim strSQL As String
'Query the look up table rows for the passed age and gender.
strSQL = "SELECT * " & _
"FROM MECurlUpTable " & _
"WHERE AgeLowLimit < " & [sngAge] & _
" And AgeHighLimit > " & [sngAge] & _
" And Male = " & [bytMale] & ";"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

When i execute this code i see that DMax(rs("HighLimit") and DMin(rs
("HighLimit") return the same value. But I know the high value in the
HighLimit column is 40 and teh low value is 13.
Debug.Print sngCurlUpCount
Debug.Print DMax(rs("HighLimit"), "MECurlUpTable")
Debug.Print DMin(rs("HighLimit"), "MECurlUpTable")

This is the code i wanted to use to return the correct score.
Select Case sngCurlUpCount
Case Is >= DMax(rs("HighLimit"), "MECurlUpTable")
varITAScoreCUC = 4
Case Is <= DMin(rs("HighLimit"), "MECurlUpTable")
varITAScoreCUC = 1
End Select

Should I be using something other than the domain functions or have I messed
up some where else?

Thanks for the help

 

[Tom van Stiphout]

On Sun, 14 Feb 2010 23:54:18 GMT, "new2access123 via

You did indeed mess something up. Check DMax in the help file. What is
the first argument? It should be a field name, not some value. This
would work better:
DMax("AgeHighLimit", "MECUrlUpTable")

-Tom.
Microsoft Access MVP

Sorry the subject should be Using DMax() and DMin() with a Recordset

I have created a recordset that is a subset of records from another table
based on age and gender. Three of the columns in the recordset (among others)
are a Score, HighLimit and LowLimit. I need to determin if sngCurlUpCount:
- is greater than the largest value in the HighLimit
- or lower thant the lowest value in the HighLimit .

'This returns my recordset of 4 rows
Dim strSQL As String
'Query the look up table rows for the passed age and gender.
strSQL = "SELECT * " & _
"FROM MECurlUpTable " & _
"WHERE AgeLowLimit < " & [sngAge] & _
" And AgeHighLimit > " & [sngAge] & _
" And Male = " & [bytMale] & ";"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

When i execute this code i see that DMax(rs("HighLimit") and DMin(rs
("HighLimit") return the same value. But I know the high value in the
HighLimit column is 40 and teh low value is 13.
Debug.Print sngCurlUpCount
Debug.Print DMax(rs("HighLimit"), "MECurlUpTable")
Debug.Print DMin(rs("HighLimit"), "MECurlUpTable")

This is the code i wanted to use to return the correct score.
Select Case sngCurlUpCount
Case Is >= DMax(rs("HighLimit"), "MECurlUpTable")
varITAScoreCUC = 4
Case Is <= DMin(rs("HighLimit"), "MECurlUpTable")
varITAScoreCUC = 1
End Select

Should I be using something other than the domain functions or have I messed
up some where else?

Thanks for the help

 

[new2access123 via AccessMonster.com]

AgeLowLimit and AgeHighLimit are fields in the original table used to filer
down to the records I wanted in the recordset. HighLimit is the field in the
recordset that I need find the highest and lowest values in. So that in the
Select Case I can return the correct score. I need the DMax() and DMin() to
operate on the recordset not the original table. HighLimit in the statement
below is a field in the recordset.

Case Is >= DMax(rs("HighLimit"), "MECurlUpTable")

Can the domain functions be used on a record set?

You did indeed mess something up. Check DMax in the help file. What is
the first argument? It should be a field name, not some value. This
would work better:
DMax("AgeHighLimit", "MECUrlUpTable")

-Tom.
Microsoft Access MVP

Sorry the subject should be Using DMax() and DMin() with a Recordset

[quoted text clipped - 34 lines]

 

[John W. Vinson]

Can the domain functions be used on a record set?

No.

 

[Tom van Stiphout]

On Mon, 15 Feb 2010 00:41:20 GMT, "new2access123 via

In addition to what John said: you're not getting my point.
Your code: DMax(rs("HighLimit"), "MECurlUpTable")
I am assuming rs("HighLimit") is some integer value. This would result
in (for example):
DMax(5, "MECurlUpTable")
That's not a good expresssion, because "5" is not a field in your
table.

-Tom.
Microsoft Access MVP

AgeLowLimit and AgeHighLimit are fields in the original table used to filer
down to the records I wanted in the recordset. HighLimit is the field in the
recordset that I need find the highest and lowest values in. So that in the
Select Case I can return the correct score. I need the DMax() and DMin() to
operate on the recordset not the original table. HighLimit in the statement
below is a field in the recordset.

Case Is >= DMax(rs("HighLimit"), "MECurlUpTable")

Can the domain functions be used on a record set?

You did indeed mess something up. Check DMax in the help file. What is
the first argument? It should be a field name, not some value. This
would work better:
DMax("AgeHighLimit", "MECUrlUpTable")

-Tom.
Microsoft Access MVP

Sorry the subject should be Using DMax() and DMin() with a Recordset

[quoted text clipped - 34 lines]

Thanks for the help

 

[new2access123 via AccessMonster.com]

I understand your point now. But even if there were a way to get the rs
reference to evaluate to a field name a domain function would not work any
way. Right?

In addition to what John said: you're not getting my point.
Your code: DMax(rs("HighLimit"), "MECurlUpTable")
I am assuming rs("HighLimit") is some integer value. This would result
in (for example):
DMax(5, "MECurlUpTable")
That's not a good expresssion, because "5" is not a field in your
table.

-Tom.
Microsoft Access MVP

AgeLowLimit and AgeHighLimit are fields in the original table used to filer
down to the records I wanted in the recordset. HighLimit is the field in the

[quoted text clipped - 20 lines]

 

[Tom van Stiphout]

On Mon, 15 Feb 2010 04:06:03 GMT, "new2access123 via

Correct. You need to redesign this section.

-Tom.
Microsoft Access MVP

I understand your point now. But even if there were a way to get the rs
reference to evaluate to a field name a domain function would not work any
way. Right?

In addition to what John said: you're not getting my point.
Your code: DMax(rs("HighLimit"), "MECurlUpTable")
I am assuming rs("HighLimit") is some integer value. This would result
in (for example):
DMax(5, "MECurlUpTable")
That's not a good expresssion, because "5" is not a field in your
table.

-Tom.
Microsoft Access MVP

AgeLowLimit and AgeHighLimit are fields in the original table used to filer
down to the records I wanted in the recordset. HighLimit is the field in the

[quoted text clipped - 20 lines]

Thanks for the help