Searching by month

[Neilw]

This is a big problem for me. I have a report which is created by a query. However, i need the user to be able to specify which records are shown - monthly. E.g. the user will say whether she wants all of april's records or all of March's records etc.

However, the field i need to use for this search is a date field that shows a dd/mmm/yy format. Is it possible to use this field to search just by month and not that date or year?

Hope that makes sense,

Thanks.

 

[raskew]

Try creating a new query in Northwind, using the following. You'll be prompted for the month and year (it could be done using just month, but it's hard to envision a situation where you'd want April's records, regardless of year).

SELECT Orders.*
FROM Orders
WHERE (((Format(Month([OrderDate]),"00") & "/" & Year([OrderDate]))=[enter mm/yyyy]));


[This message has been edited by raskew (edited 04-22-2002).]

 

[Neilw]

thanks raskew thats great but i dont understand what you mean by "using northwind".

Do i just paste all that syntax into the criteria part of the query?

And by the way, you're right, it would be better to search by month and year!

 

[Treason]

Hi neil,

I am pretty sure northwind is an example database that comes with access. I think raskew wants you to try it out there see if it works, then apply it to your query.

good luck

 

[Neilw]

thanks, i'll try it and let you know

 

[Neilw]

Ok i looked in Access and i cant find northwind anywhere. Can anyone help me?

Do i need to download it from somewhere? I looked on microsoft.com but i couldn't find it.

Thanks in advance,

Neil.

 

[Neilw]

Oh wow i got it working, i just had to paste the code from the brackets onwards into the criteria section.

Thanks a lot everyone,

Neil.

 

[Rich]

It's either in the Samples folder or MS Download site

 

[Neilw]

Nope sorry it doesn't work.

Raskew, i've put your suggested code in but there are 2 problems.

Firstly, it asks twice for you to put the date in. Is there a way to have it only ask once?

And secondly, It doesn't actually do what it's told. I put in 3 records, only one of which should have shown up after i asked for 04/2002. Whats wrong?????

I really appreciate any help.

 

[Neilw]

Oh no dont worry i got it working!

I stupidly didn't realise to change OrderDate to the actual field name!