Nest Iif Using Instr Function

Dec 11, 2007

Hello,

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;

Thanks for your help!!!!!!

View Replies


ADVERTISEMENT

Instr Function

Dec 12, 2007

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!!!


1)
EXERCISE1 SET EXERCISE1.NAME_A = IIf(InStr([Name],",")=0,[Name],IIf(InStr([Name],",")=InStrRev([Name],","),Trim(Mid([Name],InStr([Name],",")+2,20)) & " " & Mid([Name],1,InStr([Name],",")-1),Mid([Name],InStr([Name],",")+2,(InStrRev([Name],",")-InStr([Name],",")-2)) & " " & Mid([Name],1,InStr([Name],",")-1)));



2)
SELECT EXERCISE1.NAME, IIf(InStr([NAME],",")=0,[NAME],Trim(Mid([name],InStr([NAME],",")+1)) & " " & Mid([NAME],1,InStr([NAME],",")-1)) AS FLIPPEDNAME
FROM EXERCISE1;

View 2 Replies View Related

Update Query InStr Function

Sep 27, 2007

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.

Left([Forename],InStr([Forename]," "))

View 2 Replies View Related

Queries :: Instr Function In Select Query

Jun 21, 2013

I need to select the second word from a product description. Here are two examples;

2156015 Dunlop SP-30
1756514 Goodyear Sport

I found the correct code on another website to do this in Excel;

=MID([field], FIND(" ",[field],1)+1, FIND(" ",[field],FIND(" ",[field],1)+1)-(FIND(" ",[field],FIND(" ",[field],1))))

It works a treat. In order for it to work in Access you apparently change FIND to Instr.

Now this worked for the person on the other forum but when I try it, it doesn't and I get the #Error returned.

Here is what I am using. Description is obviously the field I am selecting from;

TEST: Mid([Description],InStr(" ",[Description],1)+1,InStr(" ",[Description],InStr(" ",[Description],1)+1)-(InStr(" ",[Description],InStr(" ",[Description],1))))

View 9 Replies View Related

Rat's Nest Of A Problem

May 10, 2006

I've been trying to assign tours for an upcoming conference from the three options chosen by people, based on when they chose them. Now that the first tours are starting to fill up (maximum 40), some people should get shunted to their 2nd or 3rd choices. Having a hard time figuring out how to get Access to spit out that 'remaining' top choice, though.I triedSELECT IIF([TourChoice1]=10 Or [TourChoice1]=11 Or [TourChoice1]=14 Or [TourChoice1]=22,[TourChoice2],[TourChoice1]) AS TopChoice, tableRegistrants.RegistrationDate, tableRegistrants.TourFinal, tableRegistrants.PaymentMade, tablePeople.City, tablePeople.StateFROM tablePeople INNER JOIN tableRegistrants ON tablePeople.PersonID = tableRegistrants.RegIDWHERE (((tableRegistrants.TourFinal) Is Null))ORDER BY IIF([TourChoice1]=10 Or [TourChoice1]=11 Or [TourChoice1]=14 Or [TourChoice1]=22,[TourChoice2],[TourChoice1]) DESC , tableRegistrants.RegistrationDate; once I got four full tours, but then realized that it wasn't taking into account anyone whose 1st and 2nd choices were taken. There's a much larger IF statement possible here, but I'm almost certainly missing something.Factoids: 25 tour options, 40 people max per tour, about 500 people total. They're chosen based on when they registered (early registrations get their first choice, overflow on full choices get their 2nd, etc). And no, I don't have any choice on how the registration database spits them out to me.

View 2 Replies View Related

Looping To Nest IIF Statements

Aug 8, 2005

There has got to be a better way to do what I want to do. Here is the situation:

I have a weekly forecast (up to 14 weeks worth) that I want to apply to the inventory that I have on hand to generate figure out how many 'days' of forecast that I have on hand. Eg. I have 7 units on hand, next week's forecast is 7 units therefore I have 7 days of inventory.

Here is how I am doing this:

SQL = "SELECT SKU, "
SQL = SQL & "IIF([ON_HAND]<=[W1 FORECAST], [OVERSTK_POT]/([W1 FORECAST]/7), "
weeks = "[W1 FORECAST]"
days = 7
end_brackets = ")"
For n = 2 To 14
weeks = weeks & "+[W" & n & " FORECAST]"
days = days + 7
end_brackets = end_brackets & ")"
SQL = SQL & "IIF([ON_HAND]<=(" & weeks & "), [ON_HAND]/((" & weeks & ")/" & days & "), "
Next n
SQL = SQL & "105" & end_brackets & " AS OVERSTOCK_DAYS, "

I have 2 issues with this code. The first is that 12 nested iif statements is all that will be allowed in this loop before the query becomes too complex. The second is that this cannot possibly be the best method to accomplish this. I'm hoping someone might know a better method.

Thanks!

View 1 Replies View Related

Syntax For Nest JOIN In MS Access?

Feb 22, 2008

This query works:

SELECT p.AcntNumber, p.Name, d.FromDate
FROM Daysheet AS d
INNER JOIN Patient AS p ON d.AcntNumber=p.AcntNumber

But this does not:

SELECT p.AcntNumber, p.Name, d.FromDate, c.CPT
FROM Daysheet AS d
INNER JOIN Patient AS p ON d.AcntNumber=p.AcntNumber
INNER JOIN Charge AS c ON c.Id=d.ChargeNum

Nor this:

SELECT p.AcntNumber, p.Name, d.FromDate, c.CPT
FROM Daysheet AS d
(INNER JOIN Patient AS p
(INNER JOIN Charge AS c ON c.Id=d.ChargeNum)
ON d.AcntNumber=p.AcntNumber)

How do you word a nested join?

View 2 Replies View Related

Problem With Instr()

Aug 8, 2007

Hi there,

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;

Any help would be hugely appreciated!

View 6 Replies View Related

Len, Char, Instr Not Sure What To Use Or How To Use It

Nov 13, 2007

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

View 3 Replies View Related

Having Probles With Instr In My Query

Mar 6, 2008

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

View 7 Replies View Related

Modules & VBA :: Use CASE And INSTR Together?

Jan 22, 2014

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"

View 12 Replies View Related

Data Type Mismatch With InStr

Jul 22, 2005

Hi,

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.

Am I missing something here?

View 5 Replies View Related

Modules & VBA :: Use Instr To Search Through Two Fields

Apr 27, 2015

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

[code]...

View 6 Replies View Related

Modules & VBA :: InStr (3rd Instance Of Character Required)

May 1, 2014

I use the following code to get the first and second instances of a "/" character. How to get the position of the third instance.

iUPC = "123-7754LF-(A/S red Top)-T19/97876564"
'get number of instances
xTimes = 0
xTimes = Len(iUPC) - Len(Replace(iUPC, "/", ""))
'get position of characters
xInstance1 = InStr(1, iUPC, "/")
xInstance2 = InStr(InStr(1, iUPC, "/") + 1, iUPC, "/")

View 4 Replies View Related

Splitting String Using Instr And Retrieving Part Information

Apr 26, 2006

I know there are many posts on this but still cannot find what I want ....


I have a string ....

... <surname>bloggs</surname> <fornames>Jane</fornames> etc.etc.
... <surname>williams</surname> <forenames>Jo</fornames> etc.etc.

In a query I know how to:

Find the Start and End Postions as follows:

StartSurnamePos: InStr([string],"<surname>") returns 19
EndSurnamePos: InStr([string],"</surname>") returns 34

I also know that by adding 8 to StartSurnamePos I can get Bloggs but how do I stop it there.

Using left, Right or Mid how do I pull out just

bloggs Jane
Williams Jo in seperate fields?

Thanks in advanced.

View 1 Replies View Related

Modules & VBA :: InStr - Use Clause In Opening A Datasheet Form

Jun 16, 2015

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"

intPos = InStr(1, strSQL, tempString, vbTextCompare)
BaseQueryFormStr = Left(strSQL, intPos - 1)
MsgBox (BaseQueryFormStr)

The value of intPos remains=0 and when the program hits the second to last line I get "run-time error 5"

View 10 Replies View Related

Queries :: Query Stops Working With InStr Null Value

Feb 7, 2015

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

Date: Left([BatchInput];11)
PartCertNr: Right([BatchInput];Len([BatchInput])-14)

The result is this:

Field date: 20 MAY 2004
Field PartCertNr: 9-082-79 A4147011A05

The second query (Qrybreak2) I look for the first space:

space: InStr([PartCertNr];" ")

Then with the result I cut it into two pieces

PartNumber: Trim(Left([PartCertNr];[space]-1))
CertNumber: Trim(Right([PartCertNr];[space]+2))

[code]...

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)?

View 7 Replies View Related

Queries :: Query With Instr Returns Unexpected Results

Aug 26, 2014

I have Access 2010. If I run this query

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?

View 10 Replies View Related

Parse InStr "-"

Mar 13, 2008

Hi, i have a column with data in a query that looks like the following (p.s. sbenj, mehere,.. thanks for the advice earlier)
Code:Link200-1200-1-1200-1-2200-13-1
Now everything before the 2nd hyphen is considered the base number. I.e.

Code:Link200-1200-1200-1200-13
I need to parse into a seperate field everything before the 2 hyphen.

Now i've tried the following which i thought would work, which would enable me to concatenate expr1 & expr3 unfortunately it doesn't agree with 200-1 because it doesn't have a second hyphen,

SELECT Table.Link, Left([link],InStr([link],"-")-1) AS Expr1, Mid([link],InStr([link],"-")+1) AS Expr2, Left([Expr2],InStr([Expr2],"-")-1) AS Expr3
FROM Table;

Result from this is

Code:EXPR1 EXPR2 EXPR3200 1- Error#200 1-1 1200 1-2 1200 13-1 13

Any Thought?

View 11 Replies View Related

Modules & VBA :: Creating A Function That Counts Records And Use That Function In A Query

Dec 11, 2013

So basically I need making a function that will count the number of records from another table/query based on a field from the current query.

View 2 Replies View Related

Forms :: SUM Function Produces Error From Calculated Function

Jan 30, 2014

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.

=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error

View 2 Replies View Related

Date Function/need Time Function

Jun 9, 2005

We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields.
160037
213137
224356
235716
235800
12341
21708
22732
Here is the date function we use:
Function f2Date(strDateOld As String)
Dim strDate As String, strMonth As String, strYear As String
strMonth = Mid(strDateOld, 5, 2)
strDate = Right(strDateOld, 2)
strYear = Left(strDateOld, 4)
f2Date = strMonth & "-" & strDate & "-" & strYear
f2Date = CDate(f2Date)
f2Date = Format(f2Date, "mmmm d yyyy")
End Function

View 9 Replies View Related

Now Function To Convert To Date Function

May 25, 2006

Hi all,

I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.

Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.

I tried to go back and change the NOW to DATE in VB, but the code does not run.

How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?

Thanks in advance.

View 1 Replies View Related

Function

Aug 1, 2005

Is there any function that can convert either a True, False, "Yes", "No" or Null value.... to a yes/no (check box) data type?

What I want to do is create a make table query. I would like to add a checkbox field from the query. So if there was a function that could convert a data type to a Yes/No checkbox, this would be very helpful...

To give you and idea of what I'm talking about...and how I want to use this... If I am making a table from a query and I want to add a number field, but leave it empty for the time being, what I can do is make a new field in the query maybe call it "Number1". What I would do is Add this as a field in my make table query:

Number1: Cint("")


This will add a blank field that is formatted as a number field when I run the make table query.

So using the same logic, I'd like to make a blank (or unchecked) Yes/No check box field when I run my make table query.

Anyway...that's what I'm hoping for.... I hope someone can help!

Gary

View 10 Replies View Related

Function Isn't Available

Nov 3, 2005

I'm having a problem getting a form to open. I'm using access 97 on on XP machine and whan I open my database I get Function isn't available in expressions in query expression '[Case Types]![theName]&"-"&Format([SourceDocs]![CaseNumber]&"","00")&"-"&Format([SourceDocs]![CaseYear]&"","00")'.

This database works fine on a Windows 2000 machine running Access 97 but none of the XP machines will open it.

Any suggestions?

Thanks,
Rick

View 8 Replies View Related

CHR() Function Not Available

Nov 10, 2006

I have a database in which I am designing a report which includes a name/address block. I am surprised to find that the report does not recognise the Chr() function in the text box control source string "=[Organisation] & Chr(13) & Chr(10) & [Address]..."; designed to insert a CRLF between the contents of the named fields.

I know the syntax is correct because I've checked by using the expression builder to replicate the code. (Expression builder includes the Chr() function, as expected.)

The interesting thing is that the function is recognised in other databases used on the same machine, both those created remotely and locally.

Anyone know what could be going wrong?

Stuart

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved