"Where" - did I go wrong...

[amerifax]

I am fairly new to VBA. I have a query set up, but I am not sure what I have done wrong. What I need is to pull all the records from the builder table where the SC is equal to any one of these ("14","20","28","30","40","45","51","59","64","66","67")

AND any of these "yr_b" through "yr_s" needs to be greater than 2 so b or c or d or e. I know there has to be an easier way to do this, but I am just not that good in VBA quite yet.

Any help would be greatly appreciated.

Right now this is what my query looks like:

SELECT BUILDER.*
FROM BUILDER
WHERE (((BUILDER.YR_B)>2)) OR (((BUILDER.YR_C)>2)) OR (((BUILDER.YR_D)>2)) OR (((BUILDER.YR_E)>2)) OR (((BUILDER.YR_F)>2)) OR (((BUILDER.YR_G)>2)) OR (((BUILDER.YR_H)>2)) OR (((BUILDER.YR_I)>2)) OR (((BUILDER.YR_J)>2)) OR (((BUILDER.YR_K)>2)) OR (((BUILDER.YR_L)>2)) OR (((BUILDER.YR_M)>2)) OR (((BUILDER.YR_n)>2)) OR (((BUILDER.YR_o)>2)) OR (((BUILDER.YR_p)>2)) OR (((BUILDER.YR_q)>2)) OR (((BUILDER.YR_r)>2)) OR (((BUILDER.YR_s)>2)) AND (((Left([BUILDER.SC],2)) In ("14","20","28","30","40","45","51","59","64","66","67")));

Thanks in advance.

Heather

 

[jdraw]

I think you should tell us what you are trying to do. It seems you have a database/table structure issue, but, until we know more, we're just guessing.

 

[plog]

To expand on jdraw, all those fields named like 'YR_' are a good indication that your table isn't properly structured. Most likely all those fields should be in another table. What exactly do those fields represent?

Another indication is that you are using the first 2 characters of the field 'SC', that indicates the entire field SC contains more than one discrete piece of information, which means it should be contained in more than one field. If the first 2 characters of SC are important by themselves, then they should be in their own field.

As for your query my guess is that it has to do with the AND portion of your WHERE clause. For simplicities sake, think of your WHERE clause as having 2 parts--all those criteria seperated by 'OR's and then the other part being that IN criteria.

Part1 = criteria1 OR criteria2 OR criteria3 OR...
Part2 =Left([BUILDER.SC],2) In ("14","20","28","30","40","45","51","59","64","66","67")

Right now you're query is putting them together like this

Part1 AND Part2

You need to group your parts together with another 2 sets of parenthesis around each part to seperate them like this:

(Part1) AND (Part2)

If that AND was an OR you wouldn't have this problem, but because it is, its causing ambiguity and you need to define where the ORs end and the AND begin. Confusing, but that's what I think is happening.

 

[amerifax]

OK...that does make sense. The yr fields are total for a given year. The reason we use a,b,c... etc is because it automatically updates each year. YR_A is the current YTD, YR_B is 2011 and so on. The field SC is actually a 6 character field. The field as a whole is an identifier for a town, city or village. It is formatted as 66-291. The 66 tells me the County and the 291 tells me the town, village or city. I need this field to stay together. What I am trying to do is this:

I only want to see the builders that are within the 11 counties I specified and that have a total greater than 2 in any of those "YR" fields. I am sure there is a way better and more simple way to write the query, I am just fairly new to this. Actually transitioning from the old dBase...

Thanks for your help so far!

Heather

 

[jdraw]

Heather,
You have some data base design issues.
Read this article for general info
http://forums.aspfree.com/attachment.php?attachmentid=4712

 

[plog]

Yeah, fix your database structure. That 6 character code should be put into 2 fields, all that year data should be put in its own table. I'd even think about making a table to hold/designate all those Counties you want to query for instead of using that IN function.


This query becomes trivial with the proper structure.

 

[amerifax]

I will read the article you suggest. Thanks for your help. I know I have a lot to learn about Access. Just trying to get up and running along the way as I learn. Do you a know of a way to get the query to work for now, until I can understand this article fully. I feel like I am so close...yet probably so far.

Thanks again,

Heather