form to import excel and split excel field into two access fields

[Gntlhnds]

I have created a database that I need to be able to create a form that allows
a user to import an excel spreadsheet to set up the database with their data.
One of the problems is that the spreadsheet will not always be in the same
place for each user, and the spreadsheet will have a name field set up with
"last, first mi" whereas my database has "LastName" and "FirstName" fields
(no MI). How would I create the form and code it so that I'd be able to
accomplish this? Thank you so much for the help, I know it will take a lot.

 

[Chris Reveille]

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

 

[Gntlhnds]

I'm a dummy when it comes to access, so I'm afraid you'll have to be more
specific. I can design the form, but as far as putting in the code for the
dialog box or creating the macro to call it, I'm lost. I think I can program
the append query once I have the file imported into a new table.

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I have created a database that I need to be able to create a form that allows
a user to import an excel spreadsheet to set up the database with their data.
One of the problems is that the spreadsheet will not always be in the same
place for each user, and the spreadsheet will have a name field set up with
"last, first mi" whereas my database has "LastName" and "FirstName" fields
(no MI). How would I create the form and code it so that I'd be able to
accomplish this? Thank you so much for the help, I know it will take a lot.

 

[Gntlhnds]

Reply in text:

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I tried doing this by putting these lines in the Criteria section of the
append query, but it's not working. If I put it like it is, then as soon as
I try to navigate out of the Criteria box, I get an error about an invalid .
or ! or parantheses. What am I doing wrong? I don't know much about append
queries, sorry. The names in the code are the names of my fields, so if you
showed me the SQL statement I might be able to figure it out. Thanks for
your help.

 

[hmadyson]

The 2 functions that Chris gave you are fields and should go in the first
column on the query grid, not in the Criteria, they are functions for the new
fields that you are appending to.

Please let me know if I can provide more assistance.

Reply in text:

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I tried doing this by putting these lines in the Criteria section of the
append query, but it's not working. If I put it like it is, then as soon as
I try to navigate out of the Criteria box, I get an error about an invalid .
or ! or parantheses. What am I doing wrong? I don't know much about append
queries, sorry. The names in the code are the names of my fields, so if you
showed me the SQL statement I might be able to figure it out. Thanks for
your help.

 

[Gntlhnds]

Since most of the names in my table are Last, First MI (Some with Jr or III
after the first name) is there a way to trim all that off after the first
name? Everything after the first name shows up in the FirstName field.

The 2 functions that Chris gave you are fields and should go in the first
column on the query grid, not in the Criteria, they are functions for the new
fields that you are appending to.

Please let me know if I can provide more assistance.

Reply in text:

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I tried doing this by putting these lines in the Criteria section of the
append query, but it's not working. If I put it like it is, then as soon as
I try to navigate out of the Criteria box, I get an error about an invalid .
or ! or parantheses. What am I doing wrong? I don't know much about append
queries, sorry. The names in the code are the names of my fields, so if you
showed me the SQL statement I might be able to figure it out. Thanks for
your help.

:

I have created a database that I need to be able to create a form that allows
a user to import an excel spreadsheet to set up the database with their data.
One of the problems is that the spreadsheet will not always be in the same
place for each user, and the spreadsheet will have a name field set up with
"last, first mi" whereas my database has "LastName" and "FirstName" fields
(no MI). How would I create the form and code it so that I'd be able to
accomplish this? Thank you so much for the help, I know it will take a lot.

 

[Gntlhnds]

For some reason, when it is copying each record five times into the new
record. I have 642 records in the first table, and then the second table
ends up with 3205 records. Here is the SQL Statement:


INSERT INTO Contacts ( LastName, FirstName, Rank, Religion ) IN 'k:\BE.mdb'
SELECT Left([FullName],InStr([FullName],",")-1) AS LastName,
Mid([FullName],InStr([FullName],",")+2) AS FirstName, tblImport.RankId,
tblImport.ReligionDescription
FROM tblImport;

Or should I post this in the queries thread?

The 2 functions that Chris gave you are fields and should go in the first
column on the query grid, not in the Criteria, they are functions for the new
fields that you are appending to.

Please let me know if I can provide more assistance.

Reply in text:

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I tried doing this by putting these lines in the Criteria section of the
append query, but it's not working. If I put it like it is, then as soon as
I try to navigate out of the Criteria box, I get an error about an invalid .
or ! or parantheses. What am I doing wrong? I don't know much about append
queries, sorry. The names in the code are the names of my fields, so if you
showed me the SQL statement I might be able to figure it out. Thanks for
your help.

:

I have created a database that I need to be able to create a form that allows
a user to import an excel spreadsheet to set up the database with their data.
One of the problems is that the spreadsheet will not always be in the same
place for each user, and the spreadsheet will have a name field set up with
"last, first mi" whereas my database has "LastName" and "FirstName" fields
(no MI). How would I create the form and code it so that I'd be able to
accomplish this? Thank you so much for the help, I know it will take a lot.

 

[Gntlhnds]

Actually, everytime I import the records, then delete them (I'm still in the
testing phase), and then re-import them, it copies the records same amount of
times I've imported them. For example, if I have done this process of
importing and deleting seven times, then each record is copied into the table
7 times!!!

For some reason, when it is copying each record five times into the new
record. I have 642 records in the first table, and then the second table
ends up with 3205 records. Here is the SQL Statement:


INSERT INTO Contacts ( LastName, FirstName, Rank, Religion ) IN 'k:\BE.mdb'
SELECT Left([FullName],InStr([FullName],",")-1) AS LastName,
Mid([FullName],InStr([FullName],",")+2) AS FirstName, tblImport.RankId,
tblImport.ReligionDescription
FROM tblImport;

Or should I post this in the queries thread?

The 2 functions that Chris gave you are fields and should go in the first
column on the query grid, not in the Criteria, they are functions for the new
fields that you are appending to.

Please let me know if I can provide more assistance.

Reply in text:

:

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I tried doing this by putting these lines in the Criteria section of the
append query, but it's not working. If I put it like it is, then as soon as
I try to navigate out of the Criteria box, I get an error about an invalid .
or ! or parantheses. What am I doing wrong? I don't know much about append
queries, sorry. The names in the code are the names of my fields, so if you
showed me the SQL statement I might be able to figure it out. Thanks for
your help.



:

I have created a database that I need to be able to create a form that allows
a user to import an excel spreadsheet to set up the database with their data.
One of the problems is that the spreadsheet will not always be in the same
place for each user, and the spreadsheet will have a name field set up with
"last, first mi" whereas my database has "LastName" and "FirstName" fields
(no MI). How would I create the form and code it so that I'd be able to
accomplish this? Thank you so much for the help, I know it will take a lot.

 

[Gntlhnds]

I'm an idiot, never mind. Is there a way when running my macro and using the
"TransferSpreadsheet" when it creates the new table or appends the data into
an existing table it can prevent duplicates. I would set up the macro to
delete the table before doing the "TransferSpreadsheet", but if the table
doesn't exist already it stops the macro.

Actually, everytime I import the records, then delete them (I'm still in the
testing phase), and then re-import them, it copies the records same amount of
times I've imported them. For example, if I have done this process of
importing and deleting seven times, then each record is copied into the table
7 times!!!

For some reason, when it is copying each record five times into the new
record. I have 642 records in the first table, and then the second table
ends up with 3205 records. Here is the SQL Statement:


INSERT INTO Contacts ( LastName, FirstName, Rank, Religion ) IN 'k:\BE.mdb'
SELECT Left([FullName],InStr([FullName],",")-1) AS LastName,
Mid([FullName],InStr([FullName],",")+2) AS FirstName, tblImport.RankId,
tblImport.ReligionDescription
FROM tblImport;

Or should I post this in the queries thread?

The 2 functions that Chris gave you are fields and should go in the first
column on the query grid, not in the Criteria, they are functions for the new
fields that you are appending to.

Please let me know if I can provide more assistance.

:

Reply in text:

:

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I tried doing this by putting these lines in the Criteria section of the
append query, but it's not working. If I put it like it is, then as soon as
I try to navigate out of the Criteria box, I get an error about an invalid .
or ! or parantheses. What am I doing wrong? I don't know much about append
queries, sorry. The names in the code are the names of my fields, so if you
showed me the SQL statement I might be able to figure it out. Thanks for
your help.



:

I have created a database that I need to be able to create a form that allows
a user to import an excel spreadsheet to set up the database with their data.
One of the problems is that the spreadsheet will not always be in the same
place for each user, and the spreadsheet will have a name field set up with
"last, first mi" whereas my database has "LastName" and "FirstName" fields
(no MI). How would I create the form and code it so that I'd be able to
accomplish this? Thank you so much for the help, I know it will take a lot.

 

[hmadyson]

use the SELECT DISTINCT in your query and you will remove duplicates.

Please let me know if I can provide more assistance.

I'm an idiot, never mind. Is there a way when running my macro and using the
"TransferSpreadsheet" when it creates the new table or appends the data into
an existing table it can prevent duplicates. I would set up the macro to
delete the table before doing the "TransferSpreadsheet", but if the table
doesn't exist already it stops the macro.

Actually, everytime I import the records, then delete them (I'm still in the
testing phase), and then re-import them, it copies the records same amount of
times I've imported them. For example, if I have done this process of
importing and deleting seven times, then each record is copied into the table
7 times!!!

For some reason, when it is copying each record five times into the new
record. I have 642 records in the first table, and then the second table
ends up with 3205 records. Here is the SQL Statement:


INSERT INTO Contacts ( LastName, FirstName, Rank, Religion ) IN 'k:\BE.mdb'
SELECT Left([FullName],InStr([FullName],",")-1) AS LastName,
Mid([FullName],InStr([FullName],",")+2) AS FirstName, tblImport.RankId,
tblImport.ReligionDescription
FROM tblImport;

Or should I post this in the queries thread?


:

The 2 functions that Chris gave you are fields and should go in the first
column on the query grid, not in the Criteria, they are functions for the new
fields that you are appending to.

Please let me know if I can provide more assistance.

:

Reply in text:

:

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I tried doing this by putting these lines in the Criteria section of the
append query, but it's not working. If I put it like it is, then as soon as
I try to navigate out of the Criteria box, I get an error about an invalid .
or ! or parantheses. What am I doing wrong? I don't know much about append
queries, sorry. The names in the code are the names of my fields, so if you
showed me the SQL statement I might be able to figure it out. Thanks for
your help.



:

I have created a database that I need to be able to create a form that allows
a user to import an excel spreadsheet to set up the database with their data.
One of the problems is that the spreadsheet will not always be in the same
place for each user, and the spreadsheet will have a name field set up with
"last, first mi" whereas my database has "LastName" and "FirstName" fields
(no MI). How would I create the form and code it so that I'd be able to
accomplish this? Thank you so much for the help, I know it will take a lot.

 

[Gntlhnds]

Since most of the names in the table I am importing are Last, First MI (Some
with Jr or III after the first name) is there a way to trim all that off
after the first
name? Everything after the first name shows up in the FirstName field.

use the SELECT DISTINCT in your query and you will remove duplicates.

Please let me know if I can provide more assistance.

I'm an idiot, never mind. Is there a way when running my macro and using the
"TransferSpreadsheet" when it creates the new table or appends the data into
an existing table it can prevent duplicates. I would set up the macro to
delete the table before doing the "TransferSpreadsheet", but if the table
doesn't exist already it stops the macro.

Actually, everytime I import the records, then delete them (I'm still in the
testing phase), and then re-import them, it copies the records same amount of
times I've imported them. For example, if I have done this process of
importing and deleting seven times, then each record is copied into the table
7 times!!!

:

For some reason, when it is copying each record five times into the new
record. I have 642 records in the first table, and then the second table
ends up with 3205 records. Here is the SQL Statement:


INSERT INTO Contacts ( LastName, FirstName, Rank, Religion ) IN 'k:\BE.mdb'
SELECT Left([FullName],InStr([FullName],",")-1) AS LastName,
Mid([FullName],InStr([FullName],",")+2) AS FirstName, tblImport.RankId,
tblImport.ReligionDescription
FROM tblImport;

Or should I post this in the queries thread?


:

The 2 functions that Chris gave you are fields and should go in the first
column on the query grid, not in the Criteria, they are functions for the new
fields that you are appending to.

Please let me know if I can provide more assistance.

:

Reply in text:

:

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I tried doing this by putting these lines in the Criteria section of the
append query, but it's not working. If I put it like it is, then as soon as
I try to navigate out of the Criteria box, I get an error about an invalid .
or ! or parantheses. What am I doing wrong? I don't know much about append
queries, sorry. The names in the code are the names of my fields, so if you
showed me the SQL statement I might be able to figure it out. Thanks for
your help.



:

I have created a database that I need to be able to create a form that allows
a user to import an excel spreadsheet to set up the database with their data.
One of the problems is that the spreadsheet will not always be in the same
place for each user, and the spreadsheet will have a name field set up with
"last, first mi" whereas my database has "LastName" and "FirstName" fields
(no MI). How would I create the form and code it so that I'd be able to
accomplish this? Thank you so much for the help, I know it will take a lot.

 

[hmadyson]

If you could assume that the first name contains no spaces, you could do the
following

starting with the first name field:

mid([FirstName],1,instr([FirstName]," ")-1)

unfortunately some people have names like Mary Sue, and in this case you
would only get Mary.

now if you are staring with the fullname, you could do the following

mid([FullName],instr([FullName],",")+2,instr([FullName]," ")-1)

Please let me know if I can provide more assistance.

Since most of the names in the table I am importing are Last, First MI (Some
with Jr or III after the first name) is there a way to trim all that off
after the first
name? Everything after the first name shows up in the FirstName field.

use the SELECT DISTINCT in your query and you will remove duplicates.

Please let me know if I can provide more assistance.

I'm an idiot, never mind. Is there a way when running my macro and using the
"TransferSpreadsheet" when it creates the new table or appends the data into
an existing table it can prevent duplicates. I would set up the macro to
delete the table before doing the "TransferSpreadsheet", but if the table
doesn't exist already it stops the macro.

:

Actually, everytime I import the records, then delete them (I'm still in the
testing phase), and then re-import them, it copies the records same amount of
times I've imported them. For example, if I have done this process of
importing and deleting seven times, then each record is copied into the table
7 times!!!

:

For some reason, when it is copying each record five times into the new
record. I have 642 records in the first table, and then the second table
ends up with 3205 records. Here is the SQL Statement:


INSERT INTO Contacts ( LastName, FirstName, Rank, Religion ) IN 'k:\BE.mdb'
SELECT Left([FullName],InStr([FullName],",")-1) AS LastName,
Mid([FullName],InStr([FullName],",")+2) AS FirstName, tblImport.RankId,
tblImport.ReligionDescription
FROM tblImport;

Or should I post this in the queries thread?


:

The 2 functions that Chris gave you are fields and should go in the first
column on the query grid, not in the Criteria, they are functions for the new
fields that you are appending to.

Please let me know if I can provide more assistance.

:

Reply in text:

:

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I tried doing this by putting these lines in the Criteria section of the
append query, but it's not working. If I put it like it is, then as soon as
I try to navigate out of the Criteria box, I get an error about an invalid .
or ! or parantheses. What am I doing wrong? I don't know much about append
queries, sorry. The names in the code are the names of my fields, so if you
showed me the SQL statement I might be able to figure it out. Thanks for
your help.



:

I have created a database that I need to be able to create a form that allows
a user to import an excel spreadsheet to set up the database with their data.
One of the problems is that the spreadsheet will not always be in the same
place for each user, and the spreadsheet will have a name field set up with
"last, first mi" whereas my database has "LastName" and "FirstName" fields
(no MI). How would I create the form and code it so that I'd be able to
accomplish this? Thank you so much for the help, I know it will take a lot.

 

[Gntlhnds]

Using mid([FullName],instr([FullName],",")+2,instr([FullName]," ")-1), on
some of the names it works, but then on others it doesn't do anything, and
then on others it takes too much off of the name (part of the first name will
be missing). I am running this query in a macro, and the names in the excel
spreadsheet are in all caps. after running this query, I run an update query
to put them in proper case. Should I run this query first before trying to
trim off the excess after the first name?

If you could assume that the first name contains no spaces, you could do the
following

starting with the first name field:

mid([FirstName],1,instr([FirstName]," ")-1)

unfortunately some people have names like Mary Sue, and in this case you
would only get Mary.

now if you are staring with the fullname, you could do the following

mid([FullName],instr([FullName],",")+2,instr([FullName]," ")-1)

Please let me know if I can provide more assistance.

Since most of the names in the table I am importing are Last, First MI (Some
with Jr or III after the first name) is there a way to trim all that off
after the first
name? Everything after the first name shows up in the FirstName field.

use the SELECT DISTINCT in your query and you will remove duplicates.

Please let me know if I can provide more assistance.

:

I'm an idiot, never mind. Is there a way when running my macro and using the
"TransferSpreadsheet" when it creates the new table or appends the data into
an existing table it can prevent duplicates. I would set up the macro to
delete the table before doing the "TransferSpreadsheet", but if the table
doesn't exist already it stops the macro.

:

Actually, everytime I import the records, then delete them (I'm still in the
testing phase), and then re-import them, it copies the records same amount of
times I've imported them. For example, if I have done this process of
importing and deleting seven times, then each record is copied into the table
7 times!!!

:

For some reason, when it is copying each record five times into the new
record. I have 642 records in the first table, and then the second table
ends up with 3205 records. Here is the SQL Statement:


INSERT INTO Contacts ( LastName, FirstName, Rank, Religion ) IN 'k:\BE.mdb'
SELECT Left([FullName],InStr([FullName],",")-1) AS LastName,
Mid([FullName],InStr([FullName],",")+2) AS FirstName, tblImport.RankId,
tblImport.ReligionDescription
FROM tblImport;

Or should I post this in the queries thread?


:

The 2 functions that Chris gave you are fields and should go in the first
column on the query grid, not in the Criteria, they are functions for the new
fields that you are appending to.

Please let me know if I can provide more assistance.

:

Reply in text:

:

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I tried doing this by putting these lines in the Criteria section of the
append query, but it's not working. If I put it like it is, then as soon as
I try to navigate out of the Criteria box, I get an error about an invalid .
or ! or parantheses. What am I doing wrong? I don't know much about append
queries, sorry. The names in the code are the names of my fields, so if you
showed me the SQL statement I might be able to figure it out. Thanks for
your help.



:

I have created a database that I need to be able to create a form that allows
a user to import an excel spreadsheet to set up the database with their data.
One of the problems is that the spreadsheet will not always be in the same
place for each user, and the spreadsheet will have a name field set up with
"last, first mi" whereas my database has "LastName" and "FirstName" fields
(no MI). How would I create the form and code it so that I'd be able to
accomplish this? Thank you so much for the help, I know it will take a lot.

 

[hmadyson]

Ok, now that I know that you are using Excel I would suggest switching to
excel to write the formulas and then putting the solution into Access.

Excel uses Find instead of Instr, so assuming the first column had the name,
I would put the following formulas in the next few columns

Column B: looking for where the comma is =FIND(",",A2)
Column C: looking for last name (this is the final last name that you want)
=IF(ISERROR(B2),A2,TRIM(LEFT(A2,B2-1)))
Column D: the rest of the name excluding the last name
=IF(ISERROR(B2),"",TRIM(MID(A2,B2+1,99)))
Column E: space position in column d =FIND(" ",D2)
Column F: final first name =IF(ISERROR(E2),D2,TRIM(LEFT(D2,E2-1)))

This handles the fact that there may not be a comma and that there may not
be a space. You can uppercase it in excel also by using the UPPER function. I
think that the Access query info that I gave you failed because it was not
checking that someone may not have a space, and it was not checking that
someone could have a space in their last name.

Please let me know if I can provide more assistance.

Using mid([FullName],instr([FullName],",")+2,instr([FullName]," ")-1), on
some of the names it works, but then on others it doesn't do anything, and
then on others it takes too much off of the name (part of the first name will
be missing). I am running this query in a macro, and the names in the excel
spreadsheet are in all caps. after running this query, I run an update query
to put them in proper case. Should I run this query first before trying to
trim off the excess after the first name?

If you could assume that the first name contains no spaces, you could do the
following

starting with the first name field:

mid([FirstName],1,instr([FirstName]," ")-1)

unfortunately some people have names like Mary Sue, and in this case you
would only get Mary.

now if you are staring with the fullname, you could do the following

mid([FullName],instr([FullName],",")+2,instr([FullName]," ")-1)

Please let me know if I can provide more assistance.

Since most of the names in the table I am importing are Last, First MI (Some
with Jr or III after the first name) is there a way to trim all that off
after the first
name? Everything after the first name shows up in the FirstName field.



:

use the SELECT DISTINCT in your query and you will remove duplicates.

Please let me know if I can provide more assistance.

:

I'm an idiot, never mind. Is there a way when running my macro and using the
"TransferSpreadsheet" when it creates the new table or appends the data into
an existing table it can prevent duplicates. I would set up the macro to
delete the table before doing the "TransferSpreadsheet", but if the table
doesn't exist already it stops the macro.

:

Actually, everytime I import the records, then delete them (I'm still in the
testing phase), and then re-import them, it copies the records same amount of
times I've imported them. For example, if I have done this process of
importing and deleting seven times, then each record is copied into the table
7 times!!!

:

For some reason, when it is copying each record five times into the new
record. I have 642 records in the first table, and then the second table
ends up with 3205 records. Here is the SQL Statement:


INSERT INTO Contacts ( LastName, FirstName, Rank, Religion ) IN 'k:\BE.mdb'
SELECT Left([FullName],InStr([FullName],",")-1) AS LastName,
Mid([FullName],InStr([FullName],",")+2) AS FirstName, tblImport.RankId,
tblImport.ReligionDescription
FROM tblImport;

Or should I post this in the queries thread?


:

The 2 functions that Chris gave you are fields and should go in the first
column on the query grid, not in the Criteria, they are functions for the new
fields that you are appending to.

Please let me know if I can provide more assistance.

:

Reply in text:

:

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I tried doing this by putting these lines in the Criteria section of the
append query, but it's not working. If I put it like it is, then as soon as
I try to navigate out of the Criteria box, I get an error about an invalid .
or ! or parantheses. What am I doing wrong? I don't know much about append
queries, sorry. The names in the code are the names of my fields, so if you
showed me the SQL statement I might be able to figure it out. Thanks for
your help.



:

I have created a database that I need to be able to create a form that allows
a user to import an excel spreadsheet to set up the database with their data.
One of the problems is that the spreadsheet will not always be in the same
place for each user, and the spreadsheet will have a name field set up with
"last, first mi" whereas my database has "LastName" and "FirstName" fields
(no MI). How would I create the form and code it so that I'd be able to
accomplish this? Thank you so much for the help, I know it will take a lot.

 

[Gntlhnds]

I'd prefer running the code in access, though. My users won't know enough
about excel to put the code in the spreadsheet that they will be importing.

Ok, now that I know that you are using Excel I would suggest switching to
excel to write the formulas and then putting the solution into Access.

Excel uses Find instead of Instr, so assuming the first column had the name,
I would put the following formulas in the next few columns

Column B: looking for where the comma is =FIND(",",A2)
Column C: looking for last name (this is the final last name that you want)
=IF(ISERROR(B2),A2,TRIM(LEFT(A2,B2-1)))
Column D: the rest of the name excluding the last name
=IF(ISERROR(B2),"",TRIM(MID(A2,B2+1,99)))
Column E: space position in column d =FIND(" ",D2)
Column F: final first name =IF(ISERROR(E2),D2,TRIM(LEFT(D2,E2-1)))

This handles the fact that there may not be a comma and that there may not
be a space. You can uppercase it in excel also by using the UPPER function. I
think that the Access query info that I gave you failed because it was not
checking that someone may not have a space, and it was not checking that
someone could have a space in their last name.

Please let me know if I can provide more assistance.

Using mid([FullName],instr([FullName],",")+2,instr([FullName]," ")-1), on
some of the names it works, but then on others it doesn't do anything, and
then on others it takes too much off of the name (part of the first name will
be missing). I am running this query in a macro, and the names in the excel
spreadsheet are in all caps. after running this query, I run an update query
to put them in proper case. Should I run this query first before trying to
trim off the excess after the first name?

If you could assume that the first name contains no spaces, you could do the
following

starting with the first name field:

mid([FirstName],1,instr([FirstName]," ")-1)

unfortunately some people have names like Mary Sue, and in this case you
would only get Mary.

now if you are staring with the fullname, you could do the following

mid([FullName],instr([FullName],",")+2,instr([FullName]," ")-1)

Please let me know if I can provide more assistance.

:

Since most of the names in the table I am importing are Last, First MI (Some
with Jr or III after the first name) is there a way to trim all that off
after the first
name? Everything after the first name shows up in the FirstName field.



:

use the SELECT DISTINCT in your query and you will remove duplicates.

Please let me know if I can provide more assistance.

:

I'm an idiot, never mind. Is there a way when running my macro and using the
"TransferSpreadsheet" when it creates the new table or appends the data into
an existing table it can prevent duplicates. I would set up the macro to
delete the table before doing the "TransferSpreadsheet", but if the table
doesn't exist already it stops the macro.

:

Actually, everytime I import the records, then delete them (I'm still in the
testing phase), and then re-import them, it copies the records same amount of
times I've imported them. For example, if I have done this process of
importing and deleting seven times, then each record is copied into the table
7 times!!!

:

For some reason, when it is copying each record five times into the new
record. I have 642 records in the first table, and then the second table
ends up with 3205 records. Here is the SQL Statement:


INSERT INTO Contacts ( LastName, FirstName, Rank, Religion ) IN 'k:\BE.mdb'
SELECT Left([FullName],InStr([FullName],",")-1) AS LastName,
Mid([FullName],InStr([FullName],",")+2) AS FirstName, tblImport.RankId,
tblImport.ReligionDescription
FROM tblImport;

Or should I post this in the queries thread?


:

The 2 functions that Chris gave you are fields and should go in the first
column on the query grid, not in the Criteria, they are functions for the new
fields that you are appending to.

Please let me know if I can provide more assistance.

:

Reply in text:

:

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I tried doing this by putting these lines in the Criteria section of the
append query, but it's not working. If I put it like it is, then as soon as
I try to navigate out of the Criteria box, I get an error about an invalid .
or ! or parantheses. What am I doing wrong? I don't know much about append
queries, sorry. The names in the code are the names of my fields, so if you
showed me the SQL statement I might be able to figure it out. Thanks for
your help.



:

I have created a database that I need to be able to create a form that allows
a user to import an excel spreadsheet to set up the database with their data.
One of the problems is that the spreadsheet will not always be in the same
place for each user, and the spreadsheet will have a name field set up with
"last, first mi" whereas my database has "LastName" and "FirstName" fields
(no MI). How would I create the form and code it so that I'd be able to
accomplish this? Thank you so much for the help, I know it will take a lot.

 

[hmadyson]

Then I suggest that you build your queries similarly, but do not worry so
much about doing it all in one query, when you can build a few queries on
each other. In Access, Instr replaces find, and iserror should also work. Use
your first query to split between last and rest. Use the second query to
split first.

I'd prefer running the code in access, though. My users won't know enough
about excel to put the code in the spreadsheet that they will be importing.

Ok, now that I know that you are using Excel I would suggest switching to
excel to write the formulas and then putting the solution into Access.

Excel uses Find instead of Instr, so assuming the first column had the name,
I would put the following formulas in the next few columns

Column B: looking for where the comma is =FIND(",",A2)
Column C: looking for last name (this is the final last name that you want)
=IF(ISERROR(B2),A2,TRIM(LEFT(A2,B2-1)))
Column D: the rest of the name excluding the last name
=IF(ISERROR(B2),"",TRIM(MID(A2,B2+1,99)))
Column E: space position in column d =FIND(" ",D2)
Column F: final first name =IF(ISERROR(E2),D2,TRIM(LEFT(D2,E2-1)))

This handles the fact that there may not be a comma and that there may not
be a space. You can uppercase it in excel also by using the UPPER function. I
think that the Access query info that I gave you failed because it was not
checking that someone may not have a space, and it was not checking that
someone could have a space in their last name.

Please let me know if I can provide more assistance.

Using mid([FullName],instr([FullName],",")+2,instr([FullName]," ")-1), on
some of the names it works, but then on others it doesn't do anything, and
then on others it takes too much off of the name (part of the first name will
be missing). I am running this query in a macro, and the names in the excel
spreadsheet are in all caps. after running this query, I run an update query
to put them in proper case. Should I run this query first before trying to
trim off the excess after the first name?

:

If you could assume that the first name contains no spaces, you could do the
following

starting with the first name field:

mid([FirstName],1,instr([FirstName]," ")-1)

unfortunately some people have names like Mary Sue, and in this case you
would only get Mary.

now if you are staring with the fullname, you could do the following

mid([FullName],instr([FullName],",")+2,instr([FullName]," ")-1)

Please let me know if I can provide more assistance.

:

Since most of the names in the table I am importing are Last, First MI (Some
with Jr or III after the first name) is there a way to trim all that off
after the first
name? Everything after the first name shows up in the FirstName field.



:

use the SELECT DISTINCT in your query and you will remove duplicates.

Please let me know if I can provide more assistance.

:

I'm an idiot, never mind. Is there a way when running my macro and using the
"TransferSpreadsheet" when it creates the new table or appends the data into
an existing table it can prevent duplicates. I would set up the macro to
delete the table before doing the "TransferSpreadsheet", but if the table
doesn't exist already it stops the macro.

:

Actually, everytime I import the records, then delete them (I'm still in the
testing phase), and then re-import them, it copies the records same amount of
times I've imported them. For example, if I have done this process of
importing and deleting seven times, then each record is copied into the table
7 times!!!

:

For some reason, when it is copying each record five times into the new
record. I have 642 records in the first table, and then the second table
ends up with 3205 records. Here is the SQL Statement:


INSERT INTO Contacts ( LastName, FirstName, Rank, Religion ) IN 'k:\BE.mdb'
SELECT Left([FullName],InStr([FullName],",")-1) AS LastName,
Mid([FullName],InStr([FullName],",")+2) AS FirstName, tblImport.RankId,
tblImport.ReligionDescription
FROM tblImport;

Or should I post this in the queries thread?


:

The 2 functions that Chris gave you are fields and should go in the first
column on the query grid, not in the Criteria, they are functions for the new
fields that you are appending to.

Please let me know if I can provide more assistance.

:

Reply in text:

:

What I would do is to import the excel speadsheet into a new table and use an
append query to append the records to your main table.

As far as the Excel file being in seperate locations you could use a file
dialog box
see
http://www.mvps.org/access/api/api0001.htm
Place the result on a form and create a macro that reference that result

In the apped query you would add two fields to sepeate the name
LastName:Left([FullName],InStr([FullName],",")-1)
[FirstName:Mid([FullName],InStr([FullName],",")+2)

I tried doing this by putting these lines in the Criteria section of the
append query, but it's not working. If I put it like it is, then as soon as
I try to navigate out of the Criteria box, I get an error about an invalid .
or ! or parantheses. What am I doing wrong? I don't know much about append
queries, sorry. The names in the code are the names of my fields, so if you
showed me the SQL statement I might be able to figure it out. Thanks for
your help.



:

I have created a database that I need to be able to create a form that allows
a user to import an excel spreadsheet to set up the database with their data.
One of the problems is that the spreadsheet will not always be in the same
place for each user, and the spreadsheet will have a name field set up with
"last, first mi" whereas my database has "LastName" and "FirstName" fields
(no MI). How would I create the form and code it so that I'd be able to
accomplish this? Thank you so much for the help, I know it will take a lot.