If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
...OK, I'M STUPID, PLEASE HELP!
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! |
#2
|
|||
|
|||
...OK, I'M STUPID, 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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Brad Hodges" wrote: 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! |
#3
|
|||
|
|||
...OK, I'M STUPID, PLEASE HELP!
Brad Hodges wrote:
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! 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. -- HTH, Bob Barrows |
#4
|
|||
|
|||
...OK, I'M STUPID, PLEASE HELP!
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? |
#5
|
|||
|
|||
...OK, I'M STUPID, PLEASE HELP!
Bob, Thank you very much. Please see my responce to Jerry's post. |
#6
|
|||
|
|||
...OK, I'M STUPID, PLEASE HELP!
Brad Hodges wrote:
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? 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. -- HTH, Bob Barrows |
#7
|
|||
|
|||
...OK, I'M STUPID, PLEASE HELP!
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. |
#8
|
|||
|
|||
...OK, I'M STUPID, PLEASE HELP!
Brad Hodges wrote:
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 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. 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? -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#9
|
|||
|
|||
...OK, I'M STUPID, PLEASE HELP!
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 Barrows wrote: Brad Hodges wrote: 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 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. 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? |
#10
|
|||
|
|||
...OK, I'M STUPID, PLEASE HELP!
John,
That's the missing piece of info I needed. Stupid just got a little smarter!! Thank you Sir! "John Spencer" wrote: 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 Barrows wrote: Brad Hodges wrote: 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 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. 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? |
Thread Tools | |
Display Modes | |
|
|