I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
I am having a bit of a problem with my update query. i have a field that shows a forename. i am importing data from an excel file. The forename populates with forename and middle names and they are seperated by spaces as opposed to commas. I have used the following InStr function in my update query however it works fine when the records forename field has a middle name but it deletes all data in the records forename field if it contains only one name which many do. How can I adjust the function to ignore those records that do not hold more than one name in the forename field. As you can imagine some forename and middle name combinations hold many names. For instance the filad may have Ivor as a name which I would want to keep But if the Field showed Ivor Bigun then Bigun needs deleting.
I have 2 tables, trying to update one table filed by using max(onefiled) from other table.
my qyery is as follows:
my tables are tblSubFlowForecast and tblPickCalendar
I have to update one field in tblSubFlowForecast by taking max(onefiled) from tblPickCalendar.
UPDATE tblSubFlowForecast INNER JOIN tblPickCalendar ON tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry SET tblSubFlowForecast.[Latest Replen Date] = ( SELECT max(Pick) FROM tblPickCalendar WHERE Type="Replen");
I am getting error saying that "Operation must be an updateable query" :(
I have also tried in other way:
UPDATE tblSubFlowForecast SET [Latest Replen Date] = (SELECT max(Pick) FROM tblPickCalendar WHERE tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry AND tblPickCalendar.Type="Replen" ) FROM tblSubFlowForecast,tblPickCalendar
This is giving me syntax error (missing operator) :(
Can any body tell me why it is giving error? Thanks in advance
Would I use an update query or function to complete my task?
Task: Automatically update [Status] based on DateDiff calculation of [RequestDate] and [DueDate]
Issue: [Status] can be one of 6 values, the user can manually select Review or Completed.
[Status] values: Review; Completed; Due in 24; Due in 24-48; Beyond 48; Over Due
Query: Below is the query I just started to ignore the calculation if either Review or Completed exist.
Expr1: IIf([Status]="Review",[Status],IIf([Status]="Completed",[Status],DateDiff('d',[RequestDate],[DueDate])))If the value from above DateDiff equals to the below, I want to update the [Status] field to the [Status] value.
Due in 24 = (0-1) Due in 24-48 = (2-3) Beyond 48 = (greater than or equal to 4) Over Due= (-1)
I have a column with a date in it, and the left four characters are the year. I want to add another column that is just the year. I've tried doing an update query and updating the year column to left(date,4) but it doesn't work. How should I go about updating my year column?
I have this code below that is working however the calculation are updating on my form late.
Basically, I have some calculation that are performed on a "After Update" event on some controls on my form. I wanted it to do the calculation after I update the control on the form.
The code is in a module and I just call the function after update on the control But the form is not updating when I change the value in a field. I have to change the field again for it to update.
Control
Code: Call GeraAuditCalc DoCmd.RefreshRecord
Function
Code: Function GeraAuditCalc() Dim fHrs As Double Dim Ttl As Double Dim Ttl1 As Double Dim Ttl2 As Double
I'm trying to update some field value depending two string field using a function with if and case, function below:
Code: Private Function checkDATI(tipotransazione As String, tipovendita As String) As String Dim r As String r = "" If tipotransazione = "VENDITA" Then Select Case tipovendita
[code]...
And then when I call this function in a command button event as:
The company I work for is consolidating regional server space onto a single server. I have relinked all of my tables, but my ~180 queries are still pointing to the old server.
My question: Is there a script or other process that can be executed that will search through all my queries within the database to find the string "dbo_tbl" and replace with "dbo_vwtbl"? I would very much like to avoid taking each individual query to a notepad...
I have two forms (FormA, FormB) with combo boxes (cboA, cboB) that control the record being shown in the subforms (SubformA, SubformB). I have written code and attached it to the "AfterUpdate" function on the combo boxes and this works perfectly.
Here's my problem....
I have a button on FormA that, when clicked, opens FormB, assigns the value in cboA to cboB and closes FormA. Unfortunately, I can not figure out how to have SubformB automatically update based on the new value assigned by code to cboB.
I've tried .Requery, "After Update/Change/Dirty/etc." on cboB, "OnOpen" on FormB, and moved the code from cboB "AfterUpdate" to ButtonA "AfterClick" and nothing seems to work.
I need the user to use buttons to navigate through various forms displaying information for the same record. I also need to give the user the option to switch the record using the combo box. Does anyone know how to do this? Is it possible to use code to update the value in a combo box and then have the record in the subform automatically change?
I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.
It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
I am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?
I have a database where two tables contain information that I need to update based on the 4 right most digits of a field. There are only 11 values that will need to be updated out of a large list of values. I'm not quite sure how to set up the update query so that I can do this.
Please help, the attached code works fine in Windows XP but twhen run on a Windows 2000 pc it fails on the create object, any ideas?
Function GetXmlFilename() Dim objDialog GetXmlFilename = "" Set objDialog = CreateObject("UserAccounts.CommonDialog") objDialog.Filter = "XML Files|*.xml|All Files|*.*" objDialog.FilterIndex = 1 objDialog.InitialDir = "C: emp" If Not objDialog.ShowOpen Then Exit Function End If GetXmlFilename = objDialog.Filename End Function
I have written a simple function that I would like to use in a query. The function would return a value for each record where the parameters equals 2 different fields of the same record.
Private Function FctConvertInch(LineDim As String, LineShape As String)
'To Convert a String to a value and then convert that value from mm to inches 'Lines dimension = 00.0000x00.0000 if line is flat 'Lines dimension = 00.0000 if line is round Dim StNum1 As String Dim StNum2 As String Dim DbNum1 As Double Dim DbNum2 As Double
If LineShape = "round" Then '"CDbl" convert a string to Double number FctConvertInch = CDbl([LineDim]) / 25.4 ElseIf LineShape = "flat" Then StNum1 = Left([LineDim], 7) StNum2 = Right([LineDim], 7) DbNum1 = CDbl(StNum1) DbNum2 = CDbl(StNum2) FctConvertInch = (DbNum1 / 25.4) & "x" & (DbNum2 / 25.4) End If End Function
I have put the function in a new module, and have try to use the function in a query with the fields LineDim and LineShape , I've also added the field FctConvertInch(LineDim,LineShape) but it doesn't work. Am I using the function the the correct way?
I have a table consisting of 12 columns of data type number. for each row i want to display a count wherever the value of a row is greater than 100, that is, how many times the value greater than 100 appear in the 12 columns. PLease help???
I have a field in a query that I want to select the value from a field, but if the value is null, then to select the value from the same named field in a different table.
Basically the whole point is to use the given description unless one doesnt exist, where the default one will be inserted instead.
I have a field I want to calculate using IIF. There are about 40 possible values, thus I have about 40 nested IIFs. Because I get "query is too complex" when I try to nest all 40 IIFs, I've had to break this down into 3 fields. I then have to do a little more manipulation to end up with the 1 field I originally wanted. All that works fine, however, I'd like to be able to calculate the value in 1 field instead of 4 fields.
I'm not too good with VBA, but looking at some examples, I tried creating the following function (this is just a small sample, but if I'm on the right track there will be about 40 of these "Step" variables and ElseIfs):
~~~~~~~~~~~~~~~~~~~~~~~~~~~ Public Function IfThen(BYStep As String)
Dim test2 As Integer Dim Step070 As Integer Dim Step075 As Integer Dim Step080 As Integer
If BYStep = "07.0" Then test2 = Step070
ElseIf BYStep = "07.5" Then test2 = Step075
ElseIf BYStep = "08.0" Then test2 = Step080
Else: test2 = 0 End If
End Function ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In my query I have: IfThen([BYStep]) AS Test2
It took me awhile to get to the above where when I ran the query I didn't get a message about the wrong arguments. Now, I don't get an error message, but the field Test2, comes up empty for every record.
Is there a way to do what I'd like to do (having the 40 if/then/elses in a function, and then using that function in 1 field in the query)?
How do you use the IIf function in a query if you want to specify more than one paremeter? eg. IIf(MotherBirth="Australia", "Oceania", "Other") works but what happens if Oceania applies to Australia, Fiji and New Zealand? I tried IIf(MotherBirth="Australia" or "new Zealand" or "Fiji", "Oceania", "Other") but this doesn't work... What am i doing wrong?
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total ----------------------- 2 | 1234 | 80.00 ------------------------ 1 | 43526 | 20.00 ------------------------ > | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer) Dim SPSelect As String SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE" SPSelect = SPSelect & " ItemID = '" & ItemID SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
The user will input the JobId, PositionId, ActivityId. I want to return all the TaskName ,CompetencyId, CompetencyName Where the following: The highest priority within the same keyword Basically group by keyword then pick the highest priority I have done this:
SELECT Max(Competency.Priority), Competency. Keyword FROM Competency INNER JOIN ((ActivityTask RIGHT JOIN (positionTask RIGHT JOIN (JobTask RIGHT JOIN task ON JobTask.TaskID = task.TaskID) ON positionTask.TaskID = task.TaskID) ON ActivityTask.TaskID = task.TaskID) INNER JOIN TaskCompetency ON task.TaskID = TaskCompetency.TaskID) ON Competency.CompetencyID = TaskCompetency.CompetencyID WHERE (((JobTask.JobID)=[job])) OR (((positionTask.PositionID)=[position])) OR (((ActivityTask.GroupID)=[Activity])) GROUP BY Competency.CompetencyKeyword;
It works fine, it returns the all the keyword, and its highest priority. But I cant return the Competency.CompetencyId, Competency. CompetencyName with the aggregate function Max
I was wondering if anyone can point me in the right direction please. I have created a new module in my Access database (in the module object view) and I can see it in the module view. I am also able to call it in the expression builder under Functions.
Unfortuantaly if I create a query I am unable to use it. If I create it for the first time and open the query and use it, it works lovely. But if I exit my database and go back in, it does not work. I can still view it in the modules and it is still in the expression builder under functions.
My function is as follows; Public Function GetLabourCost(WorkCodeID, PayRate, Hours) As Currency Select Case WorkCodeID Case "7800" GetLabourCost = [PayRate] * 2 * [Hours] Case "7500" GetLabourCost = [PayRate] * 1.5 * [Hours] Case "1000" GetLabourCost = [PayRate] * [Hours] Case "2000" GetLabourCost = [PayRate] * [Hours] Case "7000" GetLabourCost = [PayRate] * [Hours] Case "8000" GetLabourCost = [PayRate] * [Hours] Case "9000" GetLabourCost = [PayRate] * [Hours] Case "9500" GetLabourCost = [PayRate] * [Hours] Case "9999" GetLabourCost = [PayRate] * 0 * [Hours] Case "9550" GetLabourCost = ([PayRate] * [Hours]) / 2 Case "9560" GetLabourCost = [PayRate] * 0 * [Hours] Case Else GetLabourCost = 0 End Select End Function
In my query I have called it as; Expr1: GetLabourCost([WorkCodeID],[PayRate],[Hours])
