Why Oh Why SQL

D

DS

After much help and pain I finally got my Linked to work...the minute I
go to expand from the simple model into something more complex the thing
doesn't work....Perhaps I'm too deep into the forrest to see the trees!
Can anyone see why this SQL statement isn't working. If I take out
the WHERE statement it works. So my guess is that it's somewhere in there.
Any help is appreciated.
Thanks
DS


Private Sub List18_AfterUpdate()
With Me.List4
.RowSource = _
"SELECT [Menu Cat],[Major Cat],[Item Name]" & _
"FROM Items" & _
"WHERE [Menu Cat] = " & Me.List18.Column(0) & "" & _
"AND [Major Cat] = " & Me.List18.Column(1) & ""
Me.List4.Requery
End With
End Sub
 
S

Sylvain Lafontaine

Missing blank space or single quote/double quote.

What kind of type field (integer or string character) are [Menu Cat] and
[Major Cat] ?

S. L.
 
K

Ken Snell [MVP]

You have two double quote characters at the end of the two WHERE lines.

You don't have a space in front of the word WHERE in the first WHERE line.
 
D

DS

Sylvain said:
Missing blank space or single quote/double quote.

What kind of type field (integer or string character) are [Menu Cat] and
[Major Cat] ?

S. L.

After much help and pain I finally got my Linked to work...the minute I go
to expand from the simple model into something more complex the thing
doesn't work....Perhaps I'm too deep into the forrest to see the trees!
Can anyone see why this SQL statement isn't working. If I take out the
WHERE statement it works. So my guess is that it's somewhere in there.
Any help is appreciated.
Thanks
DS


Private Sub List18_AfterUpdate()
With Me.List4
.RowSource = _
"SELECT [Menu Cat],[Major Cat],[Item Name]" & _
"FROM Items" & _
"WHERE [Menu Cat] = " & Me.List18.Column(0) & "" & _
"AND [Major Cat] = " & Me.List18.Column(1) & ""
Me.List4.Requery
End With
End Sub
Click to expand...
Click to expand...
They are both Number Fields.....Whats funny is this coding works fine on
another Database I have.
Thanks
DS
 
K

Ken Snell [MVP]

And you don't have a space in front of the word AND in the second WHERE
line.

--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
You have two double quote characters at the end of the two WHERE lines.

You don't have a space in front of the word WHERE in the first WHERE line.

--

Ken Snell
<MS ACCESS MVP>


DS said:
After much help and pain I finally got my Linked to work...the minute I
go to expand from the simple model into something more complex the thing
doesn't work....Perhaps I'm too deep into the forrest to see the trees!
Can anyone see why this SQL statement isn't working. If I take out the
WHERE statement it works. So my guess is that it's somewhere in there.
Any help is appreciated.
Thanks
DS


Private Sub List18_AfterUpdate()
With Me.List4
.RowSource = _
"SELECT [Menu Cat],[Major Cat],[Item Name]" & _
"FROM Items" & _
"WHERE [Menu Cat] = " & Me.List18.Column(0) & "" & _
"AND [Major Cat] = " & Me.List18.Column(1) & ""
Me.List4.Requery
End With
End Sub
Click to expand...
Click to expand...
 
D

DS

Ken said:
And you don't have a space in front of the word AND in the second WHERE
line.
Click to expand...
Hi Ken,
I tried the space thing. I'm getting confused as why this isn't
working. I have the same exact snipet in another database and it works
fine...actually I copied and pasted it in. The row source works fine.
It's only when I go to add the WHERE is when the trouble begins...I have
4 listboxes. One on the First form, I click it the form closes the new
form opens with 2 more listboxes on it. The first box gets filled from
the info from the first form. The second box gets filled from
information from the first listbox on the second form. This worked fine
and now it has completely crumpled. Could something be corrupted? Also
....all of the examples always show no punctuation why is that, if you
need it for these things to work.
Thank You
Sincerely
DS
 
K

Ken Snell [MVP]

Let's be sure that we're both talking about the same code. Try using the
following step in place of your step:

.RowSource = _
"SELECT [Menu Cat],[Major Cat],[Item Name]" & _
" FROM Items" & _
" WHERE [Menu Cat] = " & Me.List18.Column(0) & _
" AND [Major Cat] = " & Me.List18.Column(1)

Post back with the results.
 
M

M.L. Sco Scofield

Try these.

If your list box is, in fact returning a number:

Private Sub List18_AfterUpdate()
With Me.List4
.RowSource = _
"SELECT [Menu Cat], [Major Cat], [Item Name] " & _
"FROM Items " & _
"WHERE [Menu Cat] = " & Me.List18.Column(0) & _
" AND [Major Cat] = " & Me.List18.Column(1)
Me.List4.Requery
End With
End Sub

However, if your list box is returning text that looks like a number:

Private Sub List18_AfterUpdate()
With Me.List4
.RowSource = _
"SELECT [Menu Cat], [Major Cat], [Item Name] " & _
"FROM Items " & _
"WHERE [Menu Cat] = """ & Me.List18.Column(0) & """" & _
" AND [Major Cat] = """ & Me.List18.Column(1) & """"
Me.List4.Requery
End With
End Sub

Copy and paste these. Do not retype them. There's too much opportunity to
mess up the spaces and quotes.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
S

Sylvain Lafontaine

Suggestion: print or display the resulting SQL string to make sure that it
is OK. You may also Copy and paste this SQL string in a query window to see
if it's valid and return the correct result set when executed.

S. L.
 
D

DS

Sylvain said:
Suggestion: print or display the resulting SQL string to make sure that it
is OK. You may also Copy and paste this SQL string in a query window to see
if it's valid and return the correct result set when executed.

S. L.
Click to expand...
Thanks...I finally got the thing to work...I went back to an early
version and built off of that, perhaps the current database I was using
became corrupted. Thank you everyone for your help.
Sincerely
DS
 
D

DS

Ken said:
Let's be sure that we're both talking about the same code. Try using the
following step in place of your step:

.RowSource = _
"SELECT [Menu Cat],[Major Cat],[Item Name]" & _
" FROM Items" & _
" WHERE [Menu Cat] = " & Me.List18.Column(0) & _
" AND [Major Cat] = " & Me.List18.Column(1)

Post back with the results.
Click to expand...
I tried this and the spaces in front of the FROM, WHERE AND kept causing

the code to go red. I went back to an earlier version and built off of
that....same code and it worked. I think the problem was the underling
queries or tables that were used. I stayed up most of the night and
tried every possibilty...it seems to do alot with the tables underneath
that are being refered to and their relationships with each other. Ken,
thank you for all of your help.
Sincerely
DS
 
D

DS

M.L. Sco Scofield said:
Try these.

If your list box is, in fact returning a number:

Private Sub List18_AfterUpdate()
With Me.List4
.RowSource = _
"SELECT [Menu Cat], [Major Cat], [Item Name] " & _
"FROM Items " & _
"WHERE [Menu Cat] = " & Me.List18.Column(0) & _
" AND [Major Cat] = " & Me.List18.Column(1)
Me.List4.Requery
End With
End Sub

However, if your list box is returning text that looks like a number:

Private Sub List18_AfterUpdate()
With Me.List4
.RowSource = _
"SELECT [Menu Cat], [Major Cat], [Item Name] " & _
"FROM Items " & _
"WHERE [Menu Cat] = """ & Me.List18.Column(0) & """" & _
" AND [Major Cat] = """ & Me.List18.Column(1) & """"
Me.List4.Requery
End With
End Sub

Copy and paste these. Do not retype them. There's too much opportunity to
mess up the spaces and quotes.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com


Ken Snell [MVP] wrote:



Hi Ken,
I tried the space thing. I'm getting confused as why this isn't
working. I have the same exact snipet in another database and it works
fine...actually I copied and pasted it in. The row source works fine.
It's only when I go to add the WHERE is when the trouble begins...I have
4 listboxes. One on the First form, I click it the form closes the new
form opens with 2 more listboxes on it. The first box gets filled from
the info from the first form. The second box gets filled from
information from the first listbox on the second form. This worked fine
and now it has completely crumpled. Could something be corrupted? Also
...all of the examples always show no punctuation why is that, if you
need it for these things to work.
Thank You
Sincerely
DS
Click to expand...
Click to expand...
I think the database I was in became corrupted, I went back and started
from an earlier one and the code that wasn't working inthe later
version, worked in the earlier version. Also I think the underlying
tables had a few things to do with it as well...go figure.
Thanks for your help.
DS
 
Top