Range Variable is it possible ?

[Garcimat]

Hi guys

I would like to assign a range of values to one variable....
var1km = 10 to 50
var2km= 55-70
var3km =85-200

I can not allow overlapping..... for example
when I give permission to someone to work between 10 and 50km I can not have a second work group working in the same limits.... I am trying to do it with arrays but I have failed so far. My other Idea is to have the two limits in different columns in my table and the use a sql query, but I have not tried it yet.

Any help ? Thanks in advanced

 

[arnelgp]

you can use Form for your data entry so you can Control the Validation.
see the form in design view and the Code behind it.

 

[Garcimat]

That is very impressive my friend, thank you so much I will study and learn from your codding, definitely a higher level.
I already have my data entry form done, I was planning to use drop boxes because the limits would be predefined, but your example have opened up a new perspective. I can have a form just to check the overlapping, I can have a sub form ..... it is blowing my mind right now hahaha

 

[arnelgp]

post again if you got stuck up.

 

[plog]

To what end? You mention "a variable" which implies coding, but you didn't post any code and don't provide much context.

Can you explain the bigger picture? One paragraph with what your organization does--no database jargon. Then another paragraph describing what your database will help with--limited database jargon.

 

[Garcimat]

To what end? You mention "a variable" which implies coding, but you didn't post any code and don't provide much context.

Can you explain the bigger picture? One paragraph with what your organization does--no database jargon. Then another paragraph describing what your database will help with--limited database jargon.

We do safeworking for the rail industry, I am building a system to manage the works when we have shutdowns on the rail lines.

 

[Pat Hartman]

As usual, I have a different take on the question

You need two variables. One for the low end of the range and the other for the high end of the range. In the beforeUpdate event, you validate the relationship and ensure it hasn't been used for the time period:

If Me.txtLow > Me.txtHigh Then
    Msgbox "Low must be <= High.",vbOKOnly
    Cancel = True
    Me.txtLow.SetFocus
    Exit Sub
End If
If dCount("*", "tblWorkAssignment", "WorkDate = #" & Me.txtWorkDate & "# AND RangeID = " & Me.txtRangeID) > 0 Then
    Msgbox "Team already working this range.",vbOKOnly
    Cancel = True
    Me.txtRangeID.SetFocus
    Exit Sub
End If

The Range table looks like this:
RangeID (autonumber, PK)
Low
High

Then in your work assignments form, you assign the RangeID to a record. I'm assuming the assignment is for a day. In the work assignments table, you would define a unique index on WorkDate + RangeID. To create a multi-field index, you MUST use the Indexes dialog. If you don't know how, post back.

Your work assignments may have a third variable for location. Just adjust the code to include it.

 

[Garcimat]

As usual, I have a different take on the question

You need two variables. One for the low end of the range and the other for the high end of the range. In the beforeUpdate event, you validate the relationship and ensure it hasn't been used for the time period:

If Me.txtLow > Me.txtHigh Then
    Msgbox "Low must be <= High.",vbOKOnly
    Cancel = True
    Me.txtLow.SetFocus
    Exit Sub
End If
If dCount("*", "tblWorkAssignment", "WorkDate = #" & Me.txtWorkDate & "# AND RangeID = " & Me.txtRangeID) > 0 Then
    Msgbox "Team already working this range.",vbOKOnly
    Cancel = True
    Me.txtRangeID.SetFocus
    Exit Sub
End If

The Range table looks like this:
RangeID (autonumber, PK)
Low
High

Then in your work assignments form, you assign the RangeID to a record. I'm assuming the assignment is for a day. In the work assignments table, you would define a unique index on WorkDate + RangeID. To create a multi-field index, you MUST use the Indexes dialog. If you don't know how, post back.

Your work assignments may have a third variable for location. Just adjust the code to include it.

Thanks Pat
Most of the works will last a day, some couple days, some weeks.... I could have an "Active" and "Cancelled" tag to change the date or create a duplicate record for the "Active" records.... That is an idea as well.
I never used an multi-field index, I will search for it.
Sometimes we have more than one line having a shutdown, so I also have to separate the limits/ranges by section (Train Line). I haven't doing any codding for 20 years, and I am loving it again, there are always several ways to achieve the same result, Thank you for all members.

 

[arnelgp]

another simple sample with multiple train/kms.

 

[gemma-the-husky]

One thing - you need to be careful of the number type, and potential gaps. Integers are OK

You can have 1-50, 51-60 etc
With real numbers, you can have a number like 50.5 that falls in the gap.

If you just want to split your data, into bands, you can use the little known partition function
You can split a range of test scores into blocks of 20, say, and access will split the data for you, and determine where each row falls.

 

[Cronk]

Why variables? Hold the range values in a lookup table.

 

[Pat Hartman]

I do believe I defined the table that holds the ranges and was specific that they were two fields.

 

[Garcimat]

another simple sample with multiple train/kms.

That is great because we can have multiple SWO (Service Work Order) that will say the limits of the Job (LIne X from KP (Kilometre Post) 1 to 350.
inside this SWO we can have 10. 30,80... permits to work (PTF Permit to Foul the Track) every work group have one PTF and they can not overlap never ever. I will use your example changing "group" for "SWO". Thank you very much for your help.

 

[Garcimat]

One thing - you need to be careful of the number type, and potential gaps. Integers are OK

You can have 1-50, 51-60 etc
With real numbers, you can have a number like 50.5 that falls in the gap.

If you just want to split your data, into bands, you can use the little known partition function
You can split a range of test scores into blocks of 20, say, and access will split the data for you, and determine where each row falls.

that is true the numbers can be something like 10.250 to 33.750

 

[Garcimat]

arnelgp I have created a form "frmLimits' with some dynamic combos, , what I am thinking is to click the button to collect the info to the tblData and then look for the overlapping in there using global variables, once validated this data will be insert in the main table where I have all the Permit and Permit Holder Information...
what do you think ?

 

[Garcimat]

another simple sample with multiple train/kms.

I have made some changings to the code and it is working....

Private Sub btnValidate_Click()
Dim HasOverlap As Boolean
Dim X As Variant, Y As Variant
Dim strMsg As String

' initial return of the function
HasOverlap = True
X = Me.cmbKP1
Y = Me.cmbKp2

' start and end of 0 is not allowed?
If X = 0 Or Y = 0 Then
MsgBox "Zero value is not allowed"
Exit Sub
End If

' check if cmbKP1 is smaller than cmbKP2
If X > Y Then
MsgBox "Limit Start Point should be less than Limit Finish Point"
Exit Sub
End If

If X = Y Then
MsgBox "Limit Start Point should not be equal Limit Finish Point"
Exit Sub
End If

' open a recordset from Same Table
' and check for overlap
With CurrentDb.OpenRecordset("SELECT * from tblData where " & "((" & X & ") Between Kp1 And KP2) Or " & "((" & Y & ") Between Kp1 And KP2)", dbOpenSnapshot)

If Not (.BOF And .EOF) Then
.MoveFirst
strMsg = strMsg & "--------------------------------" & vbCrLf
strMsg = strMsg & "ID" & vbTab & "KP1" & vbTab & "KP2" & vbCrLf
strMsg = strMsg & "--------------------------------" & vbCrLf
Else
HasOverlap = False
End If
' put the overlapping records to a variable

Do Until .EOF
strMsg = strMsg & !ID & vbTab & !KP1 & vbTab & !KP2 & vbCrLf
.MoveNext
Loop
.Close
End With

If HasOverlap Then
MsgBox "Your entry will overlap with: " & vbCrLf & vbCrLf & strMsg
End If

End Sub

 

[Garcimat]

another simple sample with multiple train/kms.

I am trying to insert in the sql query a criteria to filter the "Section" and the "Direction", I can have more than one section under works and there is a possibility to give a permit for example from Km10 - Km15 on the up track and another from Km14 to 20 on the down track..... I am doing a SQL course and it is helping me a lot to understand it.

 

[gemma-the-husky]

You probably already have this, but we tend to express a range as

"between x and y"

There is no order in the sense of ascending/descending. The expression "between 0 and 10" retrieves values from 0 to 10 inclusive.
Hence if you have "between 0 and 10", then the next range is "between 11 and 20", but in this case values of 10.5 are missed.
If you express the ranges as "between 0 and 10" and "between 10 and 20" then the value 10 will be in both results.

In your example, in order to distinguish between uptracks and downtracks, you would need some other specific uptrack/downtrack indicator in your filter and data as well as the range marker. so that uptrack 14 is not the same location as downtrack 14. I take it we are talking about carriageways on a road - that sort of thing?

If you can't use integers then instead of "between" you need something like this to ensure all potential numbers are dealt with once and once only.

>=0 and <=10
>10 and <= 20
>20 and <=30

Finally would the limits on an uptrack be different to a downtrack?

ie, lets say on the uptrack, you have mile posts 3.0 to 10.4, and then 10.4 to 18.6. so which section includes 10.4? Section 1 or section 2?
then on the down track, you have 18.6 down to 10.4 and 10.4 down to 3.0. So is 10.4 on the uptrack treated as falling in the same subsection as on the downtrack, or is it different. It can be either - so you just need to be sure how it is resolved.

 

[Garcimat]

You probably already have this, but we tend to express a range as

"between x and y"

There is no order in the sense of ascending/descending. The expression "between 0 and 10" retrieves values from 0 to 10 inclusive.
Hence if you have "between 0 and 10", then the next range is "between 11 and 20", but in this case values of 10.5 are missed.
If you express the ranges as "between 0 and 10" and "between 10 and 20" then the value 10 will be in both results.

In your example, in order to distinguish between uptracks and downtracks, you would need some other specific uptrack/downtrack indicator in your filter and data as well as the range marker. so that uptrack 14 is not the same location as downtrack 14. I take it we are talking about carriageways on a road - that sort of thing?

If you can't use integers then instead of "between" you need something like this to ensure all potential numbers are dealt with once and once only.

>=0 and <=10
>10 and <= 20
>20 and <=30

Finally would the limits on an uptrack be different to a downtrack?

ie, lets say on the uptrack, you have mile posts 3.0 to 10.4, and then 10.4 to 18.6. so which section includes 10.4? Section 1 or section 2?
then on the down track, you have 18.6 down to 10.4 and 10.4 down to 3.0. So is 10.4 on the uptrack treated as falling in the same subsection as on the downtrack, or is it different. It can be either - so you just need to be sure how it is resolve

You probably already have this, but we tend to express a range as

"between x and y"

There is no order in the sense of ascending/descending. The expression "between 0 and 10" retrieves values from 0 to 10 inclusive.
Hence if you have "between 0 and 10", then the next range is "between 11 and 20", but in this case values of 10.5 are missed.
If you express the ranges as "between 0 and 10" and "between 10 and 20" then the value 10 will be in both results.

In your example, in order to distinguish between uptracks and downtracks, you would need some other specific uptrack/downtrack indicator in your filter and data as well as the range marker. so that uptrack 14 is not the same location as downtrack 14. I take it we are talking about carriageways on a road - that sort of thing?

If you can't use integers then instead of "between" you need something like this to ensure all potential numbers are dealt with once and once only.

>=0 and <=10
>10 and <= 20
>20 and <=30

Finally would the limits on an uptrack be different to a downtrack?

ie, lets say on the uptrack, you have mile posts 3.0 to 10.4, and then 10.4 to 18.6. so which section includes 10.4? Section 1 or section 2?
then on the down track, you have 18.6 down to 10.4 and 10.4 down to 3.0. So is 10.4 on the uptrack treated as falling in the same subsection as on the downtrack, or is it different. It can be either - so you just need to be sure how it is resolved.

Thanks for your help.... yes the tracks can have same limits ( 4km to 5km) in case of a Station or Level Crossing only with one permit... and it can be totally different... (4km to 5km on the Down track for one work group, lets say doing maintenance on the stations lights with permit number 01 and another work group with permit number 25 having a heavy machine tampering with limits from (1Km to 200Km) on the Up Track. This is why I need to filter by Section, Direction and Km... maybe two different queries ? one check for UP one for Down and one for Up and Down ?

 

[gemma-the-husky]

Thanks for your help.... yes the tracks can have same limits ( 4km to 5km) in case of a Station or Level Crossing only with one permit... and it can be totally different... (4km to 5km on the Down track for one work group, lets say doing maintenance on the stations lights with permit number 01 and another work group with permit number 25 having a heavy machine tampering with limits from (1Km to 200Km) on the Up Track. This is why I need to filter by Section, Direction and Km... maybe two different queries ? one check for UP one for Down and one for Up and Down ?

Ah - so it's railway, rather than roads/highways.

I don't think it's so much two different directions, I think it's more that you need a structure that treats the up and down tracks as different entities - so when you select "a track", the selection itself indicates/knows whether it's an "up" or a "down" track. How do you distinguish between the up and down tracks out of interest?

What happens if you have a portion of railway with more than 2 tracks, or a section with only 1 track? is there a convention for the up/down designation?