Transport plan and dates (1 Viewer)

crumber

New member
Local time
Today, 16:58
Joined
Jan 28, 2020
Messages
4
Hello everyone!
I have a problem, in Access I need to make a formula for dates for the transport plan, exactly to the columns marked in yellow (arrival_date and arrival_shift), the other columns are source data.
Is anyone able to help me?
The result in the yellow columns is the effect that I would like to achieve by creating a formula.
Thx for help

MAG_FIR_KODMAG_KODshipping dayshiftdate of loadingNRarrival_datearrival_datearrival_shift
GRODZMZ2628.01.2020329.01.2020 00:00115036829.01.2020 16:0029.01.2020
2​
GRODZMZ2629.01.2020129.01.2020 06:00115042129.01.2020 11:0029.01.2020
1​
GRODZMZ2629.01.2020229.01.2020 14:00114508229.01.2020 20:0029.01.2020
2​
GRODZMZ2630.01.2020130.01.2020 12:00115074630.01.2020 23:0030.01.2020
3​
GRODZPD30.01.2020130.01.2020 10:00115028231.01.2020 14:0031.01.2020
2​
TOMTC229.01.2020330.01.2020 00:30114946530.01.2020 05:0029.01.2020
3​
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:58
Joined
Sep 21, 2011
Messages
14,044
So the shift value depends on a time range from the date?
Create a function to return that passing it the arrival date.?
Could probably use the Case statement ?
 

plog

Banishment Pending
Local time
Today, 10:58
Joined
May 11, 2011
Messages
11,611
I need to make a formula for dates for the transport plan, exactly to the columns marked in yellow

1. Any formula in particular you want to use? I can't derive it from the data given.

2. Don't use spaces in field names (e.g. [shipping day], [date of loading], etc.). Makes coding and querying that much harder.

3. You can't have 2 fields named [arrival_date] in the same query. Pick a different one for the calculated value.

4. Are you using Date/Time fields for the fields that contain date and time values? Looks like they are text values.
 

crumber

New member
Local time
Today, 16:58
Joined
Jan 28, 2020
Messages
4
I need the result as in the example, the formula which of these sample dates and times gives a similar effect as the columns highlighted in yellow. What I have pasted is an example for visualizing a problem.
 

plog

Banishment Pending
Local time
Today, 10:58
Joined
May 11, 2011
Messages
11,611
I need the result as in the example .... What I have pasted is an example for visualizing a problem.

You do realize your first sentence contradicts your second? And everything after the comma in the first makes no sense at all?

Please walk me through how to produce 29.01.2020 for the calculated arrival date of the last record. If 29.01.2020 is not the expected result for the calculated arrival date, please tell me what it is suppose to be and how to caluclate it.
 

crumber

New member
Local time
Today, 16:58
Joined
Jan 28, 2020
Messages
4
work plan details

work schedule
shiftplanning daydate
129.01.2020 06:00 - 13:5929.01.2020
229.01.2020 14:00 - 21:59
329.01.2020 22:00 - 05:59
130.01.2020 06:00 - 13:5930.01.2020
230.01.2020 14:00 - 21:59
330.01.2020 22:00 - 05:59
131.01.2020 06:00 - 13:5931.01.2020
231.01.2020 14:00 - 21:59
331.01.2020 22:00 - 05:59

example
working dayshiftloading day and timeorder idarrival date and timeexpected dateshift
29.01.2020
3​
30.01.2020 00:30
1149465​
30.01.2020 05:0029.01.20203
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:58
Joined
Sep 21, 2011
Messages
14,044
You are not really helping yourself here. :(
All I can determine from that is a possible shift pattern?

To get your date from the date & time field try using DATEVALUE()
For the shift, try using the function below GetShift() function

Code:
Function GetShift(dtDate As Date)
Dim intTime As Integer

intTime = Hour(dtDate)

Select Case intTime
    Case Is < 6
        GetShift = 3
    Case Is < 14
        GetShift = 1
    Case Else
        GetShift = 2
End Select
 
End Function
Or use the Switch() function in the GetShift function instead?
Code:
GetShift = Switch(intTime < 6, 3, intTime < 14, 1, intTime < 22, 2)
HTH
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:58
Joined
Feb 28, 2001
Messages
26,999
You are showing us data but the pattern isn't working to enlighten us. Can you perhaps step away from the data and tell us in plain language - without use of code - what problem are you trying to solve?
 

namliam

The Mailman - AWF VIP
Local time
Today, 16:58
Joined
Aug 11, 2003
Messages
11,696
Problem here is "a shift is always the same" principle, which ultimately dispite everyone's opinion is not true.

You can use a function like @Gasman showed, however the more safe solution is to create a table where you have something like
Code:
Start   End     shift
06:00  13:59  1
14:00  21:59  2
22:00  05:59  3
You can extent this (small) table to include seperate logic for different periodes (i.e. shift times change on a national holiday or in 2021) and simply join this table to find the proper shift. depending perhaps on day/year or national holiday.
 

crumber

New member
Local time
Today, 16:58
Joined
Jan 28, 2020
Messages
4
I will try again graphically.

This is a simple scheme, you are the employee who comes to work on the third shift on 29.01, a truck arrives to load it into the "y" company, you load and the delivery goes.

company details below "x"
companynamework dayshift
xcrunch29.01.20203 (22:00 - 06:00)
order in company x
workerorder IDloading day and timeshift
crunch11111130.01.2020 00:303 (29.01 22:00 - 30.01 06:00)

company details below "x"
companynamework dayshift
yjunior29.01.20203 (22:00 - 06:00)
ytimon30.01.20201 (06:00 - 14:00)

companydelivery idarrival date and timewhose shift will the delivery arrive ??on what working day?on what shift??
y
111111​
30.01.2020 05:00junior or timon??29.01 - 30.01 ??3 ?? 1??

then, company "y"
delivery from company "x" arrives on 30.01 at 05:00.
questions:
Who will accept the delivery?
On what day will the delivery to the "y" company arrive?
On what shift?

data I already have in Access
MAG_KODwork dayshift nrloading day and timeKOD_Porder IDarrival date and time
FL28/01/20203
29/01/2020 00:30​
ILFOV1149935
30/01/2020 06:42​
FL28/01/20203
29/01/2020 02:00​
KI1150423
29/01/2020 09:00​
FL28/01/20203
28/01/2020 22:30​
MZ261150476
29/01/2020 02:00​
MZ2628/01/20203
29/01/2020 23:00​
FL1150581
29/01/2020 03:00​
TC28/01/20203
29/01/2020 00:00​
MZ261150392
29/01/2020 04:00​

also what functions to use to get the effect I described above??

I will add that he coordinates flows, shipments and deliveries, which is why I need these two things because I have an incorrect delivery plan all the time.
 

Cronk

Registered User.
Local time
Tomorrow, 02:58
Joined
Jul 4, 2013
Messages
2,770
Try
Code:
function ArrivalShiftDay(ArrivalDateTime as Date)
   if timevalue(ArrivalDateTime)<0.25 then
       ArrivalShiftDay = dateSerial(year(ArrivalDateTime), month(ArrivalDateTime), Day(ArrivalDateTime) - 1)
   else
       ArrivalShiftDay = dateSerial(year(ArrivalDateTime), month(ArrivalDateTime), Day(ArrivalDateTime))
   endif
end function

function ArrivalShiftNumber(ArrivalDateTime as Date)
   select case timevalue(ArrivalDateTime)
      case < #6:00#
         ArrivalShiftNumber = 3   
      case < #14:00#
         ArrivalShiftNumber = 1 
      case < #22:00"
         ArrivalShiftNumber = 2 
      case else
         ArrivalShiftNumber = 3
   end select
end function
 

Users who are viewing this thread

Top Bottom