Modules & VBA :: Use DLookup To Return A Value From Relevant Field
Nov 14, 2014
I have a small table, that only contains one record (and should only ever contain one record, which simply gets edited to suit once in a while).The table is called OtherRates, and the fields are as follows:
WRD_Sat/Sun/BH
WRD_Mon-Fri
AHDifferentia
SKDifferentia
Sat/Sun/BH_OTRate
Mon-Fri_OTRate
I am trying to use DLookup to return a value from the relevant field in this table, however the field to look in is a variable.The code (below) that I have tried returns an error and highlights the DLookup line,.
Code:
Dim WRD
Dim DayType
[code]....
View Replies
ADVERTISEMENT
Sep 22, 2013
I have a combo box in a sub form with three values, rate1, rate2, and rate3. I have another three fields in the sub form rate1, rate2, and rate3. The rates are dependent on the item. When I select the rate from the combo box I want a cost field to update on change to the relevant rate. I tried this to no avail:
Code:
txtCost = DLookup(cboRate.Value, "tblItem", "ItemID=" & ItemID)
View 5 Replies
View Related
Aug 3, 2015
I'm trying to use a DLookup to get a specific value from a field in a table.
This is what my code currently looks like;
Code:
JOBID = DLookup("[ID]", "MASTER PLANNER", "[JOB NUMBER] = '" & JOB_NUMBER & "'")
Basically I want to get the ID (a number) from a specific record where the JOB NUMBER equals the string I have typed in to a field on the form, also called JOB NUMBER.
However, my problem is that it doesn't navigate to the record where the criteria matches, it just chooses the ID from the very first record of the table.
what I'm doing wrong?
View 9 Replies
View Related
Feb 25, 2014
I am trying to use a DLookup to search to find what block goes into the production of an item.
In a table called "dbo_vw_MCE_job_with_materials", each item contains a list of the parts which go into its production in a column titled "Expr1". However there are always more than one parts to this, with varying names.
What I am trying to do is to use a Dlookup to only return the first value which starts with B (as the block number is always the only part on the list which begins with B).
At the moment my DLookup would look ike this:
Code:
MixTypeTxt = DLookup("Expr1", "dbo_vw_MCE_job_with_materials", "[item] ='" & Forms![Theta Input]![ItemNumbertxt] & "'")
However I think as the item column contains the same item number in 8 columns, with different values in the Expr1 column, Dlookup only returns the top value "A-CF0057" (which is irrelevant as far as I am concered) - whereas I am hoping it will be able to pull the "B1499" value from the column as it is the only one starting with a B
Is there anyway to specify which value the dlookup function would return, or is there any way to apply some sort of permanent filter on my table?
View 1 Replies
View Related
Feb 11, 2014
I have been trying to find a solution to why I can't get a Dlookup with multiple criteria to return the value I need.
Essentially I am trying to use an Order Number to find the item number which is contained within another table. However the order number has multiple lines (suffixes) which alter the item number. Therefore I am trying to have the item number be populated by the correct 'combination' of Order Number and line ("suffix").
I have managed to use the Dlookup in the after update of each box of the form separately and they retrieve values in the table correctly:
Afterupdate of main order number:
Code:
Private Sub OrderNumbertxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", "[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'")
End Sub
Afterupdate of suffix:
Code:
Private Sub SuffixTxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", "[suffix] = Forms![**INPUT]![SuffixTxt]")
End Sub
However when I combine them as follows in the afterupdate of the Suffix box I receive a "Run-time error '13': Type mismatch"
Code:
Private Sub SuffixTxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] = Forms![**INPUT]![SuffixTxt]") And ("[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'"))
End Sub
I think the reason is something to do with some being numbers and some being a combination of text and numbers (based on the replies of other topics), but have been trying to modify these slightly and can't get it to work still.
Also the Order Number is a combination of letters and numbers (normally in the form of AB12345678), the suffix is just a number between 0-9 and the Item number it finds is a combination of numbers and letters.
View 3 Replies
View Related
Dec 4, 2013
I am trying to make a DLookup function to return the ID number of an entry that matches 2 or 3 criteria but I am struggling to get the syntax correct for the second and third criteria.
Here is what I have so far:
1 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] =" & Forms![Mainform]![p11] )
2 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " And [timedata]![BGSnum] = 1001" )
BGSnum is a numerical value but it changes for each form I load, so what I want to do is use the form location value as the criteria.
E.g.
=DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " AND [BGSnum] = ' " & Forms![Mainform]![BGS] & "' ')
and possibly a third criteria too. This is where I am having problems and the syntax is wrong so MS Access wont let me save the macro.
View 6 Replies
View Related
Oct 30, 2013
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?
View 2 Replies
View Related
Sep 3, 2014
I am trying to return a single value from a table and assign it to a string to be used later but Dlookup isnt working at all. below is the code im using and the error message im recieving is "wrong number of arguements or invalid property assignment"
Code:
Sub boo()
Dim result As Integer
result = dlookup("Definition", "Config", "Parameter = 'Mail Folder'")
End Sub
View 13 Replies
View Related
Oct 4, 2007
Hi there!
I would like to know whether it's possible to retrieve the field names, in order to display them in table format within a html page. I would like to dress the table with the cell data together with field names as 'headers' for each column.
Here is a snippet of what i have manages to produce. Currently, it displays all the entries that coincide with thier field names. The inly thing i wish to do now is to display the field names:
<html>
<head>
<title>date</title>
</head>
<body >
<h3>Try It Out - Sailors Table With a Counter</b></i></font></h3>
<p><br>
<%
Dim oRSeofc
Set oRSeofc=Server.createObject("ADODB.recordset")
oRSEOFc.Open "People", "dsn=20527796a"
oRSeofc.MoveFirst
response.write "<table border='1'>"
Dim PersonCounter
PersonCounter = 0
Do While Not oRSeofc.EOF
PersonCounter =PersonCounter + 1
response.write oRSeofc.fields.item(counter).name
Response.write "<tr><td>" & PersonCounter & "</td>"
Response.write "<td>" & oRSeofc("PeopleNameFirst") & "</td>"
Response.write "<td>" & oRSeofc("PeopleNameLast") & "</td>"
Response.write "<td>" & oRSeofc("PeopleDOB") & "</td></tr>"
oRSeofc.MoveNext
Loop
response.write "</table><br>"
response.write PersonCounter & " Sailors in this list"
%>
</body>
</html>
All help will be greatefully appreciated, thanx!!
View 1 Replies
View Related
Mar 13, 2014
I'm looking for a bit of code for a module that will return the field name from an expression (that uses an array) instead of returning the value in that field.
Example:
(Minimum is a working module that finds the smallest number in the array)
Field1 = 34
Field2 = 53
Field3 = 21
Field4 = 62
GetFieldName(Minimum([Field1],[Field2],[Field3],[Field4])) = "Field3"
I've searched but can't find a way to select the field name anywhere...
View 4 Replies
View Related
Apr 17, 2014
I have a code that finds the first, second, and third minimum value in a row across the fields. Now, I am trying to find a code to look at these values, find the field it is located, and return the field name. I tried several variations of my code to return the field name rather than the value, but have been unsuccessful to this point.
Function NthMinimum(intPosition As Integer, ParamArray FieldArray() As Variant) As Variant
Dim varTempArray() As Variant, varTempValue As Variant, intArrayValues As Integer
Dim I As Integer, J As Integer
ReDim varTempArray(UBound(FieldArray))
intArrayValues = 0
[Code] ....
As you can see, this works to find these values while ignoring NULLS. How to return the field name?
View 1 Replies
View Related
Dec 12, 2014
I am trying to create a multi-user web-based database where all users log-in and see different forms based on a field in the user table.
I am trying to use a DLookup to return the name of the Form I want open based on the command box that shows the userID and UserName
DoCmd.OpenForm , acNormal, DLookup("field", "table", "[UserID]=" & Me.cboName.Value)
cboName row source
SELECT [table].[UserID], [table].[UserName] FROM table ORDER BY [UserID]
View 5 Replies
View Related
Apr 6, 2014
I'm trying to create a report that's based on a query, and the query has three fields: [PersonName], [PersonDate], [PersonShift]. This table holds records for people that worked on certain days and certain shifts. What I want to do is create a report that gives a graphical calendar display of each day in a month, and on any day that the person has a record (and sometimes there are more than one), I'd like to see just the PersonShift records showing in that day's box.
tblPersons
PersonName PersonDate PersonShift
Jason 4/10/14 FIRST
Jason 4/13/14 FIRST
Jason 4/13/14 SECOND
So if I were to print this report for Jason, I'd get all the days in April laid out like a calendar, and on 4/10/14 you'd see "FIRST" in the box, and on 4/13/14. you'd see FIRST and SECOND in the box. All the other boxes would be blank.how to display the calendar, how to display the dates. I'm able to return records to those boxes by creating 31 separate queries, one for each day of the month, and each query returns records for that day. The queries are added to the report as subreports. It all works beautifully.
The thing is, I'm running 31 queries every time I pull the report. Is there a way to code a single field on a report that will run a SELECT statement on the table using variables that are located in fields elsewhere in the record?
If I have a PersonName field on the header of the report, and I have a PersonDate field in the detail of the report's record, can I create a new field in the detail of that record that runs a SELECT statement on qryPersons, and filters the tblPersons by the PersonName on the header of the report and on PersonDate in the record?
I want a field on a report that runs a SELECT statement on tblPersons, I want the field to return only the PersonShift records for that person based upon the PersonDate. Each of the fields on my report have a CalendarDate field, and I want the SELECT statement to return records where the PersonDate matches the CalendarDate, and again, it should only display PersonShift records.
View 2 Replies
View Related
Sep 16, 2014
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField]
"kick the ball"
"return the pass"
my query result should looks like this
[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
View 3 Replies
View Related
Feb 25, 2015
I have a small problem with dlookup multiple criteria. Vba code looks like this:
Code:
Label34.Caption = DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= " & Me.Text27 & " And [Shift] = '" & Me.Text29 & "'")
This gives following error:
Syntax error in number in query expression '[Date_Added]=4.02.2015 And [Shift] = '2'.
[Shift] column is a numeric field.
View 14 Replies
View Related
Jun 28, 2013
I want to return a value from a separate query and have that show up in a field on my main form. The field name from the query is "Six Month Date" and the query name is "DT6Monthqry". I want to return the value displayed in the"Six Month Date" from the query, matching the CustomerID in the query result to the CustomerID that is being displayed on the main form. The CustomerID is a text field.This is the code I placed in the Control Source property for the field I want to be displayed on the main form:
=DLookUp("[Six Month Date]","DT6Monthqry","CustomerID=" & [CustomerID])
The result of this displays a flashing "Error" in the field, even when I go to a Customer that I know has a result in the "DT6Monthqry".
View 8 Replies
View Related
Apr 6, 2015
I have a search from that has an option group, text fields, and a checkbox where the users selects a variety of option that generates a query. If the query return at least on record a split form (form on top/datasheet on bottom) is displayed and the form has an Edit and Close button. If the query returns no records the form is still displayed except the buttons are not visible.
If I knew the result of the query and then made a decision whether to open the form or release control back to the search box that would be great. To open the form I am using VBA with the DoCmd.OpenForm(,,"MyQuery","criteria") command.I thought about using the DLookup command and evaluate the return value for null to be used to control the program flow.
View 3 Replies
View Related
Jun 10, 2013
Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.
View 14 Replies
View Related
Jun 14, 2005
As part of my data structure I have a Fault table, Transaction table & Resolver table. The Resolver's are dependant on the Transaction that are selected in the fault table. What I am after (and it's not happening) is when selecting a Transaction in the Fault table the only resolver that is available to the user is the one related to the choosen Transaction.
At the moment after selecting a Transaction all Resolver's from the Resolver table are available. When creating my Fault table I selecting the Resolver field as a Lookup and choose the Transaction table and the Resolver field.
View 1 Replies
View Related
Jun 15, 2005
I have a form with a combo box this combo box looks up values in my STC table. The STC table has two fields STC & Resolver and each STC has a unique associated Resolver. What i want to happen is after the user has selected the STC from the combo box on the form the associated Resolver be displayed in a textbox on the form. I am ussing the following code but nothing seems to be happening:
Private Sub cboSTC_AfterUpdate()
Dim ResolverSource As String
ResolverSource = "SELECT tblSTC.[Resolver] " & _
"FROM tblSTC " & _
"WHERE tblSTC.[STC]='" & Me.cboSTC.Value & "';"
Me.txtResolver.RowSource = AgentSource
Me.txtResolver.Requery
End Sub
View 1 Replies
View Related
Sep 20, 2007
Morning everyone
I have made up a database to record generations of birds; along with others I have the following fields:
Ring number (primary key)
Sex
Year
Colour
Data entry via a form view.
For new entries I pick up from a combo box the parents, what I need to do now is create a relationship between 1, 3&4 and 2, 5&6 so that the data will automatically slot in the relevant fields.
1Parent Cock
2Parent Hen
3Grand Parent Cock C/S
4Grand Parent Hen C/S
5Grand Parent Cock H/S
6Grand Parent Hen H/S
would I do it via a query.’ And would one cover relevant generations
Thanks for any help
Norma
View 8 Replies
View Related
Apr 3, 2005
I have been able to successfully solve the problem of my pages showing relevant records by using form control.
How should I make my form display a new record everytime I open it? So, that when a lame user opens it , he wouldnt change the existing data on an existing record. How to solve this problem?
Kindly help. thanks
View 11 Replies
View Related
Jun 29, 2006
I have a form that functions as information display/update for a table. It also needs to display a list of relevant codes from another table. The codes in the second table have an ID that will match an ID field on the main table. However when I do this I either get every code in the code table, or nothing.
Here is the SQL that I'm trying to use:
SELECT table_code_lookup.code_text, table_codes.code_extra, table_codes.alum_id, table_codes.ID
FROM (table_code_lookup INNER JOIN table_codes ON table_code_lookup.code_id=table_codes.code_num) INNER JOIN table_alumni ON table_codes.alum_id=table_alumni.ID
WHERE (((table_codes.alum_id)=me.id));
However anything I can think of putting for me.id doesn't work. I've written a sub that will modify the query and insert the correct ID num, but I can't figure out how to get it to run when I change records with the record selector.
I have the nagging feeling that I'm missing something totally simple, but can't figure out what it is, where am I going wrong?
View 3 Replies
View Related
Apr 22, 2015
I am trying to dlookup a field (field name [client]) which is on a form (form name bkpo)
The code i use is this
CLNM = DLookup("[ClientName]", "[BKPOCL]", "[ClCode] ='" & [Forms]![BKPO]![Client] & "'")
It works just perfect however if I use the form BKPO as a subform then the code doesn't work
The form is BK1b
and the subform is BKPO
I tryed this but doesn't work
CLNM = DLookup("[ClientName]", "[BKPOCL]", "[ClCode] ='" & [Forms]![BK1b].[BKPO]![Client] & "'")
I have tryed several other options like [Forms]![BK1b]!BKPO.[Client] or [Forms].[BK1b]![BKPO]![Client] but nothing
View 7 Replies
View Related
Nov 5, 2013
I am looking to use a "If" statement with DLookup to find a record and see if the field name begins with "TW" as the characters. Here is what I have so far:
Code:
Dim matchCriteria As String
matchCriteria = "LIKE 'TW*'"
If DLookup("end_user", "tbl_module_repairs", "prikey = " & Me.txt_rid1.Value) = matchCriteria Then
MsgBox "Success"
Else
MsgBox "FAILURE"
End If
I am trying to switch between two sets of pricing for customers that begin with "TW" and then all else. Would the use of a "case" statement be better? If so I do not have the "end_user" bound to the form so I would need to embed Dlookup in the "case" method.
View 5 Replies
View Related
Aug 4, 2015
I have a form that has a field that needs to get its data from a query based on two fields in the form. The following is my DLookUp statement:
DLookup("[COMEX]", "OptionMetalsListQ", "[Metals] = '" & [cboMetals].[Column](1) & "'" And "[DateOfPrice] = " & Me.txtDateOfPrice)
Comex is a metal market. the bound column in cboMetals is text and The date is a date.I am getting the error "Run-time error '13': Type Mismatch.
View 3 Replies
View Related
