Conditional Formatting Expression

[Lou P]

I am trying to Bold an entry in a report, generated from a query, for a concatenated text field that contains specific test.

The field is called HARDWARE and the values I'm trying to bold contain the term Chassis as the first word.

Conditional Formatting accepts both of these Expressions but does not bold the report entries beginning with "Chassis".

InStr([HARDWARE],"Chassis")

Left([HARDWARE],InStr([HARDWARE],"Chassis"))

Is it not possible to do it this way? Thanks!

 

[JamesMcS]

Isn't there a "begins with" option in the conditional formatting menu? What version of Access are you using?

 

[Brianwarnock]

In the Detail_format event of the Report put

If Left(me.[HARDWARE],7)= "chassis" Then
me.[HARDWARE].fontbold = True
Esle
me.[HARDWARE].fontbold = False
End if

Brian

 

[Lou P]

I'm using Access 2007 & there isn't any selection in conditional formatting for "begins with"...all selections look like they are meant for numerical or date values...Between, No Between, Greater Than etc.

I tied the If Statement in the Detail section On_Format Event with the same results "no errors" & "no joy".

Thanks! I just don't know why it accepts the expression and If statements, but doesn't do anything.

 

[vbaInet]

You need to use "Expression Is" instead of "Field Value Is".

 

[Lou P]

Sorry...vbaInet, I was answering JamesMcS' question...I did use the Expression Is, no joy. Initially I looked under the Field Value Is when I started to see if there was something like "Begins With" etc., but that would have been too easy ;>)

I've tried calling InStr, Left, Left & InStr with calling out "7" digits...no errors on the expressions, but none of it works...?

Though it would be straight forward.

Thanks!

 

[Lou P]

BTW...neither did the If, Then, Else Statement.

 

[vbaInet]

Your Instr() statement is slightly off. Here's the amended code:

InStr(1, [HARDWARE],"Chassis")

 

[Lou P]

That didn't work either, and I probably should have supplied this info up front.

This is the Concatenated Field (Combobox)
The Record Source for the form is a query where this string is expressed as "HARDWARE:"
SELECT DISTINCTROW [tblHW].[HWID], [HWType] & " - " & [HWModel] &" "& [HWDescription] &" S/N:" & [HWSerialNum] FROM tblHW ORDER BY [HWType];

The combobox control in the form is "HARDWARE". Chassis is the HWType.

Sorry...

 

[vbaInet]

If the name of the field is "Hardware:" you forgot to include the colon. Or is that a typo?

You're trying to search within the concatenated string? You haven't given it an alias.

 

[Lou P]

When I select the field the Name = HARDWARE & Control Source = HARDWARE in Edit View.

Yes I'm search within the concatenated string.

I'm not sure what you mean by alias.

Please excuse my lack of understanding on the terminology...I've got a pile of books & I'm learning though.

 

[vbaInet]

Use this:

SELECT DISTINCTROW [tblHW].[HWID], ([HWType] & " - " & [HWModel] &" "& [HWDescription] &" S/N:" & [HWSerialNum]) AS [COLOR=Red][B]ConcatHardware[/B][/COLOR] FROM tblHW ORDER BY [HWType];

ConcatHardware is an alias. Have a look in the design view to see how it's represented.

For the formatting use:

InStr(1, [ConcatHardware],"Chassis")

And use "Expression Is"

 

[Brianwarnock]

Must be a 2007 thing, the If then Else in the Detail on Format event works in 2002

Brian

 

[vbaInet]

It would work in 2007 too.

 

[Lou P]

Thanks...I'm on travel right now & I'll try it as soon as I get back.

Wouldn't HARDWARE be considered an alias in that case? In the underlying query for the report I have...

HARDWARE: [HWID], [HWType] &" - "& [HWModel] &" "& [HWDescription] &" S/N: "& [HWSerialNum]

Also, to get Brian's If, Then, Else statement to work would I have to use the alias there too?

Thanks all for the help once again...I may be slow, but I am learning.

I'll attack it when I get back!

 

[Brianwarnock]

I thought that the control (field) on the report was HARDWARE, which is why my code used it.
You might want to replace
If Left(me.[HARDWARE],7)= "chassis" Then
by
If instr(me.[HARDWARE],"chassis" ) >0 Then

just in case chassis is not the first 7 characters.
The [] are not actually needed as HARDWARE has no spaces or special characters.

Brian

 

[Lou P]

I've tried both methods to bold that text in the report, and no joy!

I'm sure I've defined it correctly, I've even changed the underlying alias in the query to:

HARDWARE: [HWType] &" - "& [HWModel] &" "& [HWDescription] &" S/N: "& [HWSerialNum]

It still doesn't see or recognize the "Chassis" HWType. Is it because I'm using a query to populate the Report?

Thanks!

 

[Lou P]

I was able to get it with the conditional formatting though...

InStr(1,[HARDWARE],"Chassis")

Thank You both!!