Hi Nick,
I'm not sure what you mean by this talking about Combo Box...
Somehow I got the idea that you were using your SQL statement (ie. query) as
the Rowsource for a combo box. Apparently not, so you can disregard that part
of my previous answer.
Ah, so Access will see 01234 567 890 or 01 234 567 890 in a field as
01234567890 ?
No, it won't treat these the same.
If so I don't need to go through the painful process of
removing several thousand spaces...
You'll have to go through the process of removing the spaces, but who gave
you the idea that this would be a painful process? A simple update query
should do the trick. Of course, you will only run any type of action query
(Update, Append, Delete or Make Table) ONLY after making a backup of your
database, in case something goes horribly wrong.
So, after you have your backup safely made, fire up a new query and go
directly to the SQL View. By the way, I didn't explain this before, but when
you create a query using the QBE (Query By Example) grid, Access is actually
writing the SQL statement for you in the background. By default, Access uses
the JET database engine, although one can use other database engines, such as
SQL Server, Oracle, Sybase, etc. with an Access application. Anyways, the
language that RDBMS (Relational DataBase Management Systems) speak is SQL,
which you now know stands for Structured Query Language. An excellent way to
start learning SQL is to look at queries you have created in the normal QBE
design view, by displaying the corresponding SQL view. Not all SQL statements
can be represented in the more familiar design view, but most of them can be.
Another good resource is to pick up a copy of the book "SQL Queries for Mere
Mortals", by John Viescas.
Okay, so back to that SQL statement that you will need. It would look
something like this, assuming you are running Access 2000 or later (the
built-in Replace function was introduced in Access 2000):
UPDATE [Table Name]
SET [Table Name].[Field Name] = Replace([Field Name],Chr(32),"")
WHERE [Table Name].[Field Name] Is Not Null
[Table Name] is the name of your table and [Field Name] is the name of the
field that you need to update. Make the appropriate substitutions in the
names indicated in the SQL statement. My example even includes spaces in both
the table and field names. Just don't forget to make that backup copy first,
before running this update query!
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
NickTheBatMan said:
Hi Nick,
The OTHER problem I had was that I'd named the table as 2 separate
words and as I've found Access doesn't like that either...
Well, Access (and the JET database engine) can deal with this fine, as long
as you always remember to surround any references to objects with spaces in
their names with square brackets. For example, [Order Details]. The sample
Northwind.mdb database is just full of bad naming examples that violate
Microsoft's own guidelines!
Ah ha, thank you for that, another thing learnt
Good to see that
MS provide good examples as usual
You're welcome. May I suggest that you download a copy of a zipped Word
document that I call "Access Links.doc"? I plan on reposting this document as
a better organized web page sometime in the future, but for now it is a Word
document. I recommend that you pay really close attention to the first (3)
pages, and also to the two links shown in red font on page 4. Just skim the
rest of the document every now and then, so that you have a general idea of
the types of information that it includes. But pay really good attention to
the first three pages in particular. You can download a zipped copy from here:
http://www.accessmvp.com/TWickerath/
Thank you again
I'm at work and for some reason they won't let me download it - shall
do so at home tonight...
Also, an excellent resource is Access MVP Crystal's Access Basics tutorials,
available here:
http://www.accessmvp.com/Strive4Peace/Index.htm
Many thanks for this too, I shall see what I can work from that too...
You can do this, however, a combo box will only display the first column of
width greater than zero when it is not dropped down. You will only see the
second column when you click on the combo box to open it up. His first
version should do the trick:
SELECT table1.name, table1.home, table1.mobile
FROM table1
WHERE (((table1.home) Is Not Null));
Notice that the concatenation operator (+ sign) has been replaced by a
comma. Make sure that the column count matches (3 in the above case), and
that the column widths are set appropriately.
I'm not sure what you mean by this talking about Combo Box but am sure
I'll learn - using the above I see that Access creates columns where a
comma is inserted into the script, I can now create new tables from
that and get things going as I want...
I'm not understanding you on this issue. Spaces in data values should not be
a problem. The advice to avoid spaces applies to things that you assign a
name to within Access (ie. fields, tables, queries, forms, reports, macros,
modules, controls on forms and reports, variable names, etc.). It does not
apply to spaces that are a part of the data stored in the tables.
Ah, so Access will see 01234 567 890 or 01 234 567 890 in a field as
01234567890 ? If so I don't need to go through the painful process of
removing several thousand spaces...
As I think I said, I'm working on a db that has already been produced
in a certain format and am trying to import loads of data from
documents and spreadsheets that have been produced in other formats by
other people !
Many thanks again Tom
If I need any more assistance I'll be back...
Nick
__________________________________________
- Show quoted text -
