Full Disclosure: I did post issue on REDDIT yesterday but not in as much detail and I haven't received any assistance.
I am creating a Database to track IPs that we block. I would like to create a Validation Rule that would not allow you to enter an IP within a CIDR range(This is in another table in the same db). I am also thinking that I don't want them to block known scanners(I haven't created table for this but it is not complicated IPAddress and owner). I know Access will not understand IP Addresses so I changed them to IP numbers [IPNumber]. I then have a field for Low and High CIDR blocks.
So here is the process that I am looking for. Someone puts in an IP Address [IP Address] in table [IPs] the address is converted to a IP number [IP Number], that number is compared to [Low Number] and [High Number] if table [CIDR Range] if it is within that range the validation fails as the IP is already blocked the low and high range need to be in the same record.
Table IP Table CIDR Range IP Address IP Number CIDR Range Low IP Low Number High IP High Number 10.0.0.5 167772165 10.0.0.1/27 10.0.0.1 167772161 10.0.0.31 167772191 10.2.0.6 167903238 10.3.0.1/24 10.3.0.1 167968769 10.3.0.254 167969022
So 10.0.0.5 should not be allowed to be entered as it is in the range of 10.0.0.1/27. 10.2.0.6 should be allowed to be entered as it is not in a CIDR range but I don't want access to pick a low from one record and a high from another. Any help would be greatly appreciated. A great nice to have would be Validation text that says IP Address is covered by[CIDR Range].