Repost: Query Return Criteria

  • Thread starter Thread starter Bryan Hughes
  • Start date Start date
B

Bryan Hughes

Hello,

I have a table:

Table (Income_Guidelines)
[Level]; [Family_Size]; [Income]
1 1 $10,550
1 2 $12,050
1 3 $13,550
2 1 $17,550
2 2 $20,100
2 3 $22,600
3 1 $28,100
3 2 $32,150
3 3 $36,150
4 1 $28,101
4 2 $32,151
4 3 $36,151

How should I write the strSQL, to return the [Level]?

strSQL = "SELECT [Level] FROM Income_Guidelines "
strSQL = strSQL & "WHERE [Family_Size] =" & intFMS & " AND "
strSQL = strSQL & "Income ?

curIncome will be variable.

I need the level if user inputs intFMS=3 And curIncome = $27,000
How can I get the Query to return Level= 2?

TFTH
-Bryan
 
Hello,

I have a table:

Table (Income_Guidelines)
[Level]; [Family_Size]; [Income]
1 1 $10,550
1 2 $12,050
1 3 $13,550
2 1 $17,550
2 2 $20,100
2 3 $22,600
3 1 $28,100
3 2 $32,150
3 3 $36,150
4 1 $28,101
4 2 $32,151
4 3 $36,151

How should I write the strSQL, to return the [Level]?

strSQL = "SELECT [Level] FROM Income_Guidelines "
strSQL = strSQL & "WHERE [Family_Size] =" & intFMS & " AND "
strSQL = strSQL & "Income ?

curIncome will be variable.

I need the level if user inputs intFMS=3 And curIncome = $27,000
How can I get the Query to return Level= 2?
Click to expand...

Since the criterion is applying to two different records, you will
need to add this table to the query grid *TWICE*, joining the two
instances by Family_Size. The SQL will be something like

strSQL = "SELECT A.Level, A.FamilySize, A.Income, B.Income" _
& "FROM Table AS A INNER JOIN Table AS B" _
& "ON A.Family_Size = B.Family_Size" _
& "WHERE NZ(A.Level) + 1 = B.Level" _
& "AND [Family_Size] = " & intFMS _
& "AND A.Income <= " & curIncome _
& "AND B.Income > " & curIncome
 
Back
Top