I have two forms...frm1 has a text box with an expression in it and I need frm2 to display the result of the expression. I'm using the DLookup expression and it either gives me #Name? or #Error? message in the text box frm2.
=Dlookup("[loan#]","tbl_loan","[Days] =" & Forms![frm1]!Days) <that gives me #Name? message
Hi,
Im getting an odd result returned & I don't know why.
expression:=[lookup]*[yards]+[drop]:=0.296*30+-11.838
Result:=1.20E-27
If I replace value [drop] with -11.838 then the result is correct (-2.958)
ie:lookup]*[yards]+-11.838
The porblem seem to be when [drop] is neg.
I am new to MS Access, when I am making any calculation using some expression, then I have to save the result to table, it is not saved. Please anyone suggest the solution for my problem..... and mail at rupedhiman@gmail.com.
Rupinder
Hi,
I have a DLookup expression that is working in a Control on a form :
=DLookUp("[Class] ","Class","([Forms]![Orders_Crosstab]![Indcat] = [Clind]) AND ([Forms]![Orders_Crosstab]![2005] Between [From] AND [To])")
I simplified it for test purposes as an expression in a query:
Expr1: DLookUp("[Class] ","Class","([Indcat] = [Clind])")
This gave an error - MS Access cant find the name [Indcat] in the expression.
After some R&D on the web I found a solution that works:
Expr1: DLookUp("[Class] ","Class","([Clind]=" & [Indcat])
[Indcat] and [2005] are part of the recordset where [Clind], [From] and [To] are values in the Table 'Class'
MY CHALLENGE:
I cannot find the correct way to add the extra selection criteria to the query expression (as per the form expression above) that checks the value [2005] is between the [From] and [To] values in the table. Have tried a number of combinations without success.
Any help, suggestions and/or guidance very welcome.
Regards
Tony Randell
I've got these expressions in a query to extract parts of an mp3 file full path from a field and just let the name of the song.
exp: Mid([imported];[exp3]-[exp2];Len([imported]))
exp2: InStr([imported];"")-4
exp3: InStrRev([imported];"")
exp4: Left([exp];Len([exp])-4)
"exp4" is the clean name of the song.
Now how can I update my "song name" field (which is empty) to be same as "exp4" . Is it an update query? If so how can i do it?
i am trying to executed q query which has a Nz function. this works fine when exceuted from access. but when i try to executed the same from Vb i get an exception
"Undefined function 'Nz' in expression"
can anyone point out why this is happening? and wts the solution?
thanks in advance
ASMS
I have a form based on query. On form i am retrieving data from another table using DLookup in a unbound text box. So I want to save the result of DLookup function in another field/table on same form.
View 8 Replies View RelatedI've never used DLookup before and I can't get it to work for me so far.
I have 1 table which contains products and different properties of each product, such as the weight of the product.
I have created a query which sums the weight of all products, but only for those that have a value >0 in a certain field. This all works fine.
Now I simply want to display that calculated total weight in a text box on a form. So I thought DLookup could be used for that. But I can't get it to work, maybe because I'm not putting in any criteria? In the control source of the text box
I've put the following:
=dlookup("[TotalWeight]","qryTotals")
I don't have any criteria, I just want the value from my qry expression. The textbox on my form now displays #name?
1. I have a table called "CONTRACT NAMES AND NUMBERS" with a field called "REDUCED_USERS", this field is a checkbox (Yes/No in the table). This table houses all of the customers with their id numbers and basic info.
2. I have another table called "REQUESTS" which houses their orders. This also has a field called "REDUCED_USERS".
In my form "Amendment Request Tracking" I have tried to do a DLookUp in Expression Builder to check the box, per order, if the customer has reduced users in the "CONTRACT NAMES AND NUMBERS".
I have tried many variations and have just realised that this is probably because it is a yes/no field so may struggle with what to populate with (currenly nothing!).
My most recent variation of expression is (where NAD_NUMBER is the common field in both Tables and Form with relevant relationship):
=DLookUp("[REDUCED_USERS]","[CONTRACT NAMES AND NUMBERS]","[CONTRACT NAMES AND NUMBERS]![NAD_NUMBER]=[NAD_NUMBER]")
I have a field created in a query expression
BirthMon: Format([DOB],"mmmm")
I would like to create another field in the query using DLookup to return the value of the BrithMon.
BrithMonthID=DLookUp("MonID","tblListMons","BirthM on = Mon")
This works good if used with an unbound text box on a form, but when entered into a query expressions, an error is returned: cannot find the name 'BirthMon'
Can I used DLookUp in a Query expression to refer to another Query created field?
I have an asset database I am designing to manage our computer inventory and assets.
I am trying to get a DLookup to work with one of my forms that will auto-populate some of the fields depending on what is entered in to the ProductID field. For instance, Make, Model, Asset type...
My problem is that the string that returns contains special characters, specifically "#" and gives me the error message -
Run-time error '3075':
Syntax error in date in query expression 'productID=EN371UA#ABA'.
My expression is definitely working, it just looks like it things it has something to do with date/time which it does not. Unfortunately, most HP equipment contains a # in the Product ID number.
Here is my expression -
Private Sub ProductIDCombo_AfterUpdate()
Make = DLookup("Make", "productlist", "productID=" & [ProductIDCombo])
End Sub
Make is the field I am looking up from the ProductList table. The Product ID is the ID I'm looking up from the ProductList table to find the make. My problem is actually getting it to return the correct value of "HP or Dell or Lenovo". etc.
Hi All,
I am trying to display the result of the difference between two boxes in a new unbound text box.
Basically, I have a box called "Initial Downtime" --> display the system downtime
Than another box called "uptime"--> display the time the system cam back up.
I would like a third to display the difference between the two time
Any ideas on how to do this in a form?
Any help will be gratelly appreciated
the [EFS] and [CB] is one of the team for handling the project but after I execute the query as below , it just show -1, 0 as the picture but they are orginally Yes/No box from the table, how can I display the result as a Yes/No box instead of -1 ,0
--------------------------------------------------------------
SELECT Projects.[Project Name], Projects.[EFS],Projects.[CB]
FROM Projects
WHERE (((Projects.[EFS])=Yes) AND (([Forms]![A]![EFS])=Yes));
UNION SELECT Projects.[Project Name],Projects.[EFS], Projects.[CB]
FROM Projects
WHERE (((Projects.[CB])=Yes) AND (([Forms]![A]![CB])=Yes));
Hi,
I'm trying to work out a formula in a query.
At the moment it looks like this:
Days on Hold: calcworkdays([on hold date],[off hold date])-1
Now i have a module thingy set up (calcworkdays) which works out working days. What this expression does, when theres an on hold date and an off hold date is work out how many working days something is on hold.
Now, problem is, not always is something on hold, therefore fields are often blank and then i get a result in query that says "#Error", but i want to use the answer to this expression in another formula, but when error is displayed it makes the other query show error too.
What I want is some sort of If statement or similar so that if no results exist to display "0". Can anyone tell me how to add this in?
I'm a total Access Noob, it took me forever to work out this working days thing (damn access for not being as simple as excel!)
I think my problem might lie in the way the function has been written. I think i might have to modify this to show "0", rather than "error"
This is what ive got in the function.
Public Function calcWorkDays(dteStart As Date, dteEnd As Date) As Long
Dim i As Long 'day counter
Dim dteCurDay As Date
'set i = 1 if you want the first date to count as a full day
'or i = 0 if you do not want the first day to count as a full day
i = 0
dteCurDay = dteStart
Do Until dteCurDay >= dteEnd
'check date against holiday table
If 0 = DCount("[HolidayDate]", "tblHolidays", "[HolidayDate] = #" & dteCurDay & "#") Then
'continue checking for weekdays
If Weekday(dteCurDay, vbSunday) <> vbSunday And _
Weekday(dteCurDay, vbSunday) <> vbSaturday Then
i = i + 1
End If
End If
dteCurDay = DateAdd("d", 1, dteCurDay)
Loop
calcWorkDays = i
End Function
Any help would be much appreciated!
Thanks
I wish to print the result from query in a subform. In my main form, I had a textbox call 'year' which asking user to input a valid year. The query will have to find out all the records that are in that particular year, one year before and one year after. After that, it should display the result in datasheet form. Can anyone out there help me in this matter?
My second question is Can we use crosstab query in the subform?
Thank you.
Hi All, I hope someone can help with this, I have 2 tables, 1 main table that holds all my part data ( ie part num, description, etc) and a second table with vendor info. On my Form I have all my fields that display the record. I placed a combo box on my form that I need the user to be able to select a vendor, which is working but I need the form to show the vendors part number in a field. The main table has a manufactures part number, and I have 3 fields that have my 3 vendors part numbers for that part in it. I have the combo box so that it shows the vendor name but how do I get it to look at a certain field for the vendor part number. Im still really new to Access and am clueless any help would be greatly appreciated. I know I have not explained this every well so I am attaching a sample of the DB so you can see that I have Thanks again
Thanks everyone..
I would like to allow users create a query and then display the result in a new table. Just like the regular way in Access.
I know how to do it using DAO. However, I am working on a ADP, and want to use ADO for this. I cannot find a solution in ADO. So frustrated!
Any help is appreciated!
It's written a Function which takes an Integer as Arg, runs a Select Statement and returns a String.
Such that the Control Source of the Forms reads....
=ConvertCompanyNumbers([12]) = where [12] is the Name of the field. Not My choice to have numbers as Fields.. but there it is.
Anyway.. I'm getting an #Name? error... also tried [Ctl12] but again same error.
I know the function works, as I can use something like =ConvertCompanyNumbers(12) and it displays the appropriate result.
What is up with this?
I have a database that I have created for work. On the form I have several dlookups running. A couple of them show up as expected. But most of them are showing up blank. That is until you click on the box, then the result appears. Changing the Locked and Enabled settings does not resolve this.I have the database locked up for normal users. When I access it by holding shift while opening, when I access this form, the dlookups all show the desired results. No clicking on the boxes required.
I've tried refreshing, but that doesn't work. If I write code that loops through all of the textbox controls, setting focus on them and moving throuhg the form, then the results will show up. Basically having VBA click on the boxes for me. Although this works, there is a slight lag and screen flicker as it sets focus to all of these textboxes.
I'm trying to use the job number field, which is my primary key in my table, to auto assign the new job number on my Forms. Currently, I have to enter a new job number manually, and it has to be unique because that is the way I have it set. I can't use autonumber because Access does not allow you to select what number you would like to start from, which would not play well with my current job numbers.
Basically I need Access to get the job number from the last record and add 1 to it, or just find the last / highest current job number and add 1 to it.
I created a query (qryFindJob#s) that list all of the Job Numbers, but I'm not sure how to add the query results to my Form to display the Job number + 1. I created a text box, typed a simple expression in the control box to see if I was on the right path (=[qryFindJob#s]) but I keep getting a #Name? error in the txt field.
I have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.
Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.
I am currently using Access 2010.
Here's the form I'm trying to Create:
VEH POS NAME
A21: TC: CPT Somebody
G: SGT Someoneelse
D: PVT Noone
A22: TC: SFC Smith
G: SGT Jones
D: PVT Doe
and so on and so forth.
The VEH and POS are just going to be Labels in a form....no prob. Each Soldier's Squad and Team (for mounted Vehicle and Position) are stored in the Personnel Table. The below query is for vehicle A7 (ACTUAL would be the same as TC above). The query works. I just need to know how to get the result to display in a text box. What I'm planning on doing is creating text boxes for each posistion with these small select queries, so when I update the SQD and Team in the Personnel Table it updates on this form. Or is there an easier way to do this?
Dim strSQL as string
strSQL="SELECT [tblPERSONNEL]![RANK] & " " & [tblPERSONNEL]![LAST_NAME] AS NAME
FROM tblPERSONNEL
WHERE (((tblPERSONNEL.SQD)="A7") AND ((tblPERSONNEL.TEAM)="ACTUAL"))"
My question is that can we do multiplication of data of two columns and result is automatically displayed in third columns in datasheet view.? Is it possible ?
View 1 Replies View RelatedI want VBA code that will perform the following steps. The Table1 has following fields:
ScanDate,NewBatchNo,BatchNo,PolicyNo
1. First of all look into Table1 the scandate<=29082014 and then check if there is No NewBatchNo of the corresponding record then capture its corresponding BatchNo of each record whose scandate<=29082014
2.Then check for the batchno that we have captured in another table Table2 and if its present then return corresponding PolicyNo. Now keep on searching that policy no in Table2 as it could be present 50 times in the table and then return corresponding batch numbers in excel sheet of that Policy No as below:
BatchNo - Policy No - Batch No1 - Batch No2 - Batch No3 ---------------
I have the need to display the return of my "Sum Query" to display in a text box.
I need the attached below value (40500) in the "SumofQuery" attachment, which updates every 5 minutes to display on the "StatusBoard" attachment text box.
I have been searching for a good 10 hours on how to do this and still cannot find it.
I have saved query object named qrySearchBill. I wan to call this query through vba and display the result in a subform named subQrySearchBills in datasheet view. Here's how I want it to work:
When the main form loads, I want all unfiltered records to be displayed in the subform initially. The user may then decide to filter based on date range, so he enters startdate and enddate parameter values in their respective textboxes in the main form. Then click search button to run the saved query based on the date range parameter taken from the textboxes.
I have this code so far:
SQL of the saved query object:
Code:
PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date, Sum(tblInvoice.[TotalPrice]) AS Amount
FROM tblCrdCustomer INNER JOIN tblInvoice ON tblCrdCustomer.IDNo = tblInvoice.NameID
WHERE tblInvoice.Date Between [StartDate] And [EndDate]
GROUP BY tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date;
vba code to call the query and its parameter:
Private Sub btnSearchBill_Click()
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set qdf = CurrentDb.QueryDefs("qrySearchBills")
[Code] ...
This code works fine except that when the main form loads, a prompt window appears to ask for the value of dateStart and dateEnd. I don't want it to prompt because it's suppose to get these values from the main form's textboxes (txtStartDate and txtEndDate respectively), plus it should initially display all the unfiltered records.