expression in SQL works in control property but not in VBA

[Georgios Liakopoulos]

Hello and thanks for reading
I want to set the RowSource of a combo box using VBA (because I want to
synchronise it with another combo box but I will not bother you with
this - it works fine).
The problem is that my VBA code does not like the [field1] & "-" &
[field2] AS exp1 syntax in the SQL expression (says Run-time error '13':
Type mismatch). BUT, the same expression works fine placed in the Row
Source property of the control (which does not do the trick because I
cannot set criteria after the WHERE clause).

So, line in VBA code that produces the error:
......
Me!combo2.RowSource = "SELECT subprojects.subproject_id,
[subprojects.subproject_id] & " - " & [subprojects.subproject_name] AS
exp1, subprojects.subproject_name, subprojects.project_id FROM
subprojects WHERE .....///the WHERE clause works fine so I am
simplifying thinks///;"
......

And, alternatively, if I set the Row Source in the control properties to:

SELECT subprojects.subproject_id, [subprojects.subproject_id] & " - " &
[subprojects.subproject_name] AS exp1, subprojects.subproject_name,
subprojects.project_id FROM subprojects;

works fine (but without the ability to dynamically set criteria using a
WHERE clause).

Can anyone explain to me why the

[field1] & "-" & [field2] AS exp1

part of the SQL expression works in the second case (control properties)
but not in the first case (VBA)?

Thank you in advance

 

[John W. Vinson]

Hello and thanks for reading
I want to set the RowSource of a combo box using VBA (because I want to
synchronise it with another combo box but I will not bother you with
this - it works fine).
The problem is that my VBA code does not like the [field1] & "-" &
[field2] AS exp1 syntax in the SQL expression (says Run-time error '13':
Type mismatch). BUT, the same expression works fine placed in the Row
Source property of the control (which does not do the trick because I
cannot set criteria after the WHERE clause).

So, line in VBA code that produces the error:
.....
Me!combo2.RowSource = "SELECT subprojects.subproject_id,
[subprojects.subproject_id] & " - " & [subprojects.subproject_name] AS
exp1, subprojects.subproject_name, subprojects.project_id FROM
subprojects WHERE .....///the WHERE clause works fine so I am
simplifying thinks///;"
.....

I suspect that the blanks around the hyphen are confusing the parser, as well
as the bracketing. Try wrapping the expression value in quotemarks and
correcting the bracketing:

Me!combo2.RowSource = "SELECT subprojects.subproject_id, """_
& [subprojects].[subproject_id] & " - " & [subprojects].[subproject_name] _
& """ AS exp1, subprojects.subproject_name, subprojects.project_id FROM
subprojects WHERE ...

You may also want to set a string variable to your SQL expression and then
assign that variable to the RowSource property to make debugging easier.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bob Quintal]

Hello and thanks for reading
I want to set the RowSource of a combo box using VBA (because I
want to synchronise it with another combo box but I will not
bother you with this - it works fine).
The problem is that my VBA code does not like the [field1] & "-" &
[field2] AS exp1 syntax in the SQL expression (says Run-time error
'13': Type mismatch). BUT, the same expression works fine placed
in the Row Source property of the control (which does not do the
trick because I cannot set criteria after the WHERE clause).

So, line in VBA code that produces the error:
.....
Me!combo2.RowSource = "SELECT subprojects.subproject_id,
[subprojects.subproject_id] & " - " &
[subprojects.subproject_name] AS exp1,
subprojects.subproject_name, subprojects.project_id FROM
subprojects WHERE .....///the WHERE clause works fine so I am
simplifying thinks///;" .....

And, alternatively, if I set the Row Source in the control
properties to:

SELECT subprojects.subproject_id, [subprojects.subproject_id] & "
- " & [subprojects.subproject_name] AS exp1,
subprojects.subproject_name, subprojects.project_id FROM
subprojects;

works fine (but without the ability to dynamically set criteria
using a WHERE clause).

Can anyone explain to me why the

[field1] & "-" & [field2] AS exp1

part of the SQL expression works in the second case (control
properties) but not in the first case (VBA)?

Thank you in advance

The VBA parser is getting confused by the quotes around your "-"
symbol.
The SQL expression is this:
[field1] & "-" & [field2] AS exp1
in VBA it's this:
"[field1] & "-" & [field2] AS exp1"
so VBA tries to subtract field2 from field1 which are not numbers.

Change it to
"[field1] & ""-"" & [field2] AS exp1"
and VBA will realize that you want a single doublequiote each side of
the dash.

 

[Georgios Liakopoulos]

Hello and thanks for reading
I want to set the RowSource of a combo box using VBA (because I want to
synchronise it with another combo box but I will not bother you with
this - it works fine).
The problem is that my VBA code does not like the [field1]& "-"&
[field2] AS exp1 syntax in the SQL expression (says Run-time error '13':
Type mismatch). BUT, the same expression works fine placed in the Row
Source property of the control (which does not do the trick because I
cannot set criteria after the WHERE clause).

So, line in VBA code that produces the error:
.....
Me!combo2.RowSource = "SELECT subprojects.subproject_id,
[subprojects.subproject_id]& " - "& [subprojects.subproject_name] AS
exp1, subprojects.subproject_name, subprojects.project_id FROM
subprojects WHERE .....///the WHERE clause works fine so I am
simplifying thinks///;"
.....

I suspect that the blanks around the hyphen are confusing the parser, as well
as the bracketing. Try wrapping the expression value in quotemarks and
correcting the bracketing:

Me!combo2.RowSource = "SELECT subprojects.subproject_id, """_
& [subprojects].[subproject_id]& " - "& [subprojects].[subproject_name] _
& """ AS exp1, subprojects.subproject_name, subprojects.project_id FROM
subprojects WHERE ...

You may also want to set a string variable to your SQL expression and then
assign that variable to the RowSource property to make debugging easier.

Hello John,
I tried this syntax and now I get: Run-time error '2465': database can't
find the field '|' referred to in your expression.

This is the line that causes the problem:
.......
Me!combo2.RowSource = "SELECT subprojects.subproject_id, """ &
[subprojects].[subproject_id] & "-" & [subprojects].[subproject_name] &
""" AS exp1, subprojects.project_id FROM subprojects WHERE
subprojects.project_id = " & Me!notebook_project_id & ";"
.......
Thank you for your response

 

[Georgios Liakopoulos]

Hello and thanks for reading
I want to set the RowSource of a combo box using VBA (because I
want to synchronise it with another combo box but I will not
bother you with this - it works fine).
The problem is that my VBA code does not like the [field1]& "-"&
[field2] AS exp1 syntax in the SQL expression (says Run-time error
'13': Type mismatch). BUT, the same expression works fine placed
in the Row Source property of the control (which does not do the
trick because I cannot set criteria after the WHERE clause).

So, line in VBA code that produces the error:
.....
Me!combo2.RowSource = "SELECT subprojects.subproject_id,
[subprojects.subproject_id]& " - "&
[subprojects.subproject_name] AS exp1,
subprojects.subproject_name, subprojects.project_id FROM
subprojects WHERE .....///the WHERE clause works fine so I am
simplifying thinks///;" .....

And, alternatively, if I set the Row Source in the control
properties to:

SELECT subprojects.subproject_id, [subprojects.subproject_id]& "
- "& [subprojects.subproject_name] AS exp1,
subprojects.subproject_name, subprojects.project_id FROM
subprojects;

works fine (but without the ability to dynamically set criteria
using a WHERE clause).

Can anyone explain to me why the

[field1]& "-"& [field2] AS exp1

part of the SQL expression works in the second case (control
properties) but not in the first case (VBA)?

Thank you in advance

The VBA parser is getting confused by the quotes around your "-"
symbol.
The SQL expression is this:
[field1]& "-"& [field2] AS exp1
in VBA it's this:
"[field1]& "-"& [field2] AS exp1"
so VBA tries to subtract field2 from field1 which are not numbers.

Change it to
"[field1]& ""-""& [field2] AS exp1"
and VBA will realize that you want a single doublequiote each side of
the dash.

Hello Bob,
Now it works fine. It looks so simple if you fix it. Now I understand
the 'Type mismatch' message.

Thank you both for your time
Georgios