• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 437
  • Last Modified:

Filter data in a Recordset

I've seen many questions on this and can't quite follow.
I have a data1 recordset (Access) and want to view only records that meet certain criteria, lets use zip code for example. I would like to use a filter and view all zips in the data control that match the query.  I am using a combo box to select the criteria for the filter.  Can anyone walk me through this using Combo1 and data1 as the objects, address.mdb as the database, table1 as the table and zip as the field to query.
0
jclarkmcse
Asked:
jclarkmcse
1 Solution
 
fluglashCommented:
if you use DAO:

Recordset.Filter = strField & " = '" & strFilter & "'"
0
 
xThorxCommented:
Make sure you have microsoft dao 3.60 object library referenced.

dim db as dao.database
dim REC as dao.recordset
dim I as long

set db=opendatabase(database_path_name)
set rec=db.openrecordset ("SELECT * FROM Table WHERE Zip = " & combo.list(combo.listindex) & "ORDER BY name;")  'if you want to have the result ordered by name

if rec.recordcount>0 then
   rec.movelast  'You have to go to the end of the database in order to get the real value for rec.recordcount. if you d'ont, you get 1 as result...
   rec.movefirst  'To see the first recordset
   for i=1 to rec.recordcount
      data.additem rec.fields("Name")
      rec.movenext
   next i
end if
set rec=nothing
db.close
set db=nothing

Hope this help
0
 
Da_WeaselCommented:
If you just need simple filters based on field values and not complex calculations of values and/or across multiple tables then this would be the best method.
set your data1.RecordSource property equal to a SQL query.

data1.RecordSource = "SELECT field1, field2 FROM table WHERE field = value"
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Da_WeaselCommented:
the SQL method is also much faster
0
 
Da_WeaselCommented:
you should just use the builtin data bound properties of the combo box to have the control populate it self with values from the data1 control
Here is a specific example that should populate the combo with zip codes that contain the entered text
do the following in the properties window
set your data1.DatabaseName = c:\yourdatabase.mdb
set your data1.RecordSource = TableName
set your combo1.DataSource = data1
set your combo1.DataField = ZIP

do the following in code, where ever you want the population of the combo box to happen
I would suggest a text box and a command button be added to the form and do the following
Sub Command1_Click()
  data1.RecordSource = "SELECT * FROM TableName WHERE ZIP LIKE %" & Text1.Text & "%"
  data1.refresh
End Sub

You should be able to now enter some numbers in the Text box and click on the command button to populate the combo box with matching fields.
0
 
Da_WeaselCommented:
you should just use the builtin data bound properties of the combo box to have the control populate it self with values from the data1 control
Here is a specific example that should populate the combo with zip codes that contain the entered text
do the following in the properties window
set your data1.DatabaseName = c:\yourdatabase.mdb
set your data1.RecordSource = TableName
set your combo1.DataSource = data1
set your combo1.DataField = ZIP

do the following in code, where ever you want the population of the combo box to happen
I would suggest a text box and a command button be added to the form and do the following
Sub Command1_Click()
  data1.RecordSource = "SELECT * FROM TableName WHERE ZIP LIKE %" & Text1.Text & "%"
  data1.refresh
End Sub

You should be able to now enter some numbers in the Text box and click on the command button to populate the combo box with matching fields.
0
 
Da_WeaselCommented:
you should just use the builtin data bound properties of the combo box to have the control populate it self with values from the data1 control
Here is a specific example that should populate the combo with zip codes that contain the entered text
do the following in the properties window
set your data1.DatabaseName = c:\yourdatabase.mdb
set your data1.RecordSource = TableName
set your combo1.DataSource = data1
set your combo1.DataField = ZIP

do the following in code, where ever you want the population of the combo box to happen
I would suggest a text box and a command button be added to the form and do the following
Sub Command1_Click()
  data1.RecordSource = "SELECT * FROM TableName WHERE ZIP LIKE %" & Text1.Text & "%"
  data1.refresh
End Sub

You should be able to now enter some numbers in the Text box and click on the command button to populate the combo box with matching fields.
0
 
jclarkmcseAuthor Commented:
Da Weasel,  your first comment works well for me, you will get the points.  I will up it another 100 points if you can tell me how to "and" another field to the end.  ex. where field1 = value and field2 = value.  I want both field values to be true for the record to be selected.  I don't completely understand the syntax so haven't been able to figure this out.

This was the original that works for me:
data1.RecordSource = "SELECT * FROM table WHERE field = 'value'"
0
 
jclarkmcseAuthor Commented:
I got the line working to check value of 2 fields but it won't work on the date field using the following.

Data1.RecordSource = "SELECT * FROM CheckinMain WHERE PatientStat = 'Checked In' and DateCkIn = " & Date & " "

in debug mode I checked the value of dateckin and the actual date and they are the same but it refuses to return the records that meet these criteria.   any ideas?
0
 
Da_WeaselCommented:
Date values need to be surrounded by single quotes like this:
'01/01/2001'

The basic idea behind using values in SQL statements is this:
Strings and Dates: get 'single quotes' around them.
all others: use the value as is.

Try this as your code:
Data1.RecordSource = "SELECT * FROM CheckinMain WHERE PatientStat = 'Checked In' and DateCkIn = '" & Date & "'"

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now