Search Query with multiple criteria

[martin DH]

To print the query output as is, just run your query and then do either one of the following:
1. click the printer icon to print as is
2. click the print preview icon to bring up the print dialog box and modify some of the print settings before it goes to the printer.

I'm sorry, I wasn't too clear. In the code for my form, I have it set to open the results of the search in a report (see code below):
[CODE=vb]DoCmd.OpenRepor t "Quick Report", acViewPreview, , strWhere[/CODE]
My question is: in place of this code, is there some other code that will simply display the results in a table - so that I could export these results to Excel, for example.

To get the month name for the month, you can any of the following:
1. Format(Date, "mmmm")
2. MonthName(month #[, abbreviate]) where month# is the number of the month (Month# for January = 1) abbreviate is optional....set to true if you want to abbreviate the month name.
example MonthName(12, True) >>>>>Dec
3. MonthName(month ("datestring ")) where datestring is a valid date string like "12/22/2007"

Where do I apply these changes? To the code on my form? To the textbox on the report? Thanks for your help.

[Rabbit]

Puppydogbuddy and Rabbit:

Thank you very much for your help! I now have a working and incredibly useful search tool.
Per your last post, puppydogbuddy, changing the trailing AND to OR for the Year strings results in the same error as before with this sql string:
[CODE=sql](([COMPILE_HIST.Ye ar] = 2006) OR ([COMPILE_HIST.Ye ar] = 2007[/CODE]
WIth the addition of Rabbit's post (utilizing the IN syntax), the form (Survey Form) searches through the data table (COMPILE_HIST) for records matching any and all criteria entered, then presents that information on a report (Quick Report).
Thanks again for your help on this extensive post!

One last question: If I decide to add additional, slightly different reports (so I have NAC Report and AE Report), is it possible to change which report is opened by the form. I'm thinking that if the NAC field contains criteria, NAC Report opens, and if the AE field contains criteria, AE Report opens (each report would have some differences in the formating). Ideas?

martin

Not a problem, good luck.

[puppydogbuddy]

I'm sorry, I wasn't too clear. In the code for my form, I have it set to open the results of the search in a report (see code below):
[CODE=vb]DoCmd.OpenRepor t "Quick Report", acViewPreview, , strWhere[/CODE]
My question is: in place of this code, is there some other code that will simply display the results in a table - so that I could export these results to Excel, for example.



Where do I apply these changes? To the code on my form? To the textbox on the report? Thanks for your help.

Martin.
1. Re your question to display the output in table format, just open the query that is the source for the report, instead of opening the report itself.

DoCmd.OpenQuery "YourQueryN ame" , acViewNormal

2. On a second thought, if you want to change the date format everywhere, I would just go to your table, place it in design view. Go to the format property for the datefields in your table type mmmm/dd/yyyy, which would result in 06/19/2007 looking like his>>June 19, 2007. If you want the month abbreviated set the format in the table as mmm/dd/yyyy.. If it is in table, I believe it will display that way on the reports and forms, unless a format property is specified in the property sheet for the form.

[martin DH]

Martin.
1. Re your question to display the output in table format, just open the query that is the source for the report, instead of opening the report itself.

DoCmd.OpenQuery "YourQueryN ame" , acViewNormal

There is no actual query for the report. It's control source is the table COMPILE_HIST and the it's "query" is the WHERE string created by the form above.

[puppydogbuddy]

There is no actual query for the report. It's control source is the table COMPILE_HIST and the it's "query" is the WHERE string created by the form above.

Martin,
Sorry about that. you can use the sql string to open a recordset which will display your results in table format, which you can then print using the printer icon. see this link for sample code on how to do that:

http://www.fmsinc.com/tpapers/queries/index.html#SQLstring

you can also save the sql string as a querydef (query definition) using Access VBA. you can use the querydef just like a regular query. See this link:

http://www.blueclaw-db.com/access_createquerydef.htm

Also, read the entire link for one of the best tutorials on queries.

http://www.fmsinc.com/tpapers/queries/index.html