Newbie: Date Criteria

[mbtedrick]

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

 

[xenou]

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.

 

[mbtedrick]

Here is the SQL for the Make query:

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

 

[xenou]

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]

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!!

 

[xenou]

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?

 

[mbtedrick]

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]

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]

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