Criteria by DatePart?

[Aspire]

Hello All,

I have an open pop-up form where I enter a date in this field: [forms]![frmTurnAroundTimeExcel]![txtWeekEnding]. I would like to use that date in my query to limit my results to the month and year that is in txtWeekEnding. So if I enter 9/13/2013, I would like all results from the month of September 2013.

Grabbing at straws, I came up with this as the criteria, but as you might know, it didn't work - but this is the idea:
=DatePart("m",[forms]![frmturnaroundtimeexcel]![txtweekending]) And DatePart("yyyy",[forms]![frmturnaroundtimeexcel]![txtweekending])

Any help would be greatly appreciated!

Scott

 

[Uncle Gizmo]

It should look something like this:-

SELECT QRYX.mthTrue, QRYX.yrTrue
FROM (SELECT ID, Ward, Room, Condition, fDate, DatePart("m",[Forms]![Form1]![txtDate]) AS frmMth, DatePart("m",[fDate]) AS qryMth, [frmMth]=[qryMth] AS mthTrue, DatePart("yyyy",[Forms]![Form1]![txtDate]) AS frmYr, DatePart("yyyy",[fDate]) AS qryYr, [frmYr]=[qryYr] AS yrTrue FROM tblPatientInfo)  AS QRYX
WHERE (((QRYX.mthTrue)=True) AND ((QRYX.yrTrue)=True));

Please use CODE Tags when posting code...

 

[Aspire]

Thank you, Uncle Gizmo. I will try to fit your code in with my query and let everyone know how it goes. I truly appreciate your time and skill. Off topic, I could not find any way to respond to private messages on this forum.

 

[spikepl]

Actually, if you type 9/*/2013, that exploits a number of weird Access features but ought to give you what you want, when used as criterion in a date column.

 

[Uncle Gizmo]

Thank you, Uncle Gizmo. I will try to fit your code in with my query and let everyone know how it goes. I truly appreciate your time and skill. Off topic, I could not find any way to respond to private messages on this forum.

Hi Aspire,
Your thread disappeared, so I assumed you had deleted it. However I have been informed that it has happened before to other threads, so I wonder, can you recall anything strange happening? ... With a view to seeing if there is a forum issue that needs attention?

 

[Uncle Gizmo]

I could not find any way to respond to private messages on this forum.

The messaging system is .... Um ..... Shall we say "Not Good!"

 

[Aspire]

Hi Uncle,
I had no problems with the original post or need to refresh, post twice, etc. It let me know that the question would not be visible until moderated. Perhaps in the moderation process there is a sticking point.

 

[vbaInet]

Field: Format([FieldName], "mmyy")
Show: Untick
Criteria: Format([forms]![frmturnaroundtimeexcel]![txtweekending], "mmyy")

 

[Aspire]

Hi Spike,
That is a clever idea, thank you; but, unfortunately in my case the date isn't fixed. They enter a date in a separate form, and it is from that form that I need to pull the date and enter into the query criteria. Using Month([..]![..]![..]) or as a DatePart returned null values.
I am finding more and more that creative criteria is not generally accepted by Access.

 

[Aspire]

Field: Format([FieldName], "mmyy")
Show: Untick
Criteria: Format([forms]![frmturnaroundtimeexcel]![txtweekending], "mmyy")

This is it! You've done it, and now I've got it! Thank you vbaInet!

 

[vbaInet]

Glad we all could help!

 

[Uncle Gizmo]

Field: Format([FieldName], "mmyy")
Show: Untick
Criteria: Format([forms]![frmturnaroundtimeexcel]![txtweekending], "mmyy")

That's very clever! I like!