text field that needs calculation

[rrtpeds]

So here is my problem i am semi access savy but i have having trouble with one thing. At work when we make a schedule there are 5 possibilites for how you want to mark your day. I have created a field using text as my parameters since my possibilities include both letters and numbers. At the end of each week I need to create a calcuation only utilizing the number which is 1 to add up the total shifts in the week.

I have tried some IIF statements with no luck. The 5 possiblities are 1, c,r,a,l. Each of the letters stand for something. When the scheduler runs the query he needs to see the letters or the number for each day. But i can't create a properly working calculated field for adding all of the 1's together only. The letters don't need to be added. Any ideas?

Also I have to run a sum for the total of each day at the bottom so I am running into the same problem there if there is any letters in the boxes. Going to a number field is not an option
thanks for the help

 

[ColinEssex]

I would suggest the numbers go in one field and the letters in another. Then you can concatonate them if you need to (for reports etc)

Another idea may be if the number is always in the same place, use the 'Left', 'Right' or 'Mid' functions and then use the 'Int' function to make it a number and not a 'text' number.

If you use numbers and text in the same field, the number will always be text.

Col

 

[DCrake]

Simple Software Solutions

If I am correct then the field that contains the value (1, c, r, a or l) will only ever have one of the aforementioned codes.

In your query create a new column called Cntr and employ the following code:

Cntr:IIF(IsNumeric([FieldName]),1,0)

The use this column sum the number of occurances of the number 1

CodeMaster:

 

[rrtpeds]

Thank you that fixes my problem on the query now how do i fix it so each day can be totaled in the report footer

 

[DCrake]

Simple Software Solutions

If you just want to count the number of records in the report then place an unbound textbox in the detail section of the report and set the default value to 1.

Then in the report footer place another unbound textbox that is the sum of the above textbox.

However if you only want to count the number of 1's identified earlier then insert this field as a bound field in the detail section and set the summary field in the report footer to sum this field.

David

 

[gemma-the-husky]

if there are five possibilites for your user day results (there may be more in future!) use a lookup table to select an appropriate value - this avoids mistyping. and avoids having to dissect the text string to extract data - just use the lookup value.

thats the way to do it , imho

 

[rrtpeds]

Ok so i got the totals to sum but now i have a new problem. One of the options is 1l and andother is 1s. How can i tell access to calcualte it by assigning 1l a value of 1 in the calculation