How to develop increment function (1 Viewer)

[mubi_masti]

I am using following function to get increment values


THe input value is the colume of a query. if sr is divisible by 2 then increase otherwise old value of increment variable
Sr Output required
1 10
2 10
3 11
4 11
5 12

Function IncrementValues(i, Optional myBase As Long) As Integer
Dim IncrementVariable As Long
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("queryName")
With rs
IncrementVariable = 0
Do Until .EOF
IncrementVariable = IncrementVariable + 1
IncrementValues = IncrementVariable
.MoveNext
Loop
End With
End Function

 

[penguino29]

Hi mubi_masti,

Try this:

Function IncrementValues(intSR As Integer, intBase As Integer) As Integer

IncrementValues = ((intSR + intSR Mod 2) / 2) - 1 + intBase

End Function

intSR is your SR value, starting from 1.
intBase is the base value that you wish to set (from what I saw, it is 10)

So create a query, with a column listing the SRs,
and have another column using the above function as an expression and feed SR into the function.

e.g. Output: IncrementValues([SR],10)



Hope it works.

 

[mubi_masti]

Thanks a lot

you have solve my problem.

Your function is working perfectly and with the help of this i am able to develop a function with new criteria

Function IncrementValues(intSR As Integer, intBase As Integer) As Integer
IncrementValues = (intSR + (intSR * 2) Mod 3) / 3 + intBase
End Function

is it possible that i format this column as time:

i.e

10:00 am
10:00 am
10:00 am
11:00 am
11:00 am
11:00 am
12:00 pm

 

[penguino29]

Yes you simply use the convert to date function: CDate and convert the output from the function IncrementValues() to a formated time value with am/pm.

I've done the work for you

Output: format(CDate (IncrementValues([SR],intBASE)/24), "hh:mm am/pm")

[SR] is the value from your SR column
intBASE is the base value that you set up.

Let me know if it works.

 

[mubi_masti]

Thanks a lot

it works fine and according to my requirement Except at one point and that is i want to skip one value from output column.. The value is 1:00 pm, i want to skip this value as this is lunch break and there is no one to do any task. Is it possible to do?

Once again i really thankful to you.

 

[penguino29]

You are becoming lazy and asking for too much! I might start charging you!

What you can try is to add a IF-THEN statement in the function that: if the value is 13:00 then add an extra delay of 1 hour from then on.

I am sure you can achieve this - but if you are stuck, just give post here again and see if I can crack it for you.

Don't forget to thank my posts

 

[mubi_masti]

Thanks a lot, with your help and guidance i am able to solve the issu:

i am using following code

Function IncrementValues(intSR As Integer, intBase As Integer) As Integer
IncrementValues = (intSR + (intSR * 2) Mod 3) / 3 + intBase
If IncrementValues >= 13 Then
IncrementValues = (intSR + (intSR * 2) Mod 3) / 3 + intBase + 1
End If
End Function

i have still one query but because you are annoyed as i m asking too much so i am not putting my query.

my query is related to file attached.

 

[penguino29]

Hi mubi_masti,

Having now seen your xls and what you are trying to do, we need a completely new approach for the solution. This is because at the beginning I was thinking that we were dealing with signals and discrete mathematics, whereas now I can see you are creating a timetable.

It will make more sense to model it like in real life. i.e. your database table should resemble the real-life information object.

You should first create a table call “TimeSlots”. This give you the flexibility (you are not tied to an equation/function) and you can visually see or edit the slots. Enter the time slots like how you want it and miss out the lunch hours.

Similarly you need a table for the SHIFTS.

Now this is the clever/crazy part. You create a disjoined query with the two tables. Notice the order of the field (SHIFT) first and the sorting order.

Run this query – and you will see that it can generate for any given shift (A,B,C,…to Z), the list of hours as in the timeslots, similar to your xls spreadsheet.

You may also give it criteria e.g. SHIFT A only.

You can now either:

a) use the output of this query, turn it into an append query and append the data to another table, or
b) capture the query's SQL statement, write some vba codes and use the SQL as a recordset, looping each record and use the fields to update fields in another table

You may think this is a crazy concept to generate the timesheet, but it gives you the possibility to make sense of the hours in the "Timeslots" table, and also you can later create other fields (e.g. operator name, comments) in the "SHIFT" table.

 

[mubi_masti]

Thanks for your reply but i think it does not work as one timeslot is alocated to three member of same Shift which is not possible to do in this technique

 

[penguino29]

Hi mubi_masti,

Have you actually read my post and followed my instruction? Have you actually tried it? I don't think you have. You slacker!

If you had you would have seen that my method generates the SHIFTS and TIMESLOTS column in your XLS.

I attach the zipped mdb that shows you how it is done.

 

[mubi_masti]

I think you are missing the important thing and that is you are able to develop query for time and shift but actualy each name is repeated numberous times. Infact there are only 16 student but output query is giving 1400 combination.

my requirement is like following

name 1 A 10
name 2 A 10
name 3 A 10
Name 4 A 11

 

[penguino29]

Hi mubi_masti,

No I have not miss a point – that’s why I am the one that who can create solutions according to everything you have asked.

I am trying to help – but your problem is that you do not explain what you have as the source data, the correlation of the data and what you are trying to produce by turning the data into information that you need.

Well, since WHEN did you tell me about the 16 students? After 10 posts?

16 students does not equal to sample1 - sample45 in your XLS! Sample45 does not mean anything me or anyone!!!! If only you can explain everything clearly and concisely, then it will be much easier.

This is how you should have phrased the question. For example:

I would like to generate a time table.
There are 15 students
There will be 3 shifts every day of cleaning/cooking/washing/whatever duty
Each day there are 15 timeslots, each slot is allocated to each student for each shift.... etc.

My last solution gave you a starting point to generate data by query multiplication. You can use some vba codes with a bit of imagination to create what you need. If only you have explored my solution further (I want you to learn something here too rather just being given something you don’t understand), you would have quickly discovered that you can just add the student field in the timeslots table and do this.

and it will give you the following which is similar to your XLS.

Note that: I made it easier for you to understand and use the above method, so I have put the Students AND Hours in the same table. The student-timeslots pattern repeats itself and you will be unable to change that relationship. (that is to say: If in future you would like to have Shift A -10:00am with Student 1 and Shift B - 10:00am with Student B - that will not be possible)

If you want your model to be completely flexible and future-proof, you should really be doing the following that allows you to have flexible timeslots, shifts and people.

but I guess that is beyond what you need.

 

[mubi_masti]

Dear

penguino29

Thanks for spending and putting too much efforts to solve my problem and comments giving to me. I am posting all message on this forum for help but along with providing help, you comments really discouraging me.

I am a faculty member and no knowledge about access or VB language. I am developing this project for my own interest only

I really appreciate your solutions but it is not solving my problems

In fact i m developing schedule for conducting viva of students.

There are multiple sessions and each sessions start at 10, each session is not of equal duration because number of students in each session may vary.

in one hour three viva are expected and for that three students are given same time. in excel format, shift means session in a day i.e there are three session but no one student is called twice in same day for two different sessions.

 

[penguino29]

Dear mubi_masti,

Now we are getting somewhere, you begin to explain what you are trying to do and express your requirement.

There are multiple sessions and each sessions start at 10, each session is not of equal duration because number of students in each session may vary.

in one hour three viva are expected and for that three students are given same time. in excel format, shift means session in a day i.e there are three session but no one student is called twice in same day for two different sessions.

From what I understand now, you are trying to fill a timetable, visually it is like this if you don't mind my own arrangement....

I am assuming that:

a) at any time, you pull up a list of students (say from a class/faculty). There are N number of students selected. We can put them with their student ID in a table.

b) from that list, each student only attend one viva (that is to say: no student will be going to viva twice)

Is that correct? And any other information?

 

[mubi_masti]

penguino29
I have done my job with other technique which you have provided earlier i.e with help of IncrementValues function. I am able to do what i want to do. Thank a lot for time you spend for me.

One thing which i want to mention and that is not try to give too personal comment on anybody efforts. I am not programmer but i tried and done it with you help


thanks....................