Results 1 to 5 of 5
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Date Part From a Combo Box

    Hi Guy's, i am trying populate a Datasheet form from a Combo box, the combo box is the name of a folder for Month Year

    The Combo Is July 2020

    In the form datasheet Record Source, i have added the following to be based on the Combo box

    PaidDate Criteria
    Like [Forms]![frmMainMenu]![cboOptions]



    So if there 2 records Paid
    13th July 2020
    26th July 2020

    I am trying to populate the Datasheet for any paid dates within the July 2020 (doesn't matter about the day number)

    I think i am barking up the wrong tree with my criteria coding in bold ?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Date/time is really a number and LIKE doesn't work well with numbers. Try:

    Format([PaidDate], "mmmm yyyy") = [Forms]![frmMainMenu]![cboOptions]
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    as I'm sure you must know, dates are stored as numbers - I presume your combo has a text value 'July 2020'

    so you need to convert your date to a string or your string to a number

    the former is easier but likely to be slower as it negates to use of indexing

    criteria would be

    format(paiddate,"mmmm yyyy")=[Forms]![frmMainMenu]![cboOptions]

    the other way would be to include a hidden column in your combo populated with the last day of the month

    paiddate between datevalue("1 " & [Forms]![frmMainMenu]![cboOptions]) and datevalue([Forms]![frmMainMenu]![cboOptions].column(1) & " " & [Forms]![frmMainMenu]![cboOptions])

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thanks guy's i maybe should have narrowed it down further, the combo box source is based on a folder name as follows
    When an invoice is paid, there is a folder generated and a file renamed and saved in that folder
    Then a record is edited with a paid date, the combo box is populated by a folder name

    in the meantime i will try suggestions you have very kindly advised or now you know the combo source, maybe you guy's would have 1 answer rather an options

    Code:
    Me.cboOptions.RowSource = ""Me.cboOptions.Requery
        Me.cboOptions.RowSource = "Value List"
        Me.cboOptions.RowSource = ""
        Set fs2 = CreateObject("Scripting.FileSystemObject")
        Set fl2 = fs2.getfolder("T:\MyFolder\PDF\") THIS PATH I HAVE CHANGED FOR POSTING 
        Set sf2 = fl2.SubFolders
        For Each fl2 In sf2
        iFld = iFld + 1
        Me.cboOptions.AddItem fl2.Name
        Next
    Me.cboOptions.Requery

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    now you know the combo source
    really need to know the combo values, not the source. Based on your first post they will be

    January 2020
    February 2020
    etc

    then the suggestion made should work


    format([PaidDate], "mmmm yyyy") = [Forms]![frmMainMenu]![cboOptions]



Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date Part
    By Rustin788 in forum Queries
    Replies: 1
    Last Post: 03-26-2014, 12:14 PM
  2. sql error when day part of date starts with a zero
    By OceanaPolynom in forum Programming
    Replies: 4
    Last Post: 04-01-2013, 02:36 PM
  3. Replies: 5
    Last Post: 08-07-2012, 03:35 PM
  4. Getting Just the Date part of Date/Time field
    By GaryElwood in forum Reports
    Replies: 7
    Last Post: 09-28-2011, 09:58 AM
  5. Replies: 7
    Last Post: 06-30-2011, 12:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums