Problems

[acess_uk]

Hi all,

I have created a database for work that allows use to monitor the amount of stolen/lost items found in the shop. Hi have created two tables these are:

Staff
Theft

STAFF
This database has a recorded of all members of staff currently working for use and there operator id code.

THEFT
This database holds information of the stolen item were found which staff member found it and other relevant information.

One the theft form I have created I have required the user to place there operator id into a field I have created a text box next to this field and I would like this text box todisplay the users name after the operator id has been entered.

One of the reports I would like off this database is a monthly report of all stolen/lost items for a selected month. The only way I have found to do this is by entering the fisrt day on that month 01/01/02 and the last 31/01/02 I was wondering if there was a simple way like just typing the month 02.

Thanks

 

[Mile-O]

What (potentially) would happen if you entered the month number but you wanted to go back one year to February 2002, not February 2003?

 

[acess_uk]

thanks

Thanks mile-o-phile for the reply,

I had not thought that far but I cant see the database being need much longer than a year they would print out the reports monthly and then at the end of the year delete the data and start again as they would have monthly reports to look at.

Do u know of a way to back the data up so if need the data could be sent on electronically, after the main database data has been delected there is a backup copy to be sent on to head office etc.

 

[Mile-O]

1) Are you entering the number into a textbox on your form?


2) You can use an append query to keep one table for archiving all data past a specific point into.

i.e. You pick a field, set the criteria to be (to move all last year's data) : Year(DateAdd("yyyy", -1, Date()))

You would also use a delete query afterwards, with the same criteria, to remove all the records from your main (current) table.

 

[namliam]

if you want the month of the current year:

enter myMonth 2

(01-02-2003)
dateserial(Year(date),myMonth,1)
(28-02-2003)
dateserial(Year(date),myMonth+1,1)-1

Regards

 

[aleb]

What you need to do is to create the query based on the table that has date field,
In that query you need to add the field like following
Month: MonthName(DatePart("m",yourtable!yourdatefield))
In criteria grid for that new field just type [Type the month]
Now when you run the query it will ask to "Type the month"
Type full name of the month ( ex. January )
Query will show you the all the records for the month of January.

The question remains open because if you has the records for the last year for that month the query will also display them.

 

[Mile-O]

I was getting to namlian's suggestion - just trying to find out if it was going to be done by parameter or by referencing a form.

 

[namliam]

"great" minds think alike Mile

 

[acess_uk]

Big Thanks

Hi All,

Thanks for your reply. I am a little confussed were u do you place the code

Month: MonthName(DatePart("m",yourtable!yourdatefield))
In criteria grid for that new field just type [Type the month]
Now when you run the query it will ask to "Type the month"
Type full name of the month ( ex. January )

enter myMonth 2

(01-02-2003)
dateserial(Year(date),myMonth,1)
(28-02-2003)
dateserial(Year(date),myMonth+1,1)-1

Year(DateAdd("yyyy", -1, Date()))


Thanks

 

[aleb]

Well ... my solution doesn't need to place a code anywhere .
You have mixed two replies to your message,

In my example what you need to do is to make a query with SQL like this
SELECT yourtable.*,MonthName(DatePart("m",yourtable!yourdatefield)) AS Month
FROM yourtable
HAVING (((Month(DatePart("m",yourtable!yourdatefield)))=[Enter month]));
Then create report based upon the new query.

I think it is the easier way and doesn't involve coding.

Just don't forget to change "yourtable" to your table name
and "yourdatefield" to your date field name


<mailto:aleb@tengizchevroil.com>

 

[namliam]

No coding involved at all... Just put the formula's into your QBE and it'll work.

My solution will request a myMonth which is numberic (1,2,3,4,..)

Regards

P.S. Hope you figure(d) it out...