Newbie: Date Criteria

mbtedrick

New Member
Joined
Nov 27, 2018
Messages
14
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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:

mbtedrick

New Member
Joined
Nov 27, 2018
Messages
14
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 a moderator:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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?
 

mbtedrick

New Member
Joined
Nov 27, 2018
Messages
14

ADVERTISEMENT

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:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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:

mbtedrick

New Member
Joined
Nov 27, 2018
Messages
14

ADVERTISEMENT

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.



 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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])
 

mbtedrick

New Member
Joined
Nov 27, 2018
Messages
14
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
 

Forum statistics

Threads
1,141,002
Messages
5,703,663
Members
421,310
Latest member
CindaH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top