By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 1,944 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

Problem quith query criteria

P: n/a
Hi everyone,

I have this slq code

slqtext = "SELECT [total_installs].[Unité Administrative] From
total_installs, uachoisi WHERE ((([total_installs].[Unité
Administrative])='*afe*'));"

which makes a query that only accept values containing "afe". It works
perfectly.

Here's the problem : I would like to change the "*afe*" part for the
value of a table. Something like "*[table].[value]*" cause the value
changes while my program is running.
Any ideas ?

Plz excuse my poor english.

Alex

Aug 3 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"icony" <al**********@hotmail.comwrote in
news:11**********************@i3g2000cwc.googlegro ups.com:
Hi everyone,

I have this slq code

slqtext = "SELECT [total_installs].[Unité Administrative] From
total_installs, uachoisi WHERE ((([total_installs].[Unité
Administrative])='*afe*'));"

which makes a query that only accept values containing "afe".
It works perfectly.

Here's the problem : I would like to change the "*afe*" part
for the value of a table. Something like "*[table].[value]*"
cause the value changes while my program is running.
Any ideas ?

Plz excuse my poor english.

Alex
slqtext = "SELECT [total_installs].[Unité Administrative] From
total_installs, uachoisi WHERE ((([total_installs].[Unité
Administrative])='*' & [table].[value] & '*'));"
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 3 '06 #2

P: n/a
Hi Alex,

You have a few options. The easiest option is to use VBA code like
this:

Private Sub Command1_Click()
Dim DB As DAO.Database, QD As DAO.QueryDef
dim str1 As String
str1 = txt0

Set DB = CurrentDB
'--first get rid of query test1 if already exists
For Each qd In db.QueryDefs
If qd.Name = "test1" Then
db.QueryDefs.Delete qd.Name
Exit For
End If
Next
'--create new test1 query
Set QD = DB.CreateQueryDef("test1")
QD.SQL = "SELECT " _
& "[total_installs].[Unité Administrative] " _
& "From total_installs, uachoisi " _
& "WHERE " _
& "[total_installs].[UnitéAdministrative]='" & str1 & "'"
DoCmd.OpenQuery "test1"
End Sub

Here you have a form that is based on your table, and txt0 is a textbox
that is linked to the table and Command1 is a command button on the
form. You assign the value of txt0 to the variable str1 and use str1 in
your Sql Statement. When you click the button,

Your other option is to create a public function and a public variable
in a standard code module (not a class module). You assign a value to
the public variable. Place the public function in the sql code in the
Query window. The function will take the value of the variable:

Public globalStr1 As String

Public Function funcStr1() As String
funcStr1 = globalStr1
End Function

In the sql in your query add the function to the criteria section of
your query. Note: you have to include both parentheses ()

Field: [UnitéAdministrative]
Criteria: funcStr1()

You can assign a value to the function from a command button on a form:

Private Sub Command1_Click()
globalStr1 = "something"
DoCmd.OpenQuery "test1"
End Sub

With this second example, you don't have to delete the query everytime.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 3 '06 #3

P: n/a
Thx a lot Rich, it worked !!!

i used the first option and my slq text changed just a little from what
you wrote cause of the spaces and all. Here's what i used :

choix = combo1.text

myslqtxt = "SELECT " _
& "[total_installs].[Unité Administrative] " _
& "From total_installs, uachoisi " _
& "WHERE " _
& "((([total_installs].[Unité Administrative])LIKE'" & "*" & choix &
"*" & "'))"

keep up the good job !!

Alex

Aug 4 '06 #4

P: n/a
Hi Alex,

You can modify this a little bit:
>>
choix = combo1.text

myslqtxt = "SELECT " _
& "[total_installs].[Unité Administrative] " _
& "From total_installs, uachoisi " _
& "WHERE " _
& "((([total_installs].[Unité Administrative])LIKE'" & "*" & choix &
"*" & "'))"
<<

To this:

choix = combo1.text

myslqtxt = "SELECT " _
& "[total_installs].[Unité Administrative] " _
& "From total_installs, uachoisi " _
& "WHERE " _
& "((([total_installs].[Unité Administrative])LIKE '*" & choix & "*'))"

This part:

LIKE '*" & choix & "*'))"

When you use a variable in a Sql string - you have to delimit string
(text) variables with single quotes '. so you have

... '*" & variable & "*' ..."

Number variables do not require to be delimited. But Date variables
#3/1/2006# have to be delimited with # symbol. And always be sure that
you have spaces between the operators -->OR, LIKE, AND, IN, EXISTS

Like'*"... <---this is incorrect

Like '*"... <--- this is correct because you have a space

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 4 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.