Need help using the InStr() function

[Caleb]

Hello, every day one of our employees downloads a .csv off our shopping cart
website full of orders. Then they manually format the fields, take out all
but the accepted credit cards, and take out all the subscriptions (because
nothing needs shipped for them). After this they make two new fields, a
Purchased field, and a NextPurchase field in the Purchased field the enter
the short version of the product entered (ill elaborate in a minute) and for
the NextPurchase field they consult a spreadsheet that tells them the
reccommended next purchase based on what the customer just purchased. Now the
problem I'm haveing is that when the .csv is downloaded for some reason the
Product, payment methods, price, and some random other things are all entered
into one field! making it impossible to compare to the seperate spreadsheet
to find out NextPurchase as the spreadsheet only displays the Product. I have
been playing with the InStr() trying to get a query to compare the Product on
the spreadsheet with the first few words of the product on the .csv. and
based on that fill in the NextPurchase field.

I know its confusing so if there any questions just ask and Ill do my best
to explain better, thanks in advance, Caleb

 

[Clif McIrvin]

Hi Caleb --

First, is this even related to Microsoft Access?

If it is, please post the SQL of the query you're trying to write, along
with a few lines of example data and explanation so that we can get a
better idea of what you're trying to do.

If not, try taking your question to an Excel (?) forum. I've never
looked at the Microsoft portal into these newsgroups; but i understand
they can be confusing.

 

[Caleb]

Yes I am building a database to eliminate all the pointless formatting and
such. At the moment I have one Query, it removes anything in the
ApprovedStatus field that doesn't say accepted, and gets rid of all orders
with an OrderId greater than 1000000000 (all the subscriptions are in this
range and as I said before we dont need to ship anything for these). Here is
the SQL for it:

SELECT [Fresh Import From 1ShoppingCart].OrderId, [Fresh Import From
1ShoppingCart].Email, [Fresh Import From 1ShoppingCart].FirstName, [Fresh
Import From 1ShoppingCart].LastName, [Fresh Import From
1ShoppingCart].ApproveStatus, [Fresh Import From 1ShoppingCart].Phone, [Fresh
Import From 1ShoppingCart].OrderDate, [Fresh Import From
1ShoppingCart].ProductsOrdered, [Fresh Import From 1ShoppingCart].SKU, [Fresh
Import From 1ShoppingCart].Total, [Fresh Import From
1ShoppingCart].ShipToName, [Fresh Import From 1ShoppingCart].ShipToAddress1,
[Fresh Import From 1ShoppingCart].ShipToAddress2, [Fresh Import From
1ShoppingCart].ShipToCity, [Fresh Import From 1ShoppingCart].ShipToState,
[Fresh Import From 1ShoppingCart].ShipToZip, [Fresh Import From
1ShoppingCart].ShipToCountry, [Fresh Import From 1ShoppingCart].HowHeard,
[Fresh Import From 1ShoppingCart].price
FROM [Fresh Import From 1ShoppingCart]
WHERE (([OrderId]<1000000000) AND (([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted *" Or ([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted" Or ([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted: *" Or ([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted:"));

 

[Armen Stein]

(([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted *" Or ([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted" Or ([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted: *" Or ([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted:"))

Like without * is the same as =.

So your criteria is basically only going to get records that Start
With "Accepted " or "Accepted: " (note the trailing space) or that are
EXACTLY "Accepted" or "Accepted:" (note lack of trailing space).

Are you sure that you don't mean to look for "Accepted" anywhere in
the string? In that case you could use:

[Fresh Import From 1ShoppingCart].ApproveStatus Like "*Accepted*"

(note * on both sides)

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Caleb]

Well that made the code simpler and more efficient thank you, however my
question wasn't with the "Accepted" because I hadnt had any problems with the
way I had it.

(([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted *" Or ([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted" Or ([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted: *" Or ([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted:"))

Like without * is the same as =.

So your criteria is basically only going to get records that Start
With "Accepted " or "Accepted: " (note the trailing space) or that are
EXACTLY "Accepted" or "Accepted:" (note lack of trailing space).

Are you sure that you don't mean to look for "Accepted" anywhere in
the string? In that case you could use:

[Fresh Import From 1ShoppingCart].ApproveStatus Like "*Accepted*"

(note * on both sides)

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

Well that made the code simpler and more efficient thank you, however my
question wasn't with the "Accepted" because I hadnt had any problems with the
way I had it.

Well, I'm glad I was able to answer the question you didn't ask.

Let's start over. I'm assuming you don't have any control over the
format of the data you are getting. Can you rephrase exactly what you
want to do?

You mentioned InStr. That function only returns the numeric position
of a certain string found inside another string. What do you want to
accomplish with it?

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Clif McIrvin]

If it is, please post the SQL of the query you're trying to write,
thanks ... I see Armen caught that post.

It's not necessary to post actual data, just an example that illustrates
the problem that you are trying to solve. Two or three lines should be
enough, but we also need your expalnation of a) what the problem is and
b) *exactly* what you are wanting to do. It may be helpful to post an
example of both the .csv raw data and the field(s) you are having
difficulty with.

From your OP I can't tell if you are working with the raw .csv data, an
Excel worksheet, an Access linked table or an Access imported table.
That information would also be useful; particularly field definitions.

--
Clif

Yes I am building a database to eliminate all the pointless formatting
and
such. At the moment I have one Query, it removes anything in the
ApprovedStatus field that doesn't say accepted, and gets rid of all
orders
with an OrderId greater than 1000000000 (all the subscriptions are in
this
range and as I said before we dont need to ship anything for these).
Here is
the SQL for it:

SELECT [Fresh Import From 1ShoppingCart].OrderId, [Fresh Import From
1ShoppingCart].Email, [Fresh Import From 1ShoppingCart].FirstName,
[Fresh
Import From 1ShoppingCart].LastName, [Fresh Import From
1ShoppingCart].ApproveStatus, [Fresh Import From 1ShoppingCart].Phone,
[Fresh
Import From 1ShoppingCart].OrderDate, [Fresh Import From
1ShoppingCart].ProductsOrdered, [Fresh Import From 1ShoppingCart].SKU,
[Fresh
Import From 1ShoppingCart].Total, [Fresh Import From
1ShoppingCart].ShipToName, [Fresh Import From
1ShoppingCart].ShipToAddress1,
[Fresh Import From 1ShoppingCart].ShipToAddress2, [Fresh Import From
1ShoppingCart].ShipToCity, [Fresh Import From
1ShoppingCart].ShipToState,
[Fresh Import From 1ShoppingCart].ShipToZip, [Fresh Import From
1ShoppingCart].ShipToCountry, [Fresh Import From
1ShoppingCart].HowHeard,
[Fresh Import From 1ShoppingCart].price
FROM [Fresh Import From 1ShoppingCart]
WHERE (([OrderId]<1000000000) AND (([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted *" Or ([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted" Or ([Fresh Import From
1ShoppingCart].ApproveStatus) Like "Accepted: *" Or ([Fresh Import
From
1ShoppingCart].ApproveStatus) Like "Accepted:"));

Hi Caleb --

First, is this even related to Microsoft Access?

If it is, please post the SQL of the query you're trying to write,
along
with a few lines of example data and explanation so that we can get a
better idea of what you're trying to do.

If not, try taking your question to an Excel (?) forum. I've never
looked at the Microsoft portal into these newsgroups; but i
understand
they can be confusing.