Update Query Question 06-19-07

This is what I want to do.  

In the PO_TBL I have a field PO_ITEM_NBR which combines the data of the 
PO_ITEM and PO_NBR fields.
I want to parse into the actual fields of PO_ITEM and PO_NBR which will 
exist in the same table.

Current Table                    Result   
PO_ITEM_NBR                      PO_ITEM  PO_NBR
123344 001                       123344     001
123344 002                       123344     002
123455 001                       123455     001
etc.

I have created a updte query with the following SQL statememnt:

UPDATE PO_TBL SET PO_ITEM = Left(PO_ITEM_NBR,InStr(PO_ITEM_NBR," ")-1), 
                   PO_NBR = Mid(PO_ITEM_NBR,InStr(PO_ITEM_NBR," ")+1);

When I attempt to do this, it asks for the PO_ITEM_NBR.  I do not enter one 
because I want the entire database.
It then gives me the error Datatype mismatch in criteria expression.

Why to I need to provide a PO_ITEM_NBR?  How do I write this query to update 
every row in access 
without providing a PO_ITEM_NBR?

Thanks,
0
Utf
6/19/2007 11:58:01 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
953 Views

Similar Articles

[PageSpeed] 7

Nancy,

Try wrapping PO_ITEM_NBR with brackets, everywhere you find it in your query.

But I'm confused, how do you get to the point that you have a PO_ITEM_NBR, 
but don't already have PO_ITEM and PO_NBR?  I assume that once you get 
PO_ITEM and PO_NBR in your table, you are going to delete the column 
PO_ITEM_NBR since you won't need it any more, and it violates the first 
normal form of database design (No field will contain more than one data 
element).

HTH
Dale
-- 
Email address is not valid.
Please reply to newsgroup only.


"Nancy" wrote:

> This is what I want to do.  
> 
> In the PO_TBL I have a field PO_ITEM_NBR which combines the data of the 
> PO_ITEM and PO_NBR fields.
> I want to parse into the actual fields of PO_ITEM and PO_NBR which will 
> exist in the same table.
> 
> Current Table                    Result   
> PO_ITEM_NBR                      PO_ITEM  PO_NBR
> 123344 001                       123344     001
> 123344 002                       123344     002
> 123455 001                       123455     001
> etc.
> 
> I have created a updte query with the following SQL statememnt:
> 
> UPDATE PO_TBL SET PO_ITEM = Left(PO_ITEM_NBR,InStr(PO_ITEM_NBR," ")-1), 
>                    PO_NBR = Mid(PO_ITEM_NBR,InStr(PO_ITEM_NBR," ")+1);
> 
> When I attempt to do this, it asks for the PO_ITEM_NBR.  I do not enter one 
> because I want the entire database.
> It then gives me the error Datatype mismatch in criteria expression.
> 
> Why to I need to provide a PO_ITEM_NBR?  How do I write this query to update 
> every row in access 
> without providing a PO_ITEM_NBR?
> 
> Thanks,
0
Utf
6/19/2007 1:30:07 PM
This did not work.  I get the same result. The reason I am doing this is 
because I inherited this table and want to correct it so I may use it in a 
form.

Anything else I can do?  I really need this to work.

"Dale Fye" wrote:

> Nancy,
> 
> Try wrapping PO_ITEM_NBR with brackets, everywhere you find it in your query.
> 
> But I'm confused, how do you get to the point that you have a PO_ITEM_NBR, 
> but don't already have PO_ITEM and PO_NBR?  I assume that once you get 
> PO_ITEM and PO_NBR in your table, you are going to delete the column 
> PO_ITEM_NBR since you won't need it any more, and it violates the first 
> normal form of database design (No field will contain more than one data 
> element).
> 
> HTH
> Dale
> -- 
> Email address is not valid.
> Please reply to newsgroup only.
> 
> 
> "Nancy" wrote:
> 
> > This is what I want to do.  
> > 
> > In the PO_TBL I have a field PO_ITEM_NBR which combines the data of the 
> > PO_ITEM and PO_NBR fields.
> > I want to parse into the actual fields of PO_ITEM and PO_NBR which will 
> > exist in the same table.
> > 
> > Current Table                    Result   
> > PO_ITEM_NBR                      PO_ITEM  PO_NBR
> > 123344 001                       123344     001
> > 123344 002                       123344     002
> > 123455 001                       123455     001
> > etc.
> > 
> > I have created a updte query with the following SQL statememnt:
> > 
> > UPDATE PO_TBL SET PO_ITEM = Left(PO_ITEM_NBR,InStr(PO_ITEM_NBR," ")-1), 
> >                    PO_NBR = Mid(PO_ITEM_NBR,InStr(PO_ITEM_NBR," ")+1);
> > 
> > When I attempt to do this, it asks for the PO_ITEM_NBR.  I do not enter one 
> > because I want the entire database.
> > It then gives me the error Datatype mismatch in criteria expression.
> > 
> > Why to I need to provide a PO_ITEM_NBR?  How do I write this query to update 
> > every row in access 
> > without providing a PO_ITEM_NBR?
> > 
> > Thanks,
0
Utf
6/19/2007 1:58:00 PM
First check the name of the field the query is asking for and make sure you 
haven't mistyped the field name.

Second, add a where clause so you won't try to process fields that are null 
or don't have space in them.

UPDATE PO_TBL
SET PO_ITEM = Left([PO_ITEM_NBR],InStr([PO_ITEM_NBR]," ")-1),
                   PO_NBR = Mid([PO_ITEM_NBR],InStr([PO_ITEM_NBR]," ")+1)
WHERE PO_ITEM_NBR LIKE "* *"

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

"Nancy" <Nancy@discussions.microsoft.com> wrote in message 
news:5FCC9097-F1D1-449D-8F7F-AE5AD35231A8@microsoft.com...
> This is what I want to do.
>
> In the PO_TBL I have a field PO_ITEM_NBR which combines the data of the
> PO_ITEM and PO_NBR fields.
> I want to parse into the actual fields of PO_ITEM and PO_NBR which will
> exist in the same table.
>
> Current Table                    Result
> PO_ITEM_NBR                      PO_ITEM  PO_NBR
> 123344 001                       123344     001
> 123344 002                       123344     002
> 123455 001                       123455     001
> etc.
>
> I have created a updte query with the following SQL statememnt:
>
> UPDATE PO_TBL SET PO_ITEM = Left(PO_ITEM_NBR,InStr(PO_ITEM_NBR," ")-1),
>                   PO_NBR = Mid(PO_ITEM_NBR,InStr(PO_ITEM_NBR," ")+1);
>
> When I attempt to do this, it asks for the PO_ITEM_NBR.  I do not enter 
> one
> because I want the entire database.
> It then gives me the error Datatype mismatch in criteria expression.
>
> Why to I need to provide a PO_ITEM_NBR?  How do I write this query to 
> update
> every row in access
> without providing a PO_ITEM_NBR?
>
> Thanks, 


0
John
6/19/2007 2:44:32 PM
Reply:

Similar Artilces:

Is this right with exchange calender update tool?
Hi all, I just finish testing the tool in the test environment. I am really confused now with this tool. The appoinment time (ex:9:00AM) moved to 8:00AM during the extended time. If I want the appointment to stay the same time 9:00AM, is this possible with this exchange tool? thanks Correction. Actually, the appoint. 9:00AM moved to 10:00AM. Is this right with this tool? "John" wrote: > Hi all, > > I just finish testing the tool in the test environment. I am really > confused now with this tool. The appoinment time (ex:9:00AM) moved to 8:00AM > duri...

Daily Importing of External Data using Web Query
Every month I create a spreadsheet from a template by creating one ta for each day of the month named after day of the month/year (010104). then maually enter a Web Query on each tab which downloads dail weather information for an insect life cycle model. This is a very time consuming and tedious task because the onl difference in the links is the expression "20040101" (for January 01 2004) to "20040102" (for January 02,2004) INCLUDED IN THE LINK. Is it possible to use a macro (VBA code) to enter the Web Query addres in cell A1 on each tab when the workbook is created an...

Query sorting help
I am in need of assistance with a Query. I have a query that assembles a daily production schedule. The shedule consists of many fields however for the sake of this question i wish to sort the query by a series of four fields. the first fiedld in the query is "DueDate" then by field "press1" then by "Press2" then Press3". The data in the field Due date is the actual date due. The data in "press1" will only be "6c" or nothing. The data in "Press2" will be one of three Strings: "25", "29", or "2...

Auto Page Numbering Question
I have a 12 page pricelist. Recently we made changes to the last 4 pages of this sheet. I am not getting ready to have the last 4 pages printed and sent to my customers so that they can simply replace the last 4 pages, leaving the first 8 in tact. I copied the last 4 pages into a new book, and need the page numbers to read 9 of 12, 10 of 12 ect. However, because the pages are in a new book I can only figure out how to have it say 1 of 4, 2 of 4 and so on. Basically, I need to change the footer to read 9 of 12, 10 of 12, 11 of 12 and 12 of 12, but this is only a 4 page sheet. ...

Question about graphing four sets of data
So i have four columns of data. The first column contains just names. I want the names to appear as the x-axis. The second column is slowest time, the third is fastest time (in min) that each name ran. Time in minutes will be on the y-axis. Up to now, it's pretty straight forward. Just make a bar chart with the name data and time data and there you go (two bars for each name). But I also have this fourth column of data which is "Number of attempted tries" or simply sample size. I want to place the sample size as a number on top of the bars. Any ideas? -- RawlinsCross...

from Outlook 03 to 07 w/o present pst file
I am going to install Office 07 pro full, I now have Office 03 pro full installed. I would like to move all of my Outlook folders, e-mail, contacts etc from the 03 version to the 07 version WITHOUT unilizing my present 03 PST file. Need some suggestions as to the most efficient manner to accomplish this, please. tia How do you propose to do this without using your current data file? Why would you think this is possible? It isn't, of course. -- Russ Valentine <shellyfdelete@hotmail.com> wrote in message news:a8ofk5lgr6j5edem1sgf4ot7bfqlanka4k@4ax.com... >I am goi...

error sending messages in outlook 07, account settings okay
Hello, This problem is giving me a huge headache, Recently everything was working well on my Vista OS, outlook 2007. Now I can't send out any messages, only receive. The messages stay in my OUTBOX and I get an error message when sending. Outlook is trying to send them but it can't. Account settings are good. I have 2 different user profiles crated on Vista with different e-mail accounts and outlook stopped sending on both of them. Don't matter which I'm logged in to, mine of my wife's it still doesn't work. I am also dual booting my PC and when I login to my XP ever...

formula question #23
if I have a date in colum A and a date in colum B, what would the formula be to find out how many months inbetween the two dates? -- fiona05 Morning Fiona Set up the formula so (eg) =B1-A1 and format the cell using FORMAT/CELLS/NUMBER select CUSTOM and enter in the TYPE box this: mm This gives you whole months between dates and might not be as accurate as you might need Jon -- Jon Quixley ------------------------------------------------------------------------ Jon Quixley's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25803 View this thread: http://ww...

Macro/VB coding question
I want a macro to work for a worksheet name which will always be the same directory and file name but the data inside worksheet will periodically change - increase in rows/decrease in rows. # of Columns/Column names will not change. I'm trying to filter for a non-changing set of info, then delete the results. Here's what I get if I record my current run: Workbooks.Open Filename:= _ "***Directory of location and file name here***" Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$S$746").AutoFilte...

Looking for good examples of XPath queries
Anyone care to recommend a web site that has some really good examples of XPath queries utilizing attributes? My XML books, and the web sites that I've looked at, don't really utilize attributes. So, their samples on the subject is weak. I tried looking at a number of sites based upon a Google query, but didn't find any that was that helpful. Richard L Rosenheim wrote: > Anyone care to recommend a web site that has some really good examples of > XPath queries utilizing attributes? http://www.zvon.org/xxl/XPathTutorial/Output/ -- Oleg Tkachenko [XML MVP] http://blog....

Windows 2003 R2 Active Directory Performance Question
Here is our environment: 5 Windows 2003 R2 SP2 Domain Controllers (4 of which also do File/Print/DNS and 1 is running DHCP) spread across multiple VLANs (multiple NICs mapped to different VLANs in each) ��� These are HP DL380 G5's with 8GB RAM runninw Win2k3R2 Enterprise These DCs are all in the same physical location supporting 10 other buildings, some buildings are 1 mile, some are 7 miles away connected by GB fiber. Network is GB between buildings, GB between closets, 100MB to the desktop with a mix of Extreme and HP equipment with one BlackDiamond 6808 Router in the data ...

Web Query Question
I'm updating sports scores in excel 2002, The first set of Columns Are the Team Names and the Final Score of the Game (These 2 columns update after each game). My other 2 columns are used for notes and other information. When ever I refresh the data, the first 2 sets of columns update, however the notes for the game stays in the same place. How do I get my other 2 columns to move down the page with the old scores. Please help, because this is driving me crazy! If I need to explain further Please dont hesitate to ask!! Thank you, Matt Thu, 20 Dec 2007 13:22:00 -0800 from So...

Duplicate transactions from updating online
I have searched and searched for answers and I can't find any. I tried calling Microsoft but they tell me to contact my computer manufacturer to answer software problems that they didn't even give me. I searched the chat and I keep seeing you respond to everyone and was wondering if you could help. I have a few problems. 1. I keep getting duplicate transactions from my bank statement when I update online. I bank with Bank of America. It is frustrating to go through your account and edit all the transactions when they keep appearing after each update. Then it will do the same thi...

Update Sub form as user enters data in main form
I have a mainform that uses an unbound combo box to populate a subform. This works great. This will display all of the existing data for my contracts. If however they want to add more contracts, they enter the data on the main form and after continueing on to the next record and selecting that company from the combo box, they will see that their contract has been added to the list. I would like to be able to show the user the data they are entering into the main form is also going directly into the subform as a new record without the need of a command button or advancing a record. At one p...

I have bug in deleting from db, update ,insert works fine
I have a small project in c# and ms-access I use query builder to manage my tables in ms-access. the problem is, select qeury works great, update query works great, delete doesn't work , and there is no error msg!!!!!!1 please help!! OleDbDataAdapter adapter = new OleDbDataAdapter(); string queryString = "SELECT [Id],[Name] FROM [myTable]"; cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _conStrName + ";User Id=admin;Password=;"; OleDbConnec...

2007 not updating data unless manually selecting data update from
I have data link between two 2007 workbooks, cells in book1 get data from cells in book2. However, when opening book one,it does not prompt user if data should be updated. I have to manually go to data menu -> edit links -> update values. I need to be able to automatically update book1 every time it opens. I have tried setting the properties in Edit Links so it does so, but it does not work. The book1 is not password protected and niether is book2. Thanks in advance. Imran I believe the answer can be found at: http://office.microsoft.com/en-us/excel/HP101023481033.aspx ....som...

A question for Bill Yanaire
This dumb **** is such fun to pick apart. The **** keeps falling out of his mouth as fast as this idiot can move his lips. The TRUTH is Bill is on cracl/cocaine most of the day. You people are talking a language that Yanaire RyanAire does not understand. It contains LOGIC and FACTS! The Moron has studied SQL Server as a beginner in 2007, and attempted VisualBasic, making some living off these Microsoft products and hence promoting Windows. His brain is too primitive to learn a real language like C or C++. Or be a man enough to code in Assembler, machine codes, octals or just bina...

Update a combo box
I have a well functioning database that sorts hundreds of publications based on three cascading combos. - category, subcategory and publication (title). I have it set-up that on one form (FmAddNew) the user selects a category from the first cbo, then a subcategory which is linked to the category cbo. On this form, the user enters the title into a text box. All other details are text boxes within a subform. On another form (FmView) the user is able to retrieve a publication via three cbos. Firstly they select the category from a cbo, then the subcategory from another cbo, and in a third...

updating records
I have a table that consists of four fields. caEmployeeNumber (long); caPositionNumber (long); default (boolean); and Description (text) caEmployeeNumber is the foreign key that links this to its parent table (not necessary for this issue) one-to-many each employee can have multiple position numbers, but only one of those numbers can be the default and it works fine except if the user is still in "edit" mode on the record and hits the escape key to exit edit mode without saving changes. I need a way to commit the change immediately so that they can't back out o...

Address List Query
Hi, I want to create an ldap query for an address that returns only the users that are in a specific organisational unit. I have tried using the query builder but I don't find OU anywhere in the availble search criteria. So I suppose I need to learn some ldap. Can anyone recommend a good "LDAP for dummies" website? I would also appreciate any suggestions with this specific query I am trying to build. TIA, Jarryd ...

Unable to get online updates from quotes server
I am not able to get a successful update to my quotes list online. Problem grew progressively worse as I added to my Portfolio - I have about 50 quotes on the list. I connect through a cable modem with no firewall. All other on-line updates with financial instituions work OK. I have cleared the quotes list several times. I sign on with a Microsoft NET passport login. What am I missing - or is the quotes server always down??? You're not missing anything. I've tried off and on since I bough 2002 in 2002. It worked the first time but never worked again since then. I have DSL and M...

parameter query
I have a parametery query setup in excel which gets its input from a cell, which does this.. Status In [EnterStatus] [EnterStatus] query is in a cell It works fine if I only enter one value in the cell but if I enter A,I,X (Active, Inactive, Expelled) for example i receive a string truncation error... if there were only two or 3 options I coult use 2 or 3 fields, but ehre are 10 possibles... any ideas how I can ask for multiple options.. the sql query would be Select * fom table where status in ('A','I','X')... aNY IDEAS ...

Assign the result of this simple query to a variable
I have a query that will return the count of evente corresponding to a specific employee based on their employee ID number. This is the SQL from the query: SELECT Count(*) AS Expr1 FROM tblCME_Data WHERE (((tblCME_Data.EmpIDNo)=[Forms]![frmEmployeeBudget]![txtEmployeeID_No])); If I run the query it works just fine. Instead of "running the query", I want to place this SQL into my form's load event and have the result be assigned to a variable. I can't seem to find the syntax. I've even tried a DCount() funstion but I can't seem to get that to work either. An...

Combining duplicates in different queries
Hi, I have two queries with the same Part No. One query is what the dealer ordered and the other is what the dealer told us he sold. How do I make a query that puts the same part no's from the two together and then give me the final quantity of what he has left of those parts? Is this possible thanks, Dustin it's called a union query select firstname from employees union select firstname from customers this would get rid of the duplicates "Dustin" <Dustin@discussions.microsoft.com> wrote in message news:87499EDB-A347-4F83-9953-C7D5977A0CB2@microsoft.com......

Updating Form VS Not Updating Form
I have a form "frmAllSchedule" which displays volunteer scheduling records in datasheet format. The query fetching the data has a parameter to fetch the date from a secondary form ([Forms].[frmProjForm].[txtDate]) and the code from the main form ([Forms].[frmMain].[cboActivity]). If "frmAllSchedule" is launched directly from the Database Window, the user can enter the date and code into prompts. Each line of "frmAllSchedule" is then updatable. If, however, form frmMain is launched first, then "frmProjForm" is then launched, and then a spec...