...OK, I'M STUPID, PLEASE HELP!

  • Thread starter Thread starter Brad Hodges
  • Start date Start date
B

Brad Hodges

I have a class of students who on graduation receive a title modification.
I'm trying to create an update query to my main table concatenating "MR" to
the existing "rate" field. I believe it is a CStr "MR" & (ACTIVE.RATE)
construction into the query, then change to an update query.

This is my best non-functional guess. Please help!
 
What data type is ACTIVE.RATE? You really shouldn't need a CStr function
unless RATE is a number or something. If it is a number field, you won't be
able to update it with an 'MR' anyway.
 
Brad said:
I have a class of students who on graduation receive a title
modification. I'm trying to create an update query to my main table
concatenating "MR" to the existing "rate" field. I believe it is a
CStr "MR" & (ACTIVE.RATE) construction into the query, then change to
an update query.

This is my best non-functional guess. Please help!
Click to expand...

Assuming you wish to update all the records, create a new query in
design view, closing the Choose Table dialog without selecting a table,
and switch the query to SQL View using the View menu, or the toolbar
button, or the right-click context menu. Then paste the following into
the sql window:

update [Active]
set rate = "MR" & [rate]

You can switch back to Design View to see how you were supposed to do
this using the grid.
 
Jerry,

No, it's text "MC" to existing text field. A single table has two fields.
One of the fields but I would filter with query brackets [Which Course?]
for the spefic course whose students "rate" text field needs the prefix "MC"
appended on the course graduation date. Does this make sence?
 
Brad said:
Jerry,

No, it's text "MC" to existing text field. A single table has two
fields. One of the fields but I would filter with query brackets
[Which Course?] for the spefic course whose students "rate" text
field needs the prefix "MC" appended on the course graduation date.
Does this make sence?
Click to expand...

I think so. Assuming you wish to be prompted for the course, change my
previous recommendation to:
update [Active]
set rate = "MC" & [rate]
where [Which Course?] =[Enter the course]

BACK UP YOUR DATABASE and try this out.
 
Bob,

I used
UPDATE Active SET Active.Rate = "MC" & Active.Rate
WHERE (Active.Class)=[WHICH COURSE?];

and all I'm getting back is the rate field returned with no concatenated
"MC" like I want. Should it be "me.active.rate" for the set command? That
doesn't work either. The table is "active" and the fields "class" and "rate"
but it isn't working.
It looks absolutely correct to me, but what do I know.
 
Brad said:
Bob,

I used
UPDATE Active SET Active.Rate = "MC" & Active.Rate
WHERE (Active.Class)=[WHICH COURSE?];

and all I'm getting back is the rate field returned with no
concatenated "MC" like I want. Should it be "me.active.rate" for the
Click to expand...

Absolutely not.
set command? That doesn't work either. The table is "active" and
the fields "class" and "rate" but it isn't working.
It looks absolutely correct to me, but what do I know.
Click to expand...

Err ... this query does not return records. Do you mean you check the table
after running the query and fail to see the MC prefix? That probably means
you are not supplying the proper criterion for the course when you are
prompted. Show me a few rows of sample data to illustrate what the data
looks like to begin with and how you want it to look after the query runs.
Also, tell me what you enter when prompted for the course name.
PS. [Class] is not a lookup field is it?
 
In Access, I've found it is usually a good idea to bracket the table and field
name on the right side of the set clause.

UPDATE Active SET Active.Rate = "MC" & [Active].[Rate]
WHERE (Active.Class)=[WHICH COURSE?];

Perhaps an unneeded question: Are you running the query or simply switching
to datasheet view? If you just switch to datasheet view then you will see the
records and values that WILL be changed in you run the query.

Select Query: Run from the menu or click the button with the red exclamation
mark to execute the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Bob said:
Brad said:
Bob,

I used
UPDATE Active SET Active.Rate = "MC" & Active.Rate
WHERE (Active.Class)=[WHICH COURSE?];

and all I'm getting back is the rate field returned with no
concatenated "MC" like I want. Should it be "me.active.rate" for the
Click to expand...

Absolutely not.
set command? That doesn't work either. The table is "active" and
the fields "class" and "rate" but it isn't working.
It looks absolutely correct to me, but what do I know.
Click to expand...

Err ... this query does not return records. Do you mean you check the table
after running the query and fail to see the MC prefix? That probably means
you are not supplying the proper criterion for the course when you are
prompted. Show me a few rows of sample data to illustrate what the data
looks like to begin with and how you want it to look after the query runs.
Also, tell me what you enter when prompted for the course name.
PS. [Class] is not a lookup field is it?
Click to expand...
 
John,

That's the missing piece of info I needed. Stupid just got a little
smarter!! Thank you Sir!

John Spencer said:
In Access, I've found it is usually a good idea to bracket the table and field
name on the right side of the set clause.

UPDATE Active SET Active.Rate = "MC" & [Active].[Rate]
WHERE (Active.Class)=[WHICH COURSE?];

Perhaps an unneeded question: Are you running the query or simply switching
to datasheet view? If you just switch to datasheet view then you will see the
records and values that WILL be changed in you run the query.

Select Query: Run from the menu or click the button with the red exclamation
mark to execute the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Bob said:
Brad said:
Bob,

I used
UPDATE Active SET Active.Rate = "MC" & Active.Rate
WHERE (Active.Class)=[WHICH COURSE?];

and all I'm getting back is the rate field returned with no
concatenated "MC" like I want. Should it be "me.active.rate" for the
Click to expand...

Absolutely not.
set command? That doesn't work either. The table is "active" and
the fields "class" and "rate" but it isn't working.
It looks absolutely correct to me, but what do I know.
Click to expand...

Err ... this query does not return records. Do you mean you check the table
after running the query and fail to see the MC prefix? That probably means
you are not supplying the proper criterion for the course when you are
prompted. Show me a few rows of sample data to illustrate what the data
looks like to begin with and how you want it to look after the query runs.
Also, tell me what you enter when prompted for the course name.
PS. [Class] is not a lookup field is it?
Click to expand...
Click to expand...
 
Back
Top