Accesss change text to number

[sierralightfoot]

I have an access table
I want to change:

text field 444 444 444 to a number field 444444444
text field 444-444-444 to number field 444444444

 

[Steve Schapel]

Sierra,

Add the number field to the table. Then make and run an Update Query to
update NumberField to...
Val(Replace(Replace([TextField]," ",""),"-",""))

Please post back if you need more detailed help with any of this.

 

[sierralightfoot]

Can I do this at the same time that I use a "create table query"

or

can I do it by changing the properties (format or input mask) of the field
after I have run the "create query table"?... make it default to "no space"
"no dash"

second: Do I understand that I cannot create relationships between two
tables if the tables are a created by a "link" to other data?

third:
I have two tables with almost identical fields. I can't see that either
tables has any field that has unique values. Every field could have multiple
matching fields. I want to query the same field (called "status") in each
table using creteria (called "expired or inactive or withdrawn") In
otherwords I want to look in the status field of table one and table two to
match "expired or canceled or withdrawn" for my results?
search the

Sierra,

Add the number field to the table. Then make and run an Update Query to
update NumberField to...
Val(Replace(Replace([TextField]," ",""),"-",""))

Please post back if you need more detailed help with any of this.

--
Steve Schapel, Microsoft Access MVP

I have an access table
I want to change:

text field 444 444 444 to a number field 444444444
text field 444-444-444 to number field 444444444

 

[Steve Schapel]

Sierra,

Yes, you could use a calculated field, with the expression I gave you
before, in a Make-Table Query.

If you are working with linked tables, the relationships have to be
created directly in the backend database where the tables actually reside.

It will help us to understand what you mean with the Status criteria
question if you could give a specific example of the data, and whatr you
want to do with it.

 

[sierralightfoot]

Because of your answer to the relationship/link table question, my third
question becomes moot.

Thanks you've been vey helpfull. After taking a few more steps I'm sure I'll
have more questions.

I will create multiply

 

[sierralightfoot]

PS
Can I append 'tables" or only "databases"? I have multiply "Make Query Tables"
that I want to unite into one table to create one mailing list.

 

[Steve Schapel]

Sierra,

Probably you need to use Append Query instead of Make-Table Query.

 

[sierralightfoot]

Thanks, I'm through all these issues, now:

Table one
"A Status Field" "A Parcel Number Field"
active 22
sold 23
pending 24
expired etc
etc.

Tables Two
"B Status Field" "B Parcel Number Field"

I want to create a table based on this result :
use every number in Table Two "B Parcel Number"
and look for a match match in Table One "A Parcel Number,
if Status field in Table One is "Pending or active or sold"
then delete said record/s in Table A

 

[Steve Schapel]

Sierra,

Assuming I an correctly interpreting what you mean here, the SQL of such
a query would be like this...

DELETE [Table one].*
FROM [Table one] INNER JOIN [Table two] ON [Table one].[A Parcel Number]
= [Table two].[B Parcel Number]
WHERE [Table one].[A Status]="Pending" Or [Table one].[A
Status]="active" Or [Table one].[A Status]="sold"

 

[sierralightfoot]

I'll try this in a little while. I should use this in "make query table"?
And where does the SQL go?

Sierra,

Assuming I an correctly interpreting what you mean here, the SQL of such
a query would be like this...

DELETE [Table one].*
FROM [Table one] INNER JOIN [Table two] ON [Table one].[A Parcel Number]
= [Table two].[B Parcel Number]
WHERE [Table one].[A Status]="Pending" Or [Table one].[A
Status]="active" Or [Table one].[A Status]="sold"

--
Steve Schapel, Microsoft Access MVP

Thanks, I'm through all these issues, now:

Table one
"A Status Field" "A Parcel Number Field"
active 22
sold 23
pending 24
expired etc
etc.

Tables Two
"B Status Field" "B Parcel Number Field"

I want to create a table based on this result :
use every number in Table Two "B Parcel Number"
and look for a match match in Table One "A Parcel Number,
if Status field in Table One is "Pending or active or sold"
then delete said record/s in Table A

 

[Steve Schapel]

Sierra,

I am sorry, I'm not quite understanding here. You can't delete records
with a Make-Table Query. Maybe you will need to give us some specific
examples, with sample data, to illustrate what you want to achieve.

If you are in the design view of a query, you can see the SQL view by
selecting SQL from the View menu.

 

[sierralightfoot]

This works!
I have some numbers 444 444 444 000. These I need to also delete the 000.
Could you add this into the expression.

Sierra,

Add the number field to the table. Then make and run an Update Query to
update NumberField to...
Val(Replace(Replace([TextField]," ",""),"-",""))

Please post back if you need more detailed help with any of this.

--
Steve Schapel, Microsoft Access MVP

I have an access table
I want to change:

text field 444 444 444 to a number field 444444444
text field 444-444-444 to number field 444444444

 

[Steve Schapel]

Sierra,

Do you mean remove 0s wherever they might occur in the data, or do you
mean remove them if there are 3 zeros on the end of the number, or do
you mean something else? If it's that sometimes there are 3 zeros at
the end, you could do like this...

Val(Replace(Replace(IIf([TextField] Like
"*000",Left([TextField],Len([TextField])-3),[TextField])," ",""),"-",""))

 

[sierralightfoot]

I am learning to get more specific: I mean to remove the right three
characters (they are always zeros). It looks like your expression removes
them only if they are zeros? For the future what if they were numbers or text
to be removed?

Sierra,

Do you mean remove 0s wherever they might occur in the data, or do you
mean remove them if there are 3 zeros on the end of the number, or do
you mean something else? If it's that sometimes there are 3 zeros at
the end, you could do like this...

Val(Replace(Replace(IIf([TextField] Like
"*000",Left([TextField],Len([TextField])-3),[TextField])," ",""),"-",""))

--
Steve Schapel, Microsoft Access MVP

This works!
I have some numbers 444 444 444 000. These I need to also delete the 000.
Could you add this into the expression.

 

[Steve Schapel]

Sierra,

Well, if the starting data is *always* like this:
3 digits -space- 3 digits -space- 3 digits -space- 3 characters

Val(Replace(Replace(Left([TextField],11)," ",""),"-",""))

 

[sierralightfoot]

Almost there. Have everything else good!

Let's work on this some more.
Here's the expression I built using your templete
I get an invalid syntax error. Are the spaces right.

DELETE [Mailing List Final Query Table].*
FROM [Mailing List Final Query Table ] INNER JOIN [Active, Pending, Sold] ON
[Mailing List Final Query Table].[Expr1]
= [Active, Pending, Sold].[Expr1]
WHERE [Mailing List Final Query Table].[Status]="Pending" Or [Mailing List
Final Query Table].[Status]="active" Or [Mailing List Final Query
Table].[Status]="sold"


Table one, below, in my original example is really =[Mailing List Final
Query Table]
Table two, below, in my original example is really =[Active, Pending, Sold]
All parcel number fields in table one and two are = [Expr1]

Sierra,

Assuming I an correctly interpreting what you mean here, the SQL of such
a query would be like this...

DELETE [Table one].*
FROM [Table one] INNER JOIN [Table two] ON [Table one].[A Parcel Number]
= [Table two].[B Parcel Number]
WHERE [Table one].[A Status]="Pending" Or [Table one].[A
Status]="active" Or [Table one].[A Status]="sold"

--
Steve Schapel, Microsoft Access MVP

Thanks, I'm through all these issues, now:

Table one
"A Status Field" "A Parcel Number Field"
active 22
sold 23
pending 24
expired etc
etc.

Tables Two
"B Status Field" "B Parcel Number Field"

I want to create a table based on this result :
use every number in Table Two "B Parcel Number"
and look for a match match in Table One "A Parcel Number,
if Status field in Table One is "Pending or active or sold"
then delete said record/s in Table A

 

[Steve Schapel]

Sierra,

I see a rogue space at the end of...
FROM [Mailing List Final Query Table ]

Do you really have a table named [Active, Pending, Sold]? I would
definitely not recommend putting commas in the name of a table - in
fact, I didn't think Access would allow it!

And do you have a field named [Expr1] in each of these tables? What is
this?

Be aware that the newsreader is wrapping the text so I am not sure where
you have the line breaks in your actual expression.

You have copy/pasted this from the SQL view of a query, right?

 

[sierralightfoot]

So there sould be no spaces anywhere in the expression?
Yes I have a table "Active, Pending, Sold" It was allowed. Does that confuse
an expression?
And Expr1 is a field created from a previous expression.

I'm not sure I understand your last question: the expression I provided for
your examination was copy/paste from the query "build expression."

Sierra,

I see a rogue space at the end of...
FROM [Mailing List Final Query Table ]

Do you really have a table named [Active, Pending, Sold]? I would
definitely not recommend putting commas in the name of a table - in
fact, I didn't think Access would allow it!

And do you have a field named [Expr1] in each of these tables? What is
this?

Be aware that the newsreader is wrapping the text so I am not sure where
you have the line breaks in your actual expression.

You have copy/pasted this from the SQL view of a query, right?

--
Steve Schapel, Microsoft Access MVP

Almost there. Have everything else good!

Let's work on this some more.
Here's the expression I built using your templete
I get an invalid syntax error. Are the spaces right.

DELETE [Mailing List Final Query Table].*
FROM [Mailing List Final Query Table ] INNER JOIN [Active, Pending, Sold] ON
[Mailing List Final Query Table].[Expr1]
= [Active, Pending, Sold].[Expr1]
WHERE [Mailing List Final Query Table].[Status]="Pending" Or [Mailing List
Final Query Table].[Status]="active" Or [Mailing List Final Query
Table].[Status]="sold"


Table one, below, in my original example is really =[Mailing List Final
Query Table]
Table two, below, in my original example is really =[Active, Pending, Sold]
All parcel number fields in table one and two are = [Expr1]

 

[Steve Schapel]

Sierra,

See comments inline...

So there sould be no spaces anywhere in the expression?

That was not my point. ou have referred to a table called [Mailing List
Final Query Table ] which is not correct, the table is called [Mailing
List Final Query Table]

Yes I have a table "Active, Pending, Sold" It was allowed. Does that confuse
an expression?

I am not sure, I have never seen a table name with commas in it before.

And Expr1 is a field created from a previous expression.

Fair enough. Was that through a Make-Table Query or some such?

I'm not sure I understand your last question: the expression I provided for
your examination was copy/paste from the query "build expression."

How exactly do you find this query "build expression" thing?

 

[sierralightfoot]

Build Expression: Expr1 was the result of your original templete. I copied
your templete to word (easy place to save and edit) , inserted the correct
databases and fields, then copied it by going to field, and then click on
icon "build" in the menue bar.
But back to original task this time with real named: database, table, fields

I want to create a table from a "Make Table Query" based on this result :

use every number in FIELD (Expr1) in Table (Expired, Pending, Sold)

and look for a match match in FIELD (Expr1) in Table (Mailing List
Final Query Table)

if FIELD ("Status") in Table ("Active, Pending, Sold") is equal to "active" or "*pend*" or "sold"
then delete said records in Table (Mailing List Final Query Table)

Thanks for your help!

Sierra,

See comments inline...

So there sould be no spaces anywhere in the expression?

That was not my point. ou have referred to a table called [Mailing List
Final Query Table ] which is not correct, the table is called [Mailing
List Final Query Table]

Yes I have a table "Active, Pending, Sold" It was allowed. Does that confuse
an expression?

I am not sure, I have never seen a table name with commas in it before.

And Expr1 is a field created from a previous expression.

Fair enough. Was that through a Make-Table Query or some such?

I'm not sure I understand your last question: the expression I provided for
your examination was copy/paste from the query "build expression."

How exactly do you find this query "build expression" thing?