Help with SQL Structure

[Guest]

I am going the following error message:

"Sytax error (missing opeator) in query expression "[UnitID]"=1 ORDER BY
tblCategory.Category.Name ASC"



Dim sCategorySource As String

sCategorySource = "SELECT [tblCategory].[CategoryName], [tblCategory].
[UnitID],[tblCategory].[CategoryID] " & _
"FROM tblCategory " & _
"WHERE [UnitID] = " & Me.cboAssignUnit.Value & _
"ORDER BY tblCategory.CategoryName ASC;"

Me.cboCategory.RowSource = sCategorySource
Me.cboCategory.Requery
Me.UnitID = cboAssignUnit.Column(1)

 

[Dirk Goldgar]

I am going the following error message:

"Sytax error (missing opeator) in query expression "[UnitID]"=1 ORDER
BY tblCategory.Category.Name ASC"



Dim sCategorySource As String

sCategorySource = "SELECT [tblCategory].[CategoryName],
[tblCategory]. [UnitID],[tblCategory].[CategoryID] " & _
"FROM tblCategory " & _
"WHERE [UnitID] = " & Me.cboAssignUnit.Value
& _ "ORDER BY tblCategory.CategoryName ASC;"

Me.cboCategory.RowSource = sCategorySource
Me.cboCategory.Requery
Me.UnitID = cboAssignUnit.Column(1)

I suspect your problem is that you're missing a space before the ORDER
BY keywords. Try this:

sCategorySource = _
"SELECT CategoryName, UnitID, CategoryID " & _
"FROM tblCategory " & _
"WHERE UnitID = " & Me.cboAssignUnit.Value & _
" ORDER BY tblCategory.CategoryName ASC;"

 

[Brendan Reynolds]

You seem to be missing a space in front of 'ORDER BY'.

 

[George Nicholson]

"WHERE [UnitID] = " & Me.cboAssignUnit.Value & _

"ORDER BY tblCategory.CategoryName ASC;"

needs a space before the O in OrderBy (or a " " appended to the end of the
prior line) or you end up with:
"WHERE [UnitID] = 1ORDERBY..."

"Sytax error (missing opeator) in query expression "[UnitID]"=1 ORDER BY
tblCategory.Category.Name ASC"

This has one obvious typo on your part (opeator), so I assume there may be
more (better to make sure the error posted is 100% accurate if you want
accurate answers since the message provided usually provides clues....).
1) Make sure that Category.Name doesn't really have that extra dot in your
code.
2) Is [UnitID] really enclosed in quotes in the error message? Something
else is going on, I think. Double check that you have matching pairs of
quotes & double-quotes, etc.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I am going the following error message:

"Sytax error (missing opeator) in query expression "[UnitID]"=1 ORDER BY
tblCategory.Category.Name ASC"



Dim sCategorySource As String

sCategorySource = "SELECT [tblCategory].[CategoryName], [tblCategory].
[UnitID],[tblCategory].[CategoryID] " & _
"FROM tblCategory " & _
"WHERE [UnitID] = " & Me.cboAssignUnit.Value & _
"ORDER BY tblCategory.CategoryName ASC;"

Me.cboCategory.RowSource = sCategorySource
Me.cboCategory.Requery
Me.UnitID = cboAssignUnit.Column(1)

 

[Guest]

Thank You

Brendan and Dirk,

The problem was the missing space in front of "ORDER BY"


Do you have a link to proper SQL statement structure in VB coding,

"WHERE [UnitID] = " & Me.cboAssignUnit.Value & _
"ORDER BY tblCategory.CategoryName ASC;"

needs a space before the O in OrderBy (or a " " appended to the end of the
prior line) or you end up with:
"WHERE [UnitID] = 1ORDERBY..."

"Sytax error (missing opeator) in query expression "[UnitID]"=1 ORDER BY
tblCategory.Category.Name ASC"

This has one obvious typo on your part (opeator), so I assume there may be
more (better to make sure the error posted is 100% accurate if you want
accurate answers since the message provided usually provides clues....).
1) Make sure that Category.Name doesn't really have that extra dot in your
code.
2) Is [UnitID] really enclosed in quotes in the error message? Something
else is going on, I think. Double check that you have matching pairs of
quotes & double-quotes, etc.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I am going the following error message:

"Sytax error (missing opeator) in query expression "[UnitID]"=1 ORDER BY
tblCategory.Category.Name ASC"



Dim sCategorySource As String

sCategorySource = "SELECT [tblCategory].[CategoryName], [tblCategory].
[UnitID],[tblCategory].[CategoryID] " & _
"FROM tblCategory " & _
"WHERE [UnitID] = " & Me.cboAssignUnit.Value & _
"ORDER BY tblCategory.CategoryName ASC;"

Me.cboCategory.RowSource = sCategorySource
Me.cboCategory.Requery
Me.UnitID = cboAssignUnit.Column(1)

 

[Dirk Goldgar]

Thank You

Brendan and Dirk,

The problem was the missing space in front of "ORDER BY"


Do you have a link to proper SQL statement structure in VB coding,

I'm not sure what you're looking for. SQL syntax is documented in the
SQL Reference help file that was installed with Access. Given a
particular SQL statement that you want to build in VBA code, the thing
to remember is that you are using simple string operations, such as
concatenation, that you need to construct a string expression whose
value will be the (syntactically correct) SQL statement you want to
execute. Aside from that, the only other complicating factor is the
natural desire to make the VBA code readable, which usually involves
breaking the statement onto multiple lines, using the VB
line-continuation character. The syntax for strictly VBA operations,
such as string operations and line continuations, are documented in the
VB help file. Depending on your version of Access, you may need to be
in the VB Editor environment to be able to see the VB help topics.

There may be tutorial web pages out there discussing how to build up SQL
statements in VBA code. I don't have any links to such pages, but a
Google search might turn up something.

 

[Brendan Reynolds]

In addition to Doug's good advice, Debug.Printing the SQL string to the
Immediate window often helps in tracking down this kind of problem ...

Dim sCategorySource As String

sCategorySource = "SELECT etc"
Debug.Print sCategorySource

--
Brendan Reynolds
Access MVP

Thank You

Brendan and Dirk,

The problem was the missing space in front of "ORDER BY"


Do you have a link to proper SQL statement structure in VB coding,

"WHERE [UnitID] = " & Me.cboAssignUnit.Value & _
"ORDER BY tblCategory.CategoryName ASC;"

needs a space before the O in OrderBy (or a " " appended to the end of
the
prior line) or you end up with:
"WHERE [UnitID] = 1ORDERBY..."

"Sytax error (missing opeator) in query expression "[UnitID]"=1 ORDER
BY
tblCategory.Category.Name ASC"

This has one obvious typo on your part (opeator), so I assume there may
be
more (better to make sure the error posted is 100% accurate if you want
accurate answers since the message provided usually provides clues....).
1) Make sure that Category.Name doesn't really have that extra dot in
your
code.
2) Is [UnitID] really enclosed in quotes in the error message? Something
else is going on, I think. Double check that you have matching pairs of
quotes & double-quotes, etc.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I am going the following error message:

"Sytax error (missing opeator) in query expression "[UnitID]"=1 ORDER
BY
tblCategory.Category.Name ASC"



Dim sCategorySource As String

sCategorySource = "SELECT [tblCategory].[CategoryName],
[tblCategory].
[UnitID],[tblCategory].[CategoryID] " & _
"FROM tblCategory " & _
"WHERE [UnitID] = " & Me.cboAssignUnit.Value & _
"ORDER BY tblCategory.CategoryName ASC;"

Me.cboCategory.RowSource = sCategorySource
Me.cboCategory.Requery
Me.UnitID = cboAssignUnit.Column(1)

 

[Brendan Reynolds]

Oops, sorry, I mean in addition to Dirk's good advice!

--
Brendan Reynolds
Access MVP

In addition to Doug's good advice, Debug.Printing the SQL string to the
Immediate window often helps in tracking down this kind of problem ...

Dim sCategorySource As String

sCategorySource = "SELECT etc"
Debug.Print sCategorySource

--
Brendan Reynolds
Access MVP

Thank You

Brendan and Dirk,

The problem was the missing space in front of "ORDER BY"


Do you have a link to proper SQL statement structure in VB coding,

"WHERE [UnitID] = " & Me.cboAssignUnit.Value & _
"ORDER BY tblCategory.CategoryName ASC;"
needs a space before the O in OrderBy (or a " " appended to the end of
the
prior line) or you end up with:
"WHERE [UnitID] = 1ORDERBY..."

"Sytax error (missing opeator) in query expression "[UnitID]"=1 ORDER
BY
tblCategory.Category.Name ASC"
This has one obvious typo on your part (opeator), so I assume there may
be
more (better to make sure the error posted is 100% accurate if you want
accurate answers since the message provided usually provides clues....).
1) Make sure that Category.Name doesn't really have that extra dot in
your
code.
2) Is [UnitID] really enclosed in quotes in the error message? Something
else is going on, I think. Double check that you have matching pairs of
quotes & double-quotes, etc.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


I am going the following error message:

"Sytax error (missing opeator) in query expression "[UnitID]"=1 ORDER
BY
tblCategory.Category.Name ASC"



Dim sCategorySource As String

sCategorySource = "SELECT [tblCategory].[CategoryName],
[tblCategory].
[UnitID],[tblCategory].[CategoryID] " & _
"FROM tblCategory " & _
"WHERE [UnitID] = " & Me.cboAssignUnit.Value &
_
"ORDER BY tblCategory.CategoryName ASC;"

Me.cboCategory.RowSource = sCategorySource
Me.cboCategory.Requery
Me.UnitID = cboAssignUnit.Column(1)

 

[Dirk Goldgar]

Oops, sorry, I mean in addition to Dirk's good advice!

Doug gives good advice, too! <g>

 

[Brendan Reynolds]

I'm just doomed to put my foot in my mouth in this thread, aren't I? :-/