non numeric data?

[Tom Neal]

I think I have some non numeric data in a column

I had code working
added some records and now
the query dies with a compile error every time I run it

I have dissected the code in question and think I have traced it down to:

SELECT cstr( [Members.PSC Number])
FROM Members;

So it looks like my column PSC Number has a bad row

I have looked at the data but don't see it.

Any thoughts on how to find the bad row?


I am converted to a string so that I can concatenate it to some other
columns.

--
See you at the range
Tom Neal
(e-mail address removed)
713 882 5513
832 201 0453 (fax

 

[Guest]

I doubt it’s the data, its more likely because the bracketing of the table
and column name is wrong. It should be:

[Members].[PSC Number]

Incidentally you don't have to convert a number to a string to concatenate
it with a string expression.

Ken Sheridan
Stafford, England

 

[Guest]

are you trying to convert a NUMBER to a STRING?

if so, use

SELECT str([numberFieldName]) AS myNumberToString
FROM tableName

jimaldoFratelli

 

[Guest]

Ken, i've ried concatenating a number to a string without using str(), but it
returns an error

how would you do this without converting it first?

I doubt it’s the data, its more likely because the bracketing of the table
and column name is wrong. It should be:

[Members].[PSC Number]

Incidentally you don't have to convert a number to a string to concatenate
it with a string expression.

Ken Sheridan
Stafford, England

I think I have some non numeric data in a column

I had code working
added some records and now
the query dies with a compile error every time I run it

I have dissected the code in question and think I have traced it down to:

SELECT cstr( [Members.PSC Number])
FROM Members;

So it looks like my column PSC Number has a bad row

I have looked at the data but don't see it.

Any thoughts on how to find the bad row?


I am converted to a string so that I can concatenate it to some other
columns.

--
See you at the range
Tom Neal
(e-mail address removed)
713 882 5513
832 201 0453 (fax

 

[John W. Vinson]

Ken, i've ried concatenating a number to a string without using str(), but it
returns an error

how would you do this without converting it first?

Dim iNum As Integer
Dim strString As String
strString = "Anchor"
iNum = 3
strString = strString & iNum


Immediate window:

?strString
Anchor3

John W. Vinson [MVP]

 

[Guest]

i'm not conversant withVBA, but i can understand what you've done.

i tried constructing examples (following the same syntax you have shown)
through the expression builder, but they all failed.

would this be because of the number types i used, or is this an example of
why VBA is superior to the expression builder?

jimaldo

 

[John W. Vinson]

i tried constructing examples (following the same syntax you have shown)
through the expression builder, but they all failed.

What expression did you end up with?

would this be because of the number types i used, or is this an example of
why VBA is superior to the expression builder?

Well... that's like saying that a wall made of bricks and mortar is superior
to a person with a trowel. The expression builder *builds* VBA, it's not in
competition with it. What number types *did* you use? It shouldn't matter!
And what results did you get?

John W. Vinson [MVP]

 

[Tom Neal]

No I really think there is bad data here is why

The query was working

I added a hundred rows of data

The query quit working.


What I am really trying to write is a query that I can cut and paste into
Dymo printer software to print single labels as needed.
I've had it working for several months.

Now I can't seem to get it to concatenate.
Which is why I went on the 'witch hunt' for bad data in a numeric.


SQL now looks like this:

SELECT Members.[Badge Name], Members.[PSC Number], Members.[Plate Rack],
Members.[First name]+' '+IIf(ISNull([Members.MI], ''), [Members.MI])+'
'+Members.[Last name]+'
'+Members.Street+'
'+Members.City+' '+Members.State+' '+Members.Zip AS Expr1
FROM Members;



--
See you at the range
Tom Neal
(e-mail address removed)
713 882 5513
832 201 0453 (fax

I doubt it's the data, its more likely because the bracketing of the table
and column name is wrong. It should be:

[Members].[PSC Number]

Incidentally you don't have to convert a number to a string to concatenate
it with a string expression.

Ken Sheridan
Stafford, England

I think I have some non numeric data in a column

I had code working
added some records and now
the query dies with a compile error every time I run it

I have dissected the code in question and think I have traced it down to:

SELECT cstr( [Members.PSC Number])
FROM Members;

So it looks like my column PSC Number has a bad row

I have looked at the data but don't see it.

Any thoughts on how to find the bad row?


I am converted to a string so that I can concatenate it to some other
columns.

--
See you at the range
Tom Neal
(e-mail address removed)
713 882 5513
832 201 0453 (fax

 

[onedaywhen]

The expression builder *builds* VBA, it's not in
competition with it.

Are you sure the expression builder builds *VBA*, rather than, say,
SQL? e.g. why does the expression builder have 'Between' available as
a comparison operator when VBA does not?

Jamie.

--

 

[Guest]

hi John

the sql used was:

"SELECT
tblTime.xNum1, tblTime.xNum2, tblTime.xNum3,
"i am string no. "+tblTime!xNum1 AS str1,
"i am string no. "+tblTime!xNum2 AS str2,
"i am string no. "+tblTime!xNum3 AS str3
FROM tblTime;"

numbert types were: xNum1 Long, xNum2 Single, xNum3 Double.
all 3 returned "#Error"


jimaldo

 

[Guest]

Hi Tom

i can't see from your sql where you are concatenating the number to a
string..?

what happens if you create a calculated field which ADDS a number (or
performs a math calculation) to your numeric field. results of that should
highlight any non-numeric strings which may have inadvertantly found theirway
into your data

i'm also wondering, if PSC number needs to be of a numeric type at all? is
this some kind of licence/registration number?
jimaldo

 

[John W. Vinson]

hi John

the sql used was:

"SELECT
tblTime.xNum1, tblTime.xNum2, tblTime.xNum3,
"i am string no. "+tblTime!xNum1 AS str1,
"i am string no. "+tblTime!xNum2 AS str2,
"i am string no. "+tblTime!xNum3 AS str3
FROM tblTime;"

numbert types were: xNum1 Long, xNum2 Single, xNum3 Double.
all 3 returned "#Error"

Ok... think like a computer for a minute.

String constants are preceded by a doublequote character ", and terminated by
the next doublequote charater ".

Your string is being TERMINATED by the quote mark at the start of "i am string
no." Access is trying to interpret "i am string no" as if it were VBA code -
which it isn't!

To put a string constant INSIDE a string constant, either use ' as a delimter,
or use *two* doublequotes:

"SELECT
tblTime.xNum1, tblTime.xNum2, tblTime.xNum3,
'i am string no. '+tblTime!xNum1 AS str1,
i am string no. '+tblTime!xNum2 AS str2,
i am string no. '+tblTime!xNum3 AS str3
FROM tblTime;"

or

"SELECT
tblTime.xNum1, tblTime.xNum2, tblTime.xNum3,
""i am string no. ""+tblTime!xNum1 AS str1,
""i am string no. ""+tblTime!xNum2 AS str2,
""i am string no. ""+tblTime!xNum3 AS str3
FROM tblTime;"




John W. Vinson [MVP]

 

[Guest]

thanks again John

it's all coming back to me...slowly!!

jimaldo

 

[John W. Vinson]

thanks again John

it's all coming back to me...slowly!!

Like riding bicycles... <g>

John W. Vinson [MVP]