Counting Strings?

[philljp390]

Jon,

Thats excellent, thanks! I've never done a union query before, how is it done, or is there a link i could read?

 

[Mile-O]

the structures need to remain as they are

One little question...why?

 

[Mile-O]

I've never done a union query before, how is it done, or is there a link i could read?

First port of call for any help should be the Microsoft Access Help Files (just press F1 )

A Union query is basically more than one SQL statement joined with the keyword UNION. The SQL statements should have the same number of fields and their data types must be equal in each statement:

i.e.

BAD
Number, Text, Text, Date/Time, Yes/No
Number, Text, Date/Time, Text, Yes/No

GOOD
Number, Text, Text, Date/Time, Yes/No
Number, Text, Text, Date/Time, Yes/No

A simple example would be:

SELECT Forename, Surname FROM tblEmployees
UNION
SELECT Forename, Surname FROM tblApplicants;

 

[philljp390]

Thanks Mile.

Im applying the code to my tables and can get qryOne to work, but having problems with qryTwo here:

rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

Could you offer a bit of info as to what this line does? and these:

Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset

 

[Mile-O]

Could you offer a bit of info as to what this line does?

rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

This opens a connection to a recordset using a connection to a specific project (typically the current one) and then defines how the recordset is to be read. This is all detailed in the Help Files and you can also get answers on Microsoft's Knowledge Base.

And these:

Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset

These lines are setting up a connection object and a recordset object for accessing data via the ActiveX Data Objects (ADO) method. ADO is the default data access method for A2000 and above which replaced the obsolete (but still lovable Data Access Object (DAO) method.

If your error is User Defined Type not defined then read this FAQ.

 

[philljp390]

The Error is:

Data type mismatch in criteria Expression

rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

 

[Mile-O]

What's SQL with respect to that line? A string?

 

[philljp390]

Its defined as a string:

SQL = "SELECT [Vehicle], Count(*) AS [Count]" & _
" FROM qryOne" & _
" Where [IDno]='" & ID & "' and [Vehicle] Is Not Null" & _
" GROUP BY [Vehicle]"

 

[Jon K]

The IDno field in the database that you attached is a Text field.

If it is in fact a numeric field, you can change the Where line to:-
" Where [IDno]=" & ID & " and [Vehicle] Is Not Null" & _

 

[philljp390]

The data is similar, both text. All ive done is use a different table, and change the names of the field names to correspond accordingly.

 

[Jon K]

What is the function that you are using? You can copy and post it here so that we can have a look at it. Sometimes an extra space or the lack of a space may cause a problem.

 

[philljp390]

The function is exactly the same as urs. Apart from where urs says:

"Where [IDno]='" &........."

I have replaced IDno for my field name, everything else is the same.

 

[Jon K]

Does the field name contain any special characters? What is the Where line in the function?

 

[philljp390]

Found the problem, the my IDno field was set to Number as you said. Sorry, i checked the other fields.

It seems to be working now.......I hope

 

[Pat Hartman]

This DOES actually work with the code ive been working with.

- I beg to differ.

[Field1] Or [Field2] ='House' - DOES NOT test each field for the value - "House". It ONLY tests [Field2]. [Field1] is compared to itself and therefore ALWAYS returns true. When you have a compound condition condA or condB, if EITHER is True, the True Action path is take. Therefore, I maintain that this will always select all rows.

DCount("[IDno]","tblTest","[Field1] Or [Field2] ='House'")

should be:

DCount("[IDno]","tblTest","[Field1] = 'House' Or [Field2] ='House'")

 

[philljp390]

ok, my database must be special then

 

[Jon K]

Pat is absolutely correct about this:

[Field1] Or [Field2] ='House' .......... [Field1] is compared to itself and therefore ALWAYS returns true. .......... this will always select all rows.

From this table:
IDno.....Field1.....Field2
1204.....House......House
2304.....Car
3404.....Tree......House
4504.....House

though DCount("[IDno]","tblTest","[Field1] Or [Field2] ='House'") returns
IDno.....Expr1
1204.....4
2304.....4
3404.....4
4504.....4

the 4 here represents 4 rows, not 4 occurrences of "House" as you thought. If we change "House" to "Car" in the DCount expression, it will still return 4, as pointed out by Pat in the earlier post:

no matter what value you substitute for "House", including a nonexistant value, the count will be the number of rows in the table.

DCount("[IDno]","tblTest","[Field1] = 'House' Or [Field2] ='House'") will correctly return
IDno......Expr1
1204......3
2304......3
3404......3
4504......3

that is, 3 rows in the table contain the word "House".


To find the number of occurrences of "House" in each row, we need to use one DCount for each field and sum their results:-

DCount("*", "tblTest", "[Field1] = 'House' and [IDno]=" & [IDno]) +
DCount("*", "tblTest", "[Field2] = 'House' and [IDno]=" & [IDno]) & " House"

This will return
IDno......Expr1
1204......2 House
2304......0 House
3404......1 House
4504......1 House