"if statement" 1

[lars7]

Hi,
I got the following "if statement" from "jerryklmns" in this forum for another problem I had (sorry jerry still to thanks you for that) but I need it now for this and it works fine if the "Spine/Band Now" looks like this "302 /01" but sometimes it looks like this: "303Y/02" with a "Y" so instead of getting: "302" I get: "302Y"

Band: IIf(InStr([Spine/Band Now],"/")>0,Left([Spine/Band Now],InStr([Spine/Band Now],"/")-1),[spine/band now])

does anyone know how to git rid of the "Y".

 

[Remou]

I am not sure what you wish to do, for example, is the bit you want always three characters? However, here are two ideas:
Band: IIf(InStr([Spine/Band Now], "/") > 0, Left([Spine/Band Now], InStr([Spine/Band Now], "/") - 2), [Spine/Band Now])
Band: IIf(InStr([Spine/Band Now], "/") > 0, Left([Spine/Band Now], 3), [Spine/Band Now])

 

[lars7]

Hi again Remou,

Both codes work.

Yes there will always be 3 numbers but you question has reminded me that later on some will be like: 308a, b, c, or d but those letters will need to stay, is there a way to build that into the "if staterment" as well.

 

[Remou]

Hi Lars
I think the best bet would be to write out the series of events or 'ifs' that you need, then code from the list. Perhaps a Select Case may be the best bet? For example:
[tt]Select Case Mid(txtText,4,1)
Case "X"
'Do this
Case "A"
'Do that
Case Else
'Whatever
End Select[/tt]

As I said, I am not sure what you want to achieve. I am a little worried about the design of your tables / fields because, in general, this kind of splitting up should be a once-off thing.

 

[lars7]

Hi Remou,
The information in the table comes from an excel sheet sent from payroll after they have give a group of staff a new post descriptor and banding which I import into "tblAssimilation", this will be monthly with different groups of staff. There are some members of staff who have been upgraded, after an appeal, so 2 post descriptors but I can only count them once although both records must stay in the table. I hope to produce a report of how much this will cost so since these people are only doing one job I need to identify the post they where upgraded to as that is the one that they will be payed for. I hoped to seperate the "Spine/Band Now" as the third number is the band ie: 20"3" is band 3 but as I mentioned earlier band 8 can be 8a, 8b, 8c, or 8d. My plan had been to add a new field to the table and call it "JdNumber" and automaticaly put a 1 in every record if the staff member was getting counted (this would be the higher band for those with 2 post descriptors) and a 2 if not but I am not sure how I will manage this one either.

I hope what I am trying to do is clearer now and as always if you have an easier way I would give it a go.

 

[Remou]

In that case I think the best bet would be:
Band1: Left([Spine/Band Now],2)
Which will give 20, 30 etc

Band1: Mid([Spine/Band Now],3,2)
Which will give 2<space> or 2y, 8a etc

Band3: Right([Spine/Band Now],2)
Which will give 01, 02 etc, that is, the part after the slash (/).

A slighly fancy query will allow you to select a unique set of employees from the data, choosing the higher rate when a duplicate exist.

 

[lars7]

Thanks Remou for this. I'm sure I could find a way of collating for example: 4's and 4y's as the same. The query you mentioned I will have a go at tomorrow wish me luck.

 

[Remou]

Good Luck! I still think it is safer, because it is not unlikely that the management will spring 9q and 6h on you .

Thinking again, perhaps another band?

Band2: Mid([Spine/Band Now],3,2)
Which will give 2<space> or 2y, 8a etc

Split into:
Band2a: Mid([Spine/Band Now],3,1)
Which will give 2, 8 etc

Band2b: Mid([Spine/Band Now],4,1)
Which will give <space> or y, a etc

 

[lars7]

Thanks again Remou, it's taking shape now.

 

[lars7]

Hi,

I got the report finished it took me 2 query the first:

Query 1

SELECT tblprofiles.Jobfamily, tblprofiles.SubJobFamily, tblassimilation.PayNumber, Max(tblassimilation.[Post Descriptor]) AS [MaxOfPost Descriptor], Max(Mid([Spine/Band Now],3,1)) AS Band2a, Max(Mid([Spine/Band Now],4,1)) AS Band2b, Max(tblassimilation.[Whitley salary]) AS [MaxOfWhitley salary], Max(tblassimilation.[AfC Salary Now]) AS [MaxOfAfC Salary Now]
FROM tblprofiles RIGHT JOIN tblassimilation ON tblprofiles.Postdescriptor = tblassimilation.[Post Descriptor]
GROUP BY tblprofiles.Jobfamily, tblprofiles.SubJobFamily, tblassimilation.PayNumber;

Then in the report:

Query 2

SELECT QueryBandingJobFamilies.Jobfamily, QueryBandingJobFamilies.SubJobFamily, QueryBandingJobFamilies.Band2a AS [Band], Count(QueryBandingJobFamilies.PayNumber) AS Staff, Sum(QueryBandingJobFamilies.[MaxOfWhitley salary]) AS Whitley, Sum(QueryBandingJobFamilies.[MaxOfAfC Salary Now]) AS AfCSalary
FROM QueryBandingJobFamilies
GROUP BY QueryBandingJobFamilies.Jobfamily, QueryBandingJobFamilies.SubJobFamily, QueryBandingJobFamilies.Band2a;

Thanks again Remou for the start with this: