ID - File Type - Source - Delivery - Return
1 - X - LB - ROY - LB via Roy
2 - Y - USA - Smith - USA via Smith
3 - Z - LB - -
my question is how can i make the last column (Return) to fill like the data above..what i mean if (Delivery) not null So the return will be filled like the above if the (Delivery) is null the return will be null too
-the user will fill only the columns (File Type - Source - Delivery)
-the column (Return) will be filled automatically if (Delivery) not null
I am new to Microsoft Access. I have built two entry forms for product release information. I have a drop box under the label customer for each specific customer. I would like for this form to send the data entered in the form to the customer specific table from the customer names in the drop box. I have tried to research and execute this myself thus have been very unsuccessful.
I am creating a task tracker where the user will enter a task which is working. If he is complete the task at that moment he will submit the task and everything is done for that task. However, if there is some issue or if the user need any information, then the user will enter a comment and will hit hold. My queryis once the user hits hold button, the table "Main" will capture the hold date and time. I need a second table, may be by name "Inbox" which will only show records kept on hold.
Secondly there should be hyperlink for "ID" of Hold records in table "Inbox" and when the user click on the the "ID" to resume the task, it should take the user to "Main" form and display that particular record (record which the user had clicked). Also, the date and time should be recorded when the user click on the hyperlink in Resume_Date and Resume_Time fields for that record.
Is there any way other than through coding/controls I can lock specific records in a table.
I have a reference table with less than 200 records on it. 5 and only 5 records need to be locked so that they primarily can not be deleted but also so they can't be edited.
I've read about locking it at form level but that, to me, means some sort of coding.
Hope someone can help me out on this one. Searched the forum but can’t find anything to help me out.
I have two tables: tblCustomers that contains unique customer records (names addresses etc.) and tblComments that contains comment data for the customers
Tables are joined one to many tblCustomers -> tblComments on account_ref
tblComments can contain several records per customer, all are datestamped.
I want just the latest datestamped comment record per customer, just can’t seem to work the logic on this one. Must be so simple but can’t fathom it.:confused:
Ideally, I’m producing a report of Customer data (Name, address, contacts etc) + the last comment record for each customer. Have started with a simple query, but have hit an impasse. Have tried sorting, group by, top 1, distinct etc, but don’t seem to be getting anywhere.:mad:
If someone can point me in the right direction, and give me a starter for 10 I’d be much appreciated.
So I currently have a table that tracks medication activity for patients called tblMedRanges. The Fields are the ID, StartMedication, EndMedication, Med ID/Medication Name.
In the End of Medication, some patients are not currently active so they have a set date/time field property. What I have for right now is the default value is set to
Code: =date()
So that when staff adds a new medication it is set to the current date. These patients are supposed to be active, but it doesn't update the default value. I want it to continue to update to the current date every time the database is open.
I have three options that I know of(or think I know of) to update but can't commit to one in particular :
1)I want these individual records in the field to update the current time for today and was considering using some kind of data macro code using before update.
2)The other idea I had was splitting up the field into an EndOfMedication field that would display a value only if their inactive, and an Active field (text, value list active/inactive choices) that would be used in a query to generate the current date as med field. This would take some crafty query work with comparisons later on to generate list of drugs that they were on for each visit.
3) set to text field and convert the field to date field for comparison in queries.
I Have a table that contains the fields: CAT, CHAPTER, ID, someSrting and Completed
CAT, CHAPTER, ID are numbers and Completed is true/false. If I mark a certain entry as Complted (true), I want all the records with the same (CAT, CHAPTER, ID) as the one I marked to be updated to Completed.
For example, If I marked the entry cat:1 chapter:1 id:1 as completed (true), I want all the entrys that have cat:1 chapter:1 id:1 to be marked as completed (true)
the code I wrote is:
Code: Private Pub Completed_check_AfterUpdate() Dim myCC Dim myCat Dim myChap Dim myID myCC = Me.Completed_check myCat = Me.CAT myChap = Me.CHAPTER myID = Me.ID CurrentDb.Execute "Update [my Table] " _ & "SET Completed = (" & myCC & ") " _ & "WHERE CAT = (" & myCat & ") And CHAPTER = (" & myChap & ") And ID = (" & myID & ") ;" End Sub
I'm running the code (my clicking the "check box") and nothing happens, I was thinking that maybe I defined the after "WHERE" statement wrong, and there are 0 entrys changed..
UPDATE: after removing the
Code:
"& "WHERE..."
row, the code does update the entire table, so I have a problem with the syntax of the sql..
Let's say I have a table sort of like this one: [URL] .... (Table 1)
What I want to do is make another table that references the first table: [URL] ... (Table 2)
I want the cells in the Average field in Table 2 to calculate an average of all the values for records in Table 1 with Color fields that correspond to the Color field in Table 2 (this makes a little more sense if you look at the pictures). I could do this in Excel, but then problems would arise whenever I would add a new entry to the database, or re-alphabetized the data, since Excel math is depends entirely on the positions of cells, and I want these averages to be continually calculated correctly and to change whenever I add related records to the database.
One more question... I'm loving Access... much more than Lotus Approach!! :-) Here's something I've wanted to do in Approach... how do I do this in Access???
REPORT showing all customers
1. John Doe, owes 9.00 2. Jane Kane, owes 10.00 3. Rob Johnson, owes 30.00 4. Albert Icar, owes 22.00 5. Johnny James, owes 5.00
Query all customers owing more than 10.00 (in this example it would be Rob and Albert).
In the report, I want Rob and Albert's name to be red font only, while everyone else is just regular black font.
Thanks to all posters who have assisted me thusfar, and anyone that can help with this.
so i have an interesting question and im hoping that someone can help on this one. i need to pull date from a specific table, no problem, that's written and working fine, next i need to be able to join the data from another table by a primary key, again no problem. third, i need to be able to select the date (using WHERE) for a specific date range. (i.e. i enter the date range of 01/7/2007 to 15/7/2007) and the query comes back only showing the data from that specific time, not the data from before or after. this is where my problem lies, all the entered data is being shown after entering my date range. i am going to include my SQL statement, just so you can actually see what im really talking about.
SELECT srealest.Name0, srealest.Dist1, SREpayments.Face2Pd, SREpayments.Penalty2Pd, SREpayments.[2paid], SREpayments.Face3Pd, SREpayments.Penalty3Pd, SREpayments.[3paid], SREpayments.Face4Pd, SREpayments.Penalty4Pd, SREpayments.[4paid], srealest.Map, srealest.Parcel, srealest.LeaseHold, srealest.TaxRebate1, srealest.TaxFace1, srealest.TaxPenalty1, srealest.TaxYear, srealest.BillNo, srealest.PdRebate1, srealest.PdFace1, srealest.PdPenalty1, srealest.DatePd FROM SREpayments INNER JOIN srealest ON SREpayments.BillNo=srealest.BillNo WHERE (((SREpayments.[2paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[3paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[4paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date])) Or (((srealest.DatePd) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date])) ORDER BY srealest.Name0;
any thoughts or ideas on how to accomplish this would be greatly appreciated!
In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code: Set objDialog = Application.FileDialog(4) With objDialog .AllowMultiSelect = False .Title = "Please select a File" .InitialFilename = "C:" .Show If .SelectedItems.Count = 0 Then MsgBox ("Action Cancelled") Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
I have a database containing 10,000+ trouble tickets identified by a unique field called "Ticket ID". This is the primary key for my table. (It's on just one table.) These tickets came from an excel sheet that was exported out of a different database. (For various reasons, we've moved the tickets to access.)
Among many descriptor fields, I have a field called "SPR Type" that shows what type of ticket each entry is. (Bug, Defect, Enhancement, etc, etc) The problem we have here is... not all of the ticket entries have an SPR Type listed on my access table because not all of the tickets coming out of the original excel sheet had one.
I was given a new excel sheet containing 400 to 500 ish Ticket IDs and the SPR Type that applies to each of those tickets. How can I take that sheet and use it to update my access table? Ie. Use the excel sheet containing Ticket ID and SPR Type to populate the blank SPR Type fields for each of their respective Ticket IDs?
**Update: I'm trying something like this...
UPDATE [SPR Priority] inner join [EB Update of SPR] on [SPR Priority.Ticket ID] = [EB Update of SPR.Ticket ID] SET [SPR Priority.SPR Type] = [EB Update of SPR.SPR Type]
But I am getting an error that says "Invalid Bracketing of name [SPR Priority.Ticket ID] and similar errors. What am I getting wrong here?
I thought about an Update query that would populate the SPR Type fields based on Ticket IDs. I could import the new excel sheet into a separate access table and update from there.
I'm able to import new data from excel just fine, but I can't import updated data from excel due to duplicates not being allowed for a particular field. Is there a way to keep from importing duplicate records based on one field, but still import data from other fields where the information is different from the excel file?
I have a complex database app that has a form called from the main form. It requires two inputs: BeginningDate and EndingDate and I use a calendar picker for date selection. Using data assigned to a variable, I build the SQL query in VBA. The result is:
Code: SELECT [1733_All Print Orders].[Application], Sum([1733_All Print Orders].[TotalImages]) AS SumOfCCPC FROM [1733_All Print Orders] WHERE [Application] = 'CCPC' AND [StatementDate] >= #9/3/2013# AND [StatementDate] <= #9/30/2013# GROUP BY [1733_All Print Orders];[Application]
[1733_All Print Orders] is a defined query that combines 4 tables together and there are data that falls within the dates for CCPC. But the query returns no records.
I pasted the query to the query builder and using different combinations, I isolated that the [StatementDate] >= #9/3/2013# portion is what returns no records
To complicate matters even worse, prior to today, it worked. I made some adds and changes to another area of the application, but did not touch this code.
How can I get a Query Criteria To Select All Records or specific records in query design section.
I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.
I have the following code in the click event of my button, Code:Dim strCriteria As StringstrCriteria = "[1stApproval]='-1'"DoCmd.OpenForm "FrmLookupRecords", acNormal, , strCriteria
However when I click the button, there was an error that says, Quote: The OpenForm action was cancelled. You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box. For example, you used the Close method to close a changed form, then clicked Cancel in the dialog box that asks if you want to save the changes you made to the form What does this error mean? I want to open a form that only shows approved records and the column "1stApproval" is a Yes/No field. Thanks for any help!
I'm sure this has a simple solution, but iv searched this forum and every solution that i get is filled with code that goes over my head! Well here goes...
I have a main form called expense... which contains a tab control. the tabcontrol in turn has three pages containing a subform each. (lets call them sbfrm1 sbfrm2 and sbrm3.) All three subforms are based on three different queries (say Qry1 Qry2 and Qry3) but the three queries are based on the same table. this table contains all the expenses incurred over the months across three categories (hence three queries). the subforms are to display these expenses according to categories.. i.e. sbfrm1 displays records pertaining to Category1, sbfrm2 for category2 etc.(the queries ensure that!)
when the main form opens, the subforms display all the records in the table according to category...but not according to the month in which the expenditure was incurred.
I now want to add a feature that enables the user to choose records pertaining to a given month at the click of a button.
for eg. if there is an option group named month, (with toggle buttons as the month names), then if the user selects the month Aug, then immediately the subform1 displays records under category1 for the month Aug? However I want to include an "ALL" option as well whereby all the records are displayed for all months (the category criteria must be maintained at any cost!!!! Is there a solution that will not use too much code!
I am working on a Human Resources database for a big project that has a field called 'Position Number' (eg. 290) which is unique for each position. But the position can have up to 4 different stages over the life of the project which is shown in the primary key field 'Position ID' (eg. 290-A, 290-B, 290-C). There are columns for each month of the life of the project and If the Start Date and End Date of the position are in between these months then a number one will appear in the Month column.
For example, position 290 will start at level A (290-A) and will start on Jan-14 and finish on Aug14. The next stage of the project will start and position 290 will do the same role in a different area of the project (290-B) and will start on Nov-14 until May-15. Therefore the 48 columns with months as headings from Jan-14 to Dec-17 will show a number one in those months from Jan-14 to Aug-14 and Nov-14 to May-15.
There are over 150 position numbers with up to 4 different position ID's which is phased throughout the 4 years of the project. I need to do a count of how many "number ones" are in the monthly columns per Position.
I have a staff database in my office which holds, amongst other things a list of staff holiday taken. Presently the staff have no access to view holidays because they will also be able to view other employees details. What I need to do is enable members of staff to see their own holiday, but no others and Im really not sure how to go about it.
I have a workgroup in place; so each user has their own login/password.
i've created a database for city permits to be issued (which works perfectly thanx to alll your help), but now i need to be able to print out 1 permit at a time when it is issued (immediately after the data has been entered into the forms). i have 3 tables & 3 forms: contractors/owners, permtOrders (which has tabs for the different types of permits), and permitOrderDetails. the problem is the forms are set up for data entry (and only open in add mode for obvious reasons)...i need to be able to print an actual permit (like the carbon copy, landscape kind that usually tears out of a book). once the user enters the information into the forms, i need a little bit of that info from each form to be carried onto the actual printable permit. i'm not really sure how to accomplish this. :confused: i don't really know if i explained this right, but any suggestions are greatly appreciated.
And sadly i am saying hello with a problem. I am a relatively new user, and i have a amateur(i think) problem.
Doing a booking system, and working in VBA I want to check if certain records exist by date. My "bookings" table has 'roomname' and 'date' and 'timeslot' as fields.
My query is: strSQL = "SELECT * FROM bookings WHERE (((bookings.room)=" & roomName & ") AND ((bookings.date)=#" & dateStr & "#));"
I need to get all those records, and be able do certain checks on them, but I dont know how to execute that in VBA, so i check the records to see which time slots have been booked.
In pseudocode: If(bookings.date == #xxx# AND bookings.timeslot == xx) then 'some sort of action'
In short how do i filter specific records using my query and how do i access record information directly in VBA? research suggests recordsets, but not sure how to implement that
Hi I would like to exclude two specific records from my recordset.
For example suppose I have a Flights Database and I want to see all the flight records for Aircrafts 132 and 232 only, between the date jan 1, 2007 to Sep 12, 2007.
The above I know how to do achieve...
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 and [FltDate] <= Sep 12, 2007)
However, among this set of records I want to exclude flight 132 on Aug 1, 2007 and flight 232 on Sept 1, 2007.
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 AND [FltDate] <= Sep 12, 2007) AND NOT ([FltNum]=132 AND [FltDate] = Aug 1, 2007 ) AND NOT ([FltNum]=232 AND [FltDate] = Sep 1, 2007)
I haven't been able to accomplish this using the query grid. Is it possible or do I have to write SQL?
Hi ,I have to make some specific records in a table in bold.Can any body suggest me how to make a particular record in bold or set some color fonts in order to high light the record. I have to design a report which shows some priority records with different font style in ms access
