Reformatting SS# process for an employee table

[ssworthi]

I am creating a simple timekeeping data base for a farm based company. The customer would like to see employee time by entering the last 6 digits of the SS# in a query with all the fields they'd like to see. Currently I am doing a find/replace in an Excel file with employees data and then importing the file through a macro. Instead of reformatting in Excel, is there a way to replace the ###-##-#### format in Access?


Also, for the life of me I can't remember (it's been a while) how to turn off the security warning messages for this macro. It would be the first action of the macro. The macro is for updating the employee table. It clears the old data, brings in the new through delete and append queries. Thanks so much; this is my first post so please bear with me.

[pbaldy]

Not sure what you're looking for on the format. This should help with the warnings. SetWarnings is also available in macros.

http://www.baldyweb.com/SQLWarnings.htm

[ssworthi]

I am creating a simple timekeeping data base for a farm based company. The customer would like to see employee time by entering the last 6 digits of the SS# in a query with all the fields they'd like to see. Currently I am doing a find/replace in an Excel file with employees data and then importing the file through a macro. Instead of reformatting in Excel, is there a way to replace the ###-##-#### format in Access?
Also, for the life of me I can't remember (it's been a while) how to turn off the security warning messages for this macro. It would be the first action of the macro. The macro is for updating the employee table. It clears the old data, brings in the new through delete and append queries. Thanks so much; this is my first post so please bear with me.

I am sorry for not making myself clearer. Thanks so much for the set warnings, works like a champ.
I am attempting to create a process for the user. They are working out of Quickbooks and maintain their employee records there. For the most current employee list they export from Quick Books the current employee list; this includes name and the social security number formatted as "###-##-####, standard. I am creating a macro that will import that excel file from a shared dropbox into Access. There is a table with time worked by employee, linked with the employee table.
The user would like a query that prompts for the employee which reflects time worked. She would like to see a specific employee by the last 6 digits of the SS#. I am thinking the best way is to eliminate the dashes making it a whole 9 digit series. I can do that in the excel file but that would mean manipulating the file each time; what I would like to do is run an update query of some sort in the macro that eliminates the dashes when bringing in the file and appends to the current employee file. I hope this makes sense.

[pbaldy]

You can use the Replace() function to get rid of the dashes, either during import or after.

[ssworthi]

You can use the Replace() function to get rid of the dashes, either during import or after.

Thanks so much for the help. Is the Replace()Function something I would do in an update query; have not used this before. Thanks again.

[pbaldy]

It can be used in an update query, yes. If you import with a query, it could also be used there.

[ssworthi]

It can be used in an update query, yes. If you import with a query, it could also be used there.

Could you please show an example of a replace query. Do I have this correct? Create an update query, update the SS field with "replace()"; then I am blank. Like I said, I've not used replace before. Thanks so much!

[pbaldy]

UPDATE TableName
SET FieldName = Replace(FieldName, "-", "")

[ssworthi]

I'm feeling awkward here, I so far have been not clear on your instruction as I ended up updating the field to "SS No".
I created an Update query, chose the SS No field to update. In the Update field I did a "build" and typed in Replace(SS No,"-",""). I know I am close so please have patience with me. Thanks so much.

[ssworthi]

OMG, I redid it with brackets around the field name and it worked! Awesome! Thank you so much!

[Micron]

Notice that Paul had no spaces in FieldName. If SS No is the name of your field, put square brackets around it [Field Name]. Better yet, go through your project after Googling "ms access naming convention" and employ some of what you learn. I never use spaces or special characters ( "_" excepted) anywhere, (OK, maybe in .005% of cases) including file folder and file names. Don't settle for the first website you find, read a few.

You beat me to it by about 30 seconds!

[pbaldy]

OMG, I redid it with brackets around the field name and it worked! Awesome! Thank you so much!

Glad you got it sorted.