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.

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.

"SEAN DI''''ANNO" <(E-Mail Removed)> wrote in
message newsF4D4AC5-45EE-4EB8-9E09-(E-Mail Removed)
> 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.

I'm not sure exactly what you're trying to do, so I'm not sure which of
several possible approaches to the problem is best for you.

One way is to build the WHERE clause of a SQL select statement, like
this:

Dim strWhere As String
Dim strCode As String
Dim strSQL As String

Do
strCode = InputBox("What Catalogue Code do you require ?")
If Len(strCode) > 0 Then
strWhere = strWhere & _
" OR CatalogCode='" & strCode & "'"
End If
Loop Until Len(strCode) = 0

If Len(strWhere) > 0 Then
MsgBox _
"You didn't enter any catalogue codes!", _
vbExclamation
' maybe exit from here?
Else
strWhere = Mid$(strWhere, 5)
strSQL = _
"SELECT * FROM YourTable WHERE " & strWhere

' ... do something with this SQL ...

End If

Another, less efficient (but handy) way to do it is to build a delimited
list of the codes you want to search for, and then use a single InStr
function to see if a record is in that list:

Dim strList As String
Dim strCode As String
Dim strSQL As String

Do
strCode = InputBox("What Catalogue Code do you require ?")
If Len(strCode) > 0 Then
strList = strList & "," & strCode
End If
Loop Until Len(strCode) = 0

If Len(strList) > 0 Then
MsgBox _
"You didn't enter any catalogue codes!", _
vbExclamation
' maybe exit from here?
Else
strList = Chr(34) & strList & "," & Chr(34)
strSQL = _
"SELECT * FROM YourTable " & _
"WHERE InStr(" & strList & ", CatalogueCode) > 0"

' ... do something with this SQL ...

End If

Or, once you've built the list, you could apply the InStr function in
code to see if any given record meets the criteria.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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.

Thank you Dirk, your code was exactly what I wanted.

"Dirk Goldgar" wrote:

> "SEAN DI''''ANNO" <(E-Mail Removed)> wrote in
> message newsF4D4AC5-45EE-4EB8-9E09-(E-Mail Removed)
> > 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.
>
> I'm not sure exactly what you're trying to do, so I'm not sure which of
> several possible approaches to the problem is best for you.
>
> One way is to build the WHERE clause of a SQL select statement, like
> this:
>
> Dim strWhere As String
> Dim strCode As String
> Dim strSQL As String
>
> Do
> strCode = InputBox("What Catalogue Code do you require ?")
> If Len(strCode) > 0 Then
> strWhere = strWhere & _
> " OR CatalogCode='" & strCode & "'"
> End If
> Loop Until Len(strCode) = 0
>
> If Len(strWhere) > 0 Then
> MsgBox _
> "You didn't enter any catalogue codes!", _
> vbExclamation
> ' maybe exit from here?
> Else
> strWhere = Mid$(strWhere, 5)
> strSQL = _
> "SELECT * FROM YourTable WHERE " & strWhere
>
> ' ... do something with this SQL ...
>
> End If
>
> Another, less efficient (but handy) way to do it is to build a delimited
> list of the codes you want to search for, and then use a single InStr
> function to see if a record is in that list:
>
> Dim strList As String
> Dim strCode As String
> Dim strSQL As String
>
> Do
> strCode = InputBox("What Catalogue Code do you require ?")
> If Len(strCode) > 0 Then
> strList = strList & "," & strCode
> End If
> Loop Until Len(strCode) = 0
>
> If Len(strList) > 0 Then
> MsgBox _
> "You didn't enter any catalogue codes!", _
> vbExclamation
> ' maybe exit from here?
> Else
> strList = Chr(34) & strList & "," & Chr(34)
> strSQL = _
> "SELECT * FROM YourTable " & _
> "WHERE InStr(" & strList & ", CatalogueCode) > 0"
>
> ' ... do something with this SQL ...
>
> End If
>
> Or, once you've built the list, you could apply the InStr function in
> code to see if any given record meets the criteria.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>