If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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? |
#12
|
|||
|
|||
Error querying Access text fields if dash or minus sign in middle of text
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. |
#13
|
|||
|
|||
Error querying Access text fields if dash or minus sign in middle of text
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. |
#14
|
|||
|
|||
Error querying Access text fields if dash or minus sign in middle of text
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. |
#15
|
|||
|
|||
Error querying Access text fields if dash or minus sign in middle of text
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. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need Feedback on table setup | Little Penny | Database Design | 1 | December 28th, 2005 11:32 AM |
Word applies direct format on File open | Uriel | General Discussion | 16 | November 27th, 2005 07:22 PM |
Is Access even the right idea? | BMB | New Users | 19 | November 21st, 2005 08:01 PM |
Add New Field to DB | Karen | Database Design | 7 | October 19th, 2005 08:03 PM |
Outline | Renee Hendershott | Page Layout | 2 | December 25th, 2004 02:49 PM |