Report and Query Help 1

[JBuckley2000]

Hey everyone,

What I was needing help on is seeing if I can start on doing a new report. For the query I am using, I need to have it choose a production month and a production year for when a certain product was created. I am hoping I am able to choose a beginning month and end month, then a beginning year and an end year since the date is broken up into the 2 separate columns---do you think this would work? Hope so because the rest of it should be simple.

I am also breaking the report into product families and I was hoping i could report on seperate totals for each family, then at the end of the report, a combined total.

What this report will do is tell me how many rejects we have for a certain amount of time and what was the actual reject. Thanks for checking this out!
JSF

Jason Facey
jfacey@lithonia.com

 

[TheInsider]

Hi,
You don't need to break the date entry into a field for month and a field for year. Simply use a single date field and then use the Month() and Year() methods to retrieve the month and year from the date - which you can test in your query. But an easier solution is to have the user enter a start and end date for the reporting period, then for the query you just select the date and place the following as criteria:

>=[Report Start Date] and <= [Report End Date]

To group on family simply choose Sorting and Grouping in the report - group on header and footer. Place a totals field in the footer with a data source of something like =Sum(MyField).

Rob Marriott
rob@career-connections.net

 

[JBuckley2000]

Hey Rob,

Thanks for the input and that is really what I want to do, but this is a table that already has 10 years worth of data in which they broke it up into Month and Year which kind of stinks. I think what you are saying is that I should set up a query in which I will be able to pull a combination of both fields (month/year) into the report so I can read between certain dates. I am not so sure how to do this though. I have tried setting up a query where the user can select an start month/year and end month/year, but this isn't working. I wish I could combine the two, but it is already done. The table is in this format and I wish I could change it.

I hope this explanation helps out some. JSF

Jason Facey
jfacey@lithonia.com

 

[TheInsider]

Hi,
Ok, you can do this with your method but I suggest to cut the number of parametes in half and use a start date and end date, instead of start/end month start/end year. You can the use the Month() and Year() methods to extract the month and year from your parameter dates. Month() returns the number of the month, for example Jan = 1, Feb = 2...
Then simply compare the values like:

<criteria for the month field>

>= Month([Start Date]) and <= Month([End Date])

<criteria for the year field>

>= Year([Start Date]) and <= Year([End Date])

Note: this is an &quot;AND&quot; conditon, not an &quot;OR&quot; condition, so the criteria is all entered on the same line in the query. Hopefully your months are entered in numeric format - otherwise your will have to convert them.

Rob Marriott
rob@career-connections.net

 

[JBuckley2000]

Hey again,

I got all of the information into my query that I will be using for my report, but I am wondering how will it be entered into the parameter values that come up when I run the query? It asks for only one start date and one end date and I am wondering how I enter in the month and year---I tried August 1990 to October 1990 and what I put in was 08/90 and 10/90 for the start and end dates, but it did not run correctly.

I am thinking we are nearing the end, so sorry about all of these questions. By the way, in my table, the months are numeric data. JSF

Jason Facey
jfacey@lithonia.com

 

[TheInsider]

First you must check your system date format. To do this you must go Start/Settings/Control Panel in Windows. Then select the Regional Settings icon. Select the Date tab. Deturmine which date styles you want for Short and Long Date Formats ie. D/M/Y or M/D/Y. Then assuming you have a D/M/Y Format you would enter your reporting period start date as 01-01-00 (for example) or 01/01/00 and your reporting period end date as 31-11-00 or 30/11/00. The &quot;day&quot; portion is required as a technicality because you are reporting from Jan 1st 2000 to November 30th 2000. If you don't like this method, you can simply use 4 parameters as you had suggested originally, but I find this to be less cumbersome on the end user's part. You can even add the date format into your parameter - for example replace [Start Date] with [Start Date DD-MM-YY]. If you to display these parameter values in the report (in order to get the report to recognize them) you will have to create a user-defined field in the query as well like:

StartDate: [Start Date DD-MM-YY]

Then you can use &quot;StartDate&quot; as a field source in your report.

Rob Marriott
rob@career-connections.net

 

[JBuckley2000]

Hey again,

I really understand what you are trying to tell me and it is helping, but I am wondering how I can dd the date format into my parameter--how do I even get to change that?

Also, I have put all of the information into my criteria and it still telling me I am unable to evalute when I run the query by putting in 08/01/90 (start) and 10/31/90 (end). I did include the >= and <= signs so I don't know if that may be screwing things up. I checked my table format and the month and year columns are set up as text format--does this have anything to do with it?

JSF

Jason Facey
jfacey@lithonia.com

 

[TheInsider]

Oh, sorry I was misleading in the last post - I meant that your parameter can contain any text you want, so you can simply place &quot;DD-MM-YY&quot; in the parameter so the message to the end user is literally &quot;Reporting Start Date DD-MM-YY&quot; - so they know exactly what input format is expected.

Access is quite high-level so the fact that the fields are text shouldn't make a difference, however, if you can change them to numeric fields it would be better - at the very least it would take less processing time for Access not to have to convert alpha-numeric characters to numbers. Make a backup of the database and try to change the field types. There is a potential problem, however. There year function will return a four digit year ie. 2000 - if you have been entering 2-digit years ie. 00, then you can see the comparison problem mathematically &quot;00&quot; is not equal to &quot;2000&quot;. Also, are the months being entered as numbers or names ie. &quot;2&quot; or &quot;February&quot;, &quot;3&quot; or &quot;March&quot;, &quot;9&quot; or &quot;Sept&quot;... if any of these conditions are true, then we will need to make some conversions.

Rob Marriott
rob@career-connections.net

 

[JBuckley2000]

Hey,

When I run the query, when it asks for the start date I put in 08-01-90 and then the end date I put in 10-31-90, which I thought would work, but it isn't. I don't know how to fix this. In the form, the month field is entered in as the numbers 01 through 12 and the year is also a 2-digit entry.

I really don't understand why it is telling me I am not able to get an expression.

Here is what is in my criteria for my month field:
>=Month([Start Date]) And <=Month([End Date])

Here is what is in my criteria for my year field:
>=Year([Start Date]) And <=Year([End Date])

I don't think i have entered this in wrong in any way.
THanks for your continuing support.
JSF

Jason Facey
jfacey@lithonia.com

 

[TheInsider]

Hi,
At this point you have 2 choices:

1) Go with your choice of entering a start month, end month, start date, end date and use the >= and <= criteria like above (except with 4 parameters).

2) Go to the table's design view and change the 2 fields from text to numeric value type. Then you have 2 sub-choices:
1) Convert the year field to 4-digits using an update query.
2) Only convert the year to 4-digits in the report's query by replacing the Year field with:
Expr1: iif((([Year Field] >= 00) And ([Year Field] < 30)), 2000 + [Year Field], 1900 + [Year Field])

So... why did I choose 30 in the IIF statement? Windows by default decides that a 2-digit year will be recognized as being in the 20th century if it is >=30 (1930) and so 0-29 are interpreted as 2000-2029. You can view and change this in your Regional Settings in Control Panel.

You can see the obvious downfalls of using 2-digit years, the whole Y2K thing come a little closer to home =). Anyway, the update query that I talked about in suggestion 2-1 would contain the same logic as suggestion 2-2.

Rob Marriott
rob@career-connections.net

 

[JBuckley2000]

Last question because I have pretty much given up on this, but is there a way to combine the Month/Year Columns into one column on my table using an update query?

If there is, please tell me because wouldn't this save us a ton of work??? JSF

Jason Facey
jfacey@lithonia.com

 

[TheInsider]

Hi,
Yes, create a new date/time field in the table. The create an update query... here is the SQL statement to input - just choose to view the query in SQL view and overwrite the statement that is already there with this:

UPDATE Table1 SET Table1.newdate = &quot;01/&quot; & [table1].[month] & &quot;/&quot; & IIf(((Val([table1].[year])>=0) And (Val([table1].[year])<30)),Val([table1].[year])+2000,Val([table1].[year])+1900);

Replace &quot;Table1&quot; with the table name, &quot;month&quot; with the name of the month field, &quot;year&quot; with the name of the &quot;year&quot; field. Note that this only works in D/M/Y format (system settings again) - you must re-arrange the &quot;01&quot; and month to get M/D/Y format. must have the 1st of the month specified as a technicality - Windows doesn't view Month/Year as a valid date.

Rob Marriott
rob@career-connections.net

 

[ADMOODY]

Good afternoon,

I'm currently new to using Access and VBA. I'm trying to create a form that will include a selecting a month to activate my query. I have the SQL statement and the months but I'm having problems getting the connections with the 'Report_Open click statement'. Here is what I have so far:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

strSql = &quot;&quot;
strSql = strSql & &quot;SELECT dbo_Client.ClientName, dbo_BusinessType.Description, Count(dbo_Client.ClientName) AS [Count]&quot;
strSql = strSql & &quot;FROM (((dbo_Client INNER JOIN dbo_ClientIndicators ON dbo_Client.NationalClientIndicator = dbo_ClientIndicators.NationalClientIndicator) INNER JOIN dbrderRecord ON dbo_Client.ClientID = dbrderRecord.ClientID) INNER JOIN dbo_Product ON dbrderRecord.ProductID = dbo_Product.ProductID) INNER JOIN dbo_BusinessType ON dbo_Product.BusinessType = dbo_BusinessType.BusinessType &quot;
strSql = strSql & &quot;WHERE dbrderRecord.Created between &quot; & Month(Date) - 1 & &quot;/1/&quot; & Year(Date) & &quot; and &quot; & Month(Date) & &quot;/1/&quot; & Year(Date) & &quot; AND dbo_Client.NationalClientIndicator='p' &quot;
strSql = strSql & &quot; GROUP BY dbo_Client.ClientName, dbo_BusinessType.Description;&quot;


Set db = CurrentDb
Set rs = db.OpenRecordset(strSql)
Me.frmClient.Form.Recordset = rs

Please Help!!