Query Control

  • Thread starter gjameson via AccessMonster.com
  • Start date
G

gjameson via AccessMonster.com

I have about 20 or so queries that I need to run at diffrerent times. There
is only one element that changes in each query and it is the same one. I
created a form to select my query. My changing varible is in a ComboBox. Here
is the code behind this query. What is wrong with my syntax? My varible
control is 'cboBook'.

Private Sub btnQuery_Click()
On Error GoTo Err_btnQuery_Click
Dim stLinkCriteria As String
Dim stDocName As String

stDocName = "CustomerLedger"

stLinkCriteria = SELECT Customer.ID, Customer.ABREV_NAME, Customer.
AREA_STS, Customer.MEDIA, Customer.BAN_ROUTE , Customer.CURTAIL_RT, Customer.
MTR_ADDRS, Customer.RN_1_TY_SZ, Customer.CC_RT_NBR , Customer.RUN_1_NMBR
FROM Customer
WHERE (((Customer.ID) < 500000) And ((Customer.ABREV_NAME) <> "ZZZ") And
((Customer.AREA_STS) <> "archive") And ((Customer.MEDIA) = "c") And (
(Customer.BAN_ROUTE) = "cboBook"))
ORDER BY "Customer.CURTAIL_RT";


DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnQuery_Click:
Exit Sub

Err_btnQuery_Click:
MsgBox Err.Description
Resume Exit_btnQuery_Click

End Sub

Thanks for the help in advance.

Gerald
 
M

Michel Walsh

Hi,


A criteria is only what follow the WHERE clause, and it must be a string in
itself. Since inside the criteria you use already the " string delimiter, I
would change it, inside the criteria, to a single ' and get:


stLinkCriteria = "(((Customer.ID) < 500000) And ((Customer.ABREV_NAME)
<> 'ZZZ') And
((Customer.AREA_STS) <> 'archive') And ((Customer.MEDIA) = 'c') And (
(Customer.BAN_ROUTE) = 'cboBook'))"





I removed the SELECT part up to (including) the WHERE key word; I changed
the " for ' inside the criteria, I removed the ORDER BY clause, which is
not part of a criteria.

Since, nowhere, you refer to the combo box (at least, I cannot identify it),
that filter will be *constant*.

Hoping it may help,
Vanderghast, Access MVP
 
J

John Spencer

First, you need to build a string to pass in stLinkCriteria

stLinkCriteria = "SELECT Customer.ID, Customer.ABREV_NAME, " & _
"Customer.AREA_STS, Customer.MEDIA, Customer.BAN_ROUTE" & _
", Customer.CURTAIL_RT, Customer.MTR_ADDRS" & _
", Customer.RN_1_TY_SZ, Customer.CC_RT_NBR , Customer.RUN_1_NMBR"
stLinkCriteria = stLinkCriteria & " FROM Customer "
stLinkCriteria = stLinkCriteria & " WHERE Customer.ID < 500000 " & _
" And Customer.ABREV_NAME <> ""ZZZ"" " & _
" And Customer.AREA_STS <> ""archive"" " & _
" And Customer.MEDIA = ""c"" " & _
" And Customer.BAN_ROUTE = """ & me.cboBook & """ " & _
stLinkCriteria = stLinkCriteria & " ORDER BY Customer.CURTAIL_RT"

If Ban_Route is a number field instead of a text field change the line in
the where clause by changing the two """ to just "
 
G

gjameson via AccessMonster.com

Thanks for the quick response John. Here is what I have.

Private Sub btnQuery_Click()
On Error GoTo Err_btnQuery_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "CustomerLedger"

stLinkCriteria = "SELECT Customer.ID, Customer.ABREV_NAME, " & _
"Customer.AREA_STS, Customer.MEDIA, Customer.BAN_ROUTE" & _
", Customer.CURTAIL_RT, Customer.MTR_ADDRS" & _
", Customer.RN_1_TY_SZ, Customer.CC_RT_NBR , Customer.RUN_1_NMBR"
stLinkCriteria = stLinkCriteria & " FROM Customer "
stLinkCriteria = stLinkCriteria & " WHERE Customer.ID < 500000 " & _
" And Customer.ABREV_NAME <> ""ZZZ"" " & _
" And Customer.AREA_STS <> ""archive"" " & _
" And Customer.MEDIA = ""c"" " & _
" And Customer.BAN_ROUTE = " & Me.cboBook & """ " & _
stLinkCriteria = stLinkCriteria & " ORDER BY Customer.CURTAIL_RT"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnQuery_Click:
Exit Sub

Err_btnQuery_Click:
MsgBox Err.Description
Resume Exit_btnQuery_Click

End Sub

This is not returning any info. only my blank form.


Gerald





John said:
First, you need to build a string to pass in stLinkCriteria

stLinkCriteria = "SELECT Customer.ID, Customer.ABREV_NAME, " & _
"Customer.AREA_STS, Customer.MEDIA, Customer.BAN_ROUTE" & _
", Customer.CURTAIL_RT, Customer.MTR_ADDRS" & _
", Customer.RN_1_TY_SZ, Customer.CC_RT_NBR , Customer.RUN_1_NMBR"
stLinkCriteria = stLinkCriteria & " FROM Customer "
stLinkCriteria = stLinkCriteria & " WHERE Customer.ID < 500000 " & _
" And Customer.ABREV_NAME <> ""ZZZ"" " & _
" And Customer.AREA_STS <> ""archive"" " & _
" And Customer.MEDIA = ""c"" " & _
" And Customer.BAN_ROUTE = """ & me.cboBook & """ " & _
stLinkCriteria = stLinkCriteria & " ORDER BY Customer.CURTAIL_RT"

If Ban_Route is a number field instead of a text field change the line in
the where clause by changing the two """ to just "
I have about 20 or so queries that I need to run at diffrerent times. There
is only one element that changes in each query and it is the same one. I
Click to expand...
[quoted text clipped - 35 lines]
Gerald
Click to expand...
Click to expand...
 
J

John Spencer

Private Sub btnQuery_Click()
On Error GoTo Err_btnQuery_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "CustomerLedger"

stLinkCriteria = "SELECT Customer.ID, Customer.ABREV_NAME, " & _
"Customer.AREA_STS, Customer.MEDIA, Customer.BAN_ROUTE" & _
", Customer.CURTAIL_RT, Customer.MTR_ADDRS" & _
", Customer.RN_1_TY_SZ, Customer.CC_RT_NBR , Customer.RUN_1_NMBR"
stLinkCriteria = stLinkCriteria & " FROM Customer "
stLinkCriteria = stLinkCriteria & " WHERE Customer.ID < 500000 " & _
" And Customer.ABREV_NAME <> ""ZZZ"" " & _
" And Customer.AREA_STS <> ""archive"" " & _
" And Customer.MEDIA = ""c"" " & _
" And Customer.BAN_ROUTE = " & Me.cboBook & _
stLinkCriteria = stLinkCriteria & " ORDER BY Customer.CURTAIL_RT"

'===============================
' I removed the quotes after me.CboBook. I assumed that
'Ban_Route is a NUMBER field
'and not a text field containing number characters.

'Insert these 2 lines for debug purposes
Debug.Print stLinkCriteria
Stop

'Copy the sql statement that is printed in the immediate window
'Paste it into a NEW query and see if it executes - if so then you've
constructed a valid
'statement. If not, look at the error and see what is wrong with the SQL
you've
'constructed. Fix the code and try again.
'===============================

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnQuery_Click:
Exit Sub

Err_btnQuery_Click:
MsgBox Err.Description
Resume Exit_btnQuery_Click

End Sub

gjameson via AccessMonster.com said:
Thanks for the quick response John. Here is what I have.

Private Sub btnQuery_Click()
On Error GoTo Err_btnQuery_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "CustomerLedger"

stLinkCriteria = "SELECT Customer.ID, Customer.ABREV_NAME, " & _
"Customer.AREA_STS, Customer.MEDIA, Customer.BAN_ROUTE" & _
", Customer.CURTAIL_RT, Customer.MTR_ADDRS" & _
", Customer.RN_1_TY_SZ, Customer.CC_RT_NBR , Customer.RUN_1_NMBR"
stLinkCriteria = stLinkCriteria & " FROM Customer "
stLinkCriteria = stLinkCriteria & " WHERE Customer.ID < 500000 " & _
" And Customer.ABREV_NAME <> ""ZZZ"" " & _
" And Customer.AREA_STS <> ""archive"" " & _
" And Customer.MEDIA = ""c"" " & _
" And Customer.BAN_ROUTE = " & Me.cboBook & """ " & _
stLinkCriteria = stLinkCriteria & " ORDER BY Customer.CURTAIL_RT"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnQuery_Click:
Exit Sub

Err_btnQuery_Click:
MsgBox Err.Description
Resume Exit_btnQuery_Click

End Sub

This is not returning any info. only my blank form.


Gerald





John said:
First, you need to build a string to pass in stLinkCriteria

stLinkCriteria = "SELECT Customer.ID, Customer.ABREV_NAME, " & _
"Customer.AREA_STS, Customer.MEDIA, Customer.BAN_ROUTE" & _
", Customer.CURTAIL_RT, Customer.MTR_ADDRS" & _
", Customer.RN_1_TY_SZ, Customer.CC_RT_NBR , Customer.RUN_1_NMBR"
stLinkCriteria = stLinkCriteria & " FROM Customer "
stLinkCriteria = stLinkCriteria & " WHERE Customer.ID < 500000 " & _
" And Customer.ABREV_NAME <> ""ZZZ"" " & _
" And Customer.AREA_STS <> ""archive"" " & _
" And Customer.MEDIA = ""c"" " & _
" And Customer.BAN_ROUTE = """ & me.cboBook & """ " & _
stLinkCriteria = stLinkCriteria & " ORDER BY Customer.CURTAIL_RT"

If Ban_Route is a number field instead of a text field change the line in
the where clause by changing the two """ to just "
I have about 20 or so queries that I need to run at diffrerent times.
There
is only one element that changes in each query and it is the same one. I
Click to expand...
[quoted text clipped - 35 lines]
Gerald
Click to expand...
Click to expand...
Click to expand...
 
G

gjameson via AccessMonster.com

stLinkCriteria is coming up False ? Does not look like it is getting written
too.



John said:
Private Sub btnQuery_Click()
On Error GoTo Err_btnQuery_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "CustomerLedger"

stLinkCriteria = "SELECT Customer.ID, Customer.ABREV_NAME, " & _
"Customer.AREA_STS, Customer.MEDIA, Customer.BAN_ROUTE" & _
", Customer.CURTAIL_RT, Customer.MTR_ADDRS" & _
", Customer.RN_1_TY_SZ, Customer.CC_RT_NBR , Customer.RUN_1_NMBR"
stLinkCriteria = stLinkCriteria & " FROM Customer "
stLinkCriteria = stLinkCriteria & " WHERE Customer.ID < 500000 " & _
" And Customer.ABREV_NAME <> ""ZZZ"" " & _
" And Customer.AREA_STS <> ""archive"" " & _
" And Customer.MEDIA = ""c"" " & _
" And Customer.BAN_ROUTE = " & Me.cboBook & _
stLinkCriteria = stLinkCriteria & " ORDER BY Customer.CURTAIL_RT"

'===============================
' I removed the quotes after me.CboBook. I assumed that
'Ban_Route is a NUMBER field
'and not a text field containing number characters.

'Insert these 2 lines for debug purposes
Debug.Print stLinkCriteria
Stop

'Copy the sql statement that is printed in the immediate window
'Paste it into a NEW query and see if it executes - if so then you've
constructed a valid
'statement. If not, look at the error and see what is wrong with the SQL
you've
'constructed. Fix the code and try again.
'===============================

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnQuery_Click:
Exit Sub

Err_btnQuery_Click:
MsgBox Err.Description
Resume Exit_btnQuery_Click

End Sub
Thanks for the quick response John. Here is what I have.
Click to expand...
[quoted text clipped - 54 lines]
Click to expand...
 
J

John Spencer

An error in my coding I had an extra & _ at the end of the line
" And Customer.BAN_ROUTE = " & Me.cboBook & _

Remove that final & _ on that line

stLinkCriteria = "SELECT Customer.ID, Customer.ABREV_NAME, " & _
"Customer.AREA_STS, Customer.MEDIA, Customer.BAN_ROUTE" & _
", Customer.CURTAIL_RT, Customer.MTR_ADDRS" & _
", Customer.RN_1_TY_SZ, Customer.CC_RT_NBR , Customer.RUN_1_NMBR"
stLinkCriteria = stLinkCriteria & " FROM Customer "
stLinkCriteria = stLinkCriteria & " WHERE Customer.ID < 500000 " & _
" And Customer.ABREV_NAME <> ""ZZZ"" " & _
" And Customer.AREA_STS <> ""archive"" " & _
" And Customer.MEDIA = ""c"" " & _
" And Customer.BAN_ROUTE = " & Me.cboBook

stLinkCriteria = stLinkCriteria & " ORDER BY Customer.CURTAIL_RT"
gjameson via AccessMonster.com said:
stLinkCriteria is coming up False ? Does not look like it is getting
written
too.



John said:
Private Sub btnQuery_Click()
On Error GoTo Err_btnQuery_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "CustomerLedger"

stLinkCriteria = "SELECT Customer.ID, Customer.ABREV_NAME, " & _
"Customer.AREA_STS, Customer.MEDIA, Customer.BAN_ROUTE" & _
", Customer.CURTAIL_RT, Customer.MTR_ADDRS" & _
", Customer.RN_1_TY_SZ, Customer.CC_RT_NBR , Customer.RUN_1_NMBR"
stLinkCriteria = stLinkCriteria & " FROM Customer "
stLinkCriteria = stLinkCriteria & " WHERE Customer.ID < 500000 " & _
" And Customer.ABREV_NAME <> ""ZZZ"" " & _
" And Customer.AREA_STS <> ""archive"" " & _
" And Customer.MEDIA = ""c"" " & _
" And Customer.BAN_ROUTE = " & Me.cboBook & _
stLinkCriteria = stLinkCriteria & " ORDER BY Customer.CURTAIL_RT"

'===============================
' I removed the quotes after me.CboBook. I assumed that
'Ban_Route is a NUMBER field
'and not a text field containing number characters.

'Insert these 2 lines for debug purposes
Debug.Print stLinkCriteria
Stop

'Copy the sql statement that is printed in the immediate window
'Paste it into a NEW query and see if it executes - if so then you've
constructed a valid
'statement. If not, look at the error and see what is wrong with the SQL
you've
'constructed. Fix the code and try again.
'===============================

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnQuery_Click:
Exit Sub

Err_btnQuery_Click:
MsgBox Err.Description
Resume Exit_btnQuery_Click

End Sub
Thanks for the quick response John. Here is what I have.
Click to expand...
[quoted text clipped - 54 lines]
Gerald
Click to expand...
Click to expand...
Click to expand...
 
G

gjameson via AccessMonster.com

Yes much better. Thank You. I will try that query and see what happens.

Gerald

John said:
An error in my coding I had an extra & _ at the end of the line
" And Customer.BAN_ROUTE = " & Me.cboBook & _

Remove that final & _ on that line

stLinkCriteria = "SELECT Customer.ID, Customer.ABREV_NAME, " & _
"Customer.AREA_STS, Customer.MEDIA, Customer.BAN_ROUTE" & _
", Customer.CURTAIL_RT, Customer.MTR_ADDRS" & _
", Customer.RN_1_TY_SZ, Customer.CC_RT_NBR , Customer.RUN_1_NMBR"
stLinkCriteria = stLinkCriteria & " FROM Customer "
stLinkCriteria = stLinkCriteria & " WHERE Customer.ID < 500000 " & _
" And Customer.ABREV_NAME <> ""ZZZ"" " & _
" And Customer.AREA_STS <> ""archive"" " & _
" And Customer.MEDIA = ""c"" " & _
" And Customer.BAN_ROUTE = " & Me.cboBook

stLinkCriteria = stLinkCriteria & " ORDER BY Customer.CURTAIL_RT"
stLinkCriteria is coming up False ? Does not look like it is getting
written
Click to expand...
[quoted text clipped - 51 lines]
Click to expand...
 
G

gjameson via AccessMonster.com

My query is right and I am getting the value from my combobox. The critera is
not working when opening the form.

Gerald
Yes much better. Thank You. I will try that query and see what happens.

Gerald
An error in my coding I had an extra & _ at the end of the line
" And Customer.BAN_ROUTE = " & Me.cboBook & _
Click to expand...
[quoted text clipped - 18 lines]
Click to expand...
 
J

John Spencer

Check your line that is opening the form, I think that you may have too many
commas

DoCmd.OpenForm stDocName, , , stLinkCriteria
Should read
DoCmd.OpenForm stDocName, , stLinkCriteria

You were trying to pass the SQL statement as a WHERE clause. That can be
done, but then you need to make sure you are only passing the where clause
and you drop the table names unless you have two tables with an identical
field name. So with your current example, the following might work for you

'Just the where clause - without the word WHERE
stLinkCriteria = "ID < 500000 " & _
" And ABREV_NAME <> ""ZZZ"" " & _
" And AREA_STS <> ""archive"" " & _
" And MEDIA = ""c"" " & _
" And BAN_ROUTE = " & Me.cboBook

DoCmd.OpenForm stDocName, , , stLinkCriteria

gjameson via AccessMonster.com said:
My query is right and I am getting the value from my combobox. The critera
is
not working when opening the form.

Gerald
Yes much better. Thank You. I will try that query and see what happens.

Gerald
An error in my coding I had an extra & _ at the end of the line
" And Customer.BAN_ROUTE = " & Me.cboBook & _
Click to expand...
[quoted text clipped - 18 lines]
Gerald
Click to expand...
Click to expand...
Click to expand...
 
G

gjameson via AccessMonster.com

Hi John,

Yea I found that extra comma too, but still no luck. But, I did build my
query in the Form Property.Control Source and this thing works like a champ.
Thank you for your help. I did learn a lot from this. Now on to my Search
routine.

Gerald

John said:
Check your line that is opening the form, I think that you may have too many
commas

DoCmd.OpenForm stDocName, , , stLinkCriteria
Should read
DoCmd.OpenForm stDocName, , stLinkCriteria

You were trying to pass the SQL statement as a WHERE clause. That can be
done, but then you need to make sure you are only passing the where clause
and you drop the table names unless you have two tables with an identical
field name. So with your current example, the following might work for you

'Just the where clause - without the word WHERE
stLinkCriteria = "ID < 500000 " & _
" And ABREV_NAME <> ""ZZZ"" " & _
" And AREA_STS <> ""archive"" " & _
" And MEDIA = ""c"" " & _
" And BAN_ROUTE = " & Me.cboBook

DoCmd.OpenForm stDocName, , , stLinkCriteria
My query is right and I am getting the value from my combobox. The critera
is
Click to expand...
[quoted text clipped - 11 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dcount without a specific query 2

Top