DAO: Access to Excel

[Lekster07]

Hi Everyone,

I'm trying to import data from Access to Excel with multiple criterias using DAO. I got this code (at least the lines I'd like to modify) from the internet:

Set rs = DB.OpenRecordset("SELECT * FROM " & _
TableName & " WHERE " & FieldName & _
" = 'MyCriteria'", dbReadOnly) ' filter records

I can't seem to modify this line where in I can refer to at least 2 criterias instead of 1 (MyCriteria).

The criterias I'm using is a range of date.

Thanks in advance!

 

[Ruddles]

Assuming your criteria are dates, something like this:-

[FONT=Courier New][COLOR=#0000ff]Set rs = DB.OpenRecordset("SELECT * FROM " & TableName & " " _[/COLOR][/FONT]
[FONT=Courier New][COLOR=#0000ff]    & "WHERE " & FieldName & "=#" & MyCriteria & "# " _[/COLOR][/FONT]
[FONT=Courier New][COLOR=#0000ff]    & "AND " & FieldName2  & "=#" & MyCriteria2 & "#;", _[/COLOR][/FONT]
[FONT=Courier New][COLOR=#0000ff]    dbReadOnly)[/COLOR][/FONT]

I suggest that you build the SQL string first and then pass that to the .OpenRecordset command - that way you can check the contents of the SQL string fairly easily;-

[FONT=Courier New][COLOR=#0000ff]Dim strSQL As String[/COLOR][/FONT]
[FONT=Courier New][COLOR=#0000ff][/COLOR][/FONT] 
[FONT=Courier New][COLOR=#0000ff]strSQL = "SELECT * FROM " & TableName & " " _[/COLOR][/FONT]
[FONT=Courier New][COLOR=#0000ff]    & "WHERE " & FieldName & "=#" & MyCriteria & "# " _[/COLOR][/FONT]
[FONT=Courier New][COLOR=#0000ff]    & "AND " & FieldName2  & "=#" & MyCriteria2 & "#;"[/COLOR][/FONT]
[FONT=Courier New][COLOR=#0000ff][/COLOR][/FONT] 
[FONT=Courier New][COLOR=#0000ff]Debug.Print strSQL[/COLOR][/FONT]
[FONT=Courier New][COLOR=#0000ff][/COLOR][/FONT] 
[FONT=Courier New][COLOR=#0000ff]Set rs = DB.OpenRecordset(strSQL[/COLOR][/FONT][FONT=Courier New][COLOR=#0000ff], [/COLOR][/FONT][FONT=Courier New][COLOR=#0000ff]dbReadOnly)[/COLOR][/FONT]

 

[Ruddles]

If you try this and it doesn't work, hit Ctrl-G to open the Immediate window and see what's in strSQL by typing ?strsql and pressing Enter.

Then get back to us and tell us:-

• what's in MyCriteria and MyCriteria2 (whatever you call them)
• what's in strSQL
• what the SQL string is that you want to execute

 

[Lekster07]

If you try this and it doesn't work, hit Ctrl-G to open the Immediate window and see what's in strSQL by typing ?strsql and pressing Enter.


Then get back to us and tell us:-

• what's in MyCriteria and MyCriteria2 (whatever you call them)
• what's in strSQL
• what the SQL string is that you want to execute

Thanks for the response Ruddles! I did the same to what you have posted here but nothing happened when I run the code. I tried ?strsql but when I press enter, it's not giving me anything.

By the way, when I run the code, this is what I get in the immediate window:
Select * FROM testdb WHERE date_ans=#5/1/2011# AND date_ans=#5/2/2011#;

 

[Andrew Fergus]

Hi

I suspect that no records will satisfy the condition where date_ans equals two different values........it appears there is an issue with the criteria. Does one of the date_ans need to be another field?

Andrew

 

[Lekster07]

Hi

I suspect that no records will satisfy the condition where date_ans equals two different values........it appears there is an issue with the criteria. Does one of the date_ans need to be another field?

Andrew

Hi Andrew! Thanks for the observation. I noticed that as well. I think I haven't really clarified what I wanted. Basically, my database table has one column for dates and I would want to filter records that satisfies a range of date (May 1 to May 2 for example) from that column and will give me data for the entire row (that satisfies the date criteria).

I can do this easily in Excel if my database is an Excel by just using Autofilter and then selecting the range of date I wanted, but I'm using an Access database.

 

[Andrew Fergus]

Ok - I haven't tested this but based on your last post, substitute the = signs for >= and <= respectively.

Andrew

 

[jreedich]

Change your date criteria to the following format

date_ans Between #1/1/2001# And #3/1/2003#

note the word between

 

[Lekster07]

Ok - I haven't tested this but based on your last post, substitute the = signs for >= and <= respectively.

Andrew

GREAT! Exactly what I'm looking for! THANK YOU SO MUCH! This will make my life easier.

 

[Ruddles]

Sorry, I just got back!

Hopefully you understand why:-

Select * FROM testdb WHERE date_ans=#5/1/2011# AND date_ans=#5/2/2011#;

will never select any records?

[Pause for thought]

It's because no record can have date_ans=#5/1/2011# AND date_ans=#5/2/2011#. You actually want all records where date_ans=#5/1/2011# OR date_as=#5/2/2011#.

(The English language is very sloppy in that respect - SQL is not so forgiving, as you have discovered. VBA is similarly very demanding when it evaluates ANDs and ORs.)

Using:-

date_ans > = #5/1/2011# AND date_ans < = #5/2/2011#

will work, or:-

date_ans between #5/1/2011# AND =#5/2/2011#

or:-

date_ans in [#5/1/2011#, #5/2/2011#]

This is good if you plan to be using SQL at all: http://w3schools.com/sql/.