Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: Date on a Form Control

    Hi,

    I added a (Text Box) control in my main form and in its Control Source property I put Date() - 1 hoping that it will display the date for the previous day when the form is opened/loaded but currently it is only displaying #NAME?. What is the proper way of programmatically displaying the current/previous date in a form? Thank you.
    I am using MS Access 97.

    Thank you!

    PS: To pootleflump, if you get to read this sorry havent got back to you. I have the access code for my previous question stripped to the relevant parts but I am unsure of whether I should post it in this forum because the code is proprietary and I am not too sure if I have removed everything that has to be removed. i am still looking for a solution to that problem though.
    have a great week!

    ,
    g11DB

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello g11DB!

    In Control source put =Date()-1
    or
    in Default vaue put Date()-1

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    Hi MStef-ZG,

    Thanks for your reply. I already did the first one and I get #Name? displayed. I just tried your second suggestion and I got the same thing.


    cheers,
    g11DB

  4. #4
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Check FORMAT properties for your field.
    Put SHORT DATE in FORMAT.

  5. #5
    Join Date
    Jul 2006
    Posts
    111
    i have also tried this already :-(

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You will want it to be:

    =DateAdd("d",-1,Date())

    One question though, do you want the control to default to this and then let the user change it? If so then you should set the Default value equal to the above. Then for new records Access will pop in yesterday's date.

  7. #7
    Join Date
    Jul 2006
    Posts
    111
    Hi DCKunckle,

    Thanks. I tried it but it also didn't work. The error was:

    The function you entered can't be used in this expression.

    *You may have used a DoEvents, LBound, UBound, Spc, or Tab function in an expression.
    *You may have used an SQL aggregate function, such as Count, in a design grid or in a calculated control or field.

    I removed the preceding equal sign and it didn't have the error although it is still displaying #Name?.

    As for your question, no i don't want to change it after the (main) form was launched. I just want the (main) form to get the current date everytime it is launched/loaded and display it inside an text box control within the (main)form.

    Thanks again, g11DB

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by g11DB
    PS: To pootleflump, if you get to read this sorry havent got back to you. I have the access code for my previous question stripped to the relevant parts but I am unsure of whether I should post it in this forum because the code is proprietary and I am not too sure if I have removed everything that has to be removed. i am still looking for a solution to that problem though.
    Super. Please could you post me a link. I... um.... need my memory refreshing
    BTW - the = from the previous posts was critical. Anyone else thinking dodgy reference?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Certainly something strange, pootle flump! I just put a text box on a form and set the Control Source to =Date()-1 and it works like a charm, no formatting or other tweeking necessary!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Date

    I might add another text box control and try it. Perhaps Conditional Formatting or something else is the problem.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    g11DB

    Your first email doesn't mention the equals sign (=). I wonder if you tried with the equal sign (sorry if I'm stating the obvious). Without = you will get the offending error.

    I tried =Date()-1 on Access 97 and Access 2003 an both work fine.

    Maybe you could post your form and we can test it.

    Chris

  12. #12
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    If adding the equal sign, which is needed in controlsource expressions, isn't the thing, I'll believe that either someone has been naughty and named something within this scope "Date", or there's some reference hassle (in VBE (Alt+Enter) check out Tools | References, and uncheck (and perhaps recheck?) one marked "missing", "invalid" or something to that effect)

    If it's a naughty naming thingie, there are a couple of other things you might try. For instance, in stead of Date, you could format your text control to show ordinary date, but use

    =Now()

    or

    =Int(Now())

    (which shouldn't need specific formatting)

    or you could assign it in the on load event in stead.

    Me!txtTheDate.Value = VBA.Date

    prefixing with library should work in the VBE
    Roy-Vidar

  13. #13
    Join Date
    Jul 2006
    Posts
    111
    thanks!

    Code:
    =Now()-1
    solved my problem... still wondering though why
    Code:
    =Date()-1
    wouldn't work hmmn...

  14. #14
    Join Date
    Jul 2006
    Posts
    111
    Hi PF,

    Oh sorry, I was referring to this one...

    http://www.dbforums.com/showthread.php?t=1605302

    My current solution is to create 10 different reports in the mdb database and just use each one for each iteration. I was initially thinking of just using that one report "template" and creating instances of it at runtime but I was getting these errors:

    Code:
    "Run-time error '2191':
    
    You can't set the Record Source property after printing has started.
    
    Try setting this property in the OnOpen event.
    Quote Originally Posted by pootle flump
    Super. Please could you post me a link. I... um.... need my memory refreshing
    BTW - the = from the previous posts was critical. Anyone else thinking dodgy reference?

  15. #15
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    "=Now()-1 solved my problem... still wondering though why =Date()-1 wouldn't work hmmn..."
    I told you why - or the most common/likely why - you have named something within the scope of this code "Date". Date is a reserved word, and should not be used as name of any objects within Access. When you do so, Access will fail to recognize the Date function.

    http://support.microsoft.com/kb/321266/EN-US/
    http://support.microsoft.com/kb/286335/
    Roy-Vidar

Posting Permissions

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