I have fairly large table called QBInvoices that consist two fields- qbinv and date . Also, I have a report that showing info off query called TotalQueryQB. The table and query have no relations except dates . I need to show on report filed qbinv off table QBInvocies on the top of the reprot based on date. The user type date on form -frmQB text box txt Date and I would like to have contest of field qbinv off table QBInvocies on the top of the report base on date that was typed.
Hi, I'm making a form where the contents of a field is determined on the contents of another field in another form. I thought an IIF function would work, but when I tried it the contents says #NAME.
I want to print text based upon the content of the field but need more than the IIF statement. For example, if the field contains an "A" I want to print "Active", "C" I want to print "Closed", "N" I want to print "New", etc.
Thanks in advance for any help. You guys have always had an answer in the past!
I am in the process of revamping an old database. I have a main table that currently has two fields "Name" and "Job" but will have more. I have another table with the same fields. I want to use as a lookup to populate the main table with a user form from the second table. I can populate the "Name" field using a combo box, but my problem is how to autopopulate the "Job" field. E.g. If the user selects Mr Smith from the "Name" drop down list, the corresponding "Job" populates the second field.
I have two tables which contain the following fields (simplified);
Table 1: ID Startdate Enddate
Table 2: ID Unitdate Treatment
The tables are related (one-to-many) through the ID field.
In operation, the user first enters the Startdate and an Enddate for a patients' hospital stay in Table 1. Following this, the user enters the Startdate again in the Unitdate field in Table 2 and selects a Treatment from a drop-down list. After this, the user enters the following in the Unitdate field and selects a treatment. This is repeated in consecutive order until the Enddate is reached.
The end result looks like this;
Table 1: ID Startdate Enddate 123456 01/01/2005 04/01/2005
Does anyone know if there is any way to restrict the Unitdate field in Table 2 to only allow dates to be entered that are between the Startdate and Enddate fields in Table 1? Ideally, I would like the Unitdate to populate automatically with the dates, starting with the Startdate value and ending with the Enddate value and filling in all the intermediate dates. Also I would like to make sure that there is no way of having duplicate dates in the Unitdate field.
If anyone can help, I would be extremely grateful.
i am querying a single table that contains card numbers and corresponding transaction codes (amongst other things). a single card number can have multiple transaction codes.
i want to select all card numbers that have both 0100 and 0802 transaction codes.
if i use a select query with ="0100" Or "0802"as the criteria, it returns all card numbers that have either 0100 or 0802 transaction types. if i then change the query to crosstab and group by card number, it is clear that some cards have both transaction types.
when i try to change the criteria to ="0100" And "0802" it returns nothing at all.
i'm sure the solution is something really simple - any idea what i'm doing wrong?
I am trying to create a query in Access 2010. I have 2 tables BU and Color. BU has employee ID, the BU the employee is assigned as of a date. The Color table has the employee ID, a color and a date. Based on the date in the Color table, I want to select the BU from the BU table. The query should bring in the BU that the employee was assigned to as of the date in the Color table.
For example, if emplid 12345 was hired into BU abc as of 12/31/2013. Emplid 12345 was transferred to BU xyz on 1/15/2015. In the Color there are 2 rows for 12345. One with a date of 12/31/2014 and one with a date of 2/15/2015. I want the query to return abc on the row with the 12/31/2014 date and xyz on the row that contains 2/15/2015.
Attached are the two tables. I have highlighted the expected results in yellow on the Color spreadsheet.
Hello, I'm working with SQL and databases in general for the first time, and was wondering: how would I select just the most recent entry for each device? my data looks [roughly] like this:
I have a main form which has 4 combo boxes all based on tblPeople. Combo0 is skill Combo1 is discipline Combo2 is crart Comb03 is active
The sub form is a datatable list of the tblpeople.
How do I apply the filter to the datatable. I am assuming I need to build a sql statement somewhere like SELECT from tlpeople WHERE me!combo0 = [tblpeople].[skill] and me!Combo1=[tblpeople].[discipline] etc
Is there a sample database? or can somebody tell me how to go about this.
I'm working on a table which has a country field, but this field may contain a text string consisting of more than one country, eg "France, Belgium, Spain"
I want to run a query against the table to select records for any one country, but not sure how to do this.
Table1 has fields docno (text) title (text) progress (number)
sample data is like below:
doc-001 test1 90% doc-002 test2 25%
Table2 has fields
sample data is like below:
id (number) icon(attachment type)
1 image1 2 image2
Now I would like to make a query when progress of each docno is equal and more than 50% it uses image1 And when progress is less than of 50% it uses image2
sorry for the title. i didnt know what to write. this is the situation:
i have a form where it populates a table in access (ssmdata) from an oracle table w_ssm_data). this works perfectly.
Then the form read the hole new table in access (ssmdata) to update a specific field.
Problem: If i open the access table (ssmdata) it is sorted by the flight fields. However, when the form reads the table in order to update the specific field the table seems to not to be sorted by the flight field.
More info: The ssmdata table in access has many primary keys (flight, departure,arrival,caterer,frequency and aircraft).
If i take a look to the W_ssm_data table in oracle it is not sorted by flight and it is sorted in the same way the form is reading the ssmdata table in access.
example: table in access: flight dep arr caterer aircraft frequency 1 a b a 111 67 1 a b a 222 134 2 c s c 111 5
table in oracle: flight dep arr caterer aircraft frequency 2 c s c 111 5 1 a b a 111 67 65 t h t 252 1
when the update step starts it reads the access table as it looks in oracle. but if i open the table in access it looks sorted by flight.
Hi, I am trying to copy the contents of a table with 11 fields into a new table which is the same except for an additional two fields at the end. Any advice would be appreciated.. Thanks..
Any way in MS Access to read the TOC data from an Audio CD? It is I believe in the lead-in part. I have a music database, and would like to be able to read the track data directly from the CD (Title, artist, timing), without having to type it all in again.
I know this can be done, because my CD labelling software extract the data automatically - but can Access do it?
I have some code to create a table of contents/index for a report which has "chapters" which can be added or dropped depending on whether we choose to stock something or not. And I'm getting the strangest issue. The very first "chapter" starts fine on Page 1. But the next item we sell (Chap 2), then starts on Page 3, instead of Page 2. Here's what's strange. Every other item is then one page off. Below is my code.
Code: Option Compare Database Dim db As Database Dim TocTable As Recordset Dim intPageCounter As Integer Function InitToc() 'Called from the OnOpen property of the report.
[Code] ....
The InitToc fx is called upon the opening of the pricebook, the UpdateToc fx is called upon printing. There's something in here where I think I'm calling the functions incorrectly, but I'm not sure quite what the error is.
I have two combo boxes on the same form bound to a table. I want the contents of the next combo box to change based on the previous combo selection e.g
cboContinent cboCountry Africa Zambia Africa Congo Africa South africa Europe England Europe Holland
If I choose Africa in cboContinent, I want to see only Zambia, South Africa and Congo under cboCountry and if I choose Europe I want to see only England and Holland
This may seem a very simple question, but I really don't know how to do it. Basically, I want to MessageBox contents of a table with a button. I have a table called Map, and has two fiels: Column Number and Description. When a button is pressed, then I want a messagebox to come up and show the column number and the description of a single field. Although I know it is very wrong, here is the code I used for this (r![column Number] = 4 is meant to be the forth column number in the table):
Dim d As Database Set d = CurrentDb Dim r As Recordset Set r = d.OpenRecordset("Map")
I am creating a report which is about 200 pages and would like to add a table of contents for each category i have with page numbers, however i have googled and tried many different things but to no avail and what it the correct way or even showing me on a sample database.
I would like to ask if it is possible to use the contents of a table as a comparator? For example, I have this column in my table that has the schedules of the professor, if the professor for example failed to login within fifteen minutes after his/her supposed start of class a message will be displayed on the screen notifying that the professor has not yet logged in.
I have a field called "Bad Pc Part" which is a listbox. THe problem is I want it to display the contents of 3 other fields from another table.
I created a query for the Bad Pc Part field but the problem is it only displays the information in the first field of the table. It doesnt show the contents of the other columns. I need it to do this.
I have a combo box containing "ProgramType". If "DDI" is selected from this combo box, I would like it to open up another combo box containing the contents from "tblDDI". Then I would like the selection to be transferred to "ProgramType". Is this possible?
I have a parameter that I need to get a table contents between dates. In the query:
Birthdate: XXTable: criteria as follows: Between [Enter Start Date] and [Enter End Date:]
When run it gives me the Error - ! This expression is type incorrectly, or its too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables
I have tried almost everything. The formatting of the Birthdate is x/x/xxxx or shortdate. Will this affect the input thus affecting the outcome of the query.
I am very new to access less than 1 week since i started trying to build an horse racing database, i am trying to link data from 2 different sources via the horses name however one source displays this with the horses country of origin in parenthesis foe example FRANKEL(GB) and the other source displays the name as just FRANKEL, to be honest i haven't yet tried the link but guess it will fail.
I am therefore looking to get rid of the parentheses and their contents from an access table field and create another field without them.
In Excel i use the formula B1:
=TRIM(REPLACE(A1,FIND("(",A1&"("),FIND(")",A1&")")-(FIND("(",A1&"(")-1),"")) and that works fine.
I guessed the solution for Access would be newname:
