Read column names

[bustersports]

I am trying to import a spreadsheet with the following data types.

# Carrier Remit Zip Bill To Number
1 FedX 60693 968516528
2 FedX
3 FedX 60693

Each spreadsheet will have 49 fields, can be as few as one or as many as several thousand line items.

I need to compare the imported spreadsheet against field requirements. No one carrier has the same requirements for each field.

Item FedX UPS Yellow DGF DHL UVL
CARRIER Is Not Null Is Not Null Is Not Null Is Not Null Is Not Null Is Not Null
REMITZIP Is Not Null Is Null Is Not Null Is Null Is Null Is Not Null
Bill To Is Not Null Is Not Null Is Not Null Is Not Null Is Not Null Is Not Null

So, in the FedEx sample data, I would need to verify the following:

1. Carrier is not blank
2. Bill to number is not blank

If any error, error message is written to record. In my example, error messages would read as follows.

Record 2 - Error - remit zip required. Error - bill to number required.
Record 3 - Error - bill to number required

In summary, I would import the data, have checks run to verify data accuracy, if errors found, error message

Hopefully this is enough information.

Thank you.

 

[MajP]

This is not very clear. Please restate. If I understand you have several imported tables. Each table has 49 fields. Each field has a set of rules to be validated. Each table represents a different carrier and each carrier may have different validation rules for each field. As far as you have shown validation is simply if it can be null or null. Is that the only validation rule.

Although this can be done in sql, I think it could get labor intensive building a lot of queries. My first guess without understanding the rules would be a single routine that loops the fields and records. It checks the rule for that field and if error writes it to the error message field (each message gets concatentated). This assumes that a lot of rules are the same among carriers. If each carrier has different rules for each and every field this to would get labor intensive.

Need more information in the rules and a clearer description for a better answer.

 

[bustersports]

Thanks MajP. Let me try to clarify, you are close on what I am trying to do.

1. It is only 1 imported table, coming from a spreadsheet. The same spreadsheet is used for each carrier, but they do not have the same validation rules. Some fields are required by each carrier, some are not. Ex: Each carrier must include their name, but not all are required to enter city names.
2. Each table does have 49 fields.
3. Each carrier has their own validation rules. All are one of three possibilities, required blank field, data required, data is optional. However, of the 49, only about 18 are not 100% exact.
4. Each spreadsheet would be imported, processed. If no errors, file is submitted for payment, then results archived. Next spreadsheet would then be imported. It may be next day, or several weeks before used again.
5. You are correct on the error messages.

Hopefully this helps to clarify.

 

[MajP]

To verify.
1) When you import the spreadsheet is there a field that determines the Carrier?
2) As far as I can tell there is just two types of rules "must be null" and "cannot be null". If it is either than that is really not required to check.
3) Do you keep these rules somewhere now?
4) Do these rules change? Do the amount of carriers change?

Lets assume 4 is true. Then I would build a second tables of rules. It would be like this.

tblRules
carrierName
fieldName
fieldNull (boolean Y if must be null, N if cannot be null)

so my data might be like this

FEDEX BillAddress N
FEDEX SomeField Y
FEDEX SomeField2 Y
FEDEX SomeField3 N
UPS SomeField Y
UPS SomeField7 N

This would make it very easy to add and modify rules.[/b]


So here is some pseudo code off the top of my head. Not tested and not done in the vba editor.

dim RSimport as dao.recordset
dim RSRules as dao.recordset
dim fld as dao.field
dim strCarrier as string
dim strFieldName as string
dim strErrorMsg as string
dim importNull as boolean
dim fieldNull as boolean
set rsImport = currentdb.openrecordset ("tblImport",dbopendynaset)


'assume the first field has the carrier name

strCarrier = rsImport.fields(0)
'get only the rules for that carrier

set rsRules = currentdb.operecordset ("select * from tblRules where CarrierName = '" & strCarrier & "'", dbopendynaset)

'loop the records in the import table
do while not rsImport.eof
  rsRule.Movefirst
  'loop the rules for that carrier
  do while not RSrules.eof
    strFieldName = RSrules.FieldName
    'determine if it must be null or cannot be null
    fieldNull = rsRules.fieldNull
    'determine if the field has a value
    importNull = isNull(rsImport.fields(strFieldName))
    'if it must be null but has a value
    if fieldNull and not importNull then
      strErrorMsg = rsImport.fields("errormsg") & "Field " & strFieldName & " Should be null but has a value " 
    'if it cannot be null but is null
    else if not field null and importNull then
      strErrorMsg = rsImport.fields("errormsg") & "Field " & strFieldName & " Cannot by null "
    end if   
    
    if not strErrorMsg = "" then
      rsImport.edit   
      rsImport.fields("errorMsg") = strErrorMsg
      rsImport.update
      strErrorMsg = ""
    end if
    rsRules.moveNext
  loop
  rsImport.moveNext
loop

 

[bustersports]

Thanks MajP, I will give this a try.

To address your questions.

1) When you import the spreadsheet is there a field that determines the Carrier?
Yes, there is field identfying the carrier

2) As far as I can tell there is just two types of rules "must be null" and "cannot be null". If it is either than that is really not required to check.
There basically are two types, however with a slight variation in that some fields must be completed if another field is completed. I know how to check for that.

3) Do you keep these rules somewhere now?
Yes, I have already built a table, almost identical to your recommendation

4) Do these rules change? Do the amount of carriers change?
Both change very seldom.

 

[MajP]

I will give this a try

What I provided was a concept. I typed it directly into the thread. I am very confident in the basic logic and the approach, but it is just a starting point. You will have to modify to make work.

 

[bustersports]

Thanks MajP. Works. Very much appreciated.