HLP: Using 2 Filters for a Query in a FILL

  • Thread starter Thread starter Mr. B
  • Start date Start date
M

Mr. B

I'm having difficulties in Filtering an OleDbDataAdaptor... Currently I've a
single query for it such as the following:

SELECT Employee, ID, Period_End_Date, Units, [Date] FROM tblTimeEntry WHERE
(Employee LIKE ? + '%')

Simple enough. And I can do my FILL via something like:

Dim strUserID As String ' User Name Filter
strUserID = UCase("feu001")

daTimberline.SelectCommand.Parameters(0).Value = strUserID
daTimberline.Fill(dsTimberline.tblTimeEntry)

What I'd like to know is HOW to do a query with TWO (2) queries. Say Employee
(user Name - as above) _and_ something else (say Date)?

In the past I've had one reply. Although he tried to tell me. His answer
wasn't too clear to me (I've only been dealing with DB's for less than 2 weeks
now). So the answer kind of went over my head :(

So any info and some more detailed info would be appreciated.

Basically... I've an Access file that has User info and Dates and Time Sheet
entry stuff.

What I want to do is to do a FILL (or what ever I need to do) using the Users
NAME (employee) and a DATE. Right now I can do one or the other... not both.

As long as I get the records for that User and Time periods (can be 1 to 50
+/- record sets). I'd rather work with this number rather than hundreds of
plain User record sets (which is what I get with just User name filtering).

Thanks for any help in advance.

Regards,

Bruce
 
If you use a stored query in Access for your query, you
can use the PARAMETERS key word to define several
parameters in your query. You can then pass your arguments
eg.
SelectCommand.Parameters(0).Value = strUserID
SelectCommand.Parameters(1).Value = datDate

Using stored queries is faster too. See 'create a
parameter query' in the answer wizard in Access help.
-----Original Message-----
I'm having difficulties in Filtering an
Click to expand...
OleDbDataAdaptor... Currently I've a
single query for it such as the following:

SELECT Employee, ID, Period_End_Date, Units, [Date] FROM tblTimeEntry WHERE
(Employee LIKE ? + '%')

Simple enough. And I can do my FILL via something like:

Dim strUserID As String ' User Name Filter
strUserID = UCase("feu001")

daTimberline.SelectCommand.Parameters(0).Value = strUserID
daTimberline.Fill(dsTimberline.tblTimeEntry)

What I'd like to know is HOW to do a query with TWO (2) queries. Say Employee
(user Name - as above) _and_ something else (say Date)?

In the past I've had one reply. Although he tried to tell me. His answer
wasn't too clear to me (I've only been dealing with DB's for less than 2 weeks
now). So the answer kind of went over my head :(

So any info and some more detailed info would be appreciated.

Basically... I've an Access file that has User info and Dates and Time Sheet
entry stuff.

What I want to do is to do a FILL (or what ever I need to do) using the Users
NAME (employee) and a DATE. Right now I can do one or the other... not both.

As long as I get the records for that User and Time periods (can be 1 to 50
+/- record sets). I'd rather work with this number rather than hundreds of
plain User record sets (which is what I get with just User name filtering).

Thanks for any help in advance.

Regards,

Bruce
.
Click to expand...
 
geoffp said:
If you use a stored query in Access for your query, you
can use the PARAMETERS key word to define several
parameters in your query. You can then pass your arguments
eg.
SelectCommand.Parameters(0).Value = strUserID
SelectCommand.Parameters(1).Value = datDate
Click to expand...

AHA!!!! So that's it? Thanks! I'll give it a try! (:

I understand the (0) and (1) bit... cool... didn't even tweak to that :(

Thanks again... I'll go forward now...

Regards,

Bruce
 
Hi,

Did you mean two parameters, not two queries? First remove % wildcard from
the SQL string and pass it as a part of the parameter. Then add second
parameter. You code would look like


SELECT Employee, ID, Period_End_Date, Units, [Date] FROM tblTimeEntry WHERE
(Employee LIKE ? AND MyDateField = ?)

Dim strUserID As String ' User Name Filter
strUserID = UCase("feu001%")

daTimberline.SelectCommand.Parameters.Add("@Employee", OledbType.Char, 10)
daTimberline.SelectCommand.Parameters.Add("@MyDateField ", OledbType.Date)
daTimberline.SelectCommand.Parameters(0).Value = strUserID
daTimberline.SelectCommand.Parameters(1).Value = MyDateHere

daTimberline.Fill(dsTimberline.tblTimeEntry)
 
Val Mazur said:
Did you mean two parameters, not two queries? First remove % wildcard from
Click to expand...

OOPPsss... yes (head hanging down). Told you I was rather new to DB's (:
SELECT Employee, ID, Period_End_Date, Units, [Date] FROM tblTimeEntry WHERE
(Employee LIKE ? AND MyDateField = ?)
Click to expand...

Okay... That wasn't clear to me before.
daTimberline.SelectCommand.Parameters.Add("@Employee", OledbType.Char, 10)
daTimberline.SelectCommand.Parameters.Add("@MyDateField ", OledbType.Date)
daTimberline.SelectCommand.Parameters(0).Value = strUserID
daTimberline.SelectCommand.Parameters(1).Value = MyDateHere
Click to expand...

Aha! That did it... works GREAT! I had also forgotten to add:
Imports System.Data.OleDb
to the begining of my project...

But I just tried it out and it works just fine... Most excellent.

Thanks muchly!!!

Regards,

Bruce
 
Back
Top