Count how many spaces

[Guest]

Can anyone tell me the SQL syntax to count how many blank spaces are in a
field?

Thanks in advance,

Bonnie

 

[Guest]

Len([YourField]) - Len(Replace([YourField]," ",""))

 

[Guest]

Thanks Karl, that's excellent. Where would I put the results of the
calculation in a query?

Len([YourField]) - Len(Replace([YourField]," ",""))

--
KARL DEWEY
Build a little - Test a little

Can anyone tell me the SQL syntax to count how many blank spaces are in a
field?

Thanks in advance,

Bonnie

 

[Steve Schapel]

That's cool, Karl. The solution I was starting to work on was much more
complicated than that! ;-)

 

[Pieter Wijnen]

Any blank space in the query grid will do <g>

or in sql

SELECT YourField, Len([YourField]) - Len(Replace([YourField],' ','')) As
CountBlanks
FROM YourTable

Pieter

Thanks Karl, that's excellent. Where would I put the results of the
calculation in a query?

Len([YourField]) - Len(Replace([YourField]," ",""))

--
KARL DEWEY
Build a little - Test a little

Can anyone tell me the SQL syntax to count how many blank spaces are in
a
field?

Thanks in advance,

Bonnie

 

[Taryn]

I have one quick question how many addresses can u add in the address book?

- Amanda Kelski


On 9/19/07 2:18 PM, in article #9yCSlu#[email protected],
"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace

Any blank space in the query grid will do <g>

or in sql

SELECT YourField, Len([YourField]) - Len(Replace([YourField],' ','')) As
CountBlanks
FROM YourTable

Pieter

Thanks Karl, that's excellent. Where would I put the results of the
calculation in a query?

Len([YourField]) - Len(Replace([YourField]," ",""))

--
KARL DEWEY
Build a little - Test a little


:

Can anyone tell me the SQL syntax to count how many blank spaces are in
a
field?

Thanks in advance,

Bonnie

 

[Arvin Meyer [MVP]]

I have one quick question how many addresses can u add in the address book?

- Amanda Kelski

Your question may be better answered in a newsgroup appropriate to your
problem. This newsgroup is dedicated to the Microsoft Access database
product.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

This suggestion was very helpful. I am just having a problem using it in a
query. It appears to work for a second and then I get a data type mismatch
error. Any ideas?

Thanks in advance.

Bonnie

Len([YourField]) - Len(Replace([YourField]," ",""))

--
KARL DEWEY
Build a little - Test a little

Can anyone tell me the SQL syntax to count how many blank spaces are in a
field?

Thanks in advance,

Bonnie

 

[Guest]

Post your query SQL.
What is the datatype of the field you are using it on?
--
KARL DEWEY
Build a little - Test a little

This suggestion was very helpful. I am just having a problem using it in a
query. It appears to work for a second and then I get a data type mismatch
error. Any ideas?

Thanks in advance.

Bonnie

Len([YourField]) - Len(Replace([YourField]," ",""))

--
KARL DEWEY
Build a little - Test a little

Can anyone tell me the SQL syntax to count how many blank spaces are in a
field?

Thanks in advance,

Bonnie

 

[Douglas J. Steele]

Is the field being checked ever Null? If so, try:

IIf(IsNull([YourField], 0, Len([YourField]) - Len(Replace([YourField],"
","")))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Post your query SQL.
What is the datatype of the field you are using it on?
--
KARL DEWEY
Build a little - Test a little

This suggestion was very helpful. I am just having a problem using it in
a
query. It appears to work for a second and then I get a data type
mismatch
error. Any ideas?

Thanks in advance.

Bonnie

Len([YourField]) - Len(Replace([YourField]," ",""))

--
KARL DEWEY
Build a little - Test a little


:

Can anyone tell me the SQL syntax to count how many blank spaces are
in a
field?

Thanks in advance,

Bonnie

 

[Guest]

Hi -

Sorry it's taken me so long, got side tracked with another issue but I would
still love to work this out. Here's the SQL code:

UPDATE MASTER SET MASTER.CITY = Trim(Left(MASTER.CSZ,InStr(MASTER.CSZ,"
")-1)), MASTER.STATE = Trim(Mid(MASTER.CSZ,InStr(MASTER.CSZ," ")+1,2)),
MASTER.ZIP = Trim(Right(MASTER.CSZ,Len(MASTER.CSZ)-InStrRev(MASTER.CSZ," ")))
WHERE (((Len([CSZ])-Len(Replace([CSZ]," ","")))="2"));

CSZ field is Text, field size 100
CITY field is Text, field size 35
STATE field is Text, field size 2
ZIP field is Text, field size 15

I also tried Doug's suggestion with the IIF(IsNull but get a "wrong number
of arguments" error. The error on the original query was "data type mismatch
in criteria expersion."

Thanks in advance for any help you can give.

Bonnie

Post your query SQL.
What is the datatype of the field you are using it on?
--
KARL DEWEY
Build a little - Test a little

This suggestion was very helpful. I am just having a problem using it in a
query. It appears to work for a second and then I get a data type mismatch
error. Any ideas?

Thanks in advance.

Bonnie

Len([YourField]) - Len(Replace([YourField]," ",""))

--
KARL DEWEY
Build a little - Test a little


:

Can anyone tell me the SQL syntax to count how many blank spaces are in a
field?

Thanks in advance,

Bonnie

 

[Guest]

The error on the original query was "data type mismatch in criteria
expersion."
Your problem is in this --
WHERE (((Len([CSZ])-Len(Replace([CSZ]," ","")))="2"));
It should be ---
WHERE (((Len([CSZ])-Len(Replace([CSZ]," ","")))=2));
You are looking for a number and when you put it in quotes you are saying it
is text.

--
KARL DEWEY
Build a little - Test a little

Hi -

Sorry it's taken me so long, got side tracked with another issue but I would
still love to work this out. Here's the SQL code:

UPDATE MASTER SET MASTER.CITY = Trim(Left(MASTER.CSZ,InStr(MASTER.CSZ,"
")-1)), MASTER.STATE = Trim(Mid(MASTER.CSZ,InStr(MASTER.CSZ," ")+1,2)),
MASTER.ZIP = Trim(Right(MASTER.CSZ,Len(MASTER.CSZ)-InStrRev(MASTER.CSZ," ")))
WHERE (((Len([CSZ])-Len(Replace([CSZ]," ","")))="2"));

CSZ field is Text, field size 100
CITY field is Text, field size 35
STATE field is Text, field size 2
ZIP field is Text, field size 15

I also tried Doug's suggestion with the IIF(IsNull but get a "wrong number
of arguments" error. The error on the original query was "data type mismatch
in criteria expersion."

Thanks in advance for any help you can give.

Bonnie

Post your query SQL.
What is the datatype of the field you are using it on?
--
KARL DEWEY
Build a little - Test a little

This suggestion was very helpful. I am just having a problem using it in a
query. It appears to work for a second and then I get a data type mismatch
error. Any ideas?

Thanks in advance.

Bonnie

:

Len([YourField]) - Len(Replace([YourField]," ",""))

--
KARL DEWEY
Build a little - Test a little


:

Can anyone tell me the SQL syntax to count how many blank spaces are in a
field?

Thanks in advance,

Bonnie

 

[Douglas J. Steele]

Len returns a Long Integer, You shouldn't be comparing it to a string:

WHERE (((Len([CSZ])-Len(Replace([CSZ]," ","")))=2))

You do need to check whether CSZ is Null: Len(Null) returns Null.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi -

Sorry it's taken me so long, got side tracked with another issue but I
would
still love to work this out. Here's the SQL code:

UPDATE MASTER SET MASTER.CITY = Trim(Left(MASTER.CSZ,InStr(MASTER.CSZ,"
")-1)), MASTER.STATE = Trim(Mid(MASTER.CSZ,InStr(MASTER.CSZ," ")+1,2)),
MASTER.ZIP = Trim(Right(MASTER.CSZ,Len(MASTER.CSZ)-InStrRev(MASTER.CSZ,"
")))
WHERE (((Len([CSZ])-Len(Replace([CSZ]," ","")))="2"));

CSZ field is Text, field size 100
CITY field is Text, field size 35
STATE field is Text, field size 2
ZIP field is Text, field size 15

I also tried Doug's suggestion with the IIF(IsNull but get a "wrong number
of arguments" error. The error on the original query was "data type
mismatch
in criteria expersion."

Thanks in advance for any help you can give.

Bonnie

Post your query SQL.
What is the datatype of the field you are using it on?
--
KARL DEWEY
Build a little - Test a little

This suggestion was very helpful. I am just having a problem using it
in a
query. It appears to work for a second and then I get a data type
mismatch
error. Any ideas?

Thanks in advance.

Bonnie

:

Len([YourField]) - Len(Replace([YourField]," ",""))

--
KARL DEWEY
Build a little - Test a little


:

Can anyone tell me the SQL syntax to count how many blank spaces
are in a
field?

Thanks in advance,

Bonnie

 

[Guest]

Got it - you're right! I could have sworn I looked at the data type and
tried =2 and it changed it back to quotes but when I did it now - she works!
Must be USER ERROR!

Thanks very much for the help - both of you.

Bonnie

expersion."
Your problem is in this --
WHERE (((Len([CSZ])-Len(Replace([CSZ]," ","")))="2"));
It should be ---
WHERE (((Len([CSZ])-Len(Replace([CSZ]," ","")))=2));
You are looking for a number and when you put it in quotes you are saying it
is text.

--
KARL DEWEY
Build a little - Test a little

Hi -

Sorry it's taken me so long, got side tracked with another issue but I would
still love to work this out. Here's the SQL code:

UPDATE MASTER SET MASTER.CITY = Trim(Left(MASTER.CSZ,InStr(MASTER.CSZ,"
")-1)), MASTER.STATE = Trim(Mid(MASTER.CSZ,InStr(MASTER.CSZ," ")+1,2)),
MASTER.ZIP = Trim(Right(MASTER.CSZ,Len(MASTER.CSZ)-InStrRev(MASTER.CSZ," ")))
WHERE (((Len([CSZ])-Len(Replace([CSZ]," ","")))="2"));

CSZ field is Text, field size 100
CITY field is Text, field size 35
STATE field is Text, field size 2
ZIP field is Text, field size 15

I also tried Doug's suggestion with the IIF(IsNull but get a "wrong number
of arguments" error. The error on the original query was "data type mismatch
in criteria expersion."

Thanks in advance for any help you can give.

Bonnie

Post your query SQL.
What is the datatype of the field you are using it on?
--
KARL DEWEY
Build a little - Test a little


:

This suggestion was very helpful. I am just having a problem using it in a
query. It appears to work for a second and then I get a data type mismatch
error. Any ideas?

Thanks in advance.

Bonnie

:

Len([YourField]) - Len(Replace([YourField]," ",""))

--
KARL DEWEY
Build a little - Test a little


:

Can anyone tell me the SQL syntax to count how many blank spaces are in a
field?

Thanks in advance,

Bonnie