Problems with trimming combined fields when using Make-table 2

[KarlBrix]

I need to trim a field when creating a new table with make-table, but I have encountered problems, whatever I have done.
What I did in Queries in Access 2000:
1. ((tblFund.Nr)&(tblFund.Date) AS JournNr), among similar stuff, then...
2. Trim(tblNew.JournNr) AS Something, as a Make Table query creating table Test2.
I have tried Left(tbl.field,12) and LTrim()
3. Result in new created table Test2: Field length char 255.

Are there any other possibilities I haven't tried in query or VB, whatsoever?
Best regards
Carlito

 

[JoeMiller]

I assume you are trying to trim two fields and combine them into 1, it's not very clear what you're trying to do from your initial post. In a column of your query place this in a field:

JournNr: Trim(tblFund.Nr) & Trim(tblFund.Date)

HTH Joe Miller
joe.miller@flotech.net

 

[tempclerk]

Are you saying that after you creat your new table, Len([JournNr])=255, or just that the Field Size in your new table is the default 255? I don't know if you can control that in your query, but under Tools, Options, Tables/queries you can change your default field size.

 

[KarlBrix]

Thanks guys, but it is more or less what I have tried myself.
My aim is to create a new field by combining 2(Are there any differences between using + or & ?), which is no problem, although I want the new field to be only 12 char long (text). The two fields are 40 and 50 char long (I didnt made it!).

This should be done with either Trim() or Left(tblField,12). However, the new field in the new table is 255 char long whatever is done.

I checked in Tools>Options, and tried the 2xTrim(). Didnt work either.
Could it possibly be a bug in Access2000, which I have to repair by downloading stuff from MS?
karlbrix

 

[JoeMiller]

Ahhh, so you are trying to combine two fields, and once they are combined take the leftmost 12 characters?? If that's the case then this is what you need:

JournNr: Left([MyField1]&[MyField2],12)

If this is still not correct, please provide sample data and what the correct result should be after performing this "magic" function.
Joe Miller
joe.miller@flotech.net

 

[KarlBrix]

Here is what I did:
In: Table tblFundanmeldelse2
tblFundanmeldelse2 = 40 char

in Query sqlFundanmeld:
SELECT ('OBM'+(tblFundanmeldelse2.Jnr0) AS OBMJournNr, etc...

In query sqlBySagsGISDB2:
SELECT Left(sqlFundanmeld.OBMJournNr,12) AS JournNr
INTO tblTest2

In tblTest2:
JournNr = 255 char

(I tried in sqlFundanmeld with ('OBM'+Trim(tblFundanmeldelse2.Jnr0)) AS OBMJournNr, but without much luck)
I know, that this example only includes 1 field + text, but the problems seem to be the same as with 2 fields.

Thanks a lot so far
karlbrix

 

[tempclerk]

I think the problem has nothing to do with your expression. I just ran a make-table query with only expr1: "test", no trimming needed, and the field size of expr1 in the resulting table was 255, even though my default text field size is set to 50. There doesn't seem to be a way to set the field size from a query.

 

[KarlBrix]

Thanks Joe
I just tried, what you wrote. It does take the characters needed 4, 12 or whatever from left. But, I have probably not correctly explained, that I need the field size of the new field to be minimized according to this. It is still 255 char.
Thanks a lot anyway.
Karlbrix
PS. I do not have much experience in macros or VisualBasic, but perhaps it should be using this to do the job instead of in a query. What do you say?

 

[tlbroadbent]

Setting the default field or column size Access doesn't make a difference when running a Make Table Query that has concatenated fields as the source for one or more columns. Nor does truncating the data with LEFT or simlar functions make a difference.

It appears Access will always set the column size as 255. You can change the size by going into table design view and entering the size you want after creating the table. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[KarlBrix]

Sounds like a reasonable and easy solution from tlbroadbent.
However, if new data is entered into my original table (called tblFundanmeldelse2), how I am to update the new table, without altering the new field setting or overwriting the table.
What could I use then instead of Make table?
Best regards
karlbrix

 

[tlbroadbent]

Use an append query. You can open the Make Table query in desgn view. To convert it Select Query | Append Query from the menu. Access should fill in all the information. You can then save the Append query. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[tempclerk]

An Append query will not alter the structure of a table. Nor will an Update query, if you mean to change data in existing records.

 

[KarlBrix]

I think it will help a lot! I will get back tomorrow if it didnt work. Next thing for me is to make it automatical via a macro. I guess I'll be back.
Best regards and thanks a lot.
Karlbrix

 

[KarlBrix]

You are right tempclerk, but when I changed the field size in the new table, the size was fixed still after appending or updating. So it works, but it is probably not a perfect way to do, though is it fairly straightforward. If you have more efficient ideas, please let me know.
karlbrix