Find - replace automatic

C

Clemens

I have created a dbase in Access2007. The dbase is flat using an import of an
excel sheet. This excel sheet (export from 3th party tool) will be imported
in access to create multiple reports.
In the dbase there are several fields or columns that contains information
needed to be changed. The most simple solution (manual) is to open the
database in the datasheet view, select the column and then find and replace.
This action is manual, but needs to be automated like following:
In database table SCexport2 and in column/field 'Approval Staus" each cell
that contains 'approved' needs to be replaced with 'OK'.
In the same column 'Approval Status' also cells contains the value 'pending'
and this needs to be replaced with '-'. And again in the same column cells
with 'denied' must be replaced with 'X'.
Another column 'Orginator' contains names of requesters. I want to replace
the names of these requesters to only the initials + department.

How can this be automated. I am a newbie to access, so your help is
needed...thnx
 
A

Al Campagna

Clemens,
You can create an Update query, that you can run against the imported
data in SCexport2.
In the Update query, you can handle the ApprovalStatus updates with...

IIF([Approval Status] = "Approved", "OK", IIF([Approval Status] = "Pending",
"-", IIF([Approval Status] = "Denied", "X", [Approval Status])))

Regarding the Originator update, you haven't given any information as to
the "before" vs. the "after value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
C

Clemens

Hi Al,
Thanks for replying.
The Originator is from the same setup as the approval status, only with
different values. If your solution works for 'Approval Status' it also needs
to work for Originator.

Now next question (newbie to access): where and how to create this query?
can it be mixed with another query allready made based on the table, where a
selection is created based on a date filter ">=[text]".

Thanks again

Al Campagna said:
Clemens,
You can create an Update query, that you can run against the imported
data in SCexport2.
In the Update query, you can handle the ApprovalStatus updates with...

IIF([Approval Status] = "Approved", "OK", IIF([Approval Status] = "Pending",
"-", IIF([Approval Status] = "Denied", "X", [Approval Status])))

Regarding the Originator update, you haven't given any information as to
the "before" vs. the "after value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Clemens said:
I have created a dbase in Access2007. The dbase is flat using an import of
an
excel sheet. This excel sheet (export from 3th party tool) will be
imported
in access to create multiple reports.
In the dbase there are several fields or columns that contains information
needed to be changed. The most simple solution (manual) is to open the
database in the datasheet view, select the column and then find and
replace.
This action is manual, but needs to be automated like following:
In database table SCexport2 and in column/field 'Approval Staus" each cell
that contains 'approved' needs to be replaced with 'OK'.
In the same column 'Approval Status' also cells contains the value
'pending'
and this needs to be replaced with '-'. And again in the same column cells
with 'denied' must be replaced with 'X'.
Another column 'Orginator' contains names of requesters. I want to replace
the names of these requesters to only the initials + department.

How can this be automated. I am a newbie to access, so your help is
needed...thnx
Click to expand...
Click to expand...
 
A

Al Campagna

Clemens,
Open a new query in Grid design mode, and bring your imported table into
it, and add the [Approval Status] field to the grid.
Select Update from the Query selection on the main menu.
Now the [Approval Status] column has a
Update To:
Place my IIF statement in there to handle the changes you need to make
for [Approval Status] values.

Use the same procedure to Update your Originator column, with an
appropriate IIF statement for those values.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Clemens said:
Hi Al,
Thanks for replying.
The Originator is from the same setup as the approval status, only with
different values. If your solution works for 'Approval Status' it also
needs
to work for Originator.

Now next question (newbie to access): where and how to create this query?
can it be mixed with another query allready made based on the table, where
a
selection is created based on a date filter ">=[text]".

Thanks again

Al Campagna said:
Clemens,
You can create an Update query, that you can run against the imported
data in SCexport2.
In the Update query, you can handle the ApprovalStatus updates
with...

IIF([Approval Status] = "Approved", "OK", IIF([Approval Status] =
"Pending",
"-", IIF([Approval Status] = "Denied", "X", [Approval Status])))

Regarding the Originator update, you haven't given any information as
to
the "before" vs. the "after value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Clemens said:
I have created a dbase in Access2007. The dbase is flat using an import
of
an
excel sheet. This excel sheet (export from 3th party tool) will be
imported
in access to create multiple reports.
In the dbase there are several fields or columns that contains
information
needed to be changed. The most simple solution (manual) is to open the
database in the datasheet view, select the column and then find and
replace.
This action is manual, but needs to be automated like following:
In database table SCexport2 and in column/field 'Approval Staus" each
cell
that contains 'approved' needs to be replaced with 'OK'.
In the same column 'Approval Status' also cells contains the value
'pending'
and this needs to be replaced with '-'. And again in the same column
cells
with 'denied' must be replaced with 'X'.
Another column 'Orginator' contains names of requesters. I want to
replace
the names of these requesters to only the initials + department.

How can this be automated. I am a newbie to access, so your help is
needed...thnx
Click to expand...
Click to expand...
Click to expand...
 
C

Clemens

Hi Al,

I did everything you mentioned, but unfortunally I recieve an error when I
press the Run button.

I opened a new query in the design view. In the top (of the split screen) I
see the table with the list of all fields. From this list I've selected
'Approval Status' and dropped this at the bottom of this split screen. At the
field line 'Approval Status' is reported and at the Table line the correct
table is reported (the is only 1 table).
When I press the 'Update' button from the Design toolbar, I see the extra
line 'Update to:". I have copied the following line "IIF([Approval Status] =
"Approved", "OK", IIF([Approval Status] = "Pending", "-", IIF([Approval
Status] = "Denied", "X", [Approval Status])))
To test this, I've pressed the Run button and recieves following error:

The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered test without surrounding it in quotation marks.

When I press OK, the cursor is blinking at the first comma in the IFF
statement.

What is wrong?

Thanks for helping.

Clemens

Al Campagna said:
Clemens,
Open a new query in Grid design mode, and bring your imported table into
it, and add the [Approval Status] field to the grid.
Select Update from the Query selection on the main menu.
Now the [Approval Status] column has a
Update To:
Place my IIF statement in there to handle the changes you need to make
for [Approval Status] values.

Use the same procedure to Update your Originator column, with an
appropriate IIF statement for those values.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Clemens said:
Hi Al,
Thanks for replying.
The Originator is from the same setup as the approval status, only with
different values. If your solution works for 'Approval Status' it also
needs
to work for Originator.

Now next question (newbie to access): where and how to create this query?
can it be mixed with another query allready made based on the table, where
a
selection is created based on a date filter ">=[text]".

Thanks again

Al Campagna said:
Clemens,
You can create an Update query, that you can run against the imported
data in SCexport2.
In the Update query, you can handle the ApprovalStatus updates
with...

IIF([Approval Status] = "Approved", "OK", IIF([Approval Status] =
"Pending",
"-", IIF([Approval Status] = "Denied", "X", [Approval Status])))

Regarding the Originator update, you haven't given any information as
to
the "before" vs. the "after value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have created a dbase in Access2007. The dbase is flat using an import
of
an
excel sheet. This excel sheet (export from 3th party tool) will be
imported
in access to create multiple reports.
In the dbase there are several fields or columns that contains
information
needed to be changed. The most simple solution (manual) is to open the
database in the datasheet view, select the column and then find and
replace.
This action is manual, but needs to be automated like following:
In database table SCexport2 and in column/field 'Approval Staus" each
cell
that contains 'approved' needs to be replaced with 'OK'.
In the same column 'Approval Status' also cells contains the value
'pending'
and this needs to be replaced with '-'. And again in the same column
cells
with 'denied' must be replaced with 'X'.
Another column 'Orginator' contains names of requesters. I want to
replace
the names of these requesters to only the initials + department.

How can this be automated. I am a newbie to access, so your help is
needed...thnx
Click to expand...
Click to expand...
Click to expand...
 
A

Al Campagna

Clemens,
I tested my IIF statement, and it works. I built a table with a
[Approval Status] field, and ran an Update query against that field. It
properly converted the values.
Here is my cut & pasted code...
IIf([Approval Status]="Approved","OK",IIf([Approval
Status]="Pending","-",IIf([Approval Status]="Denied","X",[Approval
Status])))

I can only assume you have not correctly copied my code into the Update To:
You wrote...
"IIF([Approval Status] = "Approved", "OK", IIF([Approval Status] =
"Pending", "-", IIF([Approval Status] = "Denied", "X", [Approval Status])))

*Notice that you have a quote mark at the start of the IIF. That is
wrong.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Clemens said:
Hi Al,

I did everything you mentioned, but unfortunally I recieve an error when I
press the Run button.

I opened a new query in the design view. In the top (of the split screen)
I
see the table with the list of all fields. From this list I've selected
'Approval Status' and dropped this at the bottom of this split screen. At
the
field line 'Approval Status' is reported and at the Table line the correct
table is reported (the is only 1 table).
When I press the 'Update' button from the Design toolbar, I see the extra
line 'Update to:". I have copied the following line "IIF([Approval Status]
=
"Approved", "OK", IIF([Approval Status] = "Pending", "-", IIF([Approval
Status] = "Denied", "X", [Approval Status])))
To test this, I've pressed the Run button and recieves following error:

The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered test without surrounding it in quotation marks.

When I press OK, the cursor is blinking at the first comma in the IFF
statement.

What is wrong?

Thanks for helping.

Clemens

Al Campagna said:
Clemens,
Open a new query in Grid design mode, and bring your imported table
into
it, and add the [Approval Status] field to the grid.
Select Update from the Query selection on the main menu.
Now the [Approval Status] column has a
Update To:
Place my IIF statement in there to handle the changes you need to make
for [Approval Status] values.

Use the same procedure to Update your Originator column, with an
appropriate IIF statement for those values.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Clemens said:
Hi Al,
Thanks for replying.
The Originator is from the same setup as the approval status, only with
different values. If your solution works for 'Approval Status' it also
needs
to work for Originator.

Now next question (newbie to access): where and how to create this
query?
can it be mixed with another query allready made based on the table,
where
a
selection is created based on a date filter ">=[text]".

Thanks again

:

Clemens,
You can create an Update query, that you can run against the
imported
data in SCexport2.
In the Update query, you can handle the ApprovalStatus updates
with...

IIF([Approval Status] = "Approved", "OK", IIF([Approval Status] =
"Pending",
"-", IIF([Approval Status] = "Denied", "X", [Approval Status])))

Regarding the Originator update, you haven't given any information
as
to
the "before" vs. the "after value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have created a dbase in Access2007. The dbase is flat using an
import
of
an
excel sheet. This excel sheet (export from 3th party tool) will be
imported
in access to create multiple reports.
In the dbase there are several fields or columns that contains
information
needed to be changed. The most simple solution (manual) is to open
the
database in the datasheet view, select the column and then find and
replace.
This action is manual, but needs to be automated like following:
In database table SCexport2 and in column/field 'Approval Staus"
each
cell
that contains 'approved' needs to be replaced with 'OK'.
In the same column 'Approval Status' also cells contains the value
'pending'
and this needs to be replaced with '-'. And again in the same column
cells
with 'denied' must be replaced with 'X'.
Another column 'Orginator' contains names of requesters. I want to
replace
the names of these requesters to only the initials + department.

How can this be automated. I am a newbie to access, so your help is
needed...thnx
Click to expand...
Click to expand...
Click to expand...
 
C

Clemens

Hi Al,

I don't know where I am going wrong, but it fails with same error.

I've created a completly new database just to test it (it could be the
orignal dbase is causing the issue).
2 fields (1 - ID; 2 - Approval Status). Following all steps mentioned before
(new query in design view, drag field to bottom of split screen and copy /
past the statement at the Update to: line. And again same error.

So what could be the cause of the error I recieve?

Again for helping out this far


Al Campagna said:
Clemens,
I tested my IIF statement, and it works. I built a table with a
[Approval Status] field, and ran an Update query against that field. It
properly converted the values.
Here is my cut & pasted code...
IIf([Approval Status]="Approved","OK",IIf([Approval
Status]="Pending","-",IIf([Approval Status]="Denied","X",[Approval
Status])))

I can only assume you have not correctly copied my code into the Update To:
You wrote...
"IIF([Approval Status] = "Approved", "OK", IIF([Approval Status] =
"Pending", "-", IIF([Approval Status] = "Denied", "X", [Approval Status])))

*Notice that you have a quote mark at the start of the IIF. That is
wrong.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Clemens said:
Hi Al,

I did everything you mentioned, but unfortunally I recieve an error when I
press the Run button.

I opened a new query in the design view. In the top (of the split screen)
I
see the table with the list of all fields. From this list I've selected
'Approval Status' and dropped this at the bottom of this split screen. At
the
field line 'Approval Status' is reported and at the Table line the correct
table is reported (the is only 1 table).
When I press the 'Update' button from the Design toolbar, I see the extra
line 'Update to:". I have copied the following line "IIF([Approval Status]
=
"Approved", "OK", IIF([Approval Status] = "Pending", "-", IIF([Approval
Status] = "Denied", "X", [Approval Status])))
To test this, I've pressed the Run button and recieves following error:

The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered test without surrounding it in quotation marks.

When I press OK, the cursor is blinking at the first comma in the IFF
statement.

What is wrong?

Thanks for helping.

Clemens

Al Campagna said:
Clemens,
Open a new query in Grid design mode, and bring your imported table
into
it, and add the [Approval Status] field to the grid.
Select Update from the Query selection on the main menu.
Now the [Approval Status] column has a
Update To:
Place my IIF statement in there to handle the changes you need to make
for [Approval Status] values.

Use the same procedure to Update your Originator column, with an
appropriate IIF statement for those values.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Hi Al,
Thanks for replying.
The Originator is from the same setup as the approval status, only with
different values. If your solution works for 'Approval Status' it also
needs
to work for Originator.

Now next question (newbie to access): where and how to create this
query?
can it be mixed with another query allready made based on the table,
where
a
selection is created based on a date filter ">=[text]".

Thanks again

:

Clemens,
You can create an Update query, that you can run against the
imported
data in SCexport2.
In the Update query, you can handle the ApprovalStatus updates
with...

IIF([Approval Status] = "Approved", "OK", IIF([Approval Status] =
"Pending",
"-", IIF([Approval Status] = "Denied", "X", [Approval Status])))

Regarding the Originator update, you haven't given any information
as
to
the "before" vs. the "after value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have created a dbase in Access2007. The dbase is flat using an
import
of
an
excel sheet. This excel sheet (export from 3th party tool) will be
imported
in access to create multiple reports.
In the dbase there are several fields or columns that contains
information
needed to be changed. The most simple solution (manual) is to open
the
database in the datasheet view, select the column and then find and
replace.
This action is manual, but needs to be automated like following:
In database table SCexport2 and in column/field 'Approval Staus"
each
cell
that contains 'approved' needs to be replaced with 'OK'.
In the same column 'Approval Status' also cells contains the value
'pending'
and this needs to be replaced with '-'. And again in the same column
cells
with 'denied' must be replaced with 'X'.
Another column 'Orginator' contains names of requesters. I want to
replace
the names of these requesters to only the initials + department.

How can this be automated. I am a newbie to access, so your help is
needed...thnx
Click to expand...
Click to expand...
Click to expand...
 
A

Al Campagna

Clemens,
So there was a quote just before the first IIF statement? And you
removed that, and it still fails with a "syntax" error?

And the expression I gave you is all entered on one line? (sorry, but
I have to ask that)
And you used bracketing on all field names? Sometimes Intellisense
substitutes " quote marks when brackets are missing on a field name.

I cut and pasted the code that I tested, and then posted to you, so...
there shouldn't be a syntax problem.

Please cut and paste *exactly* what you have in the Update To:
Whenever you include code in your post... *always use cut and paste*.
That way we can make sure no other "hand entered" errors get introduced into
the problem.

If we still can't find the problem, you could send me the zipped .mdb
file/s. We'll see about that if we need it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."



Clemens said:
Hi Al,

I don't know where I am going wrong, but it fails with same error.

I've created a completly new database just to test it (it could be the
orignal dbase is causing the issue).
2 fields (1 - ID; 2 - Approval Status). Following all steps mentioned
before
(new query in design view, drag field to bottom of split screen and copy /
past the statement at the Update to: line. And again same error.

So what could be the cause of the error I recieve?

Again for helping out this far


Al Campagna said:
Clemens,
I tested my IIF statement, and it works. I built a table with a
[Approval Status] field, and ran an Update query against that field. It
properly converted the values.
Here is my cut & pasted code...
IIf([Approval Status]="Approved","OK",IIf([Approval
Status]="Pending","-",IIf([Approval Status]="Denied","X",[Approval
Status])))

I can only assume you have not correctly copied my code into the Update
To:
You wrote...
"IIF([Approval Status] = "Approved", "OK", IIF([Approval Status] =
"Pending", "-", IIF([Approval Status] = "Denied", "X", [Approval
Status])))

*Notice that you have a quote mark at the start of the IIF. That is
wrong.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Clemens said:
Hi Al,

I did everything you mentioned, but unfortunally I recieve an error
when I
press the Run button.

I opened a new query in the design view. In the top (of the split
screen)
I
see the table with the list of all fields. From this list I've selected
'Approval Status' and dropped this at the bottom of this split screen.
At
the
field line 'Approval Status' is reported and at the Table line the
correct
table is reported (the is only 1 table).
When I press the 'Update' button from the Design toolbar, I see the
extra
line 'Update to:". I have copied the following line "IIF([Approval
Status]
=
"Approved", "OK", IIF([Approval Status] = "Pending", "-", IIF([Approval
Status] = "Denied", "X", [Approval Status])))
To test this, I've pressed the Run button and recieves following error:

The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered test without surrounding it in quotation marks.

When I press OK, the cursor is blinking at the first comma in the IFF
statement.

What is wrong?

Thanks for helping.

Clemens

:

Clemens,
Open a new query in Grid design mode, and bring your imported table
into
it, and add the [Approval Status] field to the grid.
Select Update from the Query selection on the main menu.
Now the [Approval Status] column has a
Update To:
Place my IIF statement in there to handle the changes you need to
make
for [Approval Status] values.

Use the same procedure to Update your Originator column, with an
appropriate IIF statement for those values.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Hi Al,
Thanks for replying.
The Originator is from the same setup as the approval status, only
with
different values. If your solution works for 'Approval Status' it
also
needs
to work for Originator.

Now next question (newbie to access): where and how to create this
query?
can it be mixed with another query allready made based on the table,
where
a
selection is created based on a date filter ">=[text]".

Thanks again

:

Clemens,
You can create an Update query, that you can run against the
imported
data in SCexport2.
In the Update query, you can handle the ApprovalStatus updates
with...

IIF([Approval Status] = "Approved", "OK", IIF([Approval Status] =
"Pending",
"-", IIF([Approval Status] = "Denied", "X", [Approval Status])))

Regarding the Originator update, you haven't given any
information
as
to
the "before" vs. the "after value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

I have created a dbase in Access2007. The dbase is flat using an
import
of
an
excel sheet. This excel sheet (export from 3th party tool) will
be
imported
in access to create multiple reports.
In the dbase there are several fields or columns that contains
information
needed to be changed. The most simple solution (manual) is to
open
the
database in the datasheet view, select the column and then find
and
replace.
This action is manual, but needs to be automated like following:
In database table SCexport2 and in column/field 'Approval Staus"
each
cell
that contains 'approved' needs to be replaced with 'OK'.
In the same column 'Approval Status' also cells contains the
value
'pending'
and this needs to be replaced with '-'. And again in the same
column
cells
with 'denied' must be replaced with 'X'.
Another column 'Orginator' contains names of requesters. I want
to
replace
the names of these requesters to only the initials + department.

How can this be automated. I am a newbie to access, so your help
is
needed...thnx
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Copy cells from one worksheet to another workseet - increment row & do again 0
Find a value in a sheet and replace it with another value if it matches 8
Faster way to find and replace? 1
Find/Replace - from old to new 1
Excel Is this possible?? 3
Excel If a Cell Contains a Certain Text (From Another Table) 0
Complex search and replace macro based on input variables. 12
Find/replace [Zx] with [Znumber] 1

Top