Create Query that removes any entries with more then 5 digits?

[cdoyle]

Hi,
I'm trying to import data from another database (codes), and the text field in that database is set to 7.

Our new database only needs the first 5 digits of the codes, so I'm wondering is it possible to create a query that filters any entries where the code field has more then 5 digits?

 

[Alc]

You could select all of the existing entries into a new table

CREATE Table [I]tablename[/I] AS
SELECT * 
FROM [I]tablename1[/I]
WHERE Len([I]fieldname[/I]) <= 5;

(apologies if the syntax is a bit off in the above example)
Then import that table into your new Db.

Alternatively, you could edit all of the existing table entries to be five digits maximum and import all (if that's an option?) e.g.

UPDATE [I]tablename1[/I]
SET [I]fieldname[/I] = Left([I]fieldname1[/I],5);

 

[cdoyle]

thank you, option 2 seemed to work!!

woohoo!!

That just saved me hours of going through and deleting them 1 by 1

 

[Alc]

Glad to help.

 

[cdoyle]

Hey, have another question sort of related to this I think.

If I wanted to created a query, that found all records where a certain field has more then 5 digits entered. Would it be similar to what we used above?

 

[Alc]

Yep, only you'd use the length function. e.g.

SELECT * FROM [I]tablename[/I]
WHERE Len([I]fieldname[/I]) > 5;

 

[cdoyle]

Thank you that worked perfect,

but oh wow. I didn't realize how many records there were, 99k that had this condition.
What I need to do is move those last 2 digits over to another field, i didn't think there would be that many and I could just do it manually..

Is there a way to create an update query that uses the above code to find the condition, and if found moves the last 2 digits to another field?

 

[Rabbie]

Having found the records with more than five digits you should be able to use the Right function to get the last 2 digits.

 

[cdoyle]

Having found the records with more than five digits you should be able to use the Right function to get the last 2 digits.

Would I use this in a query?

I see how it works in the example, but not sure how I would use it to move them to another field?

 

[Rabbie]

Read Access help for more info about Update queries. You should be able to use this function to update the field for the last two digits.

 

[cdoyle]

How do I use the VBA from that page in a query?
My VBA skills aren't that great, and not sure how to get those results to appear within a query?

I think I don't even need the update query, because the column I'm pulling this data from will be deleted when I'm done. So I thought instead of trying to move the last 2 digits to the other field. I can just rename this column, to the column I really need and I'll be done.

 

[cdoyle]

I'm still not really sure how to use the VBA code in a query?
I did some searching, and didnt' come up (at least I dont' think I did) any info that helps.

I think all I need is to remove the first 5 digits, and leave anything that is beyond that.

I'm guessing the code I used earlier could be modified?

 

[cdoyle]

I've read the help, but it doesn't say anything about how to use the VBA function within a query? am I not looking in the right place?