Date values for this month

[Ron_dK]

This thing is driving me nuts and is possibly something simple, but can't figure it out.

I have a query which gives me data in three fields : Vendorname, vendorlocation and surveydate
The first two are text fields, the last is a date field.
What I want is those names and locations, where the surveydate is in THIS month only. So today's month (5) is May and I want all entries for this month only.

How would I do that .

 

[Rich]

Isn't it just Month(Date()) ?

 

[Ron_dK]

Thanks Rich, but this gives me the month number only.

What I have done sofar is the following :

Query :
SELECT [TbInput Query].[Name], [TbInput Query].[Location], [TbInput Query].[Survey-conducted], Month(Date()) AS ActMaand, (DatePart("m",[Survey-conducted])) AS Surveymaand, [Actmaand]=[Surveymaand] AS Thismonth
FROM [TbInput Query]
WHERE ((([TbInput Query].[Survey-conducted]) Like "*/*/2007"))
ORDER BY [TbInput Query].[Survey-conducted];

This gives me values for Thismonth of : -1 or 0
The -1 value are those meeting this month date and are the ones I need.
I putted -1 as the criteria in the Thismonth query field, but the output is nothing.

Why ?

 

[neileg]

Try using this as your WHERE clause

 WHERE Format([Survey-conducted],"MMM/YYYY")=Format(Date,"MMM/YYYY")

 

[Ron_dK]

Thanks Neil, that works.
The only but here is that I need to enter a date in the "enter parameter value" and that's what I wanted to avoid.
Would there be a way to have an" immediate" output giving this months surveys only.

 

[Rich]

Try using this as your WHERE clause

 WHERE Format([Survey-conducted],"MMM/YYYY")=Format(Date,"MMM/YYYY")

Shouldn't it be Format(Date(),"MMM/YYYY") ?

 

[neileg]

Oops! Missed that

 

[Heywood43]

Thanks Neil, that works.
The only but here is that I need to enter a date in the "enter parameter value" and that's what I wanted to avoid.
Would there be a way to have an" immediate" output giving this months surveys only.

I have used this to get Month To Date information:

Between month(now()) & "/1/" & year(now()) And now()))

Just place that in your date condition.

There is only one problem I have with this, maybe someone else on this forum can help me with. On days like today (the first of the month) the above will return 5/1/2007 to 5/1/2007. On the first of the month I really need the previous month in full.

I was about to write a function to check todays date. If it find the first of the month it would return the first date of the previous month. If not it would give the current months first day.

I would take that function and place it in the first parameter in the between condition. Before I go ahead and do that does anyone know of a better way?

Thank you,

Heywood

 

[Ron_dK]

Shouldn't it be Format(Date(),"MMM/YYYY") ?

You're a genius Rich. Exactly what I was looking for.

Thanks all ,
Ron