Query in a Recordset w/Filter

[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

 

[Ken Snell]

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

 

[Andrew Smith]

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

 

[Nick]

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!!

 

[Andrew Smith]

Yes.

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!!

 

[Tim Ferguson]

WHERE [DateField]=#" & Me.ComboBoxName & "#

.... which of course only works on computers where dates are
set to be m/d/y


Tim F

 

[Marshall Barton]

Yes, the Format function can/should be used to be absolutely sure:

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

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\#"

 

[Ken Snell]

Ken Snell wrote:
It's pick on Ken time ;-)

ok, ok! Thanks!