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

D

Dan V.

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;

Also, I ran this update query and the text field join did not update rows if
the join text field had a dash in it like: "12-45".

Update [59476] tbl1
INNER JOIN [59476 update] tbl2
ON (tbl1.SiteID = tbl2.SiteID) AND (tbl1.MDID = tbl2.MDID)
SET tbl1.field1 = tbl2.field1;
 
D

Dan V.

I still got the '#Error' in the results cell grid for those 'dashed fields'
when querying the field MDID if the column property is indexed or not with
the following query.

SELECT Str(MDID) AS Expr1, *
FROM [59476 Comment update];


Is there an update for this major bug?

What you say seems right, one of the MDID text fields I was synching was
indexed for some strange reason.
I can not believe that these 4 texts "ID;key;cod;num" all trigger auto index
as default for MS Access.

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?



Allen Browne said:
JET 4 (Access 2000 and later) handles the dash character inconsistently,
depending on whether the field is indexed or not.

Details in:
Query Returns no Records with an Indexed Field That Contains Dashes
at:
http://support.microsoft.com/kb/271661/en-us

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dan V. said:
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;

Also, I ran this update query and the text field join did not update rows
if
the join text field had a dash in it like: "12-45".

Update [59476] tbl1
INNER JOIN [59476 update] tbl2
ON (tbl1.SiteID = tbl2.SiteID) AND (tbl1.MDID = tbl2.MDID)
SET tbl1.field1 = tbl2.field1;
Click to expand...
Click to expand...
 
D

Dan V.

according to that MS KB article:
SYMPTOMS
When you run a query against data that contains dashes, and you are using
the LIKE operator as criteria in the query, no rows are returned.


It should be obvious but I am not using the 'like' operator.

There are some Jet 4.0 updates listed there, so I will try that out.
Thanks.


Allen Browne said:
JET 4 (Access 2000 and later) handles the dash character inconsistently,
depending on whether the field is indexed or not.

Details in:
Query Returns no Records with an Indexed Field That Contains Dashes
at:
http://support.microsoft.com/kb/271661/en-us

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dan V. said:
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;

Also, I ran this update query and the text field join did not update rows
if
the join text field had a dash in it like: "12-45".

Update [59476] tbl1
INNER JOIN [59476 update] tbl2
ON (tbl1.SiteID = tbl2.SiteID) AND (tbl1.MDID = tbl2.MDID)
SET tbl1.field1 = tbl2.field1;
Click to expand...
Click to expand...
 
D

Dan V.

Summary: Didn't work.

I am getting all results displayed in the query, as opposed to the MS
article which said 'no records' should be found. The dashed results are set
to "#Error" but all other rows show up fine in the qurey.

I do have the latest service packs and critical updates installed: Win 2000
SP4 and Rollup 1. (confirmed in Updates history) and am even running
Microsoft Update (includes office updates) for Windows 2000.

The dll files that I am using ,confirmed by the MDAC Component checker tool
from Microsoft , (General scan and click on File Details) are the newest
version as far as I can tell.

These file: MSWDAT10.DLL and MSWSTR10.DLL are both version: 4.0.6508.0
My file: Msjet40.dll is version: 4.0.9025.0


So I have latest 'fixes' installed...

Any ideas?
thanks.


Dan V. said:
according to that MS KB article:
SYMPTOMS
When you run a query against data that contains dashes, and you are using
the LIKE operator as criteria in the query, no rows are returned.


It should be obvious but I am not using the 'like' operator.

There are some Jet 4.0 updates listed there, so I will try that out.
Thanks.


Allen Browne said:
JET 4 (Access 2000 and later) handles the dash character inconsistently,
depending on whether the field is indexed or not.

Details in:
Query Returns no Records with an Indexed Field That Contains Dashes
at:
http://support.microsoft.com/kb/271661/en-us

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dan V. said:
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;

Also, I ran this update query and the text field join did not update rows
if
the join text field had a dash in it like: "12-45".

Update [59476] tbl1
INNER JOIN [59476 update] tbl2
ON (tbl1.SiteID = tbl2.SiteID) AND (tbl1.MDID = tbl2.MDID)
SET tbl1.field1 = tbl2.field1;
Click to expand...
Click to expand...
Click to expand...
 
D

Dan V.

The result of the MDAC checker tool was that I am running:

MDAC 2.7 SP1
MDAC 2.7 SPI REFRESH

Should I install a later version if there is one?

Dan V. said:
Summary: Didn't work.

I am getting all results displayed in the query, as opposed to the MS
article which said 'no records' should be found. The dashed results are set
to "#Error" but all other rows show up fine in the qurey.

I do have the latest service packs and critical updates installed: Win 2000
SP4 and Rollup 1. (confirmed in Updates history) and am even running
Microsoft Update (includes office updates) for Windows 2000.

The dll files that I am using ,confirmed by the MDAC Component checker tool
from Microsoft , (General scan and click on File Details) are the newest
version as far as I can tell.

These file: MSWDAT10.DLL and MSWSTR10.DLL are both version: 4.0.6508.0
My file: Msjet40.dll is version: 4.0.9025.0


So I have latest 'fixes' installed...

Any ideas?
thanks.


Dan V. said:
according to that MS KB article:
SYMPTOMS
When you run a query against data that contains dashes, and you are using
the LIKE operator as criteria in the query, no rows are returned.


It should be obvious but I am not using the 'like' operator.

There are some Jet 4.0 updates listed there, so I will try that out.
Thanks.


Allen Browne said:
JET 4 (Access 2000 and later) handles the dash character inconsistently,
depending on whether the field is indexed or not.

Details in:
Query Returns no Records with an Indexed Field That Contains Dashes
at:
http://support.microsoft.com/kb/271661/en-us

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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;

Also, I ran this update query and the text field join did not update rows
if
the join text field had a dash in it like: "12-45".

Update [59476] tbl1
INNER JOIN [59476 update] tbl2
ON (tbl1.SiteID = tbl2.SiteID) AND (tbl1.MDID = tbl2.MDID)
SET tbl1.field1 = tbl2.field1;
Click to expand...
Click to expand...
Click to expand...
 
J

John Nurick

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;
Click to expand...

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?
 
D

Dan V.

The KB article mentioned this applies to MS Access 2000 but I am running MS
Access 2002.

Does that help?

Dan V. said:
The result of the MDAC checker tool was that I am running:

MDAC 2.7 SP1
MDAC 2.7 SPI REFRESH

Should I install a later version if there is one?

Dan V. said:
Summary: Didn't work.

I am getting all results displayed in the query, as opposed to the MS
article which said 'no records' should be found. The dashed results are set
to "#Error" but all other rows show up fine in the qurey.

I do have the latest service packs and critical updates installed: Win 2000
SP4 and Rollup 1. (confirmed in Updates history) and am even running
Microsoft Update (includes office updates) for Windows 2000.

The dll files that I am using ,confirmed by the MDAC Component checker tool
from Microsoft , (General scan and click on File Details) are the newest
version as far as I can tell.

These file: MSWDAT10.DLL and MSWSTR10.DLL are both version: 4.0.6508.0
My file: Msjet40.dll is version: 4.0.9025.0


So I have latest 'fixes' installed...

Any ideas?
thanks.


Dan V. said:
according to that MS KB article:
SYMPTOMS
When you run a query against data that contains dashes, and you are using
the LIKE operator as criteria in the query, no rows are returned.


It should be obvious but I am not using the 'like' operator.

There are some Jet 4.0 updates listed there, so I will try that out.
Thanks.


JET 4 (Access 2000 and later) handles the dash character inconsistently,
depending on whether the field is indexed or not.

Details in:
Query Returns no Records with an Indexed Field That Contains Dashes
at:
http://support.microsoft.com/kb/271661/en-us

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I read somewhere that minus signs or plus signs in access fields
Click to expand...
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;

Also, I ran this update query and the text field join did not update
rows
if
the join text field had a dash in it like: "12-45".

Update [59476] tbl1
INNER JOIN [59476 update] tbl2
ON (tbl1.SiteID = tbl2.SiteID) AND (tbl1.MDID = tbl2.MDID)
SET tbl1.field1 = tbl2.field1;
Click to expand...
Click to expand...
Click to expand...
 
D

Dan V.

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)
 
J

John Nurick

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.
 
A

Allen Browne

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.

Reply to group, rather than allenbrowne at mvps dot org.

[snip]
 
D

Dan V.

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,
 
D

Dan V.

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];
 
J

John Nurick

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

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?
Click to expand...

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.
 
G

Guest

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?
Click to expand...

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ignore Leading Zeros in Mixed Alpha-Numeric Text Fields Select Query 5
Update Yes/No on One table depending on Set of Records on another 2
Is Access SQL FROM Clause different from MS SQL Server SQL? 1
Maximum length of concatenation in Access 2003 6
Transaction Processing 1
Problem with converting Text to Number 3
Ability to update two fields in a form based upon a multiple table query 3
Dash or Minus sign shos as zero in formula 2

Top