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'm examining a previously written query and I'm trying to figure out exactly what the minus sign does when placed before the Instr function. An example of a query that successfully flips a name field is below and includes the -instr function. I've also included another query below this one that is much more simple and does the same thing. Thanks in advance for your help!!!
I am trying to write a query that will search a field for a string until it discovers a comma. If there isn't a comma I want the field left as is. If there is a comma I want it to grab all strings before the comma and then take the string after the comma and flip the arrangement to another field..ie (flipname)
example if a field has [Smith, John] I want it displayed as John Smith
Here's is the code I was attempting to use below, it generates syntax errors!
SELECT Exercise1.name, Iif(Instr[name],",")=0,[Name], Mid([name],Instr([name]),+1,instr([name]),",")-1 as expr1 FROM Exercise1;
I need to be able to take a list of instructions like this one, that is in a table, and have it break out in a query so that when I go to use it in a report it will look like the second example.
1) Set the oil out so that it is at room temperature. 2) Mix the oil and the alcohol together. 3) Place oil mixture on stove and bring it up to73 degrees. 4) Pour the mixture through a cheese cloth. 5) Add 1 cup
1) Set the oil out so that it is at room temperature. 2) Mix the oil and the alcohol together. 3) Place oil mixture on stove and bring it up to73 degrees. 4) Pour the mixture through a cheese cloth. 5) Add 1 cup
I have tried different ways of using instr in my query. I can get it to start the next line at any point, but I do not seem to be able to get it to stop when it comes to the next instruction. As a result I get something like this.
3) Place oil mixture on stove and bring it up to73 degrees. 4) Pour the mixture through a cheese cloth. 5) Add 1 cup
4) Pour the mixture through a cheese cloth. 5) Add 1 cup
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 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 question about errors on null value.I have made a small database for tryout, it has to be implemented in another one.And the small database is working.I have one table where there is one field called BatchInput.I scan a barcode into it and let two query's breaking it apart. I scan this batch into the table field
BatchInput: 20 MAY 2004H149-082-79 A4147011A05
Then I have my first query (Qrybreak1) extracting the date and deleting H14
And query (QryResult) even wont start, giving a popup with Invalid procedure call..How could I handle Null on the part where there is no space after the partnumber (missing Certnumber)?
Code: SELECT Reference, InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField FROM MyTable WHERE (Reference ='ShouldBeOut' OR Reference = 'ShouldBeIn1' OR Reference = 'ShouldBeIn2')
It returns 3 rows, with values in MyField of 0, 23 and 355.
Now I add 1 more where clause to filter on MyField and the query looks like this:
Code: SELECT Reference, InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField FROM MyTable WHERE (Reference ='ShouldBeOut' OR Reference = 'ShouldBeIn1' OR Reference = 'ShouldBeIn2') AND ( InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) > 0 )
So, you would think that it should return 2 values (ShouldBeIn1 and 2), but it doesn't. It only return the value where the Instr returns value of 23. The one with the value of 355 also disappears. Why would that be? Surely Instr does not return a byte?
I need alot of advice from you guys! Basically I have a table that contains address data, and I want to isolate the country name - it may be either by itself in a field or in a string.
To do this, I have created three tables:
one with supplied data containing address data (tblProcessData) one with a list of countries (tblCountryName) and one with a list of alternative country names (tblAlternativeCountryName).
tblCountryName has all the 'correct' country names (eg. UK) and tblAlternativeCountryName has any other spellings of this (eg. United Kingdom, Great Britain, GB etc. etc.) I have joined the two together using the Primary Key of tblCountryname to a number field in the tblAlternativeCountryName (one-to-many relationship).
My question is, what is the best way of isolating the country name in the table? I was attempting to run an update query to find the country name based on 2222 records, but when I went to run it as a select query, it comes up with 142208 records, and the instr value is 0. Why is this?
SELECT tblProcessData.[6], InStr([tblProcessData]![6],[tblCountryName]![CountryName]) AS Expr1, InStr([tblProcessData]![6],[tblAlternativeCountryName]![AlternativeCountryName]) AS Expr2 FROM tblProcessData, tblCountryName INNER JOIN tblAlternativeCountryName ON tblCountryName.CountryNameID = tblAlternativeCountryName.CorrectCountryName;
I need to bee able to query out all entries that are not like the following format S01-19-01-3. Users are entering incorrect data such as So1-19-o1-3, S0119-01-3, S01-19-01-3. Users are supposed to enter the data with One letter, 3 dashes and 7 numbers. If they enter any other way I need to be able to identify it with out searching through some 4000 records. Please help
I have a check for lots of different data in a string and wondered if i can use CASE or similar.Sample code reads...
If InStr(1, Me.txt_sp, "Give & Take", 1) Then TM = "Standard" If InStr(1, Me.txt_sp, "Give Take", 1) Then TM = "Standard" If InStr(1, Me.txt_sp, "give and take", 1) Then TM = "Standard" If InStr(1, Me.txt_sp, "Give and", 1) Then TM = "Standard" If InStr(1, Me.txt_sp, "priority working", 1) Then TM = "Standard" If InStr(1, Me.txt_sp, "priority boards", 1) Then TM = "Standard" If InStr(1, Me.txt_sp", 1) Then TM = "2 Way"
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'm having some problems with a simple query and it's driving me nuts. The invoice numbers in our system have hyphens in them, and I'm trying to find invoice numbers whose part before the hyphen matches a number I enter. Here's my SQL:
SELECT dbo_NIHB_ClaimLog.InvoiceNum, Left([InvoiceNum],InStr([InvoiceNum],"-")-1) AS InvoiceNumLeft FROM dbo_NIHB_ClaimLog WHERE (((dbo_NIHB_ClaimLog.InvoiceNum) Is Not Null) AND ((Left([InvoiceNum],InStr([InvoiceNum],"-")-1))=23316));
I'm getting a "Data Type Mismatch In Criteria Expression" error, and I can't figure out why. I've wrapped a Clng() around the Left() function, but that doesn't help either.
In my developing application I am making use of searchboxes to narrow down the amount of records. On a form I have a textbox and a subform with a table connected. In the textbox I can type a character that will be used in an 'Instr' SQL query. I am using the code to query one field. (see code below) In what direction do I have to look to make this code usefull to search through two fields. In my case that will be Tag and Function.
Code:
Private Sub mnu3_txt_UnitbookSearch_Change() Dim SQLstring As String SQLstring = "Instr(Tag, " & "'" & Me.mnu3_txt_UnitbookSearch.Text & "'" & ")" ReReadDescriptions SQLstring
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:
I want to that the WHERE clause for a SQL statement that I am using options on a form to build. I intend to use the clause in opening a datasheet form.
This is the code I have for getting the substring
Code:
Dim intPos As Integer Dim tempString As String Dim BaseQueryFormStr As String 'BaseQueryFormStr is used to reopen the BaseMasterQueryFrm with the specified parameters tempString = "WHERE"
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?
