Dlookup Two Values
Nov 30, 2007
Hi
I am hoping someone can help me with this. I have a form that has two combo boxes (Location and Char). When an option is chosen from the two combo boxes, I need the next field to display a number (Density) that is dependant on the combo box choices. I will explain this more as it may not make sense like this.
Location:
Centre (1)
Urban (2)
Rural (3)
Char:
Within 400m of Town Centre (1)
Between 400 and 600m of Town Centre (2)
More than 600m of Town Centre (3)
The Density depends on what has been chosen:
(1) and (1) = 50 (2) and (1) = 30 (3) and (1) = 20
(1) and (2) = 40 (2) and (2) = 20 (3) and (2) = 15
(1) and (3) = 30 (2) and (3) = 10 (3) and (3) = 10
What I would like the form to do is automatically show the density when the options have been chosen from the two combo boxes. I have a feeling this should be a dlookup, but it will only allow me to specify one value, which is not what I want.
Please help! Thanks
View Replies
ADVERTISEMENT
Aug 15, 2013
I'm pretty familiar with getting values from a table via Dlookup. What I want to do is almost the reverse if possible? I'm declaring a variable as follows:
Dim Ref as string
Ref = [lead_id]
This is from a form.What I'd like to be able to do is go to the table [list], reference the lead ID in the table via the variable then change the field [status] to "INCALL".Can this be done in a similar way to Dlookup?
UPDATE - here is the code I am trying to use
Dim ref As String
ref = [lead_id]
Dim MySQL As String
MySQL = "UPDATE vicidial_list SET"
MySQL = MySQL & "vicidial_list.status = 'INCALL' "
MySQL = MySQL & "WHERE (((vicidial_list.status)= Ref))"
DoCmd.RunSQL MySQL
Which gives me an update clause error
View 4 Replies
View Related
May 13, 2014
So I'm working on something for housing. Each house has a "Property Reference". This property reference links to all other information on the property.
There's two tables, Referral (For a tenant, with the Property Reference as a foreign key) and PropertyInfo (Holding all the property information)
Basically I want to save users as much input time as possible, so I'd prefer if they could just enter the property reference for a person, and that populates the rest of the table.
I'm currently using DLookup on the main form where it displays the information related to the property reference, obviously it's not actually being saved into any fields.
Will this method be okay if I would want to search the records by the address on the main form?
View 9 Replies
View Related
Sep 13, 2013
I made a custom function to look certain value from table based on couple of criteria that it gets from query where I want to use it. Function's code is below:
Code:
Public Function PotteryWeights(strLocusID As Long, nrPotSubID As Long) As Variant
Dim priSubID As Long
Dim priLocusID As Long
Dim priResult As Variant
priSubID = nrPotSubID
[Code] ...
However, when I use it in query it only returns Case else - option and everything else is empty.
View 7 Replies
View Related
Jul 14, 2015
I have run into an issue with a basic DLookup. The database has grown in size and now we could have multiple entries, but I want it to return a certain one. So the information could be in it three times. Of course DLookup stops after the first one. How do I get it to loop to check the entire table? Someone mentioned to me to use a recordset, but how to write that as I have never used it before. Below is what I was using until this new request came up.
<code>
Private Sub txtloan1_AfterUpdate()
If IsNull(DLookup("[loan1]", _
"settlement", _
"[loan1]=""" & Me.txtloan1.Text & """ AND [status] = 'Open'")) = False Then
Cancel = True
MsgBox "Test", vbOKOnly, "Warning"
End If
End Sub
</code>
This was also executing after the user entered the information within a text field. I did not want them to enter all the data and then have it come back as a duplicate.
View 7 Replies
View Related
Mar 26, 2014
Access 2010 - I would like to use DLookup to show results values from a table and display in a unbound textbox on a form. the results from each column in the table need to be on seperate lines, a break if you may. Here is the code I have so far.
Code: txtKeywords = DLookup("colKeyword", "tblKEYWORDS", "cboCategory = '" & txtcategories & "'")
In colKeyword Column In tblKEYWORDS table match what i select in cboCategory Combo drop down box and populate txtcategories textbox on form
What I would like to do is show All colKeyword results in textbox [txtcategories] and display each result on its own line!
example
entry1
entry2
entry3
entry4
View 7 Replies
View Related
Aug 6, 2014
I have a report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.
View 9 Replies
View Related
Aug 29, 2014
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
View 5 Replies
View Related
Dec 5, 2014
I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?
So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.
If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.
For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.
So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.
There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.
A few notes.
1.Getting as close as possible to the requested number is a requirement.
2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).
my table (NR_PVO_120)
Row OtherID Fax
1 11098554 2063504752
2 56200936 2080906666
3 11098554 7182160901
4 25138850 7182160901
5 56148974 7182232046
6 56530104 7182234134
[code]....
A few sample outputs
one solution is taking rows 1-6 and 26.
OtherID
11098554
56200936
25138850
56148974
56530104
56148975
Another solution is taking rows 1-4 and 10-14.
OtherID
11098554
56200936
25138850
56024315
56115247
56148974
This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.
View 12 Replies
View Related
Sep 17, 2013
In Access 2010 I have a Data Entry Form on which I have an unbound textbox in the header that the user can put a default date in. In the body of the form is a bound textbox that records the date and the default value is set as =defaultdatestat (obviously the name of the box in the header).
Problem: The default date shows up perfectly until a value is put in any of the other text boxes.
For further info : If you put values in text boxes default value disappears; if you then push escape the default value reappears when the values in the text boxes disappear.
View 4 Replies
View Related
Sep 18, 2013
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;
View 2 Replies
View Related
Nov 22, 2013
I have several result fields which are all drop down lists. I want each result field's drop down list values to be different depending on the selected value of the Test1 drop down list.I came up with using the .rowsource keyword. My syntax seems to be fine but I'm not getting any values under the result fields when I run the form.Here is my code so far:
Private Sub Test1_AfterUpdate()
If Me.Test1 = "Stress Echo" Or Me.Test1 = "Stress SPECT" Or Me.Test1 = "Stress PET" Or Me.Test1 = "Stress MRI" Then
Me.Test1Result2.RowSourceType = "Value List"
Me.Test1Result3.RowSourceType = "Value List"
[code]...
View 14 Replies
View Related
Nov 3, 2005
I'd like to create a query which will consist of simple SELECT statements as follows:
SELECT [table1].[field1], [table2].[field1], [table2].[field2]
FROM table1 INNER JOIN table2 ON ([table1].[fieldX] = [table2].[fieldX]);
The challenge arises b/c instead of joining on equal values, such as the following:
[table1] INNER JOIN [table2] ON [table1].[field1] = [table2].[field1]
I would like to join based on equivalencies, such as:
[table1] INNER JOIN [table2] ON [table1].[field1] = 34 is equivalent to [table2].[field1] = 2;
I do not know the proper syntax, so this is where I need help. I tried to search online without any success.
I appreciate your help in advance.
View 1 Replies
View Related
Jun 18, 2013
I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:
Code:
AcctNum FoodType FoodClass
------- -------- ---------
A123 Apple Fruit
A123 Apple Fruit
A123 Grape Fruit
A456 Potato Vegetable
A456 Potato Perishable
A789 Carrot Vegetable
A001 Banana Fruit
For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:
Code:
AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable
It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').
View 5 Replies
View Related
Mar 2, 2015
I have two tables.descriptions I'd like to relate and use to find/replace in bulk.
[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)
I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".
Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico
[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico
View 4 Replies
View Related
Jan 15, 2008
I have a travel time database that should tally the number of minutes traveled and convert them into a time (hours and minutes). I, however, am having difficulty converting the numeric values cleanly. Is there anyway to convert 102 minutes + 100 minutes + 110 minutes = 312 minutes to 5 hours and 12 minutes cleanly? I need to take averages of time traveled and hours worked but cannot do this correctly. Thank you for any help in advance.:eek:
View 2 Replies
View Related
Sep 15, 2005
There is a text box in a form that user can enter the value.
How can I check if the value entered by user is already existed in the record of the linked table or not?
If the value (or record) do not exist, that new value/record will be added to
the table.
If the value do exist, just show the msgbox to indicate.
How can I do that ? :confused:
Thanks
View 1 Replies
View Related
Feb 23, 2015
I have a lookup value in a table, and I would like to remove all values of the lookup value from vba. How can i do it via vba? i've been trying to do it in a query but i have always some errors and i can not remove them.
View 2 Replies
View Related
Mar 8, 2015
I have a table with following two columns
TechName Version
SQLServer 2000
SQLServer 2003
SQLServer 2005
SQLServer 2008
SQLServer 2008R2
Oracle 11G
MSOffice 2000
MSOffice 2003
MSOffice 2007
MSOffice 2010
How can i show this data as components eqch version for a techname to be one component order need not follow
TechName Component1 Component2 Component3 Component4 Component5
SqlServer 2000 2003 2005 2008 2008R2
Oralce 11G NULL NULL NULL NULL
MSOffice 2000 2003 2007 2010 NULL
View 4 Replies
View Related
Feb 7, 2014
i have one table in which ID is Primary ID with Different Values
Like
ID NAME PAN
1 A X
1 B Y
1 A X
2 C Z
2 C G
3 D U
it shows that ID 1 having 2 Name (A& B,with PAN, X & Y ,respectively).how can i get this that ID having More than 1 Value like 1 and how can i select only these records ID which having more than 1 value and how can i update values for 1 ID.
View 8 Replies
View Related
May 2, 2005
Can anyone see where I've gone wrong. The purpose should be if the value in the query is 0 or less (a minus amount) and the order number and part number matches the order number and part number on the form then a warning should appear. It's not picking up the record for flagging when it should:
If (DLookup("OutstandingQty", "qryremaininginvoiceamount", "OutstandingQty <= 0" & " And [OrdNo] = " _
& Me.[OrdNo] & " And [InvPtID] = " & Me.InvPtID)) Then
View 6 Replies
View Related
May 19, 2006
Hi,
I am pretty new to Ms Access.
I have a table which has a list of trades (Builder, Carpenter, Painter etc) going down the side and going across the top I have the number of employees (1Emp, 2Emp, 3Emp). The values in the table show the charge for that number of employees for that trade.
I have a form which has three combobox's that show the Number of Manual Employees, Number of Clerical Employees and the number of working directors and a combo box that shows the trade. What I want to happen is when the user clicks a command button on the form the charge for the total number of employees for that trade is shown in a txt box.
I have the following code attached to my button:
Private Sub GetValue_Click ()
Dim TotalEmployees As Integer
TotalEmployees = [NoManual] + [NoClerical] + [NoWorkingDir]
Value = Dlookup(TotalEmployees & "Emp", "ValueTable", "[Business] = Forms![Form1]![Business]")
End Sub.
When I type "[3Emp]" in the Dlookup it will work fine and finds the charge for that trade. But when I pass it the Result from the addition and concatenate it with "Emp" it doesn't seem to work.
I originally thought it was because I declared the TotalEmployees as an Integer by I also tried declaring it as a string - to no avail.
I just can not figure out why it will not look up the TotalEmployees.
I would much appreciate any help on this on any suggest as to how I can get the values from my table.
Charlene
View 1 Replies
View Related
Jul 7, 2006
I am using Dlookup to look up LastName, I have
AssignBy = DLookup("[LastName]", Employees, "POC =" & Me.Combo22)
I am getting an ERROR ""You entered an invalid argument in a domain aggregate function"
I need AssignBy = LastName from Employees Table where POC=what is selected in Form for Combo22.
ex. POC=3 go to Employees Table find POC that equals 3 and get the LastName.
Please Help!!
Jessie
View 4 Replies
View Related
Mar 26, 2007
Please help prevent my laptop from being smashed against a wall numerous times and then being thrown out the window, after all, the wall is mostly innocent in this situation.
I have read post upon post and other dim references to DLookup in the Access Help file, etc. I am generally a bright guy (although inexperienced in DataBases, VBA and some forms of lovemaking) but I have not been able to figure out the DLookup function. Could you please give me a VERY simple explination of how to use this function and it's expressions?
If it will help, we can use the following senario...
Table_Special_Needs
Special_Need_ID
Special_Need (Data Includes: Initiatives, High Ropes, etc.)
Standard_Price
Table_Event
Event_ID
Initiatives
High_Ropes
Extra_Linens
I would like to be able to add a price field for each item in the Event table and have the table lookup the price from the Special Needs table.
Many thanks for any help you can give.
t
:confused:
View 2 Replies
View Related
Oct 19, 2007
I would like to know if i am doing an Inventory in an Invoice Program and i want the [quantity] from a subform on the Orders Form to be deleted automatically from a Products Table , Would the Dlookup Function Help and if yes how would i implement it ?
Regards Dups
View 2 Replies
View Related
Feb 18, 2008
Dear all,
Firstly, thank you all for allowing me into your group!
Secondly however, PLEASE HELP!
I've created a database for storing students details and exam records for my university department, and everthing is fine except for one final problem: In order to allow final grades to be calculated all student marks have to be stored numerically, but each number corresponds to a code: eg N=0, G2=1, G1=2 .... A1=22
I'm trying to get the student marks report to display not just the numeric code, but also the final code eg, C2. To do so I've been trying to use DLookup as follows:
Expr1: =DLookUp("[Grade Code]","Lookup Table","[Score]= Forms![Total Grade]")
where "Grade Code" is a field in the Lookup Table (ie, it lists the A1, A2), "Lookup Table" is a table listing all the numbers and their corresponding codes, "Score" is the numeric score in the "Lookup Table" table, and "Total Grade" is a calculated (Numeric) field in the open report (though created in an accompanying query). I just can't get it to work however!! When I run the report, the column is just blank, and nothing I do seems to be able to get the corresponding code (Eg, B1, B2) to appear on the report!
Please help me if you can, I'm not kidding when I say my job is on the line with getting this database to work! Once I've got this sorted, all I need to do is find a way of letting the secretaries input the alphanumeric code, but the database automatically stores that as a number. I figured however, that sorting the first problem will go some way to finding out how to sort the 2nd one!
Thank you all just for being out there!
View 3 Replies
View Related
