Criteria in expression builder

  • Thread starter Thread starter Dean Collins
  • Start date Start date
D

Dean Collins

Hi,

I have a Form that is populated with a query. There ia a
text box representing each Year below.

-------------------------
2000 2001 2002 2003

------------------------------
My query produces the data in the following format:

Dept, Year, Number
ABT 2000 435
ABT 2001 420
ABT 2002 209
ALV 2000 89
ALV 2001 109
ALV 2003 106

Is there an expression that can be used to populate each
field using a Dept and Year criteria in the control
source of each text box? without a criteria the first
data in the query is chosen. If this is not possible then
can some one show me an alternative way to achieve the
same results.

many thanks
Dean Collins
 
Is there an expression that can be used to populate each
field using a Dept and Year criteria in the control
source of each text box?
Click to expand...

=DLookUp("[Number]","MyTable","Dept=""ABT"" and [Year]=2000")

etc... By the way, Number and Year are really inadvisable names for fields
because, although being legal, they are also used as SQL key words and may
cause bugs that can be very hard to track.
If this is not possible then
can some one show me an alternative way to achieve the
same results.
Click to expand...
It is possible, but it may be more efficient to intercept the On Current
event and get all the values in one go:

Private Sub Form_Current()
' editors note: this is untested air code: treat with caution!!

' objects
Dim db as Database
Dim strSQL as String
Dim rs as Recordset

' create the query: note the projection and ordering
strSQL = "SELECT [Year], [Number] FROM MyTable " & _
"WHERE Dept = """ & Me!txtDept & """ " & _
" AND [Year] IN "2000,2001,2002,2003) " & _
"ORDER BY [Year];"

' open the query: forward snapshots are fastest
Set db=CurrentDB()
Set rs=db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)

' read the records; omit loop if there weren't any
Do While Not rs.EOF
' put the data in the controls
' I am assuming the controls are txt2000, txt2001, etc.
' you may want to put in some formatting here too
Me.Controls("txt" & rs![Year]).Value = rs![Number]

' get the next record
rs.MoveNext

Loop

' all done: tidy up
rs.Close

End Sub


Hope that makes some kind of sense

Tim F
 
Back
Top