using update query to update combo box

[Guest]

I have a spreadsheet that I import into a database. One of the columns in
the spreadsheet gets imported into a combo box (I want to limit the
selections available because of the reports that I have). Unfortunately,
there are too many things that can be written into this field. What I would
like to do is write an update query that will go through the field after
import searching for specific words and if it finds a specific word (such as
catholic or baptist) then it would update the combo box to what it is
supposed to show. What I have now is that sometimes the field will show
something like roman catholic, catholic churches, or just plain catholic, so
I want it to search for the specific key word (catholic) and then change the
value to what's in the combo box (Roman Catholic). I know all the values
that can show up, so I know what key words I need to use.

 

[Carl Rapson]

I have a spreadsheet that I import into a database. One of the columns in
the spreadsheet gets imported into a combo box (I want to limit the
selections available because of the reports that I have). Unfortunately,
there are too many things that can be written into this field. What I
would
like to do is write an update query that will go through the field after
import searching for specific words and if it finds a specific word (such
as
catholic or baptist) then it would update the combo box to what it is
supposed to show. What I have now is that sometimes the field will show
something like roman catholic, catholic churches, or just plain catholic,
so
I want it to search for the specific key word (catholic) and then change
the
value to what's in the combo box (Roman Catholic). I know all the values
that can show up, so I know what key words I need to use.

Tables don't contain combo boxes. Are you wanting to modify the value in a
particular field in the table after import? If so, you could use something
like this:

UPDATE

SET [field]="Roman Catholic"
WHERE [field] LIKE "*catholic*"

Carl Rapson

 

[Guest]

I had gotten that far, but where I'm stuck is if the field contains
"*baptist*" and I want to set it to "Baptist". Like I mentioned before, the
field can contain any of about 25 different things, and I want to narrow it
down to about 12 things that I have in a combo box for that field. Like if
the word "Pentecostal" is in the field, I want to set it to "Other
Christian". I hope I'm clear in what I want.

I have a spreadsheet that I import into a database. One of the columns in
the spreadsheet gets imported into a combo box (I want to limit the
selections available because of the reports that I have). Unfortunately,
there are too many things that can be written into this field. What I
would
like to do is write an update query that will go through the field after
import searching for specific words and if it finds a specific word (such
as
catholic or baptist) then it would update the combo box to what it is
supposed to show. What I have now is that sometimes the field will show
something like roman catholic, catholic churches, or just plain catholic,
so
I want it to search for the specific key word (catholic) and then change
the
value to what's in the combo box (Roman Catholic). I know all the values
that can show up, so I know what key words I need to use.

Tables don't contain combo boxes. Are you wanting to modify the value in a
particular field in the table after import? If so, you could use something
like this:

UPDATE

SET [field]="Roman Catholic"
WHERE [field] LIKE "*catholic*"

Carl Rapson

 

[Carl Rapson]

As far as I know, you'll have to run a separate UPDATE query for each case.
There doesn't seem to be any way to generalize this into a single SQL
statement.

Carl Rapson

I had gotten that far, but where I'm stuck is if the field contains
"*baptist*" and I want to set it to "Baptist". Like I mentioned before,
the
field can contain any of about 25 different things, and I want to narrow
it
down to about 12 things that I have in a combo box for that field. Like
if
the word "Pentecostal" is in the field, I want to set it to "Other
Christian". I hope I'm clear in what I want.

I have a spreadsheet that I import into a database. One of the columns
in
the spreadsheet gets imported into a combo box (I want to limit the
selections available because of the reports that I have).
Unfortunately,
there are too many things that can be written into this field. What I
would
like to do is write an update query that will go through the field
after
import searching for specific words and if it finds a specific word
(such
as
catholic or baptist) then it would update the combo box to what it is
supposed to show. What I have now is that sometimes the field will
show
something like roman catholic, catholic churches, or just plain
catholic,
so
I want it to search for the specific key word (catholic) and then
change
the
value to what's in the combo box (Roman Catholic). I know all the
values
that can show up, so I know what key words I need to use.

Tables don't contain combo boxes. Are you wanting to modify the value in
a
particular field in the table after import? If so, you could use
something
like this:

UPDATE

SET [field]="Roman Catholic"
WHERE [field] LIKE "*catholic*"

Carl Rapson

 

[Guest]

Is there a way to do this in VBA, though?

As far as I know, you'll have to run a separate UPDATE query for each case.
There doesn't seem to be any way to generalize this into a single SQL
statement.

Carl Rapson

I had gotten that far, but where I'm stuck is if the field contains
"*baptist*" and I want to set it to "Baptist". Like I mentioned before,
the
field can contain any of about 25 different things, and I want to narrow
it
down to about 12 things that I have in a combo box for that field. Like
if
the word "Pentecostal" is in the field, I want to set it to "Other
Christian". I hope I'm clear in what I want.

I have a spreadsheet that I import into a database. One of the columns
in
the spreadsheet gets imported into a combo box (I want to limit the
selections available because of the reports that I have).
Unfortunately,
there are too many things that can be written into this field. What I
would
like to do is write an update query that will go through the field
after
import searching for specific words and if it finds a specific word
(such
as
catholic or baptist) then it would update the combo box to what it is
supposed to show. What I have now is that sometimes the field will
show
something like roman catholic, catholic churches, or just plain
catholic,
so
I want it to search for the specific key word (catholic) and then
change
the
value to what's in the combo box (Roman Catholic). I know all the
values
that can show up, so I know what key words I need to use.

Tables don't contain combo boxes. Are you wanting to modify the value in
a
particular field in the table after import? If so, you could use
something
like this:

UPDATE

SET [field]="Roman Catholic"
WHERE [field] LIKE "*catholic*"

Carl Rapson

 

[John Spencer]

Do you have a table that contains the valid entries for the field?

IF so, you can use it and some VBA to run multiple update queries. The VBA
code to do so would look something like the following UNTESTED AIRCODE.

Assumption: You have a table named Religions with a field Religion that
contains the values you want

Public Sub sUpdateReligion()
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim strSQL as String

On Error GoTo ERROR_Handler
Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset("SELECT Religion FROM ReligionsTable")

DO While Not rstAny.EOF
strSQL = "UPDATE YourTable " & _
" SET theField = """ & rstAny!Religion & """" & _
" WHERE Thefield Like ""*" & rstAny!Religion &"*""" & _
" And theField <> """ & rstAny!Religion & """"
dbAny.Execute StrSQL, dbFailonError
Loop

Exit Sub

ERROR_Handler:
MsgBox Err.Number & ":" & Err.Description,,"Whoops!"
End Sub

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Is there a way to do this in VBA, though?

As far as I know, you'll have to run a separate UPDATE query for each
case.
There doesn't seem to be any way to generalize this into a single SQL
statement.

Carl Rapson

I had gotten that far, but where I'm stuck is if the field contains
"*baptist*" and I want to set it to "Baptist". Like I mentioned
before,
the
field can contain any of about 25 different things, and I want to
narrow
it
down to about 12 things that I have in a combo box for that field.
Like
if
the word "Pentecostal" is in the field, I want to set it to "Other
Christian". I hope I'm clear in what I want.

:

I have a spreadsheet that I import into a database. One of the
columns
in
the spreadsheet gets imported into a combo box (I want to limit the
selections available because of the reports that I have).
Unfortunately,
there are too many things that can be written into this field. What
I
would
like to do is write an update query that will go through the field
after
import searching for specific words and if it finds a specific word
(such
as
catholic or baptist) then it would update the combo box to what it
is
supposed to show. What I have now is that sometimes the field will
show
something like roman catholic, catholic churches, or just plain
catholic,
so
I want it to search for the specific key word (catholic) and then
change
the
value to what's in the combo box (Roman Catholic). I know all the
values
that can show up, so I know what key words I need to use.

Tables don't contain combo boxes. Are you wanting to modify the value
in
a
particular field in the table after import? If so, you could use
something
like this:

UPDATE

SET [field]="Roman Catholic"
WHERE [field] LIKE "*catholic*"

Carl Rapson