I have a tblQubit:
1234
5678
9876
Is it possible to use DLookup to pull the Lot in-use:
Code:
=DLookUp("[Kit]","tblQubit","[Inuse] = -1")
But when that lot changes it only applies to new records and the previous lot remain unchanged?
So if records 1,2,3,4 used lot: 1234
Record 5,6,7,8 lot: 5678
Records 9,10,11,12 lot: 9876
I am trying to add an attendance records to my database but cannot figure out how best to do it..I already have a 'children' table were all the kids info is stored and have created a 'roll' table.
i want to be able to open a form and search first and/or last name from the 'Children' table, then be able to save both first and last names and the date into the 'Roll' table. (then open reports etc later based on dates)how to pull data from one table and store it in another.
I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.
Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.
Name EE_ID JOB CAT EFF_DATE
John Doe 1006 CLERK F 01-JAN-2010
John Doe 1006 P 21-JAN-2010
John Doe 1006 CLERK P 01-FEB-2011
John Doe 1006 F 01-MAR-2011
John Doe 1006 P 01-APR-2011
John Doe 1006 CLERK F 01-JUL-2012
What is the best way to pull all records in a rolling 365 day period. Like from 4/3/2013 to 4/4/2014?
View 2 Replies View RelatedI would like to know if it is possible to pull the max and min start date and end date. Basically i have an option where by the user selects the desired date range that they would like.
Now I would like to know how to get the earliest and latest date range should they not select a date range. My date range is being displayed at the top of my report.
I'm looking for VBA code.Basically I have a drop down with 3 options on a form but need it to pull up a window to input depending on which option is selected. One selection of the options if selected will not require the input of data, as it can just input a set value or be left blank (haven't totally figured how I want to display it). With all this I will need it to display on a form in a certain format, in which I would like the drop down value displayed with the data inputted, though with the one option without data just would display drop down value.
View 4 Replies View RelatedI am looking into re-creating something I have created in Microsoft Excel into Microsoft Access.What I do in the Excel spreadsheet, is when I type in an order number, various fields in the spreadsheet are populated using vlookups and ODBC's.
I wish to re-create this in Access but not sure how. I have a basic form, with various fields such as, order number, part number, description, quantity.What I have in my mind is that I would like to be able to type in an order number, then the rest of the fields would be populated by a query to a SQL Server database.i can see a way to do this using VBA but would rather look for a more direct way if possible.
I am trying to create an expression to pull data from 2011 to present date. I need the data for 2011 to only reflect 1/1/11 through 10/7/11 (today's date in 2011). I need the same for 2012 and 2013.
I don't want to have to enter dates each time I run this, therefore, a formula would be preferred rather than hard numbers.
I have a database that pulls images from a shared folder on our network with links. Is it possible to create a button to email the current record on a form and attach the file it references in the link?
The attachment is a pdf signature. I didn't want to include the files in the database for space issues.
I am using Access 2007. We out outlook as our email client.
I am having an issue with trying to display text data "Supplier-ref-1" from a Table called "Supplier Prices" which also has the "Item-code" field. I want to display it in a text box in my "Items" form, where the Item-code is equal to that in the Supplier-prices
=DLookUp("[Supplier-ref-1]","[Suppliers prices]","[Suppliers prices]![Item-code]='" & me.Item-code & "'")
Everytime I enter this into access, it modifies it to:-
=DLookUp("[Supplier-ref-1]","[Suppliers prices]","[Suppliers prices]![Item-code]='" & [me].[Item]-[code] & "'")
I want to use a variable as FieldName in the DLookup function.
Normal DLookup:
Code:
DLookup("FieldName" , "TableName" , "Criteria= 'string'")
I want to use:
Code:
DLookup(variable, "TableName", "Criteria= 'string'")
Where variable is an integer, which represents the column number in the table.
Is this possible?
I'm having trouble with DLookup() using dates as part of a multiple criteria. I'm using the following;
Code:
If IsNull(DLookup("ExchRate", "TBL_DDPExchRates", "CurID = " & Me.Combo4 & " AND ExchDate = #" & Me.ShipOBDate & "#")) And Me.Combo4 <> 2 Then
It seems that if ShipOBDate is any date between the first and tenth of the month the DLookup fails to locate the appropriate record. I can't understand why as ShipOBDate and ExchDate are both formatted as Short Date and ExchDate is being populated via an OpenArgs which is derived form the field ShipOBDate..Is the fact the the date is getting converted from a date to string and back to a date some how upsetting things
I have a 2 unbound text boxes.1 populates the id number from a table.I want the other to display the first name where this id occurs. I am currently putting this in the control source of the seconded textbox
This is what I have
=dlookup("[FirstName]","[tblUser]","[ID]='[Textbox1]")
I have a form with multiple combo boxes. Once the combo boxes are populated, I need the text box to look up the value in the query based on two combo box selections.I am attempting to do a DLOOKUP, but I am obviously not doing it correctly because the result is #NAME?.
Here is what I have done: In Default Value section of the txtONE property sheet: =DLookUp([fieldTHREE],[qryMAIN],[cmbONE].[AfterUpdate] And [cmbTWO].[AfterUpdate])
I have this payroll system:JJAEPAYROLL.accdb
In the "qryEmployeeWTax" i need to Lookup the [InitialTax] from the "qryreftbl_WTAX" based from the [ETPES] and [SalaryAfterStatutoryDeductions], so i am using this function:
Code:
InitialTax: DLookUp("[InitialTax]","qryreftbl_WTAX","[ETPES]=" & [ETPES] & "AND [ValueRangeStart]<=" & [SalaryAfterStatutoryDeductions] & "AND [ValueRangeEnd]<" & [SalaryAfterStatutoryDeductions])
But it doesn't work
I am able to use DLookup successfully using 2 criteria s in my "qryEmployeesStatutoryInfo"
Occasionally a user of mine needs to reset a sequence number in a table.
A few months ago I made him a simple app that has two text entry boxes and a button. The first text entry box uses a dlookup:
Code:
=DLookUp("sSessionNumID","qryGetAPIsessionLastValue")
...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.
Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.
Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.
What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).
It's almost like the dlookup falls asleep now and won't wake up until the update is run...
I have a simple data entry form based on a table. However I have a few fields that I do a lookup in a field on the form from a query, and yes I know I should not have a lookup in the control source however, this is the way that I will be doing it on this occasion.
=DLookUp("[Salary]","[Salary Query]")
How I get the value from this unbound field to enter into the actual field in the table. Do I bring the actual field into the form and hide, and do some sort of after update, as I have tried and it does not work.
I have called the unbound field with lookup "Salary Level Base" and the actual field in the table is "Salary Base".
here I have capture what is error actually when i make a DLOOKUP wizard.the box of wizard. there i want to make a relationship with table2. but we could see that table1 still show. yes right, it's reality making error and show message ""you can't modify the structure of table, because it is already in use by another person or process"and warning message ""Microsoft access couldn't launch the lookup wizard, or this wizard has been disabled...blabla...blaa"
on my laptop, ms access 2010 nothing happen the mattter and when i make DLOOKUP, parent table have been not seen.and make DLOOKUP Wizard is successfull.
why in my PC, when i make DLOOKUP wizard, parent table still show?what is this bug for MS access 2010?
I have a report with quite a few subreports in it. There are a number of calculated fields on the form, most of which use Dlookup to retrieve at least one of the figures required for the calculation. The Dlookup runs fine and the report opens but the calculated fields are devoid of data in Report view. When I switch to Print Preview view the fields are now populated. Below are two variations of the DLookup syntax I have used to try and alleviate this issue.
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[sub_AllHours].[Report].[8]
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[Reports]![rpt_AllFigures]![sub_AllHours]![8]
Note that the field [8] specified here is simply a month number and forms a column in the crosstab query for the corresponding query name.
I would add the query referred to in the DLookup to the source query for the report but the source report's data is derived from a Crosstab query, which only accepts one data field (Access terms this as the value field.
I have a data source table with names, addresses and UK postcodes, and another table that has partial postcodes (the bit before the space) and I need to look up the local authority for each postcode. I created a front end that links to both tables and tried converting the full postcode in one table to the first 4 characters and the partial postcode in the other table to the first 4 characters too, using two separate queries, both of which give the right results. Then I used a third query based on both of the above to match up the records from both queries, but I get a "Data type mismatch in criteria expression" error. I am just using queries on tables because I'm not very au fait with VBA and so on. Any ideas, anyone please? (I can happily email all to anyone who wants to look.)
View 4 Replies View RelatedI need to know a way to make to perform the following tasks
When I choose a selection from the first pull down it will fill the next pull down list with different values depending on what I choose on the first pull down list
:eek:
any suggestions?
Hi,
I'm totally newbie so please go easy on me, I finally learnt how to make database and tables by reading online tutorials. I'm now able to create a simple database in access, using tables and stuff. I want to ask, say i put 500 entries in my database and then i want to search for some specific data, how do i do that?
Say I'm making a database of model's agencies, and i want to pull all the models working in same agency and stuff like that, how do i do that. Do i have to create queries or forms? i'm confused, please help.
Tania.
I am trying to have pull down information on my forms so I don't have to reimput my data twice, but I can't remember how this is done.
I am working on a database for my school and I need to input the names once. Then after just the marks.
Any good tutorials on access?
Thanks::cool:
I would like to build a query and ask it to choose a boat name out of a list of pre set names. When the pop up box appears to type the name, i would like it to be a combo box where the name can be pulled from the list. can this be done? thanks in advance.
- Andre'
Hiya all,
I know the title is confusing, and so is the problem (to me at least). Basically, I have a database with an invoice form. Each record is an invoice, and on this form there is a subform of additional charges. The tables are linked by InvoiceID. It worked smashingly when I was playing around with it, until I finally tried an invoice with no additional charges. The queries for my reports display no records (because there are none). This is an issue, because I need a summed up amount of the charges to complete the invoice. I cannot do this on the report, because additional expressions are used on the charges.
Basically, my problem comes down to this: I have no additional charges, so certain fields in my query don't exist, therefore the entire query record won't display. I was screwing around with NZ() and IsNull() before I realized that a null record and the absence of a record are different.
I've been searching, but can't seem to find the right keywords to produce a fruitful search. If anyone can point me in the right direction or give me some words of advice, I'd greatly appreciate it. And yes, as far as I know, my database is fully normalized.
I was wondering was it possible to have my queries in a pull down list and I hit a button and the query/report is ran? Or do I have to stay with about 6 or 7 buttons on a form?
I know it sounds crazy.....