Conditional Formatting within Query

[joshandsony]

Hi All,

Is there a way to use conditional formatting within a query in 2007? I found the conditional formatting icon in the settings, but it won't let me use it. Please help. I have to present this to my boss in 2 hrs.

Thanks!

 

[Rabbie]

If you want to have conditional formatting you will need to display the data in a form or report.

Queries in Access are designed for extracting recordsets from the DB - not for displaying data.

 

[boblarson]

And to go with what Rabbie said, if you create a form for that query, you can use a DATASHEET VIEW with conditional formatting, so it LOOKS like a query but isn't

 

[joshandsony]

But I would not be able to export a report to excel correct?

 

[boblarson]

But I would not be able to export a report to excel correct?

Not if you are currently on Office 2007. On previous versions, yes. But you can export a datasheet form to Excel

 

[joe31709]

any ideas?

 

[boblarson]

any ideas?

About???? Your "question" - since you had posted nothing else, is vague and not really helpful.

 

[Brianwarnock]

About???? Your "question" - since you had posted nothing else, is vague and not really helpful.

How very polite.

Brian

 

[joshandsony]

What about when I import the excel file with the conditional formatting. It will not bring over the formats will it?

 

[boblarson]

What about when I import the excel file with the conditional formatting. It will not bring over the formats will it?

Nope, you would have to code for that.

 

[joshandsony]

What about doing an if statement within the query to change the letter to bold? I just need a way to make the "Y" within that column stand out somehow. Any suggestions?

 

[boblarson]

What about doing an if statement within the query to change the letter to bold? I just need a way to make the "Y" within that column stand out somehow. Any suggestions?

You can't format things in queries. If you apply any formatting it will affect all in the query as it is just the way the user interface looks.

If you look at my code to export a form's recordset to Excel, you will see some sample formatting which might help.

http://www.btabdevelopment.com/main/CodeSnippets/SendToExcel/tabid/120/Default.aspx

 

[joshandsony]

And what about within the actual table? The query feeds from the table. If there is a way to format the table, will the format transfer to the query once it is ran?

Sorry for all the questions. I am afraid I will screw up the whole database if I change anything in VB. I can read the code, but cannot write it correctly. I must have some kind of dislexia. LOL

 

[boblarson]

And what about within the actual table? The query feeds from the table. If there is a way to format the table, will the format transfer to the query once it is ran?

Sorry for all the questions. I am afraid I will screw up the whole database if I change anything in VB. I can read the code, but cannot write it correctly. I must have some kind of dislexia. LOL

Tables and queries are very limited in the way you can do anything (like formats and stuff). Forms and reports are where the formatting takes place.

As for things like BOLD, ITALICS, UNDERLINE, etc. you do not have control over that at table or query level. If you choose one of those for your table or query, it will display EVERYTHING with that. In fact, it will not actually carry over to other people's computers as those features are installation specific. So, again - if you want formatting - you can do it in a form or report and if you want formatting to stay with you when going to Excel, you will need to do the formatting via code after exporting to Excel as the Report to Excel feature was removed in 2007 and we can only hope that Microsoft will choose to restore it at some point if they get enough complaints about it.

 

[joshandsony]

OK. Thank you very much for the info.

 

[gwunta]

I know this is an old thread, but since no Access based solution has been suggested I thought I would post the solution for reference. You CAN conditionally format results in a query. In query design, go to the field you want conditionally formatted, then in Format property of the properties box, type in something like 0[Black];0[Red];0[Black]. This will format the results according to value when the query is run.

 

[Pat Hartman]

This doesn't solve the original poster's problem. He needed the data to be formatted in Excel. The export to Excel only exports column headings and field values. To format the data in Excel, you would need to automate Excel to apply the formatting using VBA and a code loop.