I have data in a table
I am extracting using an append Query, I wish to assign a number from the original table +1 to the records pulled by the append query.
I am very new to this and have the expression below, however the result is #error in created field CertifNumber
The data I am looking for is in table Sample Details and the Field is CertNo
umm.. i have a form with a combo box which is linked to a table which gives me customer id numbers. and i have a listbox which i want the names and addresses to appear when i select a cusotmer id number from the combo box
so on the list box i put " SELECT CustName FROM Customers WHERE CustID=$combo43; " in row source; if i change $combo43 to 0 the name appear but that is fixed and i want it 2 change when i select a cusomer id number from the combo box
im trying to select data from an access database via asp. the data must be stored in a recordset and fit the criteria as given in the variable varItemSelected:
heres what i have
SQLstmt = "SELECT * FROM Products WHERE product = " &_ varItemSelected
set rs = con.Execute(SQLstmt)
but i get the error: Microsoft JET Database Engine error '80040e10' No value given for one or more required parameters
I have a database which records time spent on various projects and I would like to be able to calculate the time spent on any particular project between two selected dates. The report and the query behind it already works to display the time accrued on all the dates of a project, but I want to be able to break it down to small ranges of dates
I have a form on which the user selects the start date and the end date. How can I apply this calculated information as a criteria in a query so that I can produce a report with the relevant information on it.
I already have the report and the date selection form designed. The dates selected go into two unbound fields which have a date format.
Each record in the database has a subform (one to many relationship) with multiple records. When the user clicks on a record in the subform I want the information from the subform and from the mainform to go into a Word document. I am able to pull the data from the main form but not from the sub form. Any suggestions would be appreciated. Below is an example of what I am using to get the data out of the main form for the bookmark in Word called "ClientName".
Hi, I have 13 tables in all. 2 are Area and Scope which have unique entries of areas and scope and the remaining 11 will have a primary key, Tag_No. I want to select data from these 11 tables such that ALL the data will be pulled out but say table 1 has a column MOC and remaining 10 tables dont, then it will be a blank or null in those columns for the 10 tables. How can this be done? (I hope I am not confusing!!!) Adwait
I am having trouble writing what I think should be a fairly straight forward query... I am have a table with 2 fields, the first contains a list of subjects, the second contains a list of scores that students got in their tests in that subject.
So the data looks like this:
Maths 54 Maths 69 Maths 41 English 71 English 55 Art 44 Art 43 Art 66
I would like to write a query that selects only the highest score for each subject and presents it like this:
I am constructing a database for a friend and need to filter data from a daily log to enable a report to be generated for each months actions. I have created fields of: entry date, month and year with the view of filtering by month and year. How do I perform this task?
I would like to create a excel file to let the user to input the following column.
Date No. of success No. of failure ==== ============ ============== " " " " " " " " "
I would like to have a function that can allow the user to select the date range and its total no. of success and no. of failure
For example , the user select from 31-03-2001 to 31-12-2012 , then output the report which show the total no. of success and no. of failure in this date range , is there existing tamplate that I can use ? if no , what is the simplier way to do it ?
if the data is very large , I want to create a access db to store it , how to do it ?
I would like to create a excel file to let the user to input the following column.
Date No. of success No. of failure ==== ============ ============== " " " " " " " " "
I would like to have a function that can allow the user to select the date range and its total no. of success and no. of failure. For example , the user select from 31-03-2001 to 31-12-2012 , then output the report which show the total no. of success and no. of failure in this date range , is there existing tamplate that I can use ? if no , what is the simplier way to do it ?
If the data is very large , I want to create a access db to store it, how to do it ?
I have an on click event to mail a report which works. I want to change the text to include data from a table.
I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.
Code is : Private Sub cmd_mailreport_Click() Dim office As Object Set office = Checks.office DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _ "info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _ True, "" End Sub
I have with my database. It's holds cost data including purchase made in foreign currencies which need to be converted to GBP using the correct exchange rate so a variety of reporting & stats can be performed.I have a table called Costs within which there are 2 fields
Purchase Currency Exchange Rate
I also have another table called Exchange Rates 13/14 within which there are 2 fields
Currency Exchange Rate
When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.I have tried the following and none work:
SQL Tried
1. UPDATE Costs SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate] WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
2. UPDATE Costs SET ExchangeRate = [Exchange Rate] FROM [Exchange Rates 13/14] INNER JOIN [Exchange Rates 13/14] ON Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency
3. SELECT [Exchange Rate] FROM [Exchange Rates 13/14] WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate.
Or is the alternative to put this into the calculation of GBP Unit Cost where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.
I have staff table which is a lookup field on the phone call table.when the phonecall form opens I want the employee field to auto fill in the employees name from the lookup field according to there security level.so something like this
On Open Select Case Forms!frmLogin!cboUser.Column(4) Case X = 4'the employees security level' Case X = Insert Table_tblPhoneCalls!EmployeeID"4" into Form_frmPhoneCalls!Employee. End Select
of course this is a syntax error as I do not know the correct code words.
I created a subform that displays the records of a select query after selecting the desired data from a single combo box (for instructors name) and then using an OpenQuery macro attached to a button.
Only 1 record is displayed in the subform each time I hit the button. If I look at the query return there is no change. However, if I close the query and select a new instructors name, although I only get one record in the subform, the query when opened shows all the correct records.
Okay...I know the basic syntax for Dmax (or at least I thought I did) but I cannot get this statement to do what I want!
In the <before update> event I tyoed this:
Dim RNum As Long Me.txtRNumber = DMax("[Request_Num]", "test_request") + 1 Me.txtRnumber = RNum
txtRNumber is the field on the form Request_Num is the field on the table test_request is the table
yes...I know the name syntax is bad (it's an older db I'm using as a test for a newer one).
Obviously, I want to automatically increment the field value by 1 on every new record (an if newrecord clause is included). But I just can't get the stinkin thing to work!!!
I am having difficulty understanding under which circumstances I should be using Max() as opposed to DMax() functions. Both seem to do the same thing (find maximum value in a field), but DMax has additional arguments.
What I currently want sounds like neither of these, as I want to query the maximum of several values in various fields for a specific record, rather than the maximum of several records in a specific field. Is there a way of doing this otherwise than by nesting IIF functions several deep?
I keep running into "Microsoft Office Access has encountered an error and needs to close" error messages, and am coming around to the view that I may need a UDF. I have never done one in Access before (but did some time ago in Excel).
Currently I have fields in various tables: T_Clients.Commencement T_Clients.Cessation T_Tasks.StartDate T_Tasks.EndDate
There is a one (T_Clients.ID_Clients) to many (T_Tasks.ID_Clients) relationship T_Clients.ID_Clients and T_Tasks_ID_Tasks are primay keys.
In "pseudocode" my query, probably via a UDF, needs to return:
Commencement and Cessation may contain Null values, which are then to be treated as #1900-01-01# for the purposes of any comparisons.
My first attempt at this was to create several queries, each based on the previous query. Inefficient, perhaps, but I could trace the logic. I started by creating queries that substituted #1900-01-01# for null values in the Commencement and Cessation fields.
However when I got about 3 queries deep I ran into the "Access has encountered an error and needs to close" error, and no amount of playing around with it stopped that.
I don't know if relevant but I don't *really* have a T_Tasks.StartDate field. I have a query that calculates the start date from the previous EndDate, but I did not want to complicate the problem specs. That query seems to work ok. If I get a solution to the above I hope to jigger it to fit.
Hello there, some help would be nice! Say i have a booking field. If people wanted to book a number of seats, how would i make it so that when the number of seats that was entered on the booking form was entered it would add on to the exsisting number of seats taken?
So say for film A There is 20 seats available and 4 of been booked, then someone wants to book 2 more, how do i make it so now, the number of seats taken shows up as 6 as apposed to 4?
Hi everyone, My name is Stuart and I am from the North east of England. I am a total beginner in using access. I am a bird of prey conservationist involved in numerous breeding projects for endangered species of raptors. Currently I am looking to set up a breeding programme which will involve a daily report log involving diet, weight, activities, equipment used, etc, etc.
What I want to do is create a table whereby each day I create a new form with its own unique incrementing reference number. Say I create a form today and it is recorded as 000001. Later on today I may create another form with all of the same information fields but with varying levels of information. I want that second form to be recorded as 000002. Tomorrow I may create a third which is 000003.
Is this a process that can be created in access and if so would you know how to do it? I would greatly appreciate your help if you could, please.
I have a table within which I store data showing the results of tests. I wish to create a certificate(as a report) which displays these results. The certificate should have a unique number which is then stored with the original record within the original table, called 'Sample Results' I have created an append query(CertDetailsQuery) to pull the appropriate date into another table(CertDetails), using criteria etc, however I am struggling with the creation of a field which creates the unique reference for the certificate. I have tried autonumbering inthe "CertDetails" table but this gives a unique number for each record selected by the append query, whereas I want each record selected to have the same reference (as they will be on the same certificate together). I hope this is clear, help if you can,
I am adding two records to an empty table using an append query (or make table query). I would like the first record to have 1 in the ID field and the second to have 2. I will then delete 2, append 1 elsewhere and empty the table and start over again.
Any suggestions? It needs to be 1 and 2 each time not just an autonumber.
I want to create fields that are in the form IC0000001, IC0000002, etc. I can do the increment in the numbers by using the dmax function but how do I add the alphabets before it? How can I start with 0000001 instead of 1? Any help would be greatly appreciated, thanks so much!
