• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

update a table fileld from a query

Soory to do this but I need to get this problem sorted....

I think I may have 'created' the Db badly ... and need to go back to basics

how do I update a table field from a query (calculated field where field is blank)....and can a user then see this 'data' in the user form that is sourced from the data table field.

rgds
0
shaz0503
Asked:
shaz0503
  • 4
  • 3
  • 3
  • +1
3 Solutions
 
danishaniCommented:
You can try this:

Select [YourFieldName] From YourTableName
Where [YourFieldName] = Null;

HTH,
Daniel
0
 
shaz0503Author Commented:
Daniel

....where would I add this within the form field

rgds
0
 
danishaniCommented:
I thought you want to bound this query to a Form, where your particular Fieldname is Null?

HTH,
Daniel
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GRayLCommented:
Obviously, none of us are seeing it as you see it.  Try the question again please.
0
 
shaz0503Author Commented:
All

I will try to draw a diagram and post for you....may be easier to see what I am trying to achieve.....and I think the solution is probably simple, just done't know how to word the question effectively

back soon

rgds
0
 
OP_ZaharinCommented:
"how do I update a table field from a query (calculated field where field is blank)"
- create a query: from the Access menu > choose Create > Query Design > Select the tablename and click Add button > click Close button > right click on the Query windows (or from the bottom right side of Access) choose SQL View. the SQL editor will be displayed. then put your update command there. i'm assuming you want to update the null field to 0 or any value, then key-in this in the Query view:

UPDATE tablename SET columname = 0 WHERE columname IS NULL

- next save the query and give a name to it. to run you can either double click the Query name from the left side pane or choose Design from the Access menu and click on Run(!) button.
0
 
shaz0503Author Commented:
Thanks

I need to update the [SoE review date] field in [tblStatementDetails] - only if Null with the following calculation....

IIf(IsNull([_qrytblStatementDetails]![SoE review date]),DateAdd("m",2,[_qrytblStatementDetails]![Last Start Date]),[_qrytblStatementDetails]![SoE review date])

and I can't seem to get you solution to work....
0
 
OP_ZaharinCommented:
- do your columnname have spaces? it is not a good design to have spaces for columnname. you should name it using underscore (_) instead. because of the spaces, you can't make it work using the query window.
0
 
GRayLCommented:
Don't agree:  ThisTableName has no spaces and is easier to type than This_Table_Name and just as easy to read  -  believe me I made three corrections.  Just uppercase each word - no spaces, no underscores - the debugging world will love you for it.
0
 
OP_ZaharinCommented:
- cool GRayL! i learn new thing everyday. changing the spaces columname to uppercase does make it work on query window :)

- see if this works shaz:

UPDATE [_QRYTBLSTATEMENTDETAILS] SET [SOE REVIEW DATE] =   DateAdd("m",2,[LAST START DATE]) WHERE [SOE REVIEW DATE] IS NULL

Open in new window

0
 
GRayLCommented:
In Google type - Access naming conventions - this is the URL from the first hit:

http://www.acc-technology.com/namconv.htm

worth a read!
0
 
shaz0503Author Commented:
Sorry for the delay in responding.

I have moved away from this but will keep your comments for future reference.

thank you all for your assistance
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now