Replies

Yahoo. It worked it must of been the Dim statement. I just copied and pasted into the code what you had written. Thank you Jack. Now I can move on to something else I don’t know what I am doing. Thanks Fay

Yes a zillion times. With quotes it figures just the Else line. If I take the quotes off I get a Run Time error 13. Thanks. Keep this up I will not need my comb because there won’t be anything thing left on my head to comb. Fay

The table field is a Yes/No field it goes into txtProrated textbox. Which displays 0 or -1. When I run the code as listed below it now runs the formula for the Else part of the statement and places it on the proper page. But nothing I do will make the If part of the statement run. If I take the guotes off around No then I get a compiler error. Further more if I reverse the two txtMonthlyStandard formulas only the Else formula runs. I either end up with 9 correct answers or 1. (not good)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
‘ Figure monthly standard prorated vs non prorated
Dim txtProrated As String
If [txtProrated] = “No” Then
txtMonthlyStandard = [txtAnnualStandard] / 12
Else
txtMonthlyStandard = [txtAnnualStandard] / [txtMonthsProrated]
End If
End Sub

This is making no sense to me. Thanks for your help. Fay

Okay here is where I am. I placed the code in the On Format Event of the detail section. The code now looks like this.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
‘ Figure monthly standard prorated vs non prorated
Dim txtProrated As String
If [txtProrated] = “No” Then
txtMonthlyStandard = [txtAnnualStandard] / 12
Else
[txtProrated] = “Yes”
txtMonthlyStandard = [txtAnnualStandard] / [txtMonthsProrated]
End If
End Sub

No error messages received.
Now the only resutls in the txtMonthlyStandard is the results for the Prorated person. Everyone else has no values in the txtMonthlyStandard textbox. I have even tried placing a textbox on the form with a value of 12 in it and referencing it instead of putting 12 in the formula. With no luck or results.

I appreciate the help. Thank you. Fay

I am really out of my element here. I think I Dim the Month text entry correctly. Originally the Month textbox way placed on the report so that the Month would be placed at the top of the report to identify what month the data was summarizing. I guess you are asking if I will be doing an error message box if a number or abbreviation is entered. Yes. But one problem (new skill) at a time please.

Private Sub Report_Open(Cancel As Integer)
‘ Figure TotalUOS/Expected
Dim Month As String
Dim strTotalUOS As Integer
On Error Resume Next
strTotalUOS = Month
Select Case Month
Case “July”
txtTotalUOS = ([LengthofService Grand Total Sum] / ((DLookup(“AnnualUOSTotal”, “tblCompanyFacts”) / 12) * 1))
Case “August”
txtTotalUOS = ([LengthofService Grand Total Sum] / ((DLookup(“AnnualUOSTotal”, “tblCompanyFacts”) / 12) * 2))
Case “September”
txtTotalUOS = ([LengthofService Grand Total Sum] / ((DLookup(“AnnualUOSTotal”, “tblCompanyFacts”) / 12) * 3))
Case “October”
txtTotalUOS = ([LengthofService Grand Total Sum] / ((DLookup(“AnnualUOSTotal”, “tblCompanyFacts”) / 12) * 4))
End Select
End Sub

Should this be done on the Open event or would it be better to create a Public Sub?

Thank you. Fay

I did what you said in the last post and it worked well until I tried to print the report via the “normal” way. I only want the label visible if I go throught the cmdApril button off the frmClient form. The button is only visible in April. I altered the code to the following

Private Sub Report_Open(Cancel As Integer)
‘Makes the label “End of Year Report” visible _
during the month of April for this report
If Month(Date) = 4 Then
Me.lblEndReport.Visible = True
Else
Me.lblEndReport.Visible = False
End If
End Sub

This works. But, isn’t there always a but. Can I alter the “If Month(Date) = 4 Then” line to instead be based on the fact that the cmdApril button was clicked?

Thanks! Fay

The label in question is on the subreport. Thank you for the form info.

You were correct the code Me.subrptHUDYearlyTotals!lblEndReport.Visible = True did not work.

Normally this report is printed from a reports switchboard page. But at the end of the grant year I want to be able to mark the report as the year end report, hence the need to make a label visible only once. The button that is clicked is on the main form and only appears in April.

What I wanted to happen is for the label to become visible prior to printing. How would I use the open event of the report to see if the form is open and make the label visible only when the April button is pressed on the main form? Thanks Fay

I had formatting set in the field name of the queries. Corrected that and yahoo it is working correctly. You are a sweetheart with a great head. Thanks for peeking in on the tail end of a holiday weekend. I hope you had a great one. Thanks!!!!! Fay

This is the line I used and it isn’t working.
Me.subrptHUDYearlyTotals.Form.lblEndReport.Visible = True
subrptHUDYearlyTotals is the subreport name
lblEndReport is the label that I need to be visible.

I don’t think I am sure what you meant by subformControlName. Also why is the word Form used when calling for a control on a report?

Thanks for your help.

Yes I did and I just did it again and still get the error message. I have since gone back to the query and made an expression that will give me the half value. So my next problems is to add that to the value of another control and get a third figure in a third unbound textbox. I enter the following =[ADAMHSPercent]+DLookUp(“BothHalf”,”qryBoth”) and get 51.68%9.8%.

I don’t have a lot of experience but I am really baffled why this isn’t working. Thanks Fay

I typed =19.75 in the actual text box from BothPercent and it divided out equally. Go figure.
Thanks for helping. Fay

That expression returns a value of 19.75%. I went back to the table that the data came from it was set as text data type. I changed it to Number, long int. But that did nothing to help.

Thanks Fay

That is a possibility. I guess that will need to be checked next week. Thanks

Have a good holiday.

Fay

Hi Mark thanks for your reply.

JuneYear is a field in the tblCompanyFacts. The table has only one record. The data stored in that field reads “Between #6/1/2001# And 5/31/2001#”. I need that string to be placed in the query field criteria row and act to restrict the records used to within those dates. Just as it would if you type the sting into the criteria row.

Would you use the DLookup in the criteria row written like this [DLookup(“JuneYear”,”tblCompanyFacts”)]?

Thanks again Mark. Fay

Thanks guys for responding. I used a table and was able to pull the number into an unbound text box on a form and that worked well. Thanks, but as usual answers lead to more questions. So here goes.

Another reused fact is the fiscal year (July to June). Which I have set in various queries as Between #7/1/2001# And #6/30/2002#. In a meeting yesterday I came away with having to track three different years: the fiscal year, a April to March, and June to May. I don’t want to have to go in next year in umpteen places and changes those dates. Will the DLookup still work in those situations?

How do I write the DLookup in a query. I have tried =DLookUp(“JuneYear”,”tblCompanyFacts”) and without the = in the criteria line. I get the message that: “Your tried to execute a query that does not include the specified expression…” I have the total criteria set to expression, otherwise I get a data type mismatch message.

Next question what is your opinion table versus form to use as the base of the DLookup which is easier to handle?

Thank you. Fay