Build A User Defined STR Statement Using Access 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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.
Click to expand...

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.
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Thank you Dirk, your code was exactly what I wanted.

Dirk Goldgar said:
SEAN DI''''ANNO said:
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.
Click to expand...

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)
Click to expand...
 
Back
Top