OpenRecordset multiple criteria

[bbrazeau]

Hi, Is there a way to use multiple criteria when setting up a record set. The code below gets a recordset that meets criteria of MyPress_ID =[Schedule].Press_ID (MyPress_ID is a variable) I would like to have it further narrow the recordset by only including records after a certain date which I also have as a variable (MyStartDate). Could someone show me the correct syntax for this? Thank you.



strSQL = "SELECT * FROM [Schedule] WHERE ( " & MyPress_ID & " =[Schedule].Press_ID );"
Set MyRecordSet = MyDB.OpenRecordset(strSQL, dbOpenDynaset)

[orange]

This is a great set of tutorials. Several SQL and vba topics with full examples. Enjoy.
http://www.fontstuff.com/access/acctut14.htm

[bbrazeau]

Hi orange link does not seem to work?

[orange]

Try this, then choose the SQL/vba tutorials. Not sure why the last one isn't working. It could be down for maintenance??
http://www.fontstuff.com/access/index.htm


I just tried them both again and they work????
http://www.fontstuff.com/access/acctut14.htm

[bbrazeau]

Hi Orange, Tried several times and link is not working for me. below is the syntax I'm trying to use. Do you see an error?

strSQL = "SELECT * FROM [Schedule] WHERE ( " & MyPress_ID & " =[Schedule].Press_ID AND " & MyStartDate & " > [Schedule].[Start_Date] );"

[orange]

You can try this:

strSQL = "SELECT * FROM [Schedule] WHERE Press_ID = " & MyPress_ID & " AND Start_Date < #" & MyStartDate & "#"

I'm not having any trouble with links to Martin's material.

[boblarson]

Yes, I see several:

It Should be

strSQL = "SELECT * FROM [Schedule] WHERE [Schedule].Press_ID=" & MyPress_ID & " AND [Schedule].[Start_Date] < #" & MyStartDate & "#"

Your logic was backwards. The FIELD is first and then the value is checked, not the other way around. Also, you don't need the semi-colon at the end and you have to use Octothorpes (#) for Date Fields.

[boblarson]

Seems like I posted the same answer as orange albeit late.

[bbrazeau]

Thanks Guys, I got mine to work late in the day. Here's what I ended up with:
strSQL = "SELECT * FROM [Schedule] WHERE ( " & MyPress_ID & " =[Schedule].Press_ID AND [Schedule].Start_Date >= #" & MyStartDate & "# );"

Orange, I tried the links again this morning and their working fine. Must have been down for maint. or something.

[orange]

Glad you got it working. The tutorials on Martin's site are very good. Suggest you give them a try -- they'll make a good reference for any sql /vba activity.

[boblarson]

Just an FYI for you. If you don't have a bunch of JOINS, your Where Clause doesn't need the parentheses. It makes things simpler as you could have just used:

strSQL = "SELECT * FROM [Schedule] WHERE " & MyPress_ID & " =[Schedule].Press_ID AND [Schedule].Start_Date >= #" & MyStartDate & "#"

(you don't need the semi-colon at the end either)