Modules & VBA :: Adding Amount And Checking Daily In Hand Amount?
Jul 21, 2015
I have One table(and designed form from it) in which i have recording the daily transactions(it is like a daily book). Daily transactions took place like Purchase of items of Amount 45, sale=70, sold on account=100 etc.
What problem i am facing is: I would like to Add Cash In Hand amount and would like to show it on my form. When day end I would like to move remaining cash In Hand on separate column of that date. The next day i would like to take the previous Remaining Cash in Hand as Cash In Hand at Start for next date and so on.
View Replies
ADVERTISEMENT
Feb 26, 2015
I have built a login form that works fine currently with the exception of login attempts. The current features are
1) Checks login is in users table.
2) Ensures the user status is not blocked in the table.
3) Sends the user to the correct page based on their authorisation in the user table.
What i need to do now is
1) Allow the user to enter the incorrect password 3 times and on the 4th time it will say "Too many login attempts. Your user access has been blocked."
2) Update their status as blocked in the users table.
My Current code is below.
Private Sub LoginButton_Click()
Dim Useraccess As String
Dim Userstatus As String
If IsNull(Me.LoginUsernameText) Then
MsgBox "Please Enter Username", vbInformation, "Username Required"
Me.LoginUsernameText.SetFocus
[Code] ....
I have tried 6 different codes to try and add this and i cant get any of them to work.
View 9 Replies
View Related
Jul 17, 2014
I am trying to make a report in which I can see the amount of money that I have to pay weekly to an employee. To explain myself clearer, I have different rates for each employee. For example:
NameNormalPayRateVacationPayRateSickPayRateOvertimePayRateStandbyPayRate
John Smith$15$10$15$30$8
Ken Jonson$10$8$10$20$8
I am doing this in a query. So what I want to be able to do is, for example, take 4 hours that the employee worked at NormalPayRate and multiply it. But if tomorrow the employee worked at a different rate, for example 3 hours overtime, I want those 3 hours multiplied by OvertimePayRate.
The way I saw it on my Google researches, is that they have only one rate and the amount will be [SumOfHours]*[Rate]. But how can I tell my query to skip, for example, one rate because the employee didn't work at that rate that day. So I can have different amount for the different rates.
By the way, entering the information I can divide at what rate the employee worked x day. So if the employee work 6 hours at regular, and later on 4 hours at standby, I have those information separated in my table.
Results that I am getting: I can easily get the amount in money multiplying the whole hours by a rate, so my result in my report will be multiplied by the same rate.
I am using Access 2013 ...
View 2 Replies
View Related
Sep 16, 2013
I've got a table with a lot of data.
In the table is a SAP Number (ID), a booking date (BuchDatum) and an amount (Betrag)
Now i want to sum up, for each ID the amount in one months.
In the end i want to have a table like this
Date SAP Number Amount
01/2011 12345 1000€
02/2011 12345 0€
03/2011 12345 100€
04/2011 12345 300€
and so on.
So for each ID, a table like this.
I tried it already, but my Problem is that i either sum up the years or the months. I Need a combination.
I put the file in the Appendix.
View 1 Replies
View Related
Dec 5, 2014
I have a table of hours that have been worked by employees for each day of the week
[moh] (Monday's hours),[tuh],[weh],[thh],[frh],[sah],[suh]
data eg (this is how I would like it to be inputted into the table)
7:24:00,7:24:00,7:24:00,7:24:00,7:22:00,0:00:00,0: 00:00
This equates to 36:58:00
I have tried
Total Hours: [moh]+[tuh]+[weh]+[thh]+[frh]+[sah]+[suh]
but I am struggling to get what I want in the right format.
How to record the initial data or a formula to format the end result.
Excel just does it !!!!!
View 1 Replies
View Related
Jun 16, 2015
I have 250 separate worksheets with a lot of data to put into Access. Problem is the data is 120,000 rows in each worksheet and a lot of duplicate date eg..DATE, NAME, TIME,are some of the column headings and there are multiple rows with same DATE or NAME. That is just how I received the data. I would like to transfer all records into Access as quickly and efficiently as possible.
View 4 Replies
View Related
Sep 25, 2013
I have a subform in columns that has a list of invoices bound together by the site number on the form there is a Expense code that runs 51 to 95.
On another tab I want to add all the invoices for a expense code together and display the total amount
Below is an example of invoices
IDSite CodeInvoice DatePOInvoiceContractorInvoice AmountExpense Code
10289S20/09/2013 346603410Dj Commercial Cleaning Ltd 25.2362A
20289S28/09/2013 346061141Platinum Landscapes 240.0055A
30289S03/09/2013 353112021Clean And Green 167.0063A
40289S02/08/2013 353112015Clean And Green 174.0063A
50289S19/08/2013 111115271Southern Electric 189.5679A
60289S19/08/2013 346061121Platinum Landscapes 240.0055A
70289S02/06/2013 353112009Clean And Green 160.0063A
80289S02/05/2013 353112003Clean And Green 181.0063A
I want it to display on another tab
51 General Expenses £452.36
55 Gardens £1523.65
and so on
whats the best way to do this?
View 2 Replies
View Related
Jul 1, 2013
I want to display on my form the amount of records and which one the form is displaying just like that, which is at the bottom, in text fields.
View 4 Replies
View Related
Dec 18, 2014
I have two numbers, i need to calculate how much percentage of one is the other. E.g
num_1 = 100
num_2 = 10
percent = 10%
Users enter a dollar amount (retainer) and my code should calculate the percent of the total proposal amount.
Actual Code
Private Sub cmdSubmit_Click()
Dim intProposalTotal As Double 'if i use interger i get "overflow" error
intProposalTotal = Nz(DLookup("ProposalTotal", "qryPropsalTotalForRetainer", "proposal_id=" & Me.proposal_id), 0)
[Code] .....
billing_retainer_percent is formatted to Percent with 0 decimals. In the actual table Type - Number, Format - Percent, Field Size - Single. What i get is 100% instead of 10%.
View 4 Replies
View Related
Apr 13, 2015
I have the following code that works fine:
Dim db As DAO.Database
Dim rs As DAO.Recordset, i As Integer, ii As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("PatientPrescriptions1")
ii = [RefillAmount]
For i = 2 To ii
[Code] .....
However I am trying to make it decrease the value in [RefillAMount] each time it loops through the addnew function and I can't figure out how to do the rows keep saying the same number:
If I put Refill 3 it creates 2 extra rows and all these rows now say Refills = 2. What I want it to say is
Refills 3
Refills 2
refills 1
And end there. how to do this?
View 2 Replies
View Related
Apr 22, 2015
I have a query for loan calculation, fields are;
Loanamount
loandate
monthlyinstalment
what i want is that query to start subtracting lmonthlyinstalment from loanamount on monthly basis
View 3 Replies
View Related
Feb 3, 2005
I have a script file of FoxPro 2.6 which is use to read amount field and write it in words.
Can any body convert in VBA Script.
I manualy write amount in words.
Thankyou.
Saira
View 3 Replies
View Related
Sep 26, 2005
How many users can an access database support? I'm setting up an app accross 3 workstations but each has multiple users. There may be as many as 1000 records a day entered. Can an access database handle this or do I need to switch to sql server?
View 6 Replies
View Related
Nov 8, 2007
Hi
Is there any way i can increase the amount of columns in a table before importing data? I am importing data from a notepad doc that needs abt 300 columns but i can only get abt 100 columns.
Thanks for any help
View 8 Replies
View Related
Jun 2, 2005
Hi All.
I need to generate a query which will show me in Field1 Cost for the current year only and in Field2 Cost from a start date to the current. If is it possible how to do it?
Thanks.
View 1 Replies
View Related
Jun 3, 2005
I'm trying to produce a query that will bring up a list of all employees with their current salary amount. The table however, has all changes for an employee and reads as follows:
Table Name(NBRJOBS)
Employee ID-- Date of Change -- Salary Amt
1234 ----- 01/01/2003 -------- 500.00
1234 ----- 05/01/2003 ----- --- 600.00
1234 ----- 11/01/2003 ---- --- 700.00
2222 ----- 03/05/2004 ------- 200.00
2222 ----- 12/05/2004 ---- --- 300.00
2222 ----- 02/01/2005 ---- --- 300.00
It will be necessary to create a report to list out all employees and their current salaries. Any suggestions?
View 5 Replies
View Related
Dec 6, 2005
I have a table containing the following two fields, one with monthly dates (end of month plus year) and one with profits (per month). However, for some dates the records are missing. For example, for the 31-1-1994 there is no record (not in the date field, nor in the profits field).
How can i create a query that will only show me the records if 10 or more monthly subsequent profits are known, so meaning that in those 10 months no records are missing? So that only the timespans without the gaps (missing records) are shown.
So if the 31-1-1994 and the 30/6/1994 record are missing, then the 4 subsequent records in between those two dates should not be shown,, since the amount of records is not 10 or more. However, if the next missing date would be 30/6/1995, then all the 11 subsequent records between 30/6/1994 and 30/6/1995 should be shown. Since the number of records is bigger than the required 10.
Thanks for helping me out!
Luuk
View 4 Replies
View Related
Jul 14, 2007
I'm ok with Parameter Queries now, but I'm stuggling for the certeria to select say the top 20 records for a list. This would be based on a number, say amount spent.
Any help?
View 3 Replies
View Related
Nov 23, 2007
Dear all,in my table I have the following fields:- fldstartdate- fldstarttime- fldenddate- fldendtimeCan I calculate the time that is between those variables using an expression?fldenddate.fldendtime - fldstartdate.fldstarttime = "x days and x minutes"Kind regards,Hans B.
View 4 Replies
View Related
Feb 27, 2008
Hello All
if I have thirty records and have the end user select the first 8 or what ever amount they want, could be 3, 14, 9 etc, is this possible via a form ?
thanks
View 13 Replies
View Related
Mar 3, 2005
Make Table Name: tblnum
Fields:
1.num (for number)(Feed 1 to 99)
2.inword (for In words)(Feed One to Ninty Nine)
Make Module
Public Function inwords(amount As Variant) As String
Dim intlac As Integer
Dim intTh As Integer
Dim intHun As Integer
Dim intNum As Integer
Dim strLac As String
Dim strTh As String
Dim strHun As String
Dim strNum As String
Dim intlen As Integer
intlen = Len(amount)
Select Case intlen
Case 7
intlac = Left(amount, 2)
intTh = Mid(amount, 3, 2)
intHun = Mid(amount, 5, 1)
intNum = Right(amount, 2)
strLac = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intlac) & " Lac"
If intTh = 0 Then
strTh = ""
Else
strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"
End If
If intHun = 0 Then
strHun = ""
Else
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
End If
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 6
intlac = Left(amount, 1)
intTh = Mid(amount, 2, 2)
intHun = Mid(amount, 4, 1)
intNum = Right(amount, 2)
strLac = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intlac) & " Lac"
If intTh = 0 Then
strTh = ""
Else
strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"
End If
If intHun = 0 Then
strHun = ""
Else
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
End If
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 5
intTh = CInt(Left(amount, 2))
intHun = CInt(Mid(amount, 3, 1))
intNum = CInt(Right(amount, 2))
strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"
If intHun = 0 Then
strHun = ""
Else
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
End If
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 4
intTh = CInt(Left(amount, 1))
intHun = CInt(Mid(amount, 2, 1))
intNum = CInt(Right(amount, 2))
strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"
If intHun = 0 Then
strHun = ""
Else
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
End If
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 3
intHun = CInt(Left(amount, 1))
intNum = CInt(Right(amount, 2))
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 2
intNum = CInt(Right(amount, 2))
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
Case 1
intNum = CInt(Right(amount, 2))
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End Select
inwords = strLac & " " & strTh & " " & strHun & " " & strNum & " Only"
End Function
Result = inwords(Number TextBox)
View 2 Replies
View Related
Sep 2, 2005
What is the maximum amount of fields/columns a table can hold in access 2003?
And what do you do if you have a form and its record source is a table that has run out of fields/columns?
If I remember right in access 97 it was 256 or 257 fields/columns was the max allowed. And it seemed to me it was quite common to run out of fields.
I'm currently waiting for my access 2003 upgrade disk to arrive and I'm
anticipating issues that might occur.
Thanks--Any help is much appreciated.
View 3 Replies
View Related
Feb 8, 2008
Hi everybody....
I have a vba question....
I have a form that states how many entries a certain table can have related to that form.
For example:
Form1 - based directly on the table for diagnostic purposes
Cust ID - text - primary key
NumofEntries - number - integer
I want it to create X amount of records in form2 based upon the NumofEntries from form 1 based on table1
For example, I run form 1, and it asks for the custnum, I enter it, and then it asks for the number of entries allowed for the customer, and then I enter it. This number can be different each time. I want the 2nd form just to show that many fields for entry either by a running total (subtraction) in form view or by a for next loop to create X amount of records in the table.
Form1:
CustNum = 1011
NumofEntries = 3
CustNum = 2022
NumofEntries = 4
CustNum = 3033
NumofEntries = 2
Table2
Custnum = 1011, entrynum =1, entry data
Custnum = 1011, entrynum =2, entry data
Custnum = 1011, entrynum =3, entry data
Custnum = 2022, entrynum =1, entry data
Custnum = 2022, entrynum =2, entry data
Custnum = 2022, entrynum =3, entry data
Custnum = 2022, entrynum =4, entry data
Custnum = 3033, entrynum =1, entry data
Custnum = 3033, entrynum =2, entry data
the key is to have the entrynum to start at 1 each time, the rest I can handle.... I am at a loss right now, as I am down to one brain cell, and it's misfiring.....
Granted, tomorrow, when I wake up, I will prolly have a solution, but as always, I value all of your input and design suggestions. Yes, I know already that it's a one to many relationship from table 1 to table 2, I just want some alternate ways to do this. Thanks in advance for any insight you wish to give.
View 3 Replies
View Related
Jun 19, 2015
I have a qry which gives me this data:
date/code/hrs/amount
12/05/15 ABC 4 243.55
12/5/15 DEF 6 707.12
13/05/15 ABC 1 101.01
etc..
I'd like to sum the amount for each date. Is this possible?
I will be updating the table with new dates and want to eventually create a form where i set the dates and it does the query where I get the amount for the dates specified, e.g. 01/07/20 - 08/07/20
View 1 Replies
View Related
Apr 25, 2005
Hey! I'm doing Computing AS level and the coursework is to create a system for "Terry's Turkey". This is basically my first introduction to Access, and our teacher is little to no use!
After alot of work working out things in access and reading alot of books, other than creating the switchboard, theres only a few things left to do!
The first is to limit the amount of "turkeys" that can be ordered. I have 2 tables that this envolves... Order and Customers, they have a one to many relationship (each customer may place one or more orders, each order must be placed by one and only one customer). They are linked by Customer ID. I need to set two limits, I guess by a validation rule of some kind. The first is that each Customer can place no more than 5 orders. The second is that their can be no more than 40 orders in total. A slight complication to this is that a customer can "cancel" an order, in this case its marked as cancelled (theres a yes/no field called Active to determine this), and ideally, these wouldnt be included in the counts.
So far I have the following as a query, though I have no idea how to put this into a validation rule
SELECT Count(Orders.OrderID) AS Var1
FROM Orders;
I guessed that to expand this to not including inactive orders something like
SELECT Count(Orders.OrderID) AS Var1
FROM Orders
WHERE active="yes";
Any help at all would be most appreciated! I have spent many hours pondering over this is and looking at as many access sources that I can! I think this is my last resort :(
View 3 Replies
View Related
Sep 2, 2005
What is the maximum amount of filds/columns a table can hold in access 2003?
And what do you do if you have a form and its record source is a table that has run out of fields/columns?
If I remember right in access 97 it was 256 or 257 fields/columns was the max allowed. And it seemed to me it was quite common to run out of fileds.
I'm currently waiting for my access 2003 upgrade disk to arrive and I'm
anticipating issues that might occur.
Thanks--Any help is much appreciated.
View 3 Replies
View Related
