VB code for a calculation

[jammumeta]

Hi folks,

I am a newbie here. I am not very good in programming. I just want little help in generating an output if I create a form and input certain fields.


Basically I need the code for this output.

The requirement is :-
I have to input a date ' D' with shifts 'S' like A,B,C
A =8 hours (6am to 2pm), B = 16 hours (2pm to 10pm), C = 24 hours ( 10pm to 6am).
Also, I will input the height of anode in 'H' cm (a consumable material).
The basic consumption pattern is 1cm in every 16 hours.
And I want to remove the anode when the left over height is 20cm. Basically, I need the output date and shift when I should remove the anode.
Output 'O, = ? in Date and Hours

For example,

D = 08 Jan 2109
S = A = 8
H = 48 cm
Then,
As per consumption pattern
48-20 = 28 cm
28cm * 2 shifts = 56 shifts
[1 day = 3 shifts]
56 shifts = 18 days 2 shifts
So, the output should be
8 Jan A (8 hours) + 18 days 2 shifts
= 26 Jan C (3rd shift)

Please write a code to generate the output date and shift in format (Date + A/B/C shift).


Thanks in advance.

[kd2017]

This is more or a we'll help you with your problem forum than a we'll do all your work forum. Good luck.

PARAMETERS START_DATE DateTime, SHIFT Text ( 1 ), ANNODE_HEIGHT IEEEDouble;
SELECT [START_DATE]+IIf([SHIFT]="A",6/24,IIf([SHIFT]="B",14/24,IIf([SHIFT]="C",22/24)))+(([ANNODE_HEIGHT]-20)*2)/3 AS RemoveDateTime, Hour([RemoveDateTime]) AS RemoveAtHour, IIf([RemoveAtHour]>=6 And [RemoveAtHour]<14,"A",IIf([RemoveAtHour]>=14 And [RemoveAtHour]<22,"B","C")) AS RemoveAtShift;

[Micron]

You asked for code but got sql. One is often mistaken for the other, so if you wanted code, let us know.
Agree that you should make some sort of attempt and post what didn't work. It provides more context about what you're trying to achieve.

[kd2017]

You asked for code but got sql. One is often mistaken for the other, so if you wanted code, let us know.

They sure did, sorry about that. I'll convert to vba when I get a chance.

[ssanfu]

D = 08 Jan 2109

2109???? Really???


Try this: (and do lots of testing)

[Micron]

Hmmm, here's another skinless cat, I guess. No form as I spent oodles of time playing with date/time formats (subject of this thread) which is how I envisioned calling the function. For now, it's called like testexpression(#01/058/2019 08:00:00#,48,20,16) but the date/time, lengths and rate could come from such a form.

Function testExpression(dteStart As String, lngLenStart As Long, lngLenEnd As Long, lngRate As Long) As String
'lngRate is in cm per hour
Dim dteEnd As Date
Dim dteHour As Date

dteEnd = DateAdd("n", ((lngLenStart - lngLenEnd) * lngRate * 60), dteStart)
dteHour = Format(dteEnd, "hh:nn:ss")

Select Case dteHour
    Case #5:59:59 AM# To #1:59:59 PM#
        testExpression = dteEnd & " on A shift."
    
    Case #2:00:00 PM# To #10:00:00 PM#
        testExpression = dteEnd & " on B shift."
        
    Case #12:00:00 AM#
        testExpression = dteEnd & " " & dteHour & " on C shift"
        
    Case Else
        testExpression = dteEnd & " on C shift."
    
End Select
MsgBox testExpression

End Function

Seems to work, but I ignored the posted start times as you really can't expect to handle this if 2 shifts have exactly the same start/end times.
So much for not doing all the work...

[jammumeta]

Thanks . I understand your views.
I would just like to know a one line code to calculate the output nothing else.
Actually I am confused how to print
The date format with shift?

[jammumeta]

Hmmm, here's another skinless cat, I guess. No form as I spent oodles of time playing with date/time formats (subject of this thread) which is how I envisioned calling the function. For now, it's called like testexpression(#01/058/2019 08:00:00#,48,20,16) but the date/time, lengths and rate could come from such a form.

Function testExpression(dteStart As String, lngLenStart As Long, lngLenEnd As Long, lngRate As Long) As String
'lngRate is in cm per hour
Dim dteEnd As Date
Dim dteHour As Date

dteEnd = DateAdd("n", ((lngLenStart - lngLenEnd) * lngRate * 60), dteStart)
dteHour = Format(dteEnd, "hh:nn:ss")

Select Case dteHour
    Case #5:59:59 AM# To #1:59:59 PM#
        testExpression = dteEnd & " on A shift."
    
    Case #2:00:00 PM# To #10:00:00 PM#
        testExpression = dteEnd & " on B shift."
        
    Case #12:00:00 AM#
        testExpression = dteEnd & " " & dteHour & " on C shift"
        
    Case Else
        testExpression = dteEnd & " on C shift."
    
End Select
MsgBox testExpression

End Function

Seems to work, but I ignored the posted start times as you really can't expect to handle this if 2 shifts have exactly the same start/end times.
So much for not doing all the work...

I think I don't need this.
Pls input and output as per the example only.

[Micron]

The output is EXACTLY what you asked for. Obviously you didn't even try it. It's no coincidence that the last 2 provided solutions take the same approach. It's ime for you to put some effort into this.

[jammumeta]

2109???? Really???




Try this: (and do lots of testing)

Thanks I could understand this better.
This is what exactly I needed.

Sorry @micron I couldn't exactly read your function properly. my bad.

[jammumeta]

This is more or a we'll help you with your problem forum than a we'll do all your work forum. Good luck.

PARAMETERS START_DATE DateTime, SHIFT Text ( 1 ), ANNODE_HEIGHT IEEEDouble;
SELECT [START_DATE]+IIf([SHIFT]="A",6/24,IIf([SHIFT]="B",14/24,IIf([SHIFT]="C",22/24)))+(([ANNODE_HEIGHT]-20)*2)/3 AS RemoveDateTime, Hour([RemoveDateTime]) AS RemoveAtHour, IIf([RemoveAtHour]>=6 And [RemoveAtHour]<14,"A",IIf([RemoveAtHour]>=14 And [RemoveAtHour]<22,"B","C")) AS RemoveAtShift;

This SQL query is more or less what required but I needed in VB code.
Thanks anyways.

[Micron]

Sorry @micron I couldn't exactly read your function properly. my bad.

Rather than ask for information/help, it is easier for you to simply dismiss it?
If you copied that code into a standard module and not a form module (the db you were given has one, if I recall) and went to the immediate window of the vb editor and pasted

testexpression(#01/08/2019 08:00:00#,48,20,16)

and hit enter at the end of that line, you'd get a message box that says "1/27/19 12:00:00 AM on C shift"
If that's not what you wanted, then I give up too.

[jammumeta]

Moderators please close this thread. The solution delivered.
Thanks everyone.

[ssanfu]

You can mark it solved yourself.....