Results 1 to 9 of 9

Thread: Newbie: Date Criteria

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Newbie: Date Criteria

    Hi,

    I am creating a Make Query to retrieve specific data and categorize a date into Month, Quarter, Year to be used for other reports. I am using a form to identify the year and ThruMonth of the data needed.

    Tables in Make Query: Summary Master, Direct Time Master
    Fields In Make Query: JobComponent, DepartmentTeam, Year: DatePart("yyyy",[Date]), Quarter:DatePart("q",[Date]), Month:DatePart("m",[Date]), Hours, Product Code

    The data in the database is current with transaction through today, however, there are reports we run that would require data thru a particular month. For example, we have data through December but working on reports thru October. So in my form for Year we would have 2018 and for ThruMonth we would have 10.

    When I have [Forms]![MainMenu]![txtYear] in the criteria under Year. It works correctly and pulls all 2018 hours

    When I added <=[Forms]![MainMenu]![txtThruMonth ]to the criteria under Month. It does not work. I get nothing.

    Any help that can be provided would be greatly appreciated. I can't seem to find anything on-line like this. Would it be better to pull the actual date into the table and use a combined criteria? I don't know what that would be but it makes sense.

    Ellen

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,093
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Newbie: Date Criteria

    Code:
    When I have [Forms]![MainMenu]![txtYear] in the criteria under Year. It works correctly and pulls all 2018 hours
    
    When I added <=[Forms]![MainMenu]![txtThruMonth ]to the criteria under Month. It does not work. I get nothing.
    Can you post the query text (from SQL View)

    Also there appears to be a space after [TxtTruMonth ] so make sure that's okay - assuming its just something in the board HTML and not in your actual query or form, but otherwise that would be a possible problem.
    Last edited by xenou; Dec 5th, 2018 at 03:27 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    New Member
    Join Date
    Nov 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Newbie: Date Criteria

    Here is the SQL for the Make query:
    Code:
    SELECT 
    	[Summary-Master].[Job Component], 
    	[Direct Time-Master].[Department Team], 
    	DatePart("yyyy",[Date]) AS [Year], 
    	DatePart("q",[Date]) AS Quarter, 
    	DatePart('m',[Date]) AS [Month], 
    	Sum([Direct Time-Master].Hours) AS Hours, 
    	[Summary-Master].[Product Code], 
    	Format([Date],"mm yyyy") AS [Month Year], 
    	Format([Date],"q yyyy") AS [Quarter Year] 
    INTO [TB-Direct Time-Master-22sq Hours by YEAR-ExEdit/22P]
    FROM 
    	[Summary-Master] 
    	INNER JOIN [Direct Time-Master] 
    	ON [Summary-Master].[Job Component] = [Direct Time-Master].[Job Component]
    GROUP BY 
    	[Summary-Master].[Job Component], 
    	[Direct Time-Master].[Department Team], 
    	DatePart("yyyy",[Date]), 
    	DatePart("q",[Date]), 
    	DatePart('m',[Date]), 
    	[Summary-Master].[Product Code], 
    	Format([Date],"mm yyyy"), 
    	Format([Date],"q yyyy")
    HAVING 
    	(
    	((DatePart("yyyy",[Date]))=[Forms]![MainMenu]![txtYear]) 
    	AND 
    	((DatePart('m',[Date]))=[Forms]![MainMenu]![txtThruMonth]) 
    	AND 
    	((Sum([Direct Time-Master].Hours))<>0)
    	);
    Thank you for helping!!
    Ellen
    Last edited by xenou; Dec 6th, 2018 at 09:39 AM. Reason: format SQL

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,093
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Newbie: Date Criteria

    What's txtThruMonth and txtYear? Are those just unbound text fields you can type a number into? Or some other kind of input? What kinds of values do you provide for Year and Month in those controls?

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    New Member
    Join Date
    Nov 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Newbie: Date Criteria

    Yes, the txtYear and txtThruMonth are unbound fields on a form you input 4 character year (ex 2018) and 1 or 2 character month (ex 8, 10)

    I actually want the Thrumonth to return all month less than the month entered. Or year to date thru the specified month.

    I noticed a " was missing so I updated that and got records returned however when I just have the criteria set as [Forms]![MainMenu]![txtThruMonth] it pulls only the month entered (which is correct with that statement) and when I have <=[Forms]![MainMenu]![txtThruMonth] it includes all months even those greater than the number entered.

    I'm stumped!!


    Last edited by mbtedrick; Dec 6th, 2018 at 11:20 AM.

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,093
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Newbie: Date Criteria

    when I just have the criteria set as [Forms]![MainMenu]![txtThruMonth] it pulls only the month entered (which is correct with that statement)
    What is the comparison here? Was it = ?

    and when I have <=[Forms]![MainMenu]![txtThruMonth] it includes all months even those greater than the number entered.
    Couldn't say its necessary but are are probably comparing Text to Number. I would always make sure that the datatypes are explicit and the same when doing comparisons. The text field should be converted to an integer numeric, which is what datepart returns.

    What types of years and dates are you putting in that textbox?
    Last edited by xenou; Dec 6th, 2018 at 11:47 AM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    New Member
    Join Date
    Nov 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Newbie: Date Criteria

    See responses below:

    when I just have the criteria set as [Forms]![MainMenu]![txtThruMonth] it pulls only the month entered (which is correct with that statement)


    What is the comparison here? Was it = ?

    I put it in the criteria box with nothing in front of it, I guess = is understood?

    and when I have <=[Forms]![MainMenu]![txtThruMonth] it includes all months even those greater than the number entered.


    Couldn't say its necessary but are are probably comparing Text to Number. I would always make sure that the datatypes are explicit and the same when doing comparisons. The text field should be converted to an integer numeric, which is what datepart returns.

    What types of years and dates are you putting in that textbox?

    In year "2018", in ThruMonth "8" as an example.

    So are you saying I should change the format on the For Undbound txt boxes? I haven't messed with format changes and the choices don't seem to be what I would need. UGH.




  8. #8
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,093
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Newbie: Date Criteria

    You don't need to change the textboxes but you need to treat the values as text. So for instance (because it's text and you really need a number):

    <=CLng([Forms]![MainMenu]![txtThruMonth])

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  9. #9
    New Member
    Join Date
    Nov 2018
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Newbie: Date Criteria

    I am so thankful for you and your knowledge. I only wish I knew the best way to learn what I need to be as proficient as you.

    I really appreciate you and your willingness to help.

    Thank you,
    Ellen

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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