help please

[de049]

I need to find out what criteria to use in my query. I have an expression that counts the number of entries in my records. These entries are all letters. I now want to specify what letter to count in each record. So, instead of telling me there's WWWHHU, i want it to tell me how many W's only . My expression looks as follows:

Expr1: (([mon am]+[Mon pm]+[Tue am]+[Tue pm]+[Wed am]+[Wed pm]+[Thu am]+[Thu pm]+[Fri am]+[Fri pm]+[Sat am]+[Sat pm])).

thanks, 'D'

 

[Rich@ITTC]

Hi de049

You could use

TotalW: (IIf([Mom am]=W,1,0)+IIf([Mon pm]=W,1,0)+IIf([Tues pm]=W,1,0) and so on

I would build this up bit by bit - try it out with 2 periods, then with four etc.

However, it isn't a very elegant way of doing things ... having 12 fields to store the different days/periods (it would be more normal to put this in a separate table and have a field for day/period and a second field for value (W, H, U etc) and a foreign key linking this back to your employee.

Also you should really consider adopting a naming convention. Names of objects, controls, fields, etc should not have spaces in them and should be descriptive (eg. frmEmployee, tblAttendance, etc).

HTH

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 09-25-2001).]

 

[Joey C]

Try a combination of functions Len() and Mid() in your criteria;

Len gives back the number of characters in a string, eg:

Len("ABCDE") = 5

Mid returns the specified part of the string, eg:

Mid ("ABCDE",2,3)= BCD

Hope that puts you on the right track

Joey C.