Hi,
Thanks John, your solution is working perfectly for my requirement.
I want to trouble you guys a little more. I have a calculation which is based
on values from different tables. I have acheived the desired calculation, but I
am unable to round the results as per my requirement. What I want is if the
fraction is less than or equal to 0.5 then I want only the whole number. If the
fraction is greater than 0.5 then I want the number to be XX.50. To explain it
further please refer below examples.
Calculated Number Desired Number
1.45 1.00
1.85 1.50
2.99 2.50
3.05 3.00
In excell we have Floor function whith which we can achieve this. But how to
do this in
access. I have searched the access as wll as VBA help, but Floor function is no
where to be found. Please help me in this.
Keep up the good work.
Thieves Can't steal it,
Relatives Can't snatch it,
Time Can't depreciate it,
Knowledge is the Only Treasure
that multiplies when you distribute it.
(Famous Sanskrit Verse)
Thanks,
Rajesh
John Viescas <[EMAIL PROTECTED]> wrote:
Rajesh-
You cannot assign a SELECT statement to an integer variable - that should
give you an error. You must either open a recordset or use DLookup to fetch
the value.
LeaveYearID = DLookup("ID", "tblLeaveYearMaster", _
"Year = " & Me.LeaveYear)
Also, you have declared LeaveYearID as a local variable. The above should
assign the ID of the record to that variable, but the variable is destroyed
as soon as the Sub ends. Do you really mean to set a control on the form?
If so, don't declare the variable, and reference the control as
Me.LeaveYearID.
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf
Of RAJESH DESAI
Sent: Thursday, January 05, 2006 12:05 PM
To: Access1; Access2; Access3
Subject: [ms_access] Help with select statement
Hi,
Please refer the following code. I have put this code in the code window
of the form which is bound to tblEmployeeMaster.
What I basically want is, from the year entered in the form by the user, I
want to pick the YearID from tblLeaveYearMaster. "ID" is a PK of data type
AutoNumber and Year is of data type long integer.
On running the following code, no matter what the year is selected in the
form, it invariably store value 0 in LeaveYearID variable.
Please guide me.
Private Sub Form_AfterUpdate()
LeaveYearID As Integer
Dim dbs As Database
Set dbs = CurrentDb
Months = NoOfMonths(Fdate, LDate, DOJ, DOD)
LeaveYearID = "Select tblLeaveYearMaster.ID from tblLeaveYearMaster " _
& " WHERE tblLeaveYearMaster.Year = " & LeaveYear
dbs.Close
End Sub
Regards,
Rajesh Desai
---------------------------------
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays,
whatever.
[Non-text portions of this message have been removed]
Yahoo! Groups Links
Yahoo! Groups Links
---------------------------------
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays,
whatever.
[Non-text portions of this message have been removed]
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
