Complex Query

[Drand]

Ok. I have 6 countries in the raw data file. Each country has 1 record where the field is blank for each country. The rule is set to true for this field for each country, but the function does not find them.

 

[arnelgp]

better to upload your "raw" table and the "rule" table for me to test.
also make sure the spelling and the spaces on the fieldname is Exactly as you
have both in the "raw" table and "rule" table. any "spaces" less or more on
on the field names on either table will influence the result.

if it is too big to upload, upload on Onedrive, google drive, dropbox, etc and share the downloadable link.

 

[Drand]

Hope this file size is Ok

 

[arnelgp]

it is because you Change the Structure of table tblDataRules.
the checkbox now starts at 3 (0 based) or 4th field.

change the code to this:

...
...
Do Until .EOF
'For i = 5 To rRule.Fields.Count - 1
For i = 3 To rRule.Fields.Count - 1
...
...

 

[Drand]

Thank you. I am sorry if I messed this up. This level of coding is beyond my capabilities, but I am really pleased it is now working,

You're brilliant!

 

[Pat Hartman]

This level of coding is beyond my capabilities

A properly normalized schema would be easier to work with and not require you to create functions using VBA as well as being expandable. But as long as you're happy.

 

[Drand]

Hope this file size is Ok

Hi arnelgp

I am sorry to trouble you again with this. Ultimately, my back end will end up on a sharepoint site. When I tested this after migrating the tables to sharepoint it no longer works. Do I need to modify the "set" rules?

I have tried changing the column number but to no avail.

Thanks

 

[arnelgp]

sorry, i have no idea about sharepoint.