Select Case Statement (1 Viewer)

[casaler]

I am trying to write a select case statement module which using 3 tables; COVERAGE, TABLEC1 AND TABLEC2. The statement 1) anchors the distinct value from a table (TABLEC1), 2) goes through 4 select case scenarios based on TABLEC2 ORDER, 3) updates a table (COVERAGE) with the best result and 4) loops to the next distinct value in TABLEC1.

The code complies without error, but does not give me any results. In attempting to debug it, I found the following issues:

• The order case does not set; it only sets to ‘A’ even though I request it to equal ‘D’
  
• Code does not run through all of the select cases, only looks at the first case (D)
  
• Does not update the COVERAGE table

Attached is the database.

 

[pbaldy]

For starters, what is this supposed to test?

Select Case D

D is a variable that hasn't been set. By the way, this:

Dim A, B, C, D As String

declares A, B and C as Variant, not String.

 

[casaler]

Each Case is to test the months and mileage for each VIN in TableC1 against TableC2 to find the best coverage from TableC2. A, B, C, and D is an order to process the possibilities through to determine the best coverage. I set the Case's in descending order because I thought once the best condition is met, then the code would stop and move to the next VIN.

 

[casaler]

Okay, I changed the code. It now reads each VIN on TableC1 and copies to the COVERAGE table, but it does not go through the logic. That is, it assigned all the same coverage; there is 1 that should be different (VIN 34523N1CN7AP0EL822619572960A should have coverage PWRTRN).

Attached is the updated database.

 

[pbaldy]

You have a bunch of code that does absolutely nothing. No matter what happens with all the case stuff, the AddNew at the end will do the same thing. To do something different, you'd have to set values in the appropriate Case. In words, what are you trying to achieve? What is the logic that the code is supposed to apply?

 

[casaler]

what are you trying to achieve?
"I am trying to get the code to find the best coverage match from TableC2 (TC2) based on months or miles when compared to TableC1's (TC1) INSM (month) or miles for each VIN when the Order equals A, B, C or D."

What is the logic that the code is supposed to apply?
"When TC2 order = D, is TC1 INSM < TC2 months and TC1 mileage > TC2 miles, true or false? When TC2 order = D, is TC1 INSM > TC2 months and TC1 mileage < TC2 miles, true or false? When TC2 order = D, is TC1 INSM > TC2 months and TC1 mileage > TC2 miles, true or false? It is suppose to go through each TC2 order until a true condition is met. And when it is met, write the true coverage to the Coverage table"

As I stated, the code does all but does not test each logic statement.