Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    append queries and expressions used

    i am trying to update a column in a table, that is currently blank, with today date. i created an append query to try and populate the [table]![todaysdate] field with todays date. i selected the DATE() function as the "update to" value and the criteria value "is null". when i try and run this it gives me the mesage "function isn't available in expressions in query expression DATE()". Can anyone tell me what i am missing here?

    thanks
    mike [img]/w3timages/icons/yikes.gif[/img]

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: append queries and expressions used

    If you are adding the date to existing records you need to use an update query. It works fine for me.

    An append query will try to append new records. Each record would have only today's date in it. Access might have problems knowing how many such records you want [img]/w3timages/icons/grin.gif[/img]
    David Grugeon
    Brisbane Australia

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append queries and expressions used

    I agree with grugeon.
    Can you give more details on what you want to do?
    Do you want to append records to your table or do you want to update existing records in your table?

    If you get error messages please post the exact error message and the SQL that causes the error.

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append queries and expressions used

    Sorry gents, it is an update query, i used the expression builder to create this:

    IIf([MRComplete]![Todays Date] Is Null,Date())

    i want to update a column in 34 rows that already exist. i want to only update when the value of the cell is null. i have ACCESS97 and am running on win95....this process will then be placed on a platform running WIN NT.

    thanks

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: append queries and expressions used

    Hi,
    All you need in your update query is the field you want to update, set the Update To to Date() and then enter Is Null in the criteria field. This will then update every one of the date fields that is null to today's date.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append queries and expressions used

    i tried that and got the same messsage:

    function isn't available in expressions in query expression DATE()

    for some reason it is like i can't use this date() function in this manner, yet if i use NOW() it works fine except it give me the time as well.

    mike

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append queries and expressions used

    try now() - Time()
    it has worked for me in the past
    JerryC

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: append queries and expressions used

    Mike,
    Can you post the SQL from your query?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append queries and expressions used

    i get the same message listing these two functions as not available.

    thanks.

  10. #10
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append queries and expressions used

    here it is(i also have some code to change the case for other fields):

    UPDATE MRComplete SET MRComplete.[Todays Date] = Date(), MRComplete.Developer = IIf([MRComplete]![Developer] Is Null,"NOT ASSIGNED",UCase([MRComplete]![Developer])), MRComplete.Creator = IIf([MRComplete]![Creator] Is Null,"NOT ASSIGNED",UCase([MRComplete]![Creator])), MRComplete.System = UCase([MRComplete]![System])
    WHERE (((MRComplete.[Todays Date]) Is Null));

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: append queries and expressions used

    Mike,
    It sounds to me as though your database may have a missing reference (that tends to cause a lot of function errors).
    If you press Alt-f11 and then choose Tools-References is there anything listed as Missing?
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append queries and expressions used

    tried ALT F11 (nothing seemed to happen though) selected TOOLS and i have no selection called REFERENCE. At what point do i hit ALT F11, while in the tables tab or within the query itself?
    thanks

  13. #13
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append queries and expressions used

    try control G
    then tools
    then references
    JerryC

  14. #14
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append queries and expressions used

    hey one step closer, the only item listed as missing were
    outlook 98 type library
    microsoft scriptlet library

    there are quite a lot of items not checked off either

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: append queries and expressions used

    If you uncheck those items, does the query then run?
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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