Tech Support Guy banner
Status
Not open for further replies.
1 - 17 of 17 Posts

·
Registered
Joined
·
424 Posts
Discussion Starter · #1 ·
I am building a database in msaccess and I would like to build a query that has a criteria that will let me enter only the month and year (i.e. jan-05) and it will find all the records that have fit that criteria and ignore the day. Could someone tell me how to do this?
 

·
Registered
Joined
·
36 Posts
To drafter:
Here is one simple solution, just goto the query builder and place your
table in the query and the fields that are appropiate. Under the date
field in the criteria area insert this code: Between #4/1/97# And #7/1/97#
The end result will be you will only get records that are between those dates.
Hope this helps you also check out your access date for proper
syntax for days, months, years, ect. Flixx.
 

·
Registered
Joined
·
2,556 Posts
Create the query with the fields you require, then add 2 additional fields for your month and Year as follows:

Year:Year([YourDateField])

Month:Month(yourDateField)

This along with simple parameters will let you specify the month number and year.

Use Criteria:

[Enter 4 digit Year]

[Enter Month]

This is just a simple way of doing it, if your just doing it for your own needs than that should be fine, if you have users then I can suggest better ways.

Ziggy
 

·
Registered
Joined
·
36 Posts
To drafter:
I attached a simple database called dbx.mdb that only has 1 table
named cust and under the query you will find 2 queries. The query
named qry_cust1 has my first example and the second uses the
mon year ie... (jan, 05). Hope this helps now, sorry about not
posting a attachment as example, i sometimes forget users
are not all that experienced, my fua paw. I appoligize. Flixx
FYI this files is a zip file called dbx1.zip
 

Attachments

·
Registered
Joined
·
424 Posts
Discussion Starter · #6 ·
Thank you very much for the database sample. Is it possible for me to enter a date in a text box and it will return a total in another text box for all in voices for a given month and year.

thanks
 

·
Registered
Joined
·
2,556 Posts
Just remove the criteria from the expression so you can see the results, then you will understand what value to put in for the parameter box that pops up.

by using the square brackets (in criteria) you are forcing the query to ask you for a value. the text between the brackets don't do anything.
 

·
Registered
Joined
·
2,556 Posts
you can do the same with Flixx's suggestion, just remove the criteria and look at the results. try modifying his expression perhaps by removing the comma. like

monnam: Format([cust_dat],'mmm') & Format([cust_dat],'yy')

then you won't have to guess about putting in a space after the comma
 

·
Registered
Joined
·
424 Posts
Discussion Starter · #11 ·
Hey guys, I think I may of went down the wrong road. My intention was to insert this information into a form, well that doesn't seem to work. So I guess I have another question for you. Can I build a text box where I enter month/year and it will find all the invoices for that month and print the total in another text box. I guess I should of thought this out better.

thanx again
 

·
Registered
Joined
·
2,556 Posts
Ask and you will receive :)

That would have been my next suggestion but I did not know how far along your were.

Create the control and name lets say txtMonth, now instead of using the Criteria previously suggested replace it with the following:

[Forms]![frmViewInvoices]![txtMonth]

Create a new Unbound Form and add a control name it txtMonth and name the Form frmViewInvoices

Add a button the the new form that will open your form that you would like to restrict the records on.

That form (or report if you want) must use the query as its control source.

Ok..I modified Flixx's DB to illustrate what I mean, I could have done my own but it was there so you could relate to it better.
 

Attachments

·
Registered
Joined
·
36 Posts
To Drafter:
Sorry about not getting back earlier, but for some reason all day sunday
and monday the internet connection to this web forum was down. But
i went ahead and created your form and query and macro to what you
requested. It is in a new zip file called dbxv1.zip and the database file
is called dbxv1.mdb. If you notice on the subform in the name fields there
are couple of dummy fields that hold you record set data. And on the main
form just to right of the Current Invoice Count is another dummy field all
that is required is to hide it under one of the fields and set the border to none. and it will not show. I left it visible so it would be easy for you to
follow what i did to create this form. Anyway I hope this helps. Flixx.
 

Attachments

·
Registered
Joined
·
424 Posts
Discussion Starter · #14 ·
Where does a person learn MS access. I have the book that comes with the program but 75% of what I want to do is not in there. I learnt the basics of the program from the book only. Every time I need to know something a little advanced I have to come to this web site. I really want to learn ms access, can you suggest a good place or book for learning. Where did you learn from?
 

·
Registered
Joined
·
2,556 Posts
I just noticed my criteria in the comments of the DB might be confusing it should be:

Like([Forms]![Form1]![cobDateSel]) & "*"

not

Like([Forms]![Form1]![cobDateSel]) _ "*"

in my comments on the form...the query will be fine
 
1 - 17 of 17 Posts
Status
Not open for further replies.
Top