Replace

[steve111]

Hi ,

I have a table with parts that can be purchased these parts will have a purchase order against them
there could be 1 to 50 parts per order
what I want to do is create a query that will either add "cancelled" in the table ( I can add a field in the table called cancelled if needed )or remove the purchase order number from the part and put the word " cancelled " in it place then I still have a record of the parts cancelled

how to we create a query to do this I will be prompted for the P/O NUMBER


thanks for you help
steve

 

[CJ_London]

something like

UPDATE myTable Set PONumber='Cancelled' WHERE PONumber=[Enter PONumber]

 

[steve111]

hi ,

am I correct in thinking this is just in a normal query and the formula goes under the p/o number
also the formula will not go into the query there is a problem with the " where " part of the statement

steve

 

[CJ_London]

what have you written for your sql?

 

[steve111]

hi ,

this is the table I would like to either change the PO number to "cancelled " or create another field called cancelled. but I don't want the user to be able to change it back as he will not have access to the table

therefore I want to create a button on the form that he presses when he says cancel order and the query changes the table , the query should prompt him for the P/O number

I don't know how to create a query to replace the data or add data to a field

steve

 

[CJ_London]

I don't know how to create a query to replace the data or add data to a field

What is wrong with the update query I provided above? 'It doesn't work' is not an explanation

Again, please provide the sql or code you are using at the moment - I generally don't have time to look at uploaded db's without a detailed explanation of how the relevant form(s) are supposed to work and how to replicate the problem

 

[steve111]

hi ,

it is now working it was like you had but I had to put brackets around the fields which I did not know

UPDATE [supplier orders] SET [PO Number] = 'cancelled'
WHERE [PO Number]=[Enter PONumber];

Really appreciate your time to help me on many of my problems
you are very knowledgeable

thanks
steve

 

[CJ_London]

personally I would avoid spaces in field and table names, they cause all sorts of issues which you will find as you progress.

VBA doesn't like them - for example if you have a control called PO Number it will translate it to PO_Number - then when you try to find 'PO Number, it won't find it