expression in SQL works in control property but not in VBA

  • Thread starter Thread starter Georgios Liakopoulos
  • Start date Start date
G

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
 
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///;"
.....
Click to expand...

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
 
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
Click to expand...

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 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///;"
.....
Click to expand...

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.
Click to expand...

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
 
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
Click to expand...

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.
Click to expand...
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
 
Back
Top