No Results returned from Access query

[Chuck W]

Hi,
I am attempting to return data from a select query into my excel sheet using
Excel 2007 and Access 2003. I have one date field and five text fields my
query in access returns 63 records when I run it in Access. However, when I
select Data => From Access, select a data source, navigate to my access db
and select the query it returns no results in Excel. The fields are all
listed in Excel with drop down. Select all is chosen for all fields but
there are no results. Does anyone know why it is not returning results?

Thanks,

 

[Joel]

When you setup your query the last menu with the control button "Finish"
there are 3 buttons. Select the Edit Query option and press finish. You
will then be able to debug you problem. Look at the SQL and see if there are
any problems. You can also edit the SQL and see the results immediately to
help solve the problem. Change the SQL statements to see if you can isolate
the problem.

 

[Chuck W]

Joel,
Thanks for your help. My query runs fine in access returning 63 records.
It won't return results in Excel. I played around with it and think I see
the problem. When I remove the criteria in Access Design view for one of my
fields which is the following:

Like "*chf*" Or Like "*edema*" Or Like "*effusion*" Or Like "*sob*" Or Like
"*dib*"

The query in Excel returns results. I need this criteria statement in
Access Design View for the query to be meaningful. Can Excel handle Access
queries that have a where clause in them?

Any thoughts?

 

[Joel]

Yes. The first menu in the query wizard is the selecting of the columns.
the 2nd menu contains 6 boxes. The left 3 boxes contain the word LIKE and
the right 3 boxes are boxes when you can enter the text such as *chf*
(without the double quotes). This only gives you the option for three
filters. What you can do is manually edit the SQL in the last menu (the one
with finish) to add additional filters. Use the SQL the is working in
Access. I would add three filters using the Wizard and then add additional
ones in the Edit Query feature.

There is also a Query Edit menu option in excel on the same menu as the New
Database Query menu. It is usally Gray-Out except if you select any cell in
a returned by query. You can modify a previous query in this menu.

Another option is to record a macro while performing a query and then modify
the recorded macro. The Command Text portion of the recorded macro is the
SQL. I prefer the recorded macro becasue I can easily modify the macro to
add or remove filters and then later use the macro in other workbooks.