access reports help needed

[tecassitant]

hai,

i am working with access and VBA.but i am new to both i am creating reports.
i have differrent tables linked many to many
i want certain details from different tables and each linked through a join field.
i want a report to be created in such a way that if i enter a division(this is a field in a table )and the month ,but remember we dont have a field for month but there is a field for date in the table
if enter division and month it should throw me the datas for the fields for which i have selected from different tables.is that possible if yes,please provide me query for that as i am new to access and reports .and it is urgent

thanking you
krishnakumar.B

 

[randysmid]

Hi,
The first thing to do is to check your query for the proper joins. To do this, open the query in Design view, then right click on the line connecting the tables. Select Join Properties, and view the three different options. The first option (which is the default) is to only show those records where the field being matched is equal. The next option will allow you to select all the records from the first table, and any from the 2nd table that match. This might be useful if the 1st table is Employees, and the 2nd table is EmployeeTimeSheets. The 3rd join property will let you select all records from the 2nd table, and only those from the 1st table that match. In the example just given, you could see all time sheet records even if there is no corresponding employee.

RE: the month. There is a function in VBA that will pull out the month from a date field. I most oftentimes put this in my query as such:
tmpMonth: Month([Batch_date])
This goes into the field line inside your query. Essentially, you are creating a new field called tmpMonth, which is the month value from the Batch_date field. Now, you can select all the records from that month by using the criteria line under this new field you created. The criteria line might look like this, if you are using a form to pass the month to it:
CInt([Forms]![frmAcctClose]![cbo_MO])
The CInt function will convert the month to an integer.
The form is called frmAcctClose.
I set up a combobox on my form called cbo_MO, and this combobox is composed of 12 entries (e.g., 01,02,03,04, etc.)

By the way, I wrote two FAQ's on techniques to use in using a form to pass parameters (criteria) to a report or query. You may want to print them out, review them, and decide which you would like to use. Here are the FAQ's:
faq703-2696 (passes parameters to query)
faq703-2657 (uses "filter" to pass parameters to the report) HTH,
Randy Smith
California Teachers Association