?
Solved

Filter data in a Recordset

Posted on 2003-02-25
10
Medium Priority
?
413 Views
Last Modified: 2013-11-25
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
Comment
Question by:jclarkmcse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 7

Expert Comment

by:fluglash
ID: 8016700
if you use DAO:

Recordset.Filter = strField & " = '" & strFilter & "'"
0
 
LVL 1

Expert Comment

by:xThorx
ID: 8016757
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
 
LVL 3

Expert Comment

by:Da_Weasel
ID: 8017046
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
Industry Leaders: 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!

 
LVL 3

Expert Comment

by:Da_Weasel
ID: 8017061
the SQL method is also much faster
0
 
LVL 3

Expert Comment

by:Da_Weasel
ID: 8017543
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
 
LVL 3

Expert Comment

by:Da_Weasel
ID: 8018310
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
 
LVL 3

Expert Comment

by:Da_Weasel
ID: 8018893
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
 

Author Comment

by:jclarkmcse
ID: 8113886
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
 

Author Comment

by:jclarkmcse
ID: 8114206
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
 
LVL 3

Accepted Solution

by:
Da_Weasel earned 1200 total points
ID: 8120012
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question