Conditional Formatting of a string

[spartansFC]

Hi

I'm trying to colour code a text box based off values in a string.

the string value in the query looks like

Val: Min([lngSessionTypesID]) & Chr(13) & Chr(10) & Min([strRepValue])

this outputs values

25
B2

how do I get it to colour the text box based off of a string value which might be the second part of the string

The first string value "25" is a booked session, the second string value "B2" is if the session is funded

I've tried various ones:

InStr([Mon],"B2") Or ([FundedDesc] Like "Parent*")
InStr(1, [Mon], "B2”) <> 0 AND [FundedDesc] Like "Parent*"

I'm sure there is a simple solution

Mikie

 

[dhookom]

I'm confused. You have a column name Val that is made up of two fields lngSessionTypesID and strepValue but your conditional formatting expression contains[Mon] and [FundedDesc].

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[spartansFC]

to be fair, i'm not sure what i'm doing.

i was trying to use your crosstab query you helped me with a while ago which works perfectly, the full query i'm using for this new problem is:

TRANSFORM Min([FundedDesc]) & Chr(13) & Chr(10) & Min([lngSessionTypesID]) & Chr(13) & Chr(10) & Min([strRepValue]) AS Val
SELECT qryAttendanceEntry_v2Test.NurseryId, qryAttendanceEntry_v2Test.Nursery, qryAttendanceEntry_v2Test.strRegisterSort, tblRegisterRooms.lngRoomRegisterNo, tblRegisterRooms.strRoomRegisterDesc, qryAttendanceEntry_v2Test.strFundedOption1Desc, qryAttendanceEntry_v2Test.FundedDesc, qryAttendanceEntry_v2Test.dteDOB, qryAttendanceEntry_v2Test.lngChildID, qryAttendanceEntry_v2Test.childsname, qryAttendanceEntry_v2Test.strCurrentSessionTermNonDesc, qryAttendanceEntry_v2Test.lngMONFund, qryAttendanceEntry_v2Test.lngTUEFund, qryAttendanceEntry_v2Test.lngWEDFund, qryAttendanceEntry_v2Test.lngTHUFund, qryAttendanceEntry_v2Test.lngFRIFund, qryAttendanceEntry_v2Test.strRegisterGrp
FROM tblRegisterRooms INNER JOIN qryAttendanceEntry_v2Test ON (qryAttendanceEntry_v2Test.strRegisterSort = tblRegisterRooms.lngRoomRegisterNo) AND (tblRegisterRooms.lngRoomRegisterNurseryID = qryAttendanceEntry_v2Test.NurseryId)
GROUP BY qryAttendanceEntry_v2Test.NurseryId, qryAttendanceEntry_v2Test.Nursery, qryAttendanceEntry_v2Test.strRegisterSort, tblRegisterRooms.lngRoomRegisterNo, tblRegisterRooms.strRoomRegisterDesc, qryAttendanceEntry_v2Test.strFundedOption1Desc, qryAttendanceEntry_v2Test.FundedDesc, qryAttendanceEntry_v2Test.dteDOB, qryAttendanceEntry_v2Test.lngChildID, qryAttendanceEntry_v2Test.childsname, qryAttendanceEntry_v2Test.strCurrentSessionTermNonDesc, qryAttendanceEntry_v2Test.lngMONFund, qryAttendanceEntry_v2Test.lngTUEFund, qryAttendanceEntry_v2Test.lngWEDFund, qryAttendanceEntry_v2Test.lngTHUFund, qryAttendanceEntry_v2Test.lngFRIFund, qryAttendanceEntry_v2Test.strRegisterGrp
ORDER BY qryAttendanceEntry_v2Test.NurseryId, qryAttendanceEntry_v2Test.strRegisterSort, qryAttendanceEntry_v2Test.childsname
PIVOT qryAttendanceEntry_v2Test.DofWeek In ("Mon","Tue","Wed","Thu","Fri");

The column headings are "Mon", "Tue, "Wed, "Thu", "Fri" which is why i'm using the expression

InStr([Mon],"B2")

but i can see why this doesn't make any sense when the Val is pulling through different field names.

 

[dhookom]

I expect you are using "Expression Is" in the conditional formatting. I would start with something very simple like: InStr(1, [Mon], "B2”) <> 0 and see if it works. Then add to it as needed.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[spartansFC]

Thanks for your help again Duane, got the report to colour code things