counting unique words etc

[blake7]

Hi I have a database with a table, on fieldname is called "boiler type" I
would like to count the number of times a particular word appears in the
column and display this on a form in a text box, whats the best way ?
Thanks all

 

[Lou]

Hi I have a database with a table, on fieldname is called "boiler type" I
would like to count the number of times a particular word appears in the
column and display this on a form in a text box, whats the best way ?
Thanks all

Let us presume that the "particular word" is held in a screen text
field. The query will look for the value of that field at the
beginning, middle or end of the database column [boiler type].

The instr function will return 0 if the screen value is not found in
the column.
Therefore, these conditions should suffice to count the number of
occurrences of the string when found in the column.

SELECT Me.screenvariable.text, count(*)
from [boiler type]
where [boiler type] = Me!ScreenVariable.Text
or instr( [boiler type], Me!ScreenVariable.Text & ' ' ) = 1
or instr( [boiler type], ' ' & Me!ScreenVariable.Text & ' ' ) > 0
or instr( [boiler type], ' ' & Me!ScreenVariable.Text )
= len( [boiler type] ) - len( Me!ScreenVariable.text ) - 1

I am unsure if this addresses your question because the title
"Counting unique words etc" does not correspond to what I perceive to
be the question asked in the text of the post.

 

[Ken Sheridan]

Add the following function to a standard module:

Public Function FindWord(strFindIn As String, strWord As String) As Boolean

Const PUNCLIST = " .,?!:;(){}[]"
Dim intPos As Integer

intPos = InStr(strFindIn, strWord)

If intPos > 0 Then
' is it at start of string
If intPos = 1 Then
' is it whole string?
If Len(strFindIn) = Len(strWord) Then
FindWord = True
Exit Function
' is it followed by a space or punctuation mark?
ElseIf InStr(PUNCLIST, Mid(strFindIn, intPos + Len(strWord), 1))

0 Then

FindWord = True
Exit Function
End If
Else
' is it precedeed by a space or punctuation mark?
If InStr(PUNCLIST, Mid(strFindIn, intPos - 1, 1)) > 0 Then
' is it at end of string or followed by a space or
punctuation mark?
If InStr(PUNCLIST, Mid(strFindIn, intPos + Len(strWord), 1))

0 Then

FindWord = True
Exit Function
End If
End If
End If
End If

End Function

As the ControlSource property of a text box on the firm use an expression
which calls the DCount function, with the above function called as its
criteria argument. If for instance you want to find the number of
occurrences of the word 'condensing' in the column the expression would be:

=DCount("*", "[YourTableName]", "FindWord([Boiler Type],""condensing"")")

The FindWord function assumes that a 'word' is either the whole contents of
the column or is a substring within the column, either at the start or
preceded by a space or a punctuation mark contained in the PUNCLIST constant,
or either at the end or followed by a space or a punctuation mark contained
in the PUNCLIST constant (which you can of course add to if necessary).

Rather than an embedded literal string such as ""condensing"" you can of
course use a variable such as the name of another control on the form which
contains the 'word' sought.

Ken Sheridan
Stafford, England

 

[John Spencer]

To count instances of a word in a field you can use an expression like
(Len(X)-Len(Replace(X,Y))) / Len(Y)

So a select query that looks like the following should work


SELECT (Len([Boiler Type]) - Len(Replace([Boiler Type],[Find Word])))/
Len([Find Word]) as CountOfWord
FROM [YourTable]
WHERE [Boiler Type] Like "*" & [Find Word] & "*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

WHOOPS. Let's try that again. I seem to have forgotten the third
argument in the replace function.

To count instances of a word in a field you can use an expression like
(Len(X)-Len(Replace(X,Y,""))) / Len(Y)

So a select query that looks like the following should work


SELECT (Len([Boiler Type]) - Len(Replace([Boiler Type],[Find
Word],"")))/ Len([Find Word]) as CountOfWord
FROM [YourTable]
WHERE [Boiler Type] Like "*" & [Find Word] & "*"


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[blake7]

Hi Ken, I followed your reply and added the code to a new standard module and
then created a new form with a text box and added the expression to the
control source, I changed the word "condensing" to "isar" because this is one
of the words I want to count, when I open the form it returns #Error in the
text box, have I done something wrong ??

Add the following function to a standard module:

Public Function FindWord(strFindIn As String, strWord As String) As Boolean

Const PUNCLIST = " .,?!:;(){}[]"
Dim intPos As Integer

intPos = InStr(strFindIn, strWord)

If intPos > 0 Then
' is it at start of string
If intPos = 1 Then
' is it whole string?
If Len(strFindIn) = Len(strWord) Then
FindWord = True
Exit Function
' is it followed by a space or punctuation mark?
ElseIf InStr(PUNCLIST, Mid(strFindIn, intPos + Len(strWord), 1))

0 Then

FindWord = True
Exit Function
End If
Else
' is it precedeed by a space or punctuation mark?
If InStr(PUNCLIST, Mid(strFindIn, intPos - 1, 1)) > 0 Then
' is it at end of string or followed by a space or
punctuation mark?
If InStr(PUNCLIST, Mid(strFindIn, intPos + Len(strWord), 1))

0 Then

FindWord = True
Exit Function
End If
End If
End If
End If

End Function

As the ControlSource property of a text box on the firm use an expression
which calls the DCount function, with the above function called as its
criteria argument. If for instance you want to find the number of
occurrences of the word 'condensing' in the column the expression would be:

=DCount("*", "[YourTableName]", "FindWord([Boiler Type],""condensing"")")

The FindWord function assumes that a 'word' is either the whole contents of
the column or is a substring within the column, either at the start or
preceded by a space or a punctuation mark contained in the PUNCLIST constant,
or either at the end or followed by a space or a punctuation mark contained
in the PUNCLIST constant (which you can of course add to if necessary).

Rather than an embedded literal string such as ""condensing"" you can of
course use a variable such as the name of another control on the form which
contains the 'word' sought.

Ken Sheridan
Stafford, England

Hi I have a database with a table, on fieldname is called "boiler type" I
would like to count the number of times a particular word appears in the
column and display this on a form in a text box, whats the best way ?
Thanks all

 

[blake7]

Hi John, I tried adding your code to a new query and I changed the "find
word" areas to "Isar", when I run the query a prompt appears (enter parameter
value) with Isar as the prompt, if i enter isar in the box and run the query
it finds 569 instances but in one long column with "1"'s in it, it does not
return 569 as a total sum ?, have i done something wrong ? Thanks

To count instances of a word in a field you can use an expression like
(Len(X)-Len(Replace(X,Y))) / Len(Y)

So a select query that looks like the following should work


SELECT (Len([Boiler Type]) - Len(Replace([Boiler Type],[Find Word])))/
Len([Find Word]) as CountOfWord
FROM [YourTable]
WHERE [Boiler Type] Like "*" & [Find Word] & "*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi I have a database with a table, on fieldname is called "boiler type" I
would like to count the number of times a particular word appears in the
column and display this on a form in a text box, whats the best way ?
Thanks all

 

[John Spencer]

My mistake. I misunderstood what you wanted. I was counting the number of
times the word would appear in a field in a single record.

What you want is the number of records that contain the word "isar". If the
field contains only the word isar then you can use

SELECT Count(*)
FROM YourTable
WHERE [Boiler Type] = 'isar'

On a form, I would probably use the DCOUNT function to do this and use it as
the control source of a textbox.

Set the Control Source to:
=DCount("*","[Your Table Name]","[Boiler Type] LIKE '*isar*'")

If the field contains exactly "isar" and no other characters you can make this
more efficient using
=DCount("*","[Your Table Name]","[Boiler Type] ='isar'")

Hope this helps. Sorry for misunderstanding your request.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John, I tried adding your code to a new query and I changed the "find
word" areas to "Isar", when I run the query a prompt appears (enter parameter
value) with Isar as the prompt, if i enter isar in the box and run the query
it finds 569 instances but in one long column with "1"'s in it, it does not
return 569 as a total sum ?, have i done something wrong ? Thanks

To count instances of a word in a field you can use an expression like
(Len(X)-Len(Replace(X,Y))) / Len(Y)

So a select query that looks like the following should work


SELECT (Len([Boiler Type]) - Len(Replace([Boiler Type],[Find Word])))/
Len([Find Word]) as CountOfWord
FROM [YourTable]
WHERE [Boiler Type] Like "*" & [Find Word] & "*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi I have a database with a table, on fieldname is called "boiler type" I
would like to count the number of times a particular word appears in the
column and display this on a form in a text box, whats the best way ?
Thanks all

 

[blake7]

Thanks John, thats running fine, thanks for your time. Regards Tony

My mistake. I misunderstood what you wanted. I was counting the number of
times the word would appear in a field in a single record.

What you want is the number of records that contain the word "isar". If the
field contains only the word isar then you can use

SELECT Count(*)
FROM YourTable
WHERE [Boiler Type] = 'isar'

On a form, I would probably use the DCOUNT function to do this and use it as
the control source of a textbox.

Set the Control Source to:
=DCount("*","[Your Table Name]","[Boiler Type] LIKE '*isar*'")

If the field contains exactly "isar" and no other characters you can make this
more efficient using
=DCount("*","[Your Table Name]","[Boiler Type] ='isar'")

Hope this helps. Sorry for misunderstanding your request.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John, I tried adding your code to a new query and I changed the "find
word" areas to "Isar", when I run the query a prompt appears (enter parameter
value) with Isar as the prompt, if i enter isar in the box and run the query
it finds 569 instances but in one long column with "1"'s in it, it does not
return 569 as a total sum ?, have i done something wrong ? Thanks

To count instances of a word in a field you can use an expression like
(Len(X)-Len(Replace(X,Y))) / Len(Y)

So a select query that looks like the following should work


SELECT (Len([Boiler Type]) - Len(Replace([Boiler Type],[Find Word])))/
Len([Find Word]) as CountOfWord
FROM [YourTable]
WHERE [Boiler Type] Like "*" & [Find Word] & "*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


blake7 wrote:
Hi I have a database with a table, on fieldname is called "boiler type" I
would like to count the number of times a particular word appears in the
column and display this on a form in a text box, whats the best way ?
Thanks all

 

[blake7]

Hi John, just another bit of help if you can, I have now created six text
boxes on my form and copied the dcount to them all and changed the "like"
statement to look for all the boiler names, everything is displayed correctly
in the six boxes, but I wanted to add some date parameters to search between
( I have a date column on my table) I placed two boxes on the form and set
the parameter value to search "between" the two dates in the text boxes, but
it always shows me the entire count for each boiler from the whole table!! it
does not follow my input dates, what is restricting it would you know - I
used the following - between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
Thanks in advance

My mistake. I misunderstood what you wanted. I was counting the number of
times the word would appear in a field in a single record.

What you want is the number of records that contain the word "isar". If the
field contains only the word isar then you can use

SELECT Count(*)
FROM YourTable
WHERE [Boiler Type] = 'isar'

On a form, I would probably use the DCOUNT function to do this and use it as
the control source of a textbox.

Set the Control Source to:
=DCount("*","[Your Table Name]","[Boiler Type] LIKE '*isar*'")

If the field contains exactly "isar" and no other characters you can make this
more efficient using
=DCount("*","[Your Table Name]","[Boiler Type] ='isar'")

Hope this helps. Sorry for misunderstanding your request.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John, I tried adding your code to a new query and I changed the "find
word" areas to "Isar", when I run the query a prompt appears (enter parameter
value) with Isar as the prompt, if i enter isar in the box and run the query
it finds 569 instances but in one long column with "1"'s in it, it does not
return 569 as a total sum ?, have i done something wrong ? Thanks

To count instances of a word in a field you can use an expression like
(Len(X)-Len(Replace(X,Y))) / Len(Y)

So a select query that looks like the following should work


SELECT (Len([Boiler Type]) - Len(Replace([Boiler Type],[Find Word])))/
Len([Find Word]) as CountOfWord
FROM [YourTable]
WHERE [Boiler Type] Like "*" & [Find Word] & "*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


blake7 wrote:
Hi I have a database with a table, on fieldname is called "boiler type" I
would like to count the number of times a particular word appears in the
column and display this on a form in a text box, whats the best way ?
Thanks all

 

[John Spencer]

=DCount("*","[Your Table Name]","[Boiler Type] ='isar' and [Some Date
Field Between #" & [forms]![test count]![startdate] & "# and #" &
[forms]![test count]![enddate] & "#")

Note that the date values need to be delimited with # marks.

I must say that you may be doing this the hard way.

A query that looks like the following should give you all the boiler
types and the counts in one query.

SELECT [Boiler Type], Count([Boiler Type] as RecCount
FROM [Your Table Name]
WHERE [Your Date Field] between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
GROUP BY [Boiler Type]

If you are using a single form, you could include a sub-form based on
the above query to display the information. If you are using a
continuous form, then life is a bit more difficult.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John, just another bit of help if you can, I have now created six text
boxes on my form and copied the dcount to them all and changed the "like"
statement to look for all the boiler names, everything is displayed correctly
in the six boxes, but I wanted to add some date parameters to search between
( I have a date column on my table) I placed two boxes on the form and set
the parameter value to search "between" the two dates in the text boxes, but
it always shows me the entire count for each boiler from the whole table!! it
does not follow my input dates, what is restricting it would you know - I
used the following - between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
Thanks in advance

My mistake. I misunderstood what you wanted. I was counting the number of
times the word would appear in a field in a single record.

What you want is the number of records that contain the word "isar". If the
field contains only the word isar then you can use

SELECT Count(*)
FROM YourTable
WHERE [Boiler Type] = 'isar'

On a form, I would probably use the DCOUNT function to do this and use it as
the control source of a textbox.

Set the Control Source to:
=DCount("*","[Your Table Name]","[Boiler Type] LIKE '*isar*'")

If the field contains exactly "isar" and no other characters you can make this
more efficient using
=DCount("*","[Your Table Name]","[Boiler Type] ='isar'")

Hope this helps. Sorry for misunderstanding your request.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John, I tried adding your code to a new query and I changed the "find
word" areas to "Isar", when I run the query a prompt appears (enter parameter
value) with Isar as the prompt, if i enter isar in the box and run the query
it finds 569 instances but in one long column with "1"'s in it, it does not
return 569 as a total sum ?, have i done something wrong ? Thanks

:

To count instances of a word in a field you can use an expression like
(Len(X)-Len(Replace(X,Y))) / Len(Y)

So a select query that looks like the following should work


SELECT (Len([Boiler Type]) - Len(Replace([Boiler Type],[Find Word])))/
Len([Find Word]) as CountOfWord
FROM [YourTable]
WHERE [Boiler Type] Like "*" & [Find Word] & "*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


blake7 wrote:
Hi I have a database with a table, on fieldname is called "boiler type" I
would like to count the number of times a particular word appears in the
column and display this on a form in a text box, whats the best way ?
Thanks all

 

[blake7]

Hi John, Thanks for your help, I put your expression into a query (below) on
running it a message appears saying "syntax error missing operator in query
expression", I have tried to see what is missing but to no avail, sorry for
being a pain, i am a newby !! Thanks Tony

I must say that you may be doing this the hard way.

A query that looks like the following should give you all the boiler
types and the counts in one query.

SELECT [Boiler Type], Count([Boiler Type] as RecCount
FROM [Your Table Name]
WHERE [Your Date Field] between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
GROUP BY [Boiler Type]

If you are using a single form, you could include a sub-form based on
the above query to display the information. If you are using a
continuous form, then life is a bit more difficult.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John, just another bit of help if you can, I have now created six text
boxes on my form and copied the dcount to them all and changed the "like"
statement to look for all the boiler names, everything is displayed correctly
in the six boxes, but I wanted to add some date parameters to search between
( I have a date column on my table) I placed two boxes on the form and set
the parameter value to search "between" the two dates in the text boxes, but
it always shows me the entire count for each boiler from the whole table!! it
does not follow my input dates, what is restricting it would you know - I
used the following - between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
Thanks in advance

My mistake. I misunderstood what you wanted. I was counting the number of
times the word would appear in a field in a single record.

What you want is the number of records that contain the word "isar". If the
field contains only the word isar then you can use

SELECT Count(*)
FROM YourTable
WHERE [Boiler Type] = 'isar'

On a form, I would probably use the DCOUNT function to do this and use it as
the control source of a textbox.

Set the Control Source to:
=DCount("*","[Your Table Name]","[Boiler Type] LIKE '*isar*'")

If the field contains exactly "isar" and no other characters you can make this
more efficient using
=DCount("*","[Your Table Name]","[Boiler Type] ='isar'")

Hope this helps. Sorry for misunderstanding your request.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

blake7 wrote:
Hi John, I tried adding your code to a new query and I changed the "find
word" areas to "Isar", when I run the query a prompt appears (enter parameter
value) with Isar as the prompt, if i enter isar in the box and run the query
it finds 569 instances but in one long column with "1"'s in it, it does not
return 569 as a total sum ?, have i done something wrong ? Thanks

:

To count instances of a word in a field you can use an expression like
(Len(X)-Len(Replace(X,Y))) / Len(Y)

So a select query that looks like the following should work


SELECT (Len([Boiler Type]) - Len(Replace([Boiler Type],[Find Word])))/
Len([Find Word]) as CountOfWord
FROM [YourTable]
WHERE [Boiler Type] Like "*" & [Find Word] & "*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


blake7 wrote:
Hi I have a database with a table, on fieldname is called "boiler type" I
would like to count the number of times a particular word appears in the
column and display this on a form in a text box, whats the best way ?
Thanks all

 

[John Spencer]

Tony, I'm not quite sure what you did. I did notice that I left off a closing
parentheses in the standalone query. Note that the query was not designed to
be included into another query.

SELECT [Boiler Type]
, Count([Boiler Type]) as RecCount
FROM [Your Table Name]
WHERE [Your Date Field] between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
GROUP BY [Boiler Type]


The Expression looks good for a control
=DCount("*","[Your Table Name]","[Boiler Type] ='isar' and [Some Date Field
Between #" & [forms]![test count]![startdate] & "# and #" & [forms]![test
count]![enddate] & "#")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John, Thanks for your help, I put your expression into a query (below) on
running it a message appears saying "syntax error missing operator in query
expression", I have tried to see what is missing but to no avail, sorry for
being a pain, i am a newby !! Thanks Tony

I must say that you may be doing this the hard way.

A query that looks like the following should give you all the boiler
types and the counts in one query.

SELECT [Boiler Type], Count([Boiler Type] as RecCount
FROM [Your Table Name]
WHERE [Your Date Field] between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
GROUP BY [Boiler Type]

If you are using a single form, you could include a sub-form based on
the above query to display the information. If you are using a
continuous form, then life is a bit more difficult.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John, just another bit of help if you can, I have now created six text
boxes on my form and copied the dcount to them all and changed the "like"
statement to look for all the boiler names, everything is displayed correctly
in the six boxes, but I wanted to add some date parameters to search between
( I have a date column on my table) I placed two boxes on the form and set
the parameter value to search "between" the two dates in the text boxes, but
it always shows me the entire count for each boiler from the whole table!! it
does not follow my input dates, what is restricting it would you know - I
used the following - between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
Thanks in advance

:

My mistake. I misunderstood what you wanted. I was counting the number of
times the word would appear in a field in a single record.

What you want is the number of records that contain the word "isar". If the
field contains only the word isar then you can use

SELECT Count(*)
FROM YourTable
WHERE [Boiler Type] = 'isar'

On a form, I would probably use the DCOUNT function to do this and use it as
the control source of a textbox.

Set the Control Source to:
=DCount("*","[Your Table Name]","[Boiler Type] LIKE '*isar*'")

If the field contains exactly "isar" and no other characters you can make this
more efficient using
=DCount("*","[Your Table Name]","[Boiler Type] ='isar'")

Hope this helps. Sorry for misunderstanding your request.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

blake7 wrote:
Hi John, I tried adding your code to a new query and I changed the "find
word" areas to "Isar", when I run the query a prompt appears (enter parameter
value) with Isar as the prompt, if i enter isar in the box and run the query
it finds 569 instances but in one long column with "1"'s in it, it does not
return 569 as a total sum ?, have i done something wrong ? Thanks

:

To count instances of a word in a field you can use an expression like
(Len(X)-Len(Replace(X,Y))) / Len(Y)

So a select query that looks like the following should work


SELECT (Len([Boiler Type]) - Len(Replace([Boiler Type],[Find Word])))/
Len([Find Word]) as CountOfWord
FROM [YourTable]
WHERE [Boiler Type] Like "*" & [Find Word] & "*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


blake7 wrote:
Hi I have a database with a table, on fieldname is called "boiler type" I
would like to count the number of times a particular word appears in the
column and display this on a form in a text box, whats the best way ?
Thanks all

 

[blake7]

Hi John, I tried your expression for a control and linked it to two text
boxes "startdate" and "enddate" as follows

=DCount("*","[Main Audit Data]","[Boiler Type] ='isar' and [Audit Date]
Between #" & [Forms]![test count]![startdate] & "# and #" & [Forms]![test
count]![enddate] & "#")

my main audit data table has daily date records for the last 9 months, when
I open the form and enter 01/01/2008 to 31/01/2008 it works fine and tells me
there were 52 instances for example for the word "isar" then if I enter the
start and end dates for february i should see 30 but it displays 82 it is
adding the january total to the february total even though the february dates
are showing in the text boxes,any ideas ?? best regards Tony.

Tony, I'm not quite sure what you did. I did notice that I left off a closing
parentheses in the standalone query. Note that the query was not designed to
be included into another query.

SELECT [Boiler Type]
, Count([Boiler Type]) as RecCount
FROM [Your Table Name]
WHERE [Your Date Field] between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
GROUP BY [Boiler Type]


The Expression looks good for a control
=DCount("*","[Your Table Name]","[Boiler Type] ='isar' and [Some Date Field
Between #" & [forms]![test count]![startdate] & "# and #" & [forms]![test
count]![enddate] & "#")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John, Thanks for your help, I put your expression into a query (below) on
running it a message appears saying "syntax error missing operator in query
expression", I have tried to see what is missing but to no avail, sorry for
being a pain, i am a newby !! Thanks Tony

I must say that you may be doing this the hard way.

A query that looks like the following should give you all the boiler
types and the counts in one query.

SELECT [Boiler Type], Count([Boiler Type] as RecCount
FROM [Your Table Name]
WHERE [Your Date Field] between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
GROUP BY [Boiler Type]

If you are using a single form, you could include a sub-form based on
the above query to display the information. If you are using a
continuous form, then life is a bit more difficult.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


blake7 wrote:
Hi John, just another bit of help if you can, I have now created six text
boxes on my form and copied the dcount to them all and changed the "like"
statement to look for all the boiler names, everything is displayed correctly
in the six boxes, but I wanted to add some date parameters to search between
( I have a date column on my table) I placed two boxes on the form and set
the parameter value to search "between" the two dates in the text boxes, but
it always shows me the entire count for each boiler from the whole table!! it
does not follow my input dates, what is restricting it would you know - I
used the following - between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
Thanks in advance

:

My mistake. I misunderstood what you wanted. I was counting the number of
times the word would appear in a field in a single record.

What you want is the number of records that contain the word "isar". If the
field contains only the word isar then you can use

SELECT Count(*)
FROM YourTable
WHERE [Boiler Type] = 'isar'

On a form, I would probably use the DCOUNT function to do this and use it as
the control source of a textbox.

Set the Control Source to:
=DCount("*","[Your Table Name]","[Boiler Type] LIKE '*isar*'")

If the field contains exactly "isar" and no other characters you can make this
more efficient using
=DCount("*","[Your Table Name]","[Boiler Type] ='isar'")

Hope this helps. Sorry for misunderstanding your request.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

blake7 wrote:
Hi John, I tried adding your code to a new query and I changed the "find
word" areas to "Isar", when I run the query a prompt appears (enter parameter
value) with Isar as the prompt, if i enter isar in the box and run the query
it finds 569 instances but in one long column with "1"'s in it, it does not
return 569 as a total sum ?, have i done something wrong ? Thanks

:

To count instances of a word in a field you can use an expression like
(Len(X)-Len(Replace(X,Y))) / Len(Y)

So a select query that looks like the following should work


SELECT (Len([Boiler Type]) - Len(Replace([Boiler Type],[Find Word])))/
Len([Find Word]) as CountOfWord
FROM [YourTable]
WHERE [Boiler Type] Like "*" & [Find Word] & "*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


blake7 wrote:
Hi I have a database with a table, on fieldname is called "boiler type" I
would like to count the number of times a particular word appears in the
column and display this on a form in a text box, whats the best way ?
Thanks all

 

[John Spencer]

Dates must be in US format or in yyyy-mm-dd format. So try

=DCount("*","[Your Table Name]","[Boiler Type] ='isar' and [Some Date
Field Between #" & Format([forms]![test count]![startdate],"yyyy-mm-dd")
& "# and #" & Format([forms]![test count]![enddate],"yyyy-mm-dd") & "#")

When you enter 01/02/2008 it is getting interpreted as January 2, 2008
instead of as February 1, 2008.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John, I tried your expression for a control and linked it to two text
boxes "startdate" and "enddate" as follows

=DCount("*","[Main Audit Data]","[Boiler Type] ='isar' and [Audit Date]
Between #" & [Forms]![test count]![startdate] & "# and #" & [Forms]![test
count]![enddate] & "#")

my main audit data table has daily date records for the last 9 months, when
I open the form and enter 01/01/2008 to 31/01/2008 it works fine and tells me
there were 52 instances for example for the word "isar" then if I enter the
start and end dates for february i should see 30 but it displays 82 it is
adding the january total to the february total even though the february dates
are showing in the text boxes,any ideas ?? best regards Tony.

Tony, I'm not quite sure what you did. I did notice that I left off a closing
parentheses in the standalone query. Note that the query was not designed to
be included into another query.

SELECT [Boiler Type]
, Count([Boiler Type]) as RecCount
FROM [Your Table Name]
WHERE [Your Date Field] between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
GROUP BY [Boiler Type]


The Expression looks good for a control
=DCount("*","[Your Table Name]","[Boiler Type] ='isar' and [Some Date Field
Between #" & [forms]![test count]![startdate] & "# and #" & [forms]![test
count]![enddate] & "#")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John, Thanks for your help, I put your expression into a query (below) on
running it a message appears saying "syntax error missing operator in query
expression", I have tried to see what is missing but to no avail, sorry for
being a pain, i am a newby !! Thanks Tony

:


I must say that you may be doing this the hard way.

A query that looks like the following should give you all the boiler
types and the counts in one query.

SELECT [Boiler Type], Count([Boiler Type] as RecCount
FROM [Your Table Name]
WHERE [Your Date Field] between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
GROUP BY [Boiler Type]

If you are using a single form, you could include a sub-form based on
the above query to display the information. If you are using a
continuous form, then life is a bit more difficult.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


blake7 wrote:
Hi John, just another bit of help if you can, I have now created six text
boxes on my form and copied the dcount to them all and changed the "like"
statement to look for all the boiler names, everything is displayed correctly
in the six boxes, but I wanted to add some date parameters to search between
( I have a date column on my table) I placed two boxes on the form and set
the parameter value to search "between" the two dates in the text boxes, but
it always shows me the entire count for each boiler from the whole table!! it
does not follow my input dates, what is restricting it would you know - I
used the following - between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
Thanks in advance

:

My mistake. I misunderstood what you wanted. I was counting the number of
times the word would appear in a field in a single record.

What you want is the number of records that contain the word "isar". If the
field contains only the word isar then you can use

SELECT Count(*)
FROM YourTable
WHERE [Boiler Type] = 'isar'

On a form, I would probably use the DCOUNT function to do this and use it as
the control source of a textbox.

Set the Control Source to:
=DCount("*","[Your Table Name]","[Boiler Type] LIKE '*isar*'")

If the field contains exactly "isar" and no other characters you can make this
more efficient using
=DCount("*","[Your Table Name]","[Boiler Type] ='isar'")

Hope this helps. Sorry for misunderstanding your request.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

blake7 wrote:
Hi John, I tried adding your code to a new query and I changed the "find
word" areas to "Isar", when I run the query a prompt appears (enter parameter
value) with Isar as the prompt, if i enter isar in the box and run the query
it finds 569 instances but in one long column with "1"'s in it, it does not
return 569 as a total sum ?, have i done something wrong ? Thanks

:

To count instances of a word in a field you can use an expression like
(Len(X)-Len(Replace(X,Y))) / Len(Y)

So a select query that looks like the following should work


SELECT (Len([Boiler Type]) - Len(Replace([Boiler Type],[Find Word])))/
Len([Find Word]) as CountOfWord
FROM [YourTable]
WHERE [Boiler Type] Like "*" & [Find Word] & "*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


blake7 wrote:
Hi I have a database with a table, on fieldname is called "boiler type" I
would like to count the number of times a particular word appears in the
column and display this on a form in a text box, whats the best way ?
Thanks all

 

[blake7]

Brilliant !! Thanks John that works great, I have another question if you
dont mind - its about code in a REPORT, I have a text box on a report called
"repeatIssue" with the following simple code which is looking at a CHECK BOX
called "Issue" on a form.

if[forms]![correctiveaction]![Issue] = true then
me.repeatissue = "Yes"
else
me.repeatissue = "No"
end if

Question - is the context of the code correct for a text box on a report?,
it will not work the text box on the report just remains blank! am I doing
something wrong again?, Regards Tony

Dates must be in US format or in yyyy-mm-dd format. So try

=DCount("*","[Your Table Name]","[Boiler Type] ='isar' and [Some Date
Field Between #" & Format([forms]![test count]![startdate],"yyyy-mm-dd")
& "# and #" & Format([forms]![test count]![enddate],"yyyy-mm-dd") & "#")

When you enter 01/02/2008 it is getting interpreted as January 2, 2008
instead of as February 1, 2008.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John, I tried your expression for a control and linked it to two text
boxes "startdate" and "enddate" as follows

=DCount("*","[Main Audit Data]","[Boiler Type] ='isar' and [Audit Date]
Between #" & [Forms]![test count]![startdate] & "# and #" & [Forms]![test
count]![enddate] & "#")

my main audit data table has daily date records for the last 9 months, when
I open the form and enter 01/01/2008 to 31/01/2008 it works fine and tells me
there were 52 instances for example for the word "isar" then if I enter the
start and end dates for february i should see 30 but it displays 82 it is
adding the january total to the february total even though the february dates
are showing in the text boxes,any ideas ?? best regards Tony.

Tony, I'm not quite sure what you did. I did notice that I left off a closing
parentheses in the standalone query. Note that the query was not designed to
be included into another query.

SELECT [Boiler Type]
, Count([Boiler Type]) as RecCount
FROM [Your Table Name]
WHERE [Your Date Field] between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
GROUP BY [Boiler Type]


The Expression looks good for a control
=DCount("*","[Your Table Name]","[Boiler Type] ='isar' and [Some Date Field
Between #" & [forms]![test count]![startdate] & "# and #" & [forms]![test
count]![enddate] & "#")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

blake7 wrote:
Hi John, Thanks for your help, I put your expression into a query (below) on
running it a message appears saying "syntax error missing operator in query
expression", I have tried to see what is missing but to no avail, sorry for
being a pain, i am a newby !! Thanks Tony

:


I must say that you may be doing this the hard way.

A query that looks like the following should give you all the boiler
types and the counts in one query.

SELECT [Boiler Type], Count([Boiler Type] as RecCount
FROM [Your Table Name]
WHERE [Your Date Field] between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
GROUP BY [Boiler Type]

If you are using a single form, you could include a sub-form based on
the above query to display the information. If you are using a
continuous form, then life is a bit more difficult.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


blake7 wrote:
Hi John, just another bit of help if you can, I have now created six text
boxes on my form and copied the dcount to them all and changed the "like"
statement to look for all the boiler names, everything is displayed correctly
in the six boxes, but I wanted to add some date parameters to search between
( I have a date column on my table) I placed two boxes on the form and set
the parameter value to search "between" the two dates in the text boxes, but
it always shows me the entire count for each boiler from the whole table!! it
does not follow my input dates, what is restricting it would you know - I
used the following - between[forms]![test count]![startdate] and
[forms]![test count]![enddate]
Thanks in advance

:

My mistake. I misunderstood what you wanted. I was counting the number of
times the word would appear in a field in a single record.

What you want is the number of records that contain the word "isar". If the
field contains only the word isar then you can use

SELECT Count(*)
FROM YourTable
WHERE [Boiler Type] = 'isar'

On a form, I would probably use the DCOUNT function to do this and use it as
the control source of a textbox.

Set the Control Source to:
=DCount("*","[Your Table Name]","[Boiler Type] LIKE '*isar*'")

If the field contains exactly "isar" and no other characters you can make this
more efficient using
=DCount("*","[Your Table Name]","[Boiler Type] ='isar'")

Hope this helps. Sorry for misunderstanding your request.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

blake7 wrote:
Hi John, I tried adding your code to a new query and I changed the "find
word" areas to "Isar", when I run the query a prompt appears (enter parameter
value) with Isar as the prompt, if i enter isar in the box and run the query
it finds 569 instances but in one long column with "1"'s in it, it does not
return 569 as a total sum ?, have i done something wrong ? Thanks

:

To count instances of a word in a field you can use an expression like
(Len(X)-Len(Replace(X,Y))) / Len(Y)

So a select query that looks like the following should work


SELECT (Len([Boiler Type]) - Len(Replace([Boiler Type],[Find Word])))/
Len([Find Word]) as CountOfWord
FROM [YourTable]
WHERE [Boiler Type] Like "*" & [Find Word] & "*"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


blake7 wrote:
Hi I have a database with a table, on fieldname is called "boiler type" I
would like to count the number of times a particular word appears in the
column and display this on a form in a text box, whats the best way ?
Thanks all