RE: Build A User Defined STR Statement Using Access 2000
- From: SEAN DI''''ANNO <SEANDIANNO@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 20 Mar 2006 08:49:31 -0800
Thank you very much Klatuu,
To make it easier on my self (this is the second database I have ever
written), and the fact that I doubt whether there would ever be more than a
few codes to check, I will use the text field method. In your example;
strCatCodes = "IN (" & Left(strCatCodes, Len(strCatCodes) -1) & ")" Am I
right in saying this would work a bit like, "Is "Sean" in the string
"DavidSeanPaul"?
I have taken an extract from a query which I currently use to find specific
items, would you mind very much incorporating the statemet above into the
query below so that I can use it.
SELECT dbo_dimItems.MainDescription, dbo_trnBillingDetail.Qty
FROM dbo_trnBillingDetail INNER JOIN dbo_dimItems ON
dbo_trnBillingDetail.ItemID = dbo_dimItems.ItemID
WHERE (((InStr([ItemNo],"Z470")+InStr([CatalogCode],"Z470"))<>0));
In the method you suggest,
"Klatuu" wrote:
First, I would Dim a module level variable to hold the string of catalogue.
codes. For example purposes, I will call it strCatCodes
Something like this in the After Update event of the control where catalogue
codes are entered:
strCatCodes = strCatCodes & Me.CatalogueCode & ","
Then, to prepare it for the query:
strCatCodes = "IN (" & Left(strCatCodes, Len(strCatCodes) -1) & ")"
Now, as to saving it for later use, you could use one of two approaches.
One would be to save it to a text field in a table so you could retrieve it
when you want to add it to your query as a where condition. The only problem
you may run into would be possibly creating a string longer than 255 bytes.
The other would be to use the CreateQueryDef method. For this, you would
have to create a complete query that you can save as a stored query with a
name unique to the promotion. Then save the name of the query in a table
with other promotion data so you call it later.
I would use the CreateQueryDef way to avoid the 255 byte limit.
"SEAN DI''''ANNO" wrote:
Good morning,
can come one please help me with my following datasbase dilemma
The user will be asked, "What Catalogue Code do you require ?" The
question will be repeated until there are no more catralogue codes. I would
like to build up a STR statement in the background.
Suppose the user enters 2 Catalogue Codes;
E.G Item 1, Item 2. I would like a variable to buld up a resulting STR
statement of
code]InStr([CatalogCode],"Item1")+InStr([CatalogCode],"Item2")[/code].
I then want the database to automatically build a query with the STR
statement so that it can be used again. The reason being.........The
database will be used to track different promotions and therefore have a
different STR statment for each promotion.
As always, If you could give me the basic skeleton code, if its possible?, I
would really appreciate it.
Thank you.
- Prev by Date: Re: How do I turn off the Control Box for the MS Access Application
- Next by Date: Using Access to create dynamic chart in Excel
- Previous by thread: Re: Build A User Defined STR Statement Using Access 2000
- Next by thread: Re: How do I turn off the Control Box for the MS Access Application
- Index(es):
Relevant Pages
|
