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 have a form with 48 unbound text fields name txt1 to txt48.
These are to represent 24 hrs in half hours, to visually represent the hours capacity of my workshop. Now if I choose a date, which is say Monday, I check my settings, and see that the workshop has a capacity of 15 hrs on a monday. Now I would like to in a loop set the backcolour of the remaining fields to red.
Something like for I = 15 (workshop capacity) to 48
me."txt" & I.backcolour=1111
next I
I have got a query which gives me the following output;
Nr ----------- area ------- area 2 ---------- holler
14-1096-------1------------1-----------------5.9
14-1097-------2------------2-----------------7.8
14-1100-------1------------1-----------------13.4
14-1101-------2------------2-----------------7.8
What i would like to do is to calculate the sum of holler when they are in the same area.So the sum of nr 14-1096 + 14-1100 and 14-1097 + 14-1101. Ive tried to do the following;I tried to do the following just to check it would even work;
Code:
test: (SELECT Sum([holler]) FROM querytoetsn2hr_gemiddelde_filter WHERE ((querytoetsn2hr_gemiddelde_filter.area)=("1")))
Which worked perfectly, it gave me 19.3..
Code:
test: (SELECT Sum([holler]) FROM querytoetsn2hr_gemiddelde_filter WHERE ((querytoetsn2hr_gemiddelde_filter.area)=(querytoetsn2hr_gemiddelde_filter.area2)))
That gave me the sum of all 4 the Nrs. Which makes sense, because you basically say that as long as area and area are the same calculate the sum of holler.if there is a way to say "sum of holler when area has the same value".
I have a database where I would like to change a fieldname to something different.
I know I can use edit replace in the code and that works ok, but is there a way to change the fieldname in the code, the Table, the queries, and sql all in 1 foul swoop or do I have to do them all seperately by hand.
I want to retrieve a users account balance for a certain year and was thinking dlookup may do it but am beginning to wonder.
I have fields in the table for years 2004 - 1012. Based on the users input I would like dlookup to fetch the info from a certain year for a particular person. Roughly presented, dlookup(yearx,"tbl_InfoTable",username = 'Mike')
Can I get dlookup to accept a variable for the field name, ie yearx?
Is there a better way to quickly grab the info I need?
TIA
Mike
Why does this work when the text box is used and not when the variable is used directly?
Code:
Private Sub Command61_Click()
Dim strDBName As String
strDBName = getDBName()
Me.Text59 = strDBName
Me.Text62 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = text59")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = " & strDBName)
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = strDBName")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName]" = strDBName)
End Sub
Text62 returns the correct value
Text64 failes on everyone of the examples
Is there a way to use a variable in an SQL statement?
Something like this:
Code:
dim mysql1, newname
newname = inputbox("direction on what to do go here?",,)
mysql1 = "INSERT INTO FinalAll SELECT newname.* FROM newname;"
DoCmd.RunSQL mysql1
Where newname would be a variable assigned from an input box.
I want to populate a single table from several different tables but have the user specify which tables to use.
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 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 ahve in error typed the above - however it compiles and compacts and repairs without throwing an error. Should this be spotted before I actually run the line of code??
Ta
I want to make a loop like this:
Dim var1 As Variant, var2 As Variant, var3 As Variant, var4 As Variant
DIm var5 As Variant, var6 As Variant, var7 As Variant, var8 As Variant
Dim var9 As Variant, var10 As Variant
Dim i1 As Long
i1 = 1
Do Until i1 > 10
var & i1 ??? = "0" & i1 & "." & txt1 & "." & txt2
i1 = i1 + 1
Loop
How to make concatenate var + i1 to make loop function?
TextBox1 is Rich Text:-
I want to replace my String ("Evidence required of this please" ) in this code 1) following which works fine, with a variable (textToPaste) as in code 2) which doesn't work.
1) TextBox1 = "<div><font style=""BACKGROUND-COLOR:#FFFF00"">Evidence required of this please</font>"
2) TextBox1 = "<div><font style=""BACKGROUND-COLOR:#FFFF00"">textToPaste</font>"
Can I persuade the statement to accept "textToPaste" as a variable and not a normal string?
Code:
Private Sub CommandButton3_Click()
Dim rngCell As Range
Dim i As Long
Application.ScreenUpdating = False
'Coverage code
i = Range("J" & Rows.Count).End(xlUp).Row
[Code] ....
Why doesn't access like the underlined (For control variable already in use).
I have a printed form that pulls some of its data from a table.What i need is that printed form to have a variable that two area in the form can change based on who is printing the form.I have a table that has all the users data in it. the current form pulls that information from the label and places it into the for. easy enough so far right.
for example. But i need this printed form to be able to be dynamic. So for instance Tom inst sitting that the computer wanting to print the form and so on for four different users who will print the form.So the button that allows the print either needs a way to have a selection or i need four different button. So the second part of this Mark Twain quits his job and Johnny takes his place. easy enough to change the data in the table that my form is pulling the data from but the button label needs to change to that the button.
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 am trying to create a query that can be customised by a combobox.
I have a combobox that lists the fieldnames from table (rather than records)
I want to be able to run a query that updates the field selected from combobox with the vaule from another txtBox
What I want to be able to write in SQL is;
UPDATE Products
SET [Forms]![Master_Form]![Combobox] = [Forms]![Master_Form]![Qty]
WHERE (((Products.ProductName)=[Forms]![Master_Form]![ProductName]));
But of course this does not work.
Any help would be appreciated in SQL or VBA?
i have got a query that returns the top 100 results. i would like to link this to my report form where i have a text box that you can enter a number and the query returns the first of that ammount rather than going into the query everytime to adjust the results.the sql of the query is as follows
PHP Code:
SELECT TOP 100 tblClientDetails.FirstName, tblClientDetails.Surname, Sum(tblOrdersItems.Cost)
AS SumOfCostFROM (tblClientDetails INNER JOIN tblOrders ON tblClientDetails.ClientDetailsID = tblOrders.ClientDetailsID)
INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderIDWHERE (((tblOrders.OrderDate)>DateAdd('yyyy',-1,Date())))
GROUP BY tblClientDetails.FirstName, tblClientDetails.SurnameORDER BY Sum(tblOrdersItems.Cost) DESC;
I have a form I use to create photo captions. The form has a field where users can edit a pre-generated caption and a field that stores their updates as a final caption in the field "selected_caption".. On my form is a button that runs the code below to check and see if a field ("updated") has been marked true, then copies the content of caption field into the selected_caption field. It works fine 90 percent of the time but for some reason this caption:
LA 109535, LA109535 partial site overview showing blowout with PL's 01- 04. Crew person is excavating a shovel test probe. Photo direction facing southwest. (Roll 31066, Frame 4958)
Results in a missing operator error. I thought it was like a special character thing, so I removed the "'" and the "-". Same error.
Code:
Dim db As Database
Dim rst As Recordset
Dim upcap As String
Dim strSQL As String
Set db = CurrentDb
[Code] .....
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"
I have a form with numerous fields and would like to be able to move the focus to a selected field by typing the field name into a control box. How can I do this? TIA
View 4 Replies View RelatedI have a database structure with a series of queries which are all inter-linked.
I want my database to be user friendly, what is the cleanest way to be able to enter a single variable eg as a criteria for a query - say the date, rather than having to change it in a series of queries manually, can I call it from somewhere?
Is there a means of making an application grow/shrink to accommodate different screen sizes? eg. not all users of a distributed application will necessarily have the same screen size.
View 2 Replies View RelatedOccasionally 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 report named rpt100 with two subreports srpt100a and srpt100b. The subreports are based on query qry100a and qry100b. Both queries are based on tbl100. I removed a field named 'Comment' from tbl100, as it wasn't useful; Also removed the fieldname from both qry100a and qry100b. When opening rpt100 a parameter dialog opens asking for data on the deleted fieldname 'Comment'. The field 'Comment' was never used in the report or subreports.
Inspection of the subreport fieldlist shows field 'Comment' still present.
How, other than remaking the rpt100 and both srpt100a and srpt100b, do I remove the field 'Comment'?
Gunner...:confused: