I'm working two dates that will highlight the accounts that have made a payment Today (Last Payment) + 60 days & move the entire row to another tab
Payment Made: iif ([Date of Last Pay])>60 days from ([Dis Date) highlight and move to tab number 2
and
Payment Made60: iif ([Date ()])>60 days highlight and move to tab number 3
I once saw a video online of a Memo field showing the times in which changes had been made to it.
I believe the instructor pressed a few keys when his mouse hovered over the Memo field and a pop-up with dates came up. I do not believe he used VBA.
I have a table linked to a form where I am able to change the values of my "Comments" field in my Source Table. I want to be able to see when changes were made (ie, new comments were added to my "Comments" field).
I'm trying to make a query to filter or show only those customers when it is the time for their monthly payment.The query I have consists of four fields which are
1- Order ID
2- Payment
3- Date (Default value set to Date ())
4- Date for next time Payment (Default value set to date () + 30)
I made another field called "states". In this field I putted the following expression
Code:
IIf(([Date for next time Payment]-Date())= 0 "Should Pay";" ")
Then, I set the criteria for such field to "should pay" so that only customer "should pay" will appear in the query datasheet. However, this method has the following shortcomings:
1- It works only for one day (alert day) ,i.e., customers will filtered only when the expression is true.
2- Customers who have paid will still appear as a "should pay" until the day (the day that make the expression true) finish.
3- Customers whose pay late (maybe after 1 week) will disappear form the query datasheet after alert day finish.
To overcome the above shortcomings, i modified the expression to
Code:
IIf(([Date for next time Payment]-Date())<-1 And ([Date for next time Payment]-Date())>-15;"Should Pay";" ")
This method will extend the alert duration to 15 days, so the customers who haven't pay yet will appear in the query datasheet for 15 days. However, the customers who have payed will appear also, and that is the problem. The problem here is I can't compare the current payment date with previous one for one customer. method to create suitable expression, or even another query scheme ??
I use the code below when a form is closed to either save or undo changes. However I only want the msg box to appear if there have been any changes to the record that the form is based on, if there aren't any changes then it will just docmd.close without the msgbox. How would I check if there have been any changes to the record? I think you have to use 'Dirty' but im not really sure how to use it.
Private Sub CLoseUR_Click()
Dim strMsg As String, strTitle As String
strMsg = "Do you want to save changes?"
strTitle = " Save Changes?"
[Code] .....
I have a few tables, one of which stores a record of changes made to user accounts, and some of those changes are linked to a "multichange" table for when I have made the same change to multiple accounts.
What I am trying to do is select the change_id from my changes table, with a limit of 1 as I only want one result (it will always be the same) but I am using this query and getting a syntax error telling me I have a missing operator.
Code:
SQL_SELECT = "SELECT change_id from dbo_user_change where username = '" & username & "' AND change_type = 'new account' LIMIT 1"
Set qresults = CurrentDb.OpenRecordset(SQL_SELECT)
Do While Not qresults.EOF
multichange_id = qresults.Fields(1)
qresults.MoveNext
Loop
qresults.Close
I created a form for data entry only where records could be continually added until you were done and closed the form. Into that form I dropped a query that kept a running list in datasheet form of all the new records entered while the form was open, and then cleared when the form was closed (so the query was empty when the form was opened again).
My Problem: I accidentally deleted that query.
What I need:
-Query to be empty when form is opened.
-Records added while form is open get displayed in the query after record is saved.
I want to make a query to figure out profit made by month year and room type.
View 1 Replies View RelatedI have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies View RelatedI have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014
table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014
So the answer of the query would be 2,0,4.
Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
at the moment i am making a system that calculates the cost of gym membership cost. however, i am stuck on how i can determine whether or not the member's have paid. i have a make table query that calculates each members fees. i would like the payments to be made quarterly. does anyone have any ideas on how i would be able to do this.
View 1 Replies View RelatedI have a database with 10 properties which I want to record details of weekly or monthy payments. I have a table for properties, one for tenants details and one called payments. I then have them on a form. I want to open the tenants form and insert a command button to open the payments form and record payments as the occur but i can only enter one - it is giving me an error message as I am duplicating a record. The tables are linked with a property reference which I have set as the primary key. I have obviously done it wrong. Can anyone advise me of a way to do this please.
Thanks Tracey
i am making a db for a gym. the members pay yearly and the fact that they have paid is shown by a tick box in the members table. i therefore want to reset these boxes to 'No' at the beginning of each year. anyone got any way that this could be done.
thanks
Hello.
I am having dificulty setting up a query. What I have so far is two tables one with customer contact info (name address phone etc..) and one with transactions. I want to have a query that can show me the customer and all of his transactions in one record. I am able to list it now as multiple records but i want to have it all in one record.
i am unsure as to whether this can be acheived in a database..
basically this task is almost calendar like.
the company operates 7 days a week.
i have a table of company drivers and each has their own driver ID.
my user would like to store what time each driver comes into work each day and what time each driver leaves. this needs to be quite flexible however because some days certain drivers wont work, but each day..if a driver has a 'come to work' time then they will for sure have a 'leave work' time.
each driver also pays rent to the company once a week. within this calendar like system i would also like to store on what day the driver paid what amount towards their weekly rent.
i cant quite imagine how this can be done in a database. but if these kind of applications are common, i would be most grateful for some direction.
thank you.
one more thing..
i would also need to record during each day what time the driver left and returned from lunch (sometimes our drivers leave for a few hours during lunch time)
i want to store this because if they miss a job while they are on an extended lunch break im gonna roast em.
Hello,
I have a database for rental units and i have two problems that i cannot solve. I have a table with 4 different options of units with different prices. what i would like to do is for the first time customers there is 1 time fee, how would i be able to charge them only 1 time and no charge after that.
My second question would be i have a payment form also and lets say a person joined today and has to pay 40 for monthly rental and 20$(the 1 time payments) which comes to 60$, and the person pays it in full so the balance is 0$, how would i set it up that a month from now, the 0$ will turn again to 40$ so the person has to pay again?
And also would i be able to set up a late charge if they dont pay on the day that they are supposed to?
Thanks for all the help....
hello all.. trying to resolve an issue in my system..
basically i run a taxi firm.. for those that dont know.
this issue deals with credit card payments, and in particular payments for multiple transfers.
lets say you are booking 2 journeys..
the first journey is on 2nd June from London Heathrow to your hotel in Kensington
the return journey is on 5th June from your hotel back to Heathrow
you want to pay for both journeys at once with your credit card.
both transfers cost £40 each, 40x2 is £80 plus 5% transaction fee is £84.
we keep the credit card details on file to save our passengers time when they want to pay for their journey using the same card.
so lets say we have a job table.. and a credit card table..
Card Table
cardid (pk)
cardtype
nameoncard
last4digits
secnumber
expirydate
startdate
issuenumber
Job Table
job id (pk)
jobdate
jobtime
jobnotes
should i create a new transaction table which holds the jobids that were paid for and the cardid that paid for it..?
shall i include the price of the job in the jobtable.. and store the transaction fee in the transaction table?
if this is clear i would like to hear a few opinions on this.. i will be thinking hard about this over the next few days so i might come to some conclusion but what do you guys think
cheers.
the reason im asking this is because at the moment i am storing the credit card details.. pricing information and job details in the job table..
when i send my passenger an email confirmation.. it sends them the job details, card details and payment details..
this is fine for single transfers.. but if they book more then one transfer i have to send them multiple emails outlining each job individually.. and if they wish to use the same card i have to find their card from the original job and copy those details.
if someone books 4 jobs and pays for them all with one card and one transaction i really have difficulties, i would have to send them 4 emails (with 4 job details respectively) and i would have to include all the payment details in each job to verify it has been paid.. this means i would have to enter their card details 4 times.. and even though each job might be £20 each.. i would have to write £80 in the total charged field because it is covering all the jobs..
anyways.. if you can spare me a thought i would be grateful.. thank you
Hello,
I have a query that lists members who have outstanding payments. The results of this query are being used to populate a winword mail merge document.
The problem with this query is that if there are multiple members from the same family who owe then I will get an entry for each of them in my query results. What I would like to do is add the amounts due so that I only have 1 entry for that family. Additionally, since it is possible that more than 1 person can have the same last name and not be related I would also like to have the query check the address field and if it's the same than add the amounts due. The following is the sql statement for the current query.
SELECT Members.FirstName, Members.LastName, Members.ParentsNames, Members.HomePhone, Members.PaymentDue, Members.Active, Members.MemberDues, Members.FamilyAmount
FROM Members INNER JOIN MemberTypes ON Members.MemberTypeID = MemberTypes.MemberTypeID
WHERE (((Members.PaymentDue)=Yes) AND ((Members.Active)=Yes))
ORDER BY Members.LastName;
Any assistance would be greatly appreciated.
Regards,
Chris
I have a project to take. It relates to school fee payments by students. I have created tables, query and forms for the same. The database is able to calculate the Balance using the query. Now my major issue is how do I go about Calculating the Balance for the second fee payment, given that the amount payable on the next payment will be the previous balance. I have tried to think using the nested if to try it, but invain!
View 4 Replies View RelatedI'm trying to design a billing and invoicing database and can't seem to figure out how to link the tables and track the payments made by customers. How can I proceed to track the payments and make payments applicable to certain line items or invoices? If the line item hasn't been paid, it needs to show up on the next invoice and be included in the total due.
View 14 Replies View RelatedI have built a simple database for a dance school.
The junction table that handles enrollments is behaving a bit weird.
I have a form that deals with class payments. This is linked to the enrollments table via datasheet.
When i refresh the datasheet (which is embedded in a form) after a payment the first record in the enrollments table changes its value.
Effectively the first student changes to whatever class i am currently viewing.
Hi all:
Well, as part of my new job I get to revise a current database for a client and I am hitting a brick wall in my mind at the moment and could use some ideas thrown my way on how to deal with it.
First of all I have two tables -
Table AR_Due
AR_Due_ID - Autonumber (PK)
Entity_ID - Long Integer (FK)
AR_Due_Amt - Currency
AR_Due_Date - Date/Time
AR_Due_Waive (boolean and if checked the amt due is forgiven and not due)
Table AR_Pay
AR_Pay_ID - Autonumber (PK)
AR_Due_ID - Long Integer (FK)
AR_Pay_Date - Date/Time
AR_Pay_Amt - Currency
Now, I have to pull all AR_Due where the payments are late (past the AR_Due_Date) and have no in the Waive column and, if partial payments were made those payments can't be past the due date or else they need to be included as late (that's my main issue - trying to figure out the logic on that one).
So, any ideas on how that might be best accomplished? I need a report that shows all late payments (not counting lates more than two years ago) including any where there were partial payments made but the total partial payments were not made before the due date.
I'll take any ideas, although I might not be able to use some of them due to having to follow certain standards here (don't know all of them yet but I've kind of figured a few out based on the existing db). And, I didn't put this all together so there are some things I can't change (at least at this point), but I'm happy to get any ideas that might spark the "aha" moment :).
I have a report that has two sub reports one to report deposits and the other to report payments.
The report prints fine if there are records in both sub reports, however when the payments sub report is blank then the report hangs and won't print to pdf at all.
I would like to display the sum of all the payments referred to an invoice, in an unbound field in a form.
The invoices are stored in the table FACTURAS, and the payments, in the table REC-COB.
The main form is bounded to table FACTURAS and it has a subform bounded to REC-COB. The INVOICE number connects both form and subform (NĀ° DOC <-> FACTURA).
Table: FACTURAS
Field: NĀ° DOC
Table: REC-COB
Field: FACTURA
Field: IMPORTE indiv
If I am displaying the invoice information of FACTURA "A1000", in the subform I currently display all its payments (stored in REC-COB) but I also want to display the SUM OF THOSE PAYMENTS.
I tried:
=DSum("[IMPORTE indiv]",[REC-COB],[REC-COB]![FACTURA]=[detalle_fact]![NĀ° DOC])
* detalle_fact: name of the main form
I have a members DB that apart from full details also show payments of yearly subscriptions. I am trying to print a report that shows a list of all the lapsed members. sounds like a simple simple report, BUT...I have a table that includes male and female members on one club number, idealy man and wife, but if for some reason the part company one may not pay the others subs those making the other a lapsed member. is there anyway i can print one part without the other?
E.g. this is haw it prints now
Male Name Paid Female Name Paid
J Smith No M Smith Yes
and this is what i am after
Male Name Paid Female Name Paid
M Smith Yes
I have written a large number of queries to gather data for a quarterly monitoring form. How do I replace the dates to update them for next quarter.For example I have written a query which counts the number of new members who started in the period 01/04/2013 to 31/07/2013. How can I change this (and the other 200 queries) without opening them all individually and manually altering it. Is there an Access equivalent of Word's Find and Replace?By the way I am using Access 2000.
View 11 Replies View Related