Query in a Recordset w/Filter

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

Hi,
I'm trying to understand how I can use a value (date)
from my combo box, use it as a criteria in my query for
two different date fields and then use the this query in
a ADO.RECORDSET to pull the data.

Currently (because I couldn't get the above piece to
work) I have the code below that works but I'm pulling
all records and I have to hard code the query without any
criteria. I tried FILTER but it didn't work either. I
tried replacing the SQL stmt with a "sel query" and that
didn't work (gave me a run time error)...
-------------------------------------------------------
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset

rs.Open "Select * from tblAllAuction order by RecordID,
c_record", cn

Do While rs.EOF = False
 
Not sure if this is what you seek, but try something like this:

rs.Open "Select * from tblAllAuction WHERE [DateField]=#" & Me.ComboBoxName
& "# ORDER BY RecordID, c_record", cn
 
You should be able to simply add a WHERE clause to your SQL that is based on
the dates.

You do need to make sure that the dates are correctly formatted in SQL
strings - this means that they must be enclosed in # marks and be in US date
format of mm/dd/yyyy. You can use the format function to convert a date from
a combo box to the correct format, eg

rs.Open "Select * from tblAllAuction Where AcutionDate >=" &
Format(me.cboDate, "\#\mm\/dd\/yyyy\#") & " order by RecordID"

Andrew
 
Can I expand this to add another table field?
e.g.

rs.Open "Select * from tblAllAuction Where AcutionDate ="
& Format(me.cboDate, "\#\mm\/dd\/yyyy\#") " Or PostDate
=" & Format(me.cboDate, "\#\mm\/dd\/yyyy\#") & " order
by RecordID"

Thanks!!
 
Yes.

Nick said:
Can I expand this to add another table field?
e.g.

rs.Open "Select * from tblAllAuction Where AcutionDate ="
& Format(me.cboDate, "\#\mm\/dd\/yyyy\#") " Or PostDate
=" & Format(me.cboDate, "\#\mm\/dd\/yyyy\#") & " order
by RecordID"

Thanks!!
Click to expand...
 
Ken said:
Yes, the Format function can/should be used to be absolutely sure:

WHERE [DateField]=#" & Format(Me.ComboBoxName, "mm/dd/yyyy") & "#
Click to expand...

It's pick on Ken time ;-)

Actually, the / is also a user locale setting and might be
replaced by something the US format doesn't accept.

To be totally safe, use Andrew's expression (without the
second \ ) "\#mm\/dd\/yyyy\#"
 
Back
Top