Hey all, this is my first post here. Im am an access n00b.

I need some way of bringing up all the infomation in a certain query for a single month. I can do this for one specific month by doing 'Between 01/01/04 And 30/01/04' but this will only work for that month. Having 12 querries, one for each month seems a bit exsesive. I would like it so the user could enter just a month and year (eg. 01/04) instead of a full date. I know i can do [please enter a date] but the user has to enter the specific date.

I plan on building a report on this query.

Thanks much.

Hi, and welcome to Utter Access!

Try placing the following in the Where clause of your query:
WHERE Month(YourDateField) = [Enter Month Number] and Year(YourDateField) = [Enter Year]

You'll have to replace YourDateField with the actual name of the date field from your table. This will bring up two parameter boxes when the query/report is run. The use will be able to enter a month and year, and the query will return the data for that month and year.

Let me know if you need more help.
-AJ

P.S. It may help if you fill out the "Location" field in your user profile. It will help others answer your questions, especially when dates are concerned, as regional settings for dates affect Access databases greatly.

Cheers much AJS.

Tis greatly appreciated.

Will change my Location now.

Thanks again

Edited by: Jammin1984 on Tue Nov 9 8:34:37 EST 2004.

You're welcome. Glad to help.

-AJ

AJ, im getting the "contains invalid syntax" error with:

WHERE Month(RentalDate) = [Enter Month Number] And Year(RentalDate) = [Enter Year]

This query is using more than one table if thats making any difference.

Thanks again

Edited by: Jammin1984 on Tue Nov 9 8:40:17 EST 2004.

Can you post the SQL of your Query? It will help me identify the problem.

-AJ

SELECT tblCustomers.Forname, tblCustomers.Surname, tblEquipment.EquipmentType, tblRental.RentalDate, tblRental.RentalTime, tblEquipment.PricePerHour
FROM (tblCustomers INNER JOIN tblRental ON tblCustomers.CustomerID = tblRental.CustomerID) INNER JOIN (tblEquipment INNER JOIN tblRentalOrder ON tblEquipment.EquipID = tblRentalOrder.EquipID) ON tblRental.RentalID = tblRentalOrder.RentalID
WHERE (((tblRental.RentalDate)=[Enter Date]));

I have just put my old critera in [Enter Date]

Thanks again AJ.

As far as I can tell, the following should work

SELECT tblCustomers.Forname, tblCustomers.Surname, tblEquipment.EquipmentType, tblRental.RentalDate, tblRental.RentalTime, tblEquipment.PricePerHour
FROM (tblCustomers INNER JOIN tblRental ON tblCustomers.CustomerID = tblRental.CustomerID) INNER JOIN (tblEquipment INNER JOIN tblRentalOrder ON tblEquipment.EquipID = tblRentalOrder.EquipID) ON tblRental.RentalID = tblRentalOrder.RentalID
WHERE ((Month(tblRental.RentalDate) = [Enter Month Number]) And (Year(tblRental.RentalDate) = [Enter Year]))

-AJ

I dont get an "incorect syntax", the query runs but does not return anything.

Im putting 05 for the month and 2004 for the year, i have some records in tblRentals 12/05/2004

Any suggestions?

Thanks again AJ all this help is apprecitaed

Try just 5 for month

Nice one!!!

Gonna put [Enter month (with no 0`s) e.g. april = 4]

Thanks AJ and ScottGem, tis greatly appreciated.

Glad to Assist.

You might want to consider creating a form instead of parameters. This way you can make sure they enter a numeric values. Instead of typing [Enter month] as the criteria type:

=Forms!formname!controlname

Right, thanks for the advice, i will have a go and report back later.

Thanks again

Hi -

No need for all those gyrations. If you have a valid date/time data type date field,

try this in the criteria cell:

Between DateValue([enter mm/yyyy]) And DateAdd("m",1,DateValue([enter mm/yyyy]))-1

It'll bring up all the records in the specified month. As an example, copy/paste this to a new

query in Northwind. Run the query and, when prompted, enter 12/1995. The query will return

all Orders from December 1995.


HTH - Bob

You could also do this,

Replace your where clause with;

HAVING (((Format([report_date],'mm/yyyy')) Like [month (for November type '11' etc)] & "/2004"));


The year 2004 is hard coded here but its easy enough to manually enter it like the month.

cheers!

Hmmm, the only time you should use the HAVING clause is if you are trying to restrict a multi row function (i.e. an aggregated value) Having restricts AFTER the results are returned, WHERE restricts the results being returned and is therefore more efficient.
Just my tuppence ha'penny
Jim