Hi

I have a table containing post codes for boxes and street address but it is all in CAPITAL LETTERS. I do not like all letters to be capital letters (what I want is commonly called PROPER format, ie. the first letter of a word is capitalised and the rest of the letters are in lowercase. - The Hills Are Alive. I have managed to do this in excel and am now trying to do it in access. Id rather make changes to the data in access than having to import an excel file into access. How do I do this? It would seem to me that I need to make an action query. First I copies the table in question and gave it a new name - I know action queries can be a bit dangerous to use so Id prefer to have it "action" a table that is not needed, afterwards I can delete the original table and rename the actioned table to the same name as the original had. Or is there an easier way? Like I said I am sure I need to create an action query but am not sure how to do it correctly. When I try to run my action query I get this error message "The action or event has been blocked by Disabled Mode"

Take a look at the StrConv function: it supports proper case.



(Note that you cannot use the intrinsic constant vbProperCase in a query: you must use the value)

While I agree that action queries can cause issues, that's more with untested action queries. Once you know you didn't forget anything in it, I'd say they're safe to use.

As to the Disabled Mode, since the query requires the use of VBA, you have to ensure that the database exists in a trusted location, or that you've enabled it to run VBA.

Personal note: I never cared for the term "Proper case" to describe capitalization of each word. I prefer "Title Case", since it's really only "proper" to capitalize each word in a title.

Just my two cents,

Dennis

I am only dicovering my way around access at the moment. I have no idea what you just said, I created an action query on a copy of the table I want change and inserted the following into the criteria section of the query " StrConv([Field1],3) "

Nick,

While viewing your query, you've got different possible views available to you, including Datasheet View (where you can view the data selected) and Design View (where you see the design grid). There's another view, called "SQL View". Go to that one to see the raw SQL code for your query. That's where you can edit code like Doug's example.

Hope this helps,

Dennis

Thanx Dennis - I will have a look.

It worked, thanx guys.

Had a bit of trouble though. First of all changed the SQL as asked to. Tried to run the query and it still gave me the message about Disabled Mode. I went to the trusted locations and added my flash drive (the database is stored on there. ((Note to self: remove that from trusted locations, security risk)) anyway the I tried to run the query still wouldnt run and was giving me the same message. I decided that parhaps the database had to be re-oped - it was the solution and the query ran. I have two fields the needed to be converted to the proper capitalisation. So I changed the query's SQL to change the other field, I made a bit of an error and it did change the 2nd field but inserted it on top of the 1st field. There was no option to UNDO because of it being an action query. I decided that the best way would be to rename the "dummy" table I had copied off of the original, obviously deleting the original first. I couldnt delete the table because it was related to other tables, deleted the relationships, deleted the table (original), renamed the table (dummy), re-instated the relationships and hey presto.

Sorry its so long winded, just though my experience might help someone faced with the same problem.

Glad you got it working.

Just a word of advice. It's usually a bad idea to work directly from the USB key with any Office product (not just Access). Copy from your USB key to the hard drive, check whether it works, then copy it back to the USB key.

Point noted, but why is it a bad idea.

Sorry, I don't remember the reason, but I read it in a KB article a couple of years ago. Something to do with how the Office products buffer data, I think.

Well Ill follow your advice anyway.

Hi

Im once again trying to fix up on of my tables with an action query.My table of postcodes is arranged in a layout Id like to change. I have street postcodes and Box postcodes, obviously in 2 columns. Im trying to get the addresses right on my form and having 1 column of postcodes will make things a whole lot simpler. Right now I have 4 columns (Town, BoxCode, StreetCode, City) - what I want to do is combine all of the entries in the Town coloumn with those in the City column and all the entries in the BoxCode column with those in the StreetCode column - to end up with a table that has just 2 columns (namely City/town and Code) the problem is that this entries in the Town (Box codes) often have the same name as those in the city column - I dont think the Code will matter. My idea is to add the word BOX to the end of the entries in the Town column (thereby not losing any of the entries and also to add the word STREET to the end of the entries in the City column. Then how do I add the columns together? This is a mammoth task to do by have because I have 16800 rows of data in the table. I think it would be an append query to add the words, BOX and STREET.

Append queries add new rows to the table: it sounds as though you're trying to add values to existing rows, which means you'd want an Update query.

Can you give an example of what you're trying to do? Simply show 5 or 6 rows of data as it currently exists, and as you want it to look when you're done.

Thanx for the help Doug.

I figured out for myself that it needs to be an update query, once its is updated then Ill probably use an append queries to add the columns together.

Line as as they currently show:

TownID Town BoxCode StreetCode City
1 Alphaville 1234 Durban
2 Alice Springs 7548 7548 Alice Springs
3 Bentley 3548 Cape Town
4 Bloomers 2587 2587 Bloomers
5 Capital 1596 Capital

What I want:
TownID CityTown Code
1 Alphaville - BOX 1234
2 Alice Springs - STREET 7548
2 Bentley - BOX 3548
4 Bloomers - STREET 2587
5 Capital - STREET 1596

The entries in the Town column with a code to be re-entered but now with - BOX at the end
The entries in the City column with a code to be re-entered but now with - STREET at the end
The entire town and City columns to be joined (appended)
The entire BoxCode and StreetCode columns to be joined (appended)

Sorry: since the data isn't lined up, I can't determine what it is you're trying to do.

Please either put [ code ] [ /code ] tags around your data (which is fiddly to get things to line up properly: you'll likely have to preview the post several times! If you're not familiary with previewing your posts, you click on the More Options button, rather than simply submitting the post) or put some sort of delimiter between the values.

UtterAccess forum does not like tabs or spaces.

In the section "as they currently show" there are 5 coloumns (namely TownID, Town, BoxCode, StreetCode and City)

In the section "what I want" there are 3 columns (namely TownID, CityTown and Code)

I see the empty spaces have been removed from my example lines, trying again but with . instead of spaces

Lines as as they currently show:
TownID...............Town...........BoxCode.................StreetCode..........
......City
1........................Alphaville...........................................12
34..............Durban
2........................Alice Springs....7548...........................7548..............Alice Springs
3........................Bentley..............................................35
48..............Cape Town
4........................Bloomers.........2587...........................2587...
............Bloomers
5........................Capital..............................................15
96................Capital

What I want:
TownID...............CityTown..........................Code
1.........................Alphaville - BOX...............1234
2........................ Alice Springs - STREET......7548
2........................Bentley - BOX...................3548
4.......................Bloomers - STREET.............2587
5......................Capital - STREET..................1596

Hope you can read it now. A effort and a half

In excel format - what I should have done in the first place.

Oh, that's little different: You never mentioned the need for BOX, STREET before, nor the need to have both Town and City name! However, we're getting there (although I think you made a mistake for the value for Code for Bloomers and Capital: they should be 2587 and 1596 respectively, not 2547)

Please confirm the following cases:

• if Town and City are the same, BoxCode has a value and CityCode has no value then set CityTown = tttt - BOX and Code = bbbb
• if Town and City are the same, BoxCode has a value and CityCode has same value then set CityTown = tttt - BOX, STREET and Code = bbbb
• if Town and City are the same, BoxCode has no value and CityCode has a value then set CityTown = tttt - STREET and Code = xxxx
• if Town and City are different, BoxCode has a value and CityCode has no Value then set CityTown = tttt, cccc - BOX and Code = bbbb
• if Town and City are different, BoxCode has a value and CityCode has same value then set CityTown = tttt, cccc - BOX, STREET and Code = bbbb
• if Town and City are different, BoxCode has no value and CityCode has a value then set CityTown = tttt, cccc - STREET and Code = xxxx

(where tttt is the value of Town, cccc is the value of City, bbbb is the value of BoxCode and xxxx is the value of CityCode)

Questions:

1. What value should be put in Code if BoxCode and CityCode both exist, but have different values?
2. What should be done if there is no value for either BoxCode or CityCode?
3. When BoxCode or CityCode don't have values, will the field be Null, or a zero-length string ("")?
4. Is it ever possible that Town or City will not have a value? (if so, will they be Null or a zero-length string?)
5. Have you considered creating a new table, rather than updating the existing one?

For future reference, when trying to do columns, I use Windows Notepad, set to a non-proportional font, like Lucida Console or Courier New. Just using spaces and the Delete key to nudge things around until I have nice columns of data. Then, I just copy/paste into my post, and wrap it all in Code tags.

Hope this helps,

Dennis

Let me answer your questions:

1. I have filtered the list and if a different value exists in BoxCode and CityCode then the name will become the name of the town or city with the word added (BOX) for BoxCode and the word added (STREET) for CityCode.
2. There are no rows with a null entry for BoxCode and also for CityCode.
3. If the is a row with no value in either BoxCode or in CityCode then the value will be "".
4. Town has an entry in every row, City does contain empty rows.
5. I am using a copy of the original table - just incase the results are not as expected.

I am going to export the table into excel and attach to this post. You will notice in the list of City names sometimes "-" appears I take it that the original creator of this spreasheet (I downloaded it from the internet) intended the City name to be the same as what is in the Town name field. I did find at least one letter entry in the BoxCode field, I dont know what this should be.

On further looking at the desired results I made a NEW excel spreadsheet of what Im looking for, hope it is a help to you.

Thanks, Dennis, that's what I do too. I'd actually cobbled together instructions, but didn't bother posting them once the spreadsheet was posted.

@nickynoo: Your new spreadsheet introduces more confusion!

I don't understand why you'd want to create rows without a value for TownID. You say that TownID is the primary key: you cannot create rows that don't have a value for the primary key! I'm also unsure what you mean by "The fields with a "-" in the City field must be sorted out." (And you still seem to have the wrong codes for Bloomers!)

Given your desired end result, it would definitely be better to run a query that creates a new table for you, rather than try to update an existing table.

See whether this SELECT query will give you what you want:

The new rows are created once a new entry has to appear in the table - i.e. when Ive got one city name that is either box or street code - these "new entries" will need primary key autonumbers aswell, there is no intention of them being left blank.
If you look throught the city names you will see a number of cities indicated with a " - ". I think the author of the spreadsheet intended the same name (in that row) as town (in that row) to be inserted there.
I may have the code wrong for Bloomers but never mind about that as it was only a ficticious example I produced, the real information is held in the spreadsheet.

I will try the SELECT query but I think things will be easier if I edit the table in excel, I know the power of access lies in it queries but if you have a table you cant query properly or that requires great effort to do so then I dont see the point of battling to get something right. What Im trying to achieve is to have the table designed how I want it so that I can use access to use it - be it for forms, queries or anything else.

No, I believe it'll be much easier to create a query that transforms your initial data to the final data you want, not the intermediate table you suggested. However, you need to find out what the dashes are intended to mean.

Doug Im so sorry if my last post seemed to come off as being rude but I was frustrated - something happened at work yesterday and it frustrated me, I took it out on you - please accept my apologies.

I tried using excel to edit the data last night, a nightmare because I didnt know the formulas I had to use. I got even more frustrated searching the internet for the correct formulas.

I think we will approach this access table problem in such a way: first I want to replace the values of ( - )(dash) in the City column with the town name that appears in the town column.

The blank entries in the City field I have no choice but to enter manually, there is a booklet of postcodes in this country, Ill have to lookup the relevant town name and see what should be in the city field. A tedious job.

My thinking - dont try to insert everything that the query needs to do, to avoid confusion, let us do it one step at a time.

Eventually we will get there!

Don't worry: I didn't think you were being rude.

I was trying to point out that the intermediate table you have in your spreadsheet (where some of the entries have both BOX and STREET associated with them) is actually relatively hard to produce. And given that your ultimate goal is to have two rows for such tables, I think you'll find the UNION query I proposed will produce your desired end result in a single step. All we need to worry about is assigning TownID values for the new rows, and handling the dashes in the City column.

Now, are the existing TownID values important, or can we assign new values? If we can assign new values, then assigning values for TownID in the new table becomes trivial.

Correction - My ultimate goal is to have 4 columns (TownID (Autonumber); Place; City and Code)
The TownID column is not important regarding the number order as this is purely an access column. Im am not using these autonumbers at the moment.
In the Place column I want all the places - those currently in the Town column, if in the original table there is a BoxCode entry then the string in the Town column will become "$string$ - BOX" if in the original table there is a CityCode entry then the string City column will become "$string$ - STREET", if there is an entry in the BoxCode column as well as in the CityCode column then a new row needs to be created duplicating the name in the Town column - the original Town column entry will now become "$string$ - BOX and the string in the new row will become "$string$ - STREET".
THerefore the TownID column will have an autonumber, the place column will have the place (currently the town column), the city column will have the city (currently the city column and also the new rows) and the code will have the postcode (the only number field)
I would like the table to read: 62......Buckley - BOX........Durban........4000 or 63.......Buckley - STREET.........Durban........4000 or 64.......Durban.......Durban........4001

Im not sure how to explain this, but for address perposes I need to know the City that the place is located in. I.e. an entry in the new table should read as follows TownID -> 64; Place -> Athlone; City -> Capetown; Code -> 8001

I currently have 16700 rows in the original table, with these changes I expect the table to double in size.

Please let me know if anything is unclear.

Did you try running the UNION query I suggested yesterday? I believe it'll give you exactly what you want...

If it does, then the trick is to create a new table with the 4 fields you mention, with TownID being an AutoNumber field. You can then use the UNION query to populate that new table:

I will give the union query a go - and report back.

Ive just tried the last query you posted while it dis add BOX to entries there, no entries were added with STREET added were inserted into the table, they were deleted I think because I originally had 16700 records and the records in the new table amounted to just over 8000. Also when I ran the query I was asked for a parameter for citycode, I entered space and clicked OK.

How Im going to approach this thing, I am going to try an work out how to "find and replace" in an access query. The main problem there is when I find (dash) to replace it needs to be replaced with the entry in the town column - I dont know how to do this as yet. doing this PIECE by PIECE is the key I think plus it will enable me to learn about action queries. I do see hoe access is more powerful than excel in data manipulation though.

Sorry, my typos.

Im busy going thru the table manually and filling in the blanks - oh my fingers. Gonna give your query another go.

Your query seemed to have worked - however I dont know if things are correct. I see I still have dashes - how do I replace those dashes with what is in the suburb field? Also how do I get this query to output this data in a new table.

Assuming I've understood you correctly (use the name of the town when the city contains a dash), this should eliminate the dashes:



Assuming that's good, then as I already said, ensure your new table has been created with TownID as an AutoNumber field, then use the following to populate it:

Doug Im a little lost - the query I have manipulates the data from the table (adds BOX and STREET, add new rows) this query that you have given me now for the removal of the dashes - is that a new query, is it a replacement for withs already in the sql of the existing query or am I wrong on both accounts? Also if it is a new query - can a query act on a query?

I see in your code for the query to eliminate the dashes a possible error

AS Suburb, IIf(MyTable.[City] = "-",

should that be If or IF?

I gave you two queries in my last response. The first query replaces the UNION query you were already using. Assuming that it gives you what you want, the second query uses the same UNION query as the basis of an Append query. The second query added 4 lines before the UNION query you were using:


and one line after it:


The query should be correct as written: it uses IIf (Immediate If), not If, because If is only usable in VBA, whereas IIf is a function that can be used in VBA or in queries.

Doug your query is working

I need to ammend the query slightly there were dashes in the city field - now they are replaced - PERFECT but there are also blank lines in the city field to which i want to apply the same procedure.

Your first line of code reads as follows:
SELECT TownID, Town & " - STREET" AS Suburb, IIf(MyTable.[City] = "-", MyTable.[Town], MyTable.[City]) AS

Do I just say ----- AND " " next to "."

Once I have that right I do the new table thing.

I figured it out

its OR " " after "-" and it needs to be in the SELECT row and also in the UNION SELECT row.

Now to insert into a new table.

Ive added the new table thing

INSERT INTO NewTable(Place, City, Code)
SELECT Suburb, City, Code
FROM
(
SELECT TownID, Town & " - STREET" AS Suburb, IIf(tbluTownCOPY.[City] = "-" OR " ", tbluTownCOPY.[Town], tbluTownCOPY.[City]) AS City, StreetCode AS Code
FROM tbluTownCOPY
WHERE StreetCode IS NOT NULL
UNION SELECT TownID, Town & " - BOX" AS Suburb, IIf(tbluTownCOPY.[City] = "-" OR " " , tbluTownCOPY.[Town], tbluTownCOPY.[City]), BoxCode
FROM tbluTownCOPY
WHERE BoxCode IS NOT NULL;
)

when I try to save it access give me the following error message "Syntax error in FROM clause."

You can't "short circuit" comparisons like that.

it is still giving me the same error message "Syntax error in FROM Clause"

Aargh! Remove the semi-colon on the second-last line.

Sorry about that.

Hi thanx for the help, it seems to have worked but when I run the query now it gives me another error message. I think access it trying to tell me that it couldnt convert one of the rows, this is what is displayed.

Microsoft Office Access cant append all the records in the append query
Microsoft Office Access set one field to Null due to a type conversion failure, and it didnt add 0 records to the
table due to key violations, 0 records due to lock violations, ) records due to validation rule violations.
Do you want to run the action query anyway?
To ignore the errorsa and run the query, click YES.
For an explanations of the causes of the errors, click HELP.

I clicked yes and it seems to work, however I see it is only one records so I can manually edit this field but how do I know which record and where to find it?

Bit of an update.

Ive been trying to sort out this query on my own, I tried a few things and now the error message I spoke of earlier in a post has dissapeared. but on looking at my newly created table I see that it had blank spaces in the city column, this is not what I need.

Just to be clear, the original UNION query returns multiple rows?



Open tbluTownCOPY in Design view and take a look at the various fields. Make sure it says Text for the Town and City fields. Note the data types of BoxCode and StreetCode.

Open the new table in Design view as well. Make sure TownID is an AutoNumber field, and that Suburb and City are text fields. Make sure Code has the same data type as BoxCode and StreetCode in tbluTownCOPY.

Another update - I went back got the query working and extracting and converting the correct information then I got it to insert it into a new table - it works perfectly now. One extra question, in all the trying and failing and then evetually getting it right my autonumbers have grown to start at 159459 instead of 1 - is it necessary to change this - if so how? Delete the autonumber field, save and reopen the table then add the autonumber field again?

And it worked

Glad you got it working. Not that it matters, but compacting the database before doing the final insert should have started the AutoNumber counter back at 1.

Realistically, no significance should ever be given to the value of the AutoNumber field. AutoNumber fields exist for one purpose only: to provide a (practically guaranteed) unique value that can be used as a primary key. 159459, 159461, 159462 fulfills that needs just as well as 1, 2, 3 does.