Like statement in Text box

[mcadle]

All, I have been trying to figure this out. I have a textbox in the page footer with this as the control source:

=Sum(IIf([vehicle description]="" Like "*" & "sand" & "*""",1,0))

What I want it to do is search all of the vehicle description fields (its bound text box is in the detail) and give me the total count of vehicles with sand in their description. Access accepts the code above, but I get 0 as a result. I know that there are exactly 3 vehicles with sand in their description. Any ideas?

 

[Rich]

You can't Sum records in this way in the Page footer, why not just use the Report footer, if you really need to use the page Footer then post back

 

[mcadle]

Its actually a group footer. I have about 14 other textboxes doing the same stuff but without the like statement. They work fine.

Here is an example:

=Sum(IIf(Left([vehicle number],1)="e" And [speciality]=False,1,0))

Works great...I just can't figure out how to do the like statement. Hopefully, this will help.

 

[Nirious]

I haven't tested it out but if I had do do it, I would write it like this:

=Sum(IIf([vehicle description] Like "*sand*";1;0))

Or maybe something like this:

=Sum(IIf( InStr (; "sand"; [vehicle description]; ) > 0 ;1;0))

Again not tested so don't shoot me if the syntax is wrong. But it should give you some ideas

 

[mcadle]

Sweet your first kind of worked...had to use commas here is the code:

=Sum(IIf([vehicle description] Like "*sand*",1,0))

Thanks Nirious!