Color Code Query Results

[jo15765]

I have an access query that I want certain rows to stand-out to the people viewing them. I was wondering if within the query, I could write a conditional statement to say something like IF dollar_amount > 5,000 Then BackColor = vbRed?



If this can not be done in the query itself, I am actually copying/pasting the data from an access query into an excel report (I have to copy paste because of memo field being to many characters), is there an alternate way to do it (other than a access report or form)

[rpeare]

You can't do conditional formatting in a query, you can, however, do conditional formatting on a report or form.

[jo15765]

That's what I thought. I'll try and do it via Excel. Thanks for the update


EDIT ----
Is it possible to export a report to Excel?

[rpeare]

Why not just build a report based on your query then just print the report, you can make it look like a spreadsheet if that's really your desire. Way easier than exporting just to color a final product you're going to print anyway.

[jo15765]

Because the spreadsheet is then emailed out, so I have to have it in .xls format so I can email.

EDIT---
I actually just tried it and my query contains to many fields to be displayed in a report.

[rpeare]

Do the recipients do anything with the spreadsheet or is it purely informational? If it's purely informational you can print a report (with the conditional formatting) as a file and send that file instead of an excel spreadsheet.

Otherwise you can export your dataset like this:

Dim rst As Recordset
Dim sTargetFile As String
Dim objExcel
Dim objWorkbook
Dim objSht As Excel.worksheet
Set db = CurrentDb
Set rst = db.OpenRecordset("Table or Query Name Here")
rst.MoveFirst

sTargetFile = "c:\test\Test-Export2.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
iRow = 1

Do While rst.EOF <> True

    objExcel.Cells(iRow, 1).Value = rst!RecordID
    objExcel.Cells(iRow, 2).Value = rst!title1
    If rst!title1 = "1a" Then
        objExcel.Cells(iRow, 2).Interior.Color = RGB(200, 100, 35) 'this is where you set the color of the conditional format
    End If
    objExcel.Cells(iRow, 3).Value = rst!Title2
    objExcel.Cells(iRow, 4).Value = rst!Title3
    objExcel.Cells(iRow, 5).Value = rst!Title4
    objExcel.Cells(iRow, 6).Value = rst!Title5
    objExcel.Cells(iRow, 7).Value = rst!Title6

    rst.MoveNext
    iRow = iRow + 1
Loop
objExcel.ActiveWorkbook.SaveAs (sTargetFile)
objExcel.Application.Quit

rst.Close
Set db = Nothing

[magicrat]

You can do "some" conditional formating of query results.

Right-click on the column you want to apply the conditions to in query design view and select properties.

In the properties box that opens, you may specify conditional color formatting for numbers ... like this:

$#,###.000[Blue];($#,###.000)[Red];$0.000[Blue];$0.000[Blue]

I am looking for ways to do conditional text ...

But for numbers, this DOES work without having to build a form or report.