Results 1 to 14 of 14

Thread: Dates (2000)

  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates (2000)

    Looking for the value to place in the column of a combo box for displaying all the records that have a CreatDate field of from today in the last seven days, I thought I could do it date() or date()-1, etc but it doesn't work out like this. I have tried >=date()-2 but I get a syntax error.

    Any ideas?
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates (2000)

    I'm confused by your description. Where are you entering the expression?

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (2000)

    I have a Combo box With the Following

    Column 0 Column 1
    =date() Todays Viewing
    =date()-1 Yesterdays Viewings
    >=date()-2 Last 2 Days
    >=Date()-7 Last 7 Days

    User select which they wish to see and then press a command button with the onclick event of: -

    Dim stDocName As String
    stDocName = "frmViewingsSheet"

    DoCmd.OpenForm stDocName, , , WhereCondition:="CreatDate" & Me!cmbDate.Column(0)

    Luke
    Best Regards,

    Luke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates (2000)

    The syntax seems OK, it works in an example I created. What is the exact text of the error message, and on which line does it occur?

    BTW, if you use commas to indicate the position of an argument, you don't need to name it:

    DoCmd.OpenForm stDocName, , , "CreatDate" & Me!cmbDate

    or

    DoCmd.OpenForm FormName:=stDocName, WhereCondition:="CreatDate" & Me!cmbDate

  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (2000)

    Have been able to sort it so the sytax error does not appear. However If I alter the date on the pc to 18/05 and create a record in viewings and view the form selecting Today and click the button I can see the record. Then if I alter the date to 19/05 and create another record and tell I want to see yesterday's viewing it returns that no viewings are there. Sam e for 2days ago and 7 days ago.

    I have check the tblViewing to see if the creatdate is being etered and it is.

    Luke
    Best Regards,

    Luke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates (2000)

    Is CreatDate a date/time field, or perhaps a text field?

    (Perhaps you can post a stripped down copy of the database)

  7. #7
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (2000)

    I have check the CreatDate field in the tblViewings and it is a date field.

    I have attached the stripped down DB and create some entries intbl viewings for 17/5 and 16/5 and 14/5.

    If you open frmProperty and look at the top right hand corner you have a drop down combo and a command button. This is what I am using at the date control.
    Best Regards,

    Luke

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates (2000)

    Your form frmViewingsSheet is based on the query qryViewingTodaySalesStaff. This query has Date() as criteria for the CreatDate field, so it will never return records for other dates than today. You should remove the Date() criteria, or if you need this query for other purposes, make a duplicate, remove the Date() criteria from the duplicate and use that as record source for frmViewingsSheet.

  9. #9
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (2000)

    DOH!
    Best Regards,

    Luke

  10. #10
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (2000)

    I have a issue with the combo box now after tring to make it three columns.

    In the column two I want to the following value to be passed to the query but not sure how it's best to enter it.

    The combo have is as follows
    Colum1 Column2 Column3
    =date() date() Today
    =date()-1 date()-1 Yesterday
    >=date()-2 >=date()-2 and <=date() Last 2 Days
    >=date()-7 >=date()-7 and <=date() Last 7 Days

    On click event is the same but reads colum(1) for cmbdate instead of column(0)
    Best Regards,

    Luke

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates (2000)

    I don't understand - what do you need the extra column for? Why not simply modify the first column?

  12. #12
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (2000)

    Hans,

    Sorry I have two command button, one is based on a field called CreatDate and the next one which uses column(1) uses APPMTDate field.
    Best Regards,

    Luke

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates (2000)

    The items in the middle column should be
    <pre>=Date()
    =Date()-1
    Between Date()-2 And Date()
    Between Date()-7 And Date()
    </pre>

    including the space in front of the latter two.

  14. #14
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (2000)

    Thanks will give it a whirl at work tomorrow.
    Best Regards,

    Luke

Posting Permissions

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