Error querying Access text fields if dash or minus sign in middle of text

Looks like John Nurick has given you useful information on your issue.

Yes: that's the setting to turn off the automatic indexing of fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dan V." wrote in message
...
[snip]

By the way, how do you turn off the MS Access auto-index feature again
for
good per user machine?
I don't know if this is correct, but when I go to Tools \ Options \
Tables/Queries tab I erase the text ""ID;key;cod;num" in the "AutoIndex on
Import/Create" section for any field type in the drop down list and it
seems
to remember that for all field types and the next blank database I create
on
that machine.

Sound correct?

Thanks John, that is what I need to use then.
I did use MS Access help, but for help on queries MS Access is very poor.
You barely see any SQL text examples and maybe casting in SQL was buried
somewhere, but too deep for me. I didn't think to look in VBA for SQL
answers.

I could use Cstr(), though that still isn't the normal SQL syntax that other
Databases use. If it can't handle NULL's then that is really useless for
me. I will try Format() as you say.

I very much appreciate your response though and will try that out.
So you are saying Access does not support the basic SQL call, Cast as
string, or Cast as number...type of thing?

I would like to use the same function / SQL call to transform any field
value to string - this sounds difficult in MS Access or am I missing
something?

thanks,

"John Nurick" wrote in message
news
Hi Dan,

Str() is an holdover from the early days of Basic. If you read the
documentation you'd have realised that it doesn't do what you thought it
did.

Look up "Type conversion functions" in VBA help and you'll find a fairly
full set. CStr() does its best to convert whatever you pass it into a
string, but chokes if you pass it a NULL; if that's an issue you need to
handle it explicitly with Nz(). Also check out Format(), which helps
cast numeric and date values into consistently structured strings.

On Thu, 19 Jan 2006 13:43:21 -0500, "Dan V." wrote:


I am trying to use the SQL function CAST to transform any table column
into
text. I thought with Access you had to use Str(). If you don't please
let
me know!

I wanted to develop a reusable query, where a novice could just replace
any
column name and field name to update something. And all columns should
be
able to be cast to string.

The update query is very limited when you want to join on more than one
field so I sometimes force a join by calling cast on any field. Here is
an
example that works in Paradox that I was trying to run in MS Access:

//////* multiple field join for Update query by calling CAST*/

Update "MD.DB"
Set GlobalActivity = (GlobalActivity * 2)
Where CAST(Site_cod AS Char(3)) + '.' + CAST(Patient_cod AS Char(14)) +
'.'
+ CAST(VisitDate As Char(25)) IN
( SELECT CAST(ID.Site_cod AS Char(3)) + '.' + CAST(ID.Patient_cod AS
Char(14)) + '.' + CAST(ID.VisitDate As Char(25))
FROM "MD.DB" MD
INNER JOIN "NormID.DB" ID
ON (MD.Site_cod = ID.Site_cod)
AND (MD.Patient_cod = ID.Patient_cod)
AND (MD.VisitDate = ID.VisitDate)
WHERE ID.PageID = 9216)


"John Nurick" wrote in message
.. .
On Wed, 18 Jan 2006 16:48:59 -0500, "Dan V." wrote:


I read somewhere that minus signs or plus signs in access fields are
bad
news.
Is that true? If so this is a major bug with MS Access.

I can't 'cast' using str() if a dash is in the middle of a text field.
will
get "#Error" in the query cell.

Example: MDID is a text field with some dashes in some of the rows.

SELECT *, Str(MDID)
FROM 59476;

What you describe sounds like the normal behaviour of Str(). If you
read
the help topic you'll find that it takes a NUMERIC expression and
stringifies it. If you pass it a string, VBA's automatic type
conversion
will have a go at it and pass the result to Str(), so

?Str(99)
99
?Str("99") 'Automatic type conversion
99
?Str(99-42) 'Evaluates an numeric expression before converting
57
?Str("99-42")
Type Mismatch 'Can't evaluate a string

But since a text field can only contain a string or NULL, it seems
utterly pointless to use Str() on it. What are you really trying to do?



--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.




--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Thanks John! for getting me on the right track.

Instead of casting to string in Microsoft access 2003 I had to use the
format function and this syntax:
Format([MDID],"&;")

This works for NULLs and text that has a dash in the middle of it.
And for anyone who likes to look at the actual SQL in MS Access, here it is.

SELECT Format([MDID],"&;") AS newMDID, MDID
FROM [59476];

"Dan V." wrote in message
...
Thanks John, that is what I need to use then.
I did use MS Access help, but for help on queries MS Access is very poor.
You barely see any SQL text examples and maybe casting in SQL was buried
somewhere, but too deep for me. I didn't think to look in VBA for SQL
answers.

I could use Cstr(), though that still isn't the normal SQL syntax that
other
Databases use. If it can't handle NULL's then that is really useless for
me. I will try Format() as you say.

I very much appreciate your response though and will try that out.
So you are saying Access does not support the basic SQL call, Cast as
string, or Cast as number...type of thing?

I would like to use the same function / SQL call to transform any field
value to string - this sounds difficult in MS Access or am I missing
something?

thanks,

"John Nurick" wrote in message
news
Hi Dan,

Str() is an holdover from the early days of Basic. If you read the
documentation you'd have realised that it doesn't do what you thought it
did.

Look up "Type conversion functions" in VBA help and you'll find a fairly
full set. CStr() does its best to convert whatever you pass it into a
string, but chokes if you pass it a NULL; if that's an issue you need to
handle it explicitly with Nz(). Also check out Format(), which helps
cast numeric and date values into consistently structured strings.

On Thu, 19 Jan 2006 13:43:21 -0500, "Dan V." wrote:


I am trying to use the SQL function CAST to transform any table column
into
text. I thought with Access you had to use Str(). If you don't please
let
me know!

I wanted to develop a reusable query, where a novice could just replace
any
column name and field name to update something. And all columns should
be
able to be cast to string.

The update query is very limited when you want to join on more than one
field so I sometimes force a join by calling cast on any field. Here
is
an
example that works in Paradox that I was trying to run in MS Access:

//////* multiple field join for Update query by calling CAST*/

Update "MD.DB"
Set GlobalActivity = (GlobalActivity * 2)
Where CAST(Site_cod AS Char(3)) + '.' + CAST(Patient_cod AS Char(14)) +
'.'
+ CAST(VisitDate As Char(25)) IN
( SELECT CAST(ID.Site_cod AS Char(3)) + '.' + CAST(ID.Patient_cod AS
Char(14)) + '.' + CAST(ID.VisitDate As Char(25))
FROM "MD.DB" MD
INNER JOIN "NormID.DB" ID
ON (MD.Site_cod = ID.Site_cod)
AND (MD.Patient_cod = ID.Patient_cod)
AND (MD.VisitDate = ID.VisitDate)
WHERE ID.PageID = 9216)


"John Nurick" wrote in message
.. .
On Wed, 18 Jan 2006 16:48:59 -0500, "Dan V." wrote:


I read somewhere that minus signs or plus signs in access fields are
bad
news.
Is that true? If so this is a major bug with MS Access.

I can't 'cast' using str() if a dash is in the middle of a text
field.
will
get "#Error" in the query cell.

Example: MDID is a text field with some dashes in some of the rows.

SELECT *, Str(MDID)
FROM 59476;

What you describe sounds like the normal behaviour of Str(). If you
read
the help topic you'll find that it takes a NUMERIC expression and
stringifies it. If you pass it a string, VBA's automatic type
conversion
will have a go at it and pass the result to Str(), so

?Str(99)
99
?Str("99") 'Automatic type conversion
99
?Str(99-42) 'Evaluates an numeric expression before converting
57
?Str("99-42")
Type Mismatch 'Can't evaluate a string

But since a text field can only contain a string or NULL, it seems
utterly pointless to use Str() on it. What are you really trying to
do?



--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.




--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

On Fri, 20 Jan 2006 10:12:46 -0500, "Dan V." wrote:

So you are saying Access does not support the basic SQL call, Cast as
string, or Cast as number...type of thing?

What Access supports depends on the database engine it's using. Your
ordinary .mdb database uses the Jet engine, and Jet SQL doesn't include
the CAST function. But Jet lets you call a great many VBA functions from
SQL. The combination of Access and Jet - lets you use even more,
including custom VBA functions.

I would like to use the same function / SQL call to transform any field
value to string - this sounds difficult in MS Access or am I missing
something?

There's no single Jet SQL or built-in VBA function that will do this.
CStr(Nz([TheField],""))
is about as close as you can get, though AFAIK it doesn't give you a
useful - or any - stringification of the contents of an OLE field, and
in some circumstances it may cast a Memo field to Text(255), which you
probably don't want.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

I would like to use the same function / SQL call to transform any field
value to string - this sounds difficult in MS Access or am I missing
something?

Like this:

MDID & "" as NewMDID

or this:

MDID & '' as NewMDID


& is the string concatenation operator. The concatenation operator
performs an implicit cast. Single or double quotes are acceptable.
Any string may be used.

BTW, casting between numeric types is automatic.

(david)



"Dan V." wrote in message
...
Thanks John, that is what I need to use then.
I did use MS Access help, but for help on queries MS Access is very poor.
You barely see any SQL text examples and maybe casting in SQL was buried
somewhere, but too deep for me. I didn't think to look in VBA for SQL
answers.

I could use Cstr(), though that still isn't the normal SQL syntax that
other
Databases use. If it can't handle NULL's then that is really useless for
me. I will try Format() as you say.

I very much appreciate your response though and will try that out.
So you are saying Access does not support the basic SQL call, Cast as
string, or Cast as number...type of thing?

I would like to use the same function / SQL call to transform any field
value to string - this sounds difficult in MS Access or am I missing
something?

thanks,

"John Nurick" wrote in message
news
Hi Dan,

Str() is an holdover from the early days of Basic. If you read the
documentation you'd have realised that it doesn't do what you thought it
did.

Look up "Type conversion functions" in VBA help and you'll find a fairly
full set. CStr() does its best to convert whatever you pass it into a
string, but chokes if you pass it a NULL; if that's an issue you need to
handle it explicitly with Nz(). Also check out Format(), which helps
cast numeric and date values into consistently structured strings.

On Thu, 19 Jan 2006 13:43:21 -0500, "Dan V." wrote:


I am trying to use the SQL function CAST to transform any table column
into
text. I thought with Access you had to use Str(). If you don't please
let
me know!

I wanted to develop a reusable query, where a novice could just replace
any
column name and field name to update something. And all columns should
be
able to be cast to string.

The update query is very limited when you want to join on more than one
field so I sometimes force a join by calling cast on any field. Here
is
an
example that works in Paradox that I was trying to run in MS Access:

//////* multiple field join for Update query by calling CAST*/

Update "MD.DB"
Set GlobalActivity = (GlobalActivity * 2)
Where CAST(Site_cod AS Char(3)) + '.' + CAST(Patient_cod AS Char(14)) +
'.'
+ CAST(VisitDate As Char(25)) IN
( SELECT CAST(ID.Site_cod AS Char(3)) + '.' + CAST(ID.Patient_cod AS
Char(14)) + '.' + CAST(ID.VisitDate As Char(25))
FROM "MD.DB" MD
INNER JOIN "NormID.DB" ID
ON (MD.Site_cod = ID.Site_cod)
AND (MD.Patient_cod = ID.Patient_cod)
AND (MD.VisitDate = ID.VisitDate)
WHERE ID.PageID = 9216)


"John Nurick" wrote in message
.. .
On Wed, 18 Jan 2006 16:48:59 -0500, "Dan V." wrote:


I read somewhere that minus signs or plus signs in access fields are
bad
news.
Is that true? If so this is a major bug with MS Access.

I can't 'cast' using str() if a dash is in the middle of a text
field.
will
get "#Error" in the query cell.

Example: MDID is a text field with some dashes in some of the rows.

SELECT *, Str(MDID)
FROM 59476;

What you describe sounds like the normal behaviour of Str(). If you
read
the help topic you'll find that it takes a NUMERIC expression and
stringifies it. If you pass it a string, VBA's automatic type
conversion
will have a go at it and pass the result to Str(), so

?Str(99)
99
?Str("99") 'Automatic type conversion
99
?Str(99-42) 'Evaluates an numeric expression before converting
57
?Str("99-42")
Type Mismatch 'Can't evaluate a string

But since a text field can only contain a string or NULL, it seems
utterly pointless to use Str() on it. What are you really trying to
do?



--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.




--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.