Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: update a field

  1. #1
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69

    Unanswered: update a field

    hello,
    I need to write an update query that will update a fild based on the calculation of a week data from another field. Meening:

    I need to update Col1 to the week of the year 2005


    Col 1 Col2
    0 01022005
    0 12142005
    0 12222005
    0 12292005

    Please help me, I do not know how to set this quey?

    Thank you so much

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    There is an inbuilt VBA functio which can generate a week number for you, i forget which & am not using a Microsoft platform today. The main problem with it is that it depends on the first (full?) week in the year as 1 [I seem to remember]. So provifing you aren't trying to use a week number mposed by say your organisations financial year which could be anything then that would do the job
    you don't need to store the information

    assuming that
    Col 1 Col2
    0 01022005 'equates to 02 Feb 2005
    0 12142005 'equates to 14 Dec 2005
    0 12222005 'equates to 22 Dec 2005
    0 12292005 'equates to 29 Dec 2005

    then just call the relevant function passing that date

    first off I'd try the help system looking for 'date functions'
    faling that I'd find a related funciton (such as datediff and follow the "see also" leads)

  3. #3
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69

    Update fields

    I never used the VBA nor VB. Is there a way to update the fields using sql? And if so how?

    Thank you.





    Quote Originally Posted by healdem
    There is an inbuilt VBA functio which can generate a week number for you, i forget which & am not using a Microsoft platform today. The main problem with it is that it depends on the first (full?) week in the year as 1 [I seem to remember]. So provifing you aren't trying to use a week number mposed by say your organisations financial year which could be anything then that would do the job
    you don't need to store the information

    assuming that
    Col 1 Col2
    0 01022005 'equates to 02 Feb 2005
    0 12142005 'equates to 14 Dec 2005
    0 12222005 'equates to 22 Dec 2005
    0 12292005 'equates to 29 Dec 2005

    then just call the relevant function passing that date

    first off I'd try the help system looking for 'date functions'
    faling that I'd find a related funciton (such as datediff and follow the "see also" leads)

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are using JET then you can use VBA functions in an update query.

    however I still would like you to think, why do you need to store the week number, when it is derived information and therefore unless you have a very good reason you shouldn't store derived information in the db.

  5. #5
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    I'm new to that db, I was asked to prepopulate temp table with all data for this year, and update a column in that table to the weeks. based on the loaded values from another field.
    If Col 1 is 0 and a Col2 is 12142005 'equates to 14 Dec 2005, then a Col1 has to be as a 3 ( for third week of the year)

    That how it is in this DB.

    Thank you.
    I just do not know how to do that

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    confused - you are claiming 14th Dec is the 3rd week of the year.... so your year starts 30th November ???

    what are the rules for calculating week# in your environment?

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ok, clearer when i see a calendar.
    week 1 for you is the week containing 1st December.

    what is your "week" definition Sun---Sat, Mon---Sun or ??

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    the week def is sun to sat
    the first week of 2005 in the calendar i have begins on
    sun - dec26 and ends on sat1 (which is jan1)

    then jan2 to jan8 is a week #2
    then jan9 to jan15 week # 3 and so on


    Thank you for the help

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    search Access help for ww
    ...format() seems to calculate week number exactly as you want it

    you will also need to fix that ugly text date
    see help on left$(), mid$(), right$()
    ...and maybe also dateserial() - but that's a matter of taste

    maybe it will work directly in JET-SQL, but if not, try a public function in a module and call that from your SQL.

    if you get stuck, post what you have.

    is this homework?
    (?? does anybody in the real world have database dates as text DDMMYYYY)

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    thank you I will try

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by izyrider
    (?? does anybody in the real world have database dates as text DDMMYYYY)

    izy
    Not here my friend ... If there is it's for legacy reasons or for that homework/class project ... Everyone I know uses the Date/Timestamp type ... [rhetorical] Why reinvent the wheel? Let alone the headache of validation of the dates ...

    HINT: I DID do this once with a flat file DB (text based) ...
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    If I need an Update query how to use this in it?
    =Format(Now(), "ww")

    I'm not a VBA person, please need help

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's not VBA, that's SQL.

    In general:

    UPDATE yourTable
    SET yourField = Format("ww", now())
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    was that transposition deliberate Teddy?
    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    No, my bad. I've been writing T-SQL all day. (convert(type, target)).

    Now that I'm slowed down a bit, I would probably recommend using FormatDateTime.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •