I have a date related problem. I have an IF statement, that verifyes if a certain date entered in a table is <=Now(). The problem is, that it's vorking great just for curent year, curent month. For example: today is 31.05.2005 and if the value in my table is 13.06.2005 than for the <=Now() testing, it's telling me that 13.06.2005<31.05.2005. Why? Please help!!!!!!!!!!!!
Thanks,
Attila
Hi there,
I have a table which shows telemarketing that has been completed on a weekly basis where the caller places the date they called in one field and a followup date (if needed) into another field if a followup call needs to take place.
I want to create a query which will show me all calls that have got a followup date put inplace but the followup call has not been made.
E.g i call mum on 29/11/06 i put down followup on 30/11/06. I call on the 30/11/06 so the followup has been completed but need to follow up again on the 01/12/06 but i do not complete this one.
Any ideas???
Thanks
Dear all, i have the following codes:
enter_date = Format(txt_date, "dd/mm/yyyy")
Set db = CurrentDb()
Set rs = db.OpenRecordset("select * from [entered_date] where Date = # " & enter_date & " # ")
If Not rs.RecordCount = 0 Then
DoCmd.Beep
Msgbox "Selected Date's report already been created", vbInformation + vbOKOnly, "Error"
Exit Sub
End If
rs.Close
Set rs = Nothing
suppose,i have one record in the table [entered_date] which matches the user input - enter_date = "08/06/2007",but whne i run the above coding,i get the rs.recordcount = 0. it suppose to be rs.recordcount = 1.
can anybody pls help? thanks
Hi, Ive got another query I need to do which I just dont know where to start from.
I have two tables, membersdata and bankdata. These tables are linked in a relationship thou the field 'description' in both tables.
What I want to do is be able to input a date and have the fields I require from both tables displayed. the dates are stored in the bankdetail table. I have made a query that allows me to input the dates in the bankdata table and that works but I need the membersdata to be shown on this query also.
How can I get the query to show up all the data i requre from both tables?
This query will give me the information of people that have paid us in the dates selected. I also need to revese trhe query so it shows the people who have not paid. so Im basically showing the records with a null value in the description field.
This ones totally caved in my brain this time of the morning! Any help would be fantastic!
Cheers
Phill
Hi,
I want to make the default value of my column "TimeonOW" to be the difference in months(past 12) from the entered date in "OWStartDate" and the current system date.
For for example
mm/dd/yyyy
CurrentDate - OWStartDate = TimeonOW
07/01/2007 - 05/01/2005 = 26
I played around with the DateDiff function but to no avail.
=DateDiff("d",Now(),[OWStartDate])
=DateDiff("d",Now(),#2004-31-12#) etc
The reason behind this is because I need to count how many people fall into different month span categories, maybe theres a better way?
Thanks in advance.
Hi i have a field which is a date/time field and its format is short date which is xx/xx/xxxx. I want to ask if there is any way i can add a validation rule for only the year to be larger than 1980???
View 2 Replies View RelatedI have a table that lists classes available (tblCourses); one that lists class dates (tblDates) and another that lists employees (tblEmployees). I have a fourth table that combines that data into classes taken by the employee (tblTraining). I have a form to input the employee's training with combobox fields for the COURSETITLE and CLASSDATE. I want the CLASSDATE field to only display the dates available for the specific class that was selected. How do I do that?
View 1 Replies View RelatedI have a list of class sessions. To help with maintaining attendance, each session has its own roster. I just need to add a functionality so user can make sure the attendance roster is synchronized with the past sessions and future sessions in events of last minute additions/drop or updating names/contact infos. The sessions are related to themselves as below:
ClassID PreviousClassID
1 Null
2 1
3 2
4 Null
5 4
So therefore to propagate the changes into all sessions how would I instruct the query to look for each related ID then checks if it's also related...
I'm thinking I may have to use VBA to do what, but I hope that can be done with a append query.
Thanks!
Hey guys, new to access and from what I used to use it was simple seems a little more hidden or done differently in access. Its basic, all I am trying to do is match field value from products and another field value from mounts table and if they match on a new record creation in the products table then it auto fills in data on a third field coming from the mounts table. Seems like a simple relationship I come from using filemaker so I would just relate the two tables and if they matched on a new record it would auto fill in the value from any field that was related was point and click specify get value from there.. Figured it would work something similar, maybe im missing something.. Anyone answer this, im sure its an easy one.
View 2 Replies View RelatedI've been using an access d/b for ages, it's one of the template (Orders) databases included with access but modded to suit my own use. copy attached with customer details removed.
Last time I used it it worked fine but yesterday, when I tried entering order information, I get an error saying cant add or change a record because a related record is required in table orders.
Can anyone shed some light on the potential problem? Bear in mind I'm not an advanced user and have very limited knowledge on relationships in access.
Thanks in advance.
Marc
The "last" total in an Access query is unpredictiable. I have a Journal, with the main form/dataset being linked to the entries of that day. I would expect the 'last' total word to give me the last entry of that day, ie. what I ate for supper. But in the two qrys shown, it does the opposite. What is the most relable way to get the last entry of that day (ie. the one with the highest journalID)? :confused:
Sample Database attached.
Hi,
i need a query to return the first orderdetailsID and related productsID, so that all the other products that are the same as oldest order can be manufactured together.
In access help menu it shows it would be something like using the criteria under productname1 field:
(SELECT [productname1] FROM [the next one to cut query] WHERE min([orderdetailsID]))
But this returns an error cannot have aggregate function in where clause
min([oderdetailsID])).
I've also tried with 2 queries, 1 with the min function and the using inner join, but can't get it to work.
Thankyou
Before I spend an age trying to get this to work I thought I'd consult some experts.
I have a main data table for recording all possible insurance claims against a company. What I want to do is create say 2 incidents but link them in some way. i.e. One of our drivers had a car accident (1 claim against our motor policy), but then the driver of the other car sues us(1 claim against our public liability).
I can create 2 seperate records and maybe have a field to highlight parent/child reference numbers, but is there anyway I could allow the user to click a button and see the related records simultaneously?
Cheers
I have a form (data entry setting-yes) with several tab control-grouped forms. All the forms/underlying tables are linked by a field called ID. When I enter data in the main form, related records are only created in the tables for the subforms if data is actually typed into them as well. How do I force the tables to enter a record in the subforms' table, when only entering data in the main form even if the only field with data is the ID??
View 1 Replies View RelatedHi
I'm have created a video database with a genre field. Is there a way that when i click a button a list is displayed of all the records which are the same genre?
Thanks
Aden
Hi everyone!
My names james, im 18 and im a data manager for a school.
My schools database stores student data such as names, classes and exam grades. I have a table containing each students personal details such as name and address. Each pupil is assigned a unique PupilID which is the primary key. I then have a number of other tables linked to it with one to many relationships. Each of these tables holds a different set of exam results. The tables are linked through PupilID. The primary keys for the other tables are auto numbers.
The problem is this: when i add a new student to the Students table i cannot get their PupilID to automatically be entered into the other tables containing grades as part of a new field. How can i make this happen?
Thanks.
Hello
Could you help me ?
My problem is :
I'm developing a ms database for the a "repair service" .
I created a table witch contains basic data from customer, including its unique ID number (key field), name and contacts ...
Now I need to store the "repair service" data from each costumer ( its time consumption, replaced materials...) in a separate table but, If I'm thinking well, I must have a table for each costumer so, I will need to link each individual "repair service" data table from each costumer to each costumer itself.
Do you know how to do this ?
Lots of thanks in advance,
Miguel
Hello!I've one issue with relationships.Ok, first of all I got two tables "Persons" & "Vehicles" and there's primary key on both of tables called "Person_ID" and "Vehicle_ID". There's also "Person_Vehicle_ID" field in "Persons" table and "Vehicle_Person_ID" in "Vehicles" table. Rowsourcetype is set to "Table/Query" and rowsource is set to "Person_Vehicle_ID" to "Vehicle_ID" and "Vehicle_Person_ID" to "Person_ID".Ok, here's so called issue what I've noticed. I need to create somekind of dynamic connection between these two fields on different tables. Let me explain, when I change "Person_Vehicle_ID" value to 2, value in other field "Vehicle_Person_ID" would change too to equal that persons ID. So, if "Person_ID" value is 1 and "Person_Vehicle_ID" is set to 2, then that record "Vehicle_Person_ID" would be 1, where "Vehicle_ID" value is 2.(Hope you did understand me and sorry about my crap english grammar.)How would you build this kind of feature?Feel free to ask further questions, also waiting replies asap. :)
View 10 Replies View RelatedOk due to user error (and my own fault for not archiving an old database) we now have two databases which have had data entered into with the same table structure.
The table structure we have at the moment is with the related field in []:
Propertytbl[Propertycode] is related to Systemtbl[sysID] which is related to Assessmenttbl.
There is also another Temperaturetbl linked to Systemtbl by [sysID]
The problem i have is sysID is an autonumber. I can't simply paste in the difference in data as the assessmenttbl records and Temperaturetbl records will point to a different system (as the sysID will change in the Systemtbl data)
Can anyone think how i can do this either through using queries or programmatically so that i don't have to enter 5000 records manually.
I've tried using append queries but this produces the same affect as above.
Many thanks
Matt Collins
I have a combo box in my main form that gets its list of physician names from tblPhysician. When the physician is chosen and the record saved, it saves the PhysicianID to my main table, tblOncReg.
When I delete a physician, any record that had that name chosen continues to have that name in the record despite it being deleted from tblPhysician.
I thought that setting relationships would solve it, but when I select "Enforce Referential Integrity" and "Cascade Update Related Fields", I get an error saying "Invalid field definition 'ID' in definition of index or relationship.", and it doesn't set the relationship.
Would setting the relationship solve that problem?
I have two databases, one containing the tables and one containing the forms and queries. The forms/query database is built from linked tables from the tables database. I would like to expand one of the fields from 7 characters to 9 characters. I planned on doing the in the table database, then updating the linked table. However, the table I need to alter has relationships with about 10 other tables and I get the (you cannot make changes to a related table error message). Is there a way I can make this change without deleting all the relationships?
For future reference, should the relationships have been created in the form/query database?
Thanks, N
In a case where two tables are related, can the field names which relate them be the same? Or is it necessary to use different field names?
Robert
Greetings to everyone,
I'm building a database which will generate work orders, and those have a opening date and closing date, besides that it has a work order number that is of the following format:
YYYY/ NNN (being the NNN the work order number in that year)
to create the number of the work order I'm trying to fill the WOYear field
with the year of the WODate field...but I'm not being able to do it...
How should I do it?
Thanks.
I have a table that I'm storing Horsepower and Watt in. Is there anyway to set it up so that when I provide Horsepower it automatically populates Watts? (just a simple multiplication)
View 1 Replies View RelatedHi :confused:
I am trying to set up a query. i have to work out the cost of a phone call with the use of three fields--
i have: lenght of call, country call code-e.g england is +44(these are fields in my query) and then the cost per minute values for each country call code(but these are in a table)
e.g.Country CodeRegion Cost per minute
+1 United States, Canada, Caribbean£0.20
+20Egypt £0.30
+212Morocco £0.30
How would i set up a query to solve this problem?#Can someone please help me???
whats the best way of appending tbl_orders and the related data in tbl_order details to the two identical archive tables when the "COMPLETE" column is "YES" in tbl_orders
View 1 Replies View Related