By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,761 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,761 IT Pros & Developers. It's quick & easy.

periods and comma's, VBA and queries...............

P: n/a
Hiya!

I have a form with a combobox. After updating the combobox a variable
is filled with the value of the combobox. The value in the combobox
can either be typed or chosen from a underlying query.
The found value is then fed into a variable which in turn is used in a
string (strSQL) that is executed using querydef.

It then creates the following string:

strSQL = "SELECT CG_OKGR_ID AS ZOEKVELD , CG_EFF_PERC_CR_4 FROM
qryCreditGeld GROUP BY CG_EFF_PERC_CR_4, CG_OKGR_ID HAVING
(CG_EFF_PERC_CR_4 = 2,238) ORDER BY CG_OKGR_ID;"

Set qryDef = db.CreateQueryDef("myQuery", strSQL)

In this case I want all numbers equal to 2.238

Now, when in the countrysettings the decimalsymbol is set to a comma
and the groupsymbol as a period (f.e. 1.234,56) the code will fail
reporting error 3075 (Syntax error in query expression
(CG_EFF_PERC_CR_4 = 2,238) ) regardless of what I type; 2.238 / 2,238
or chosen from the combobox

When I turn settings around I get the same error if I type 2,238 but
no errors when I choose the value from the combolist or when typing
2.238

This behaviour can be reproduced. Clearly it has to do with the
countrysettings and I supsect a conflict between dutch settings
(decimalsymbol set to a comma and the groupsymbol as a period) and VB
settings, these are american I think.
Is there a solution to this? Can I for example tell VBA to use dutch
settings within this database?

Very curious whether the problem is known and whether there are
solutions,

thanks for thinking with me

Henro

May 23 '07 #1
Share this Question
Share on Google+
4 Replies

P: n/a
No suggestions at all to the problem mentioned below?

Or am I missing something and is this just a stupid question?
Henrootje schreef:
Hiya!

I have a form with a combobox. After updating the combobox a variable
is filled with the value of the combobox. The value in the combobox
can either be typed or chosen from a underlying query.
The found value is then fed into a variable which in turn is used in a
string (strSQL) that is executed using querydef.

It then creates the following string:

strSQL = "SELECT CG_OKGR_ID AS ZOEKVELD , CG_EFF_PERC_CR_4 FROM
qryCreditGeld GROUP BY CG_EFF_PERC_CR_4, CG_OKGR_ID HAVING
(CG_EFF_PERC_CR_4 = 2,238) ORDER BY CG_OKGR_ID;"

Set qryDef = db.CreateQueryDef("myQuery", strSQL)

In this case I want all numbers equal to 2.238

Now, when in the countrysettings the decimalsymbol is set to a comma
and the groupsymbol as a period (f.e. 1.234,56) the code will fail
reporting error 3075 (Syntax error in query expression
(CG_EFF_PERC_CR_4 = 2,238) ) regardless of what I type; 2.238 / 2,238
or chosen from the combobox

When I turn settings around I get the same error if I type 2,238 but
no errors when I choose the value from the combolist or when typing
2.238

This behaviour can be reproduced. Clearly it has to do with the
countrysettings and I supsect a conflict between dutch settings
(decimalsymbol set to a comma and the groupsymbol as a period) and VB
settings, these are american I think.
Is there a solution to this? Can I for example tell VBA to use dutch
settings within this database?

Very curious whether the problem is known and whether there are
solutions,

thanks for thinking with me

Henro
Jun 4 '07 #2

P: n/a
"Henrootje" <He****@gmail.comwrote in message
<11**********************@n4g2000hsb.googlegroups. com>:
No suggestions at all to the problem mentioned below?

Or am I missing something and is this just a stupid question?
Henrootje schreef:
>Hiya!

I have a form with a combobox. After updating the combobox a
variable is filled with the value of the combobox. The value in the
combobox can either be typed or chosen from a underlying query.
The found value is then fed into a variable which in turn is used in
a string (strSQL) that is executed using querydef.

It then creates the following string:

strSQL = "SELECT CG_OKGR_ID AS ZOEKVELD , CG_EFF_PERC_CR_4 FROM
qryCreditGeld GROUP BY CG_EFF_PERC_CR_4, CG_OKGR_ID HAVING
(CG_EFF_PERC_CR_4 = 2,238) ORDER BY CG_OKGR_ID;"

Set qryDef = db.CreateQueryDef("myQuery", strSQL)

In this case I want all numbers equal to 2.238

Now, when in the countrysettings the decimalsymbol is set to a comma
and the groupsymbol as a period (f.e. 1.234,56) the code will fail
reporting error 3075 (Syntax error in query expression
(CG_EFF_PERC_CR_4 = 2,238) ) regardless of what I type; 2.238 /
2,238 or chosen from the combobox

When I turn settings around I get the same error if I type 2,238 but
no errors when I choose the value from the combolist or when typing
2.238

This behaviour can be reproduced. Clearly it has to do with the
countrysettings and I supsect a conflict between dutch settings
(decimalsymbol set to a comma and the groupsymbol as a period) and
VB settings, these are american I think.
Is there a solution to this? Can I for example tell VBA to use dutch
settings within this database?

Very curious whether the problem is known and whether there are
solutions,

thanks for thinking with me

Henro
When you concatenate strings that you pass to the Jet engine for
evaluation/execution, try to replace the unvanted characters

MyVar = 2.238
strSQL = "SELECT CG_OKGR_ID AS ZOEKVELD , CG_EFF_PERC_CR_4 " & _
"FROM qryCreditGeld " & _
"GROUP BY CG_EFF_PERC_CR_4, CG_OKGR_ID " & _
"HAVING CG_EFF_PERC_CR_4 = " & Replace(MyVar, ",", ".") & _
"ORDER BY CG_OKGR_ID;"

--
Roy-Vidar
Jun 4 '07 #3

P: n/a
On Jun 4, 7:42 am, RoyVidar <roy_vidarNOS...@yahoo.nowrote:
"Henrootje" <Hen...@gmail.comwrote in message

<1180952744.378650.264...@n4g2000hsb.googlegroups. com>:
No suggestions at all to the problem mentioned below?
Or am I missing something and is this just a stupid question?
Henrootje schreef:
Hiya!
I have a form with a combobox. After updating the combobox a
variable is filled with the value of the combobox. The value in the
combobox can either be typed or chosen from a underlying query.
The found value is then fed into a variable which in turn is used in
a string (strSQL) that is executed using querydef.
It then creates the following string:
strSQL = "SELECT CG_OKGR_ID AS ZOEKVELD , CG_EFF_PERC_CR_4 FROM
qryCreditGeld GROUP BY CG_EFF_PERC_CR_4, CG_OKGR_ID HAVING
(CG_EFF_PERC_CR_4 = 2,238) ORDER BY CG_OKGR_ID;"
Set qryDef = db.CreateQueryDef("myQuery", strSQL)
In this case I want all numbers equal to 2.238
Now, when in the countrysettings the decimalsymbol is set to a comma
and the groupsymbol as a period (f.e. 1.234,56) the code will fail
reporting error 3075 (Syntax error in query expression
(CG_EFF_PERC_CR_4 = 2,238) ) regardless of what I type; 2.238 /
2,238 or chosen from the combobox
When I turn settings around I get the same error if I type 2,238 but
no errors when I choose the value from the combolist or when typing
2.238
This behaviour can be reproduced. Clearly it has to do with the
countrysettings and I supsect a conflict between dutch settings
(decimalsymbol set to a comma and the groupsymbol as a period) and
VB settings, these are american I think.
Is there a solution to this? Can I for example tell VBA to use dutch
settings within this database?
Very curious whether the problem is known and whether there are
solutions,
thanks for thinking with me
Henro

When you concatenate strings that you pass to the Jet engine for
evaluation/execution, try to replace the unvanted characters

MyVar = 2.238
strSQL = "SELECT CG_OKGR_ID AS ZOEKVELD , CG_EFF_PERC_CR_4 " & _
"FROM qryCreditGeld " & _
"GROUP BY CG_EFF_PERC_CR_4, CG_OKGR_ID " & _
"HAVING CG_EFF_PERC_CR_4 = " & Replace(MyVar, ",", ".") & _
"ORDER BY CG_OKGR_ID;"

--
Roy-Vidar
As the problem resides in a string output of a numeric value,
converted by the users regional settings, forcing a string before a
concatenation should work:

...
"HAVING CG_EFF_PERC_CR_4 = " & Str(MyVar) & _
Jun 4 '07 #4

P: n/a
@RoyVidar
@Spier

U make this seem so simple.........................
Thank you for your answers!

On 4 jun, 14:50, spier <spie...@yahoo.comwrote:
On Jun 4, 7:42 am, RoyVidar <roy_vidarNOS...@yahoo.nowrote:


"Henrootje" <Hen...@gmail.comwrote in message
<1180952744.378650.264...@n4g2000hsb.googlegroups. com>:
No suggestions at all to the problem mentioned below?
Or am I missing something and is this just a stupid question?
Henrootje schreef:
>Hiya!
>I have a form with a combobox. After updating the combobox a
>variable is filled with the value of the combobox. The value in the
>combobox can either be typed or chosen from a underlying query.
>The found value is then fed into a variable which in turn is used in
>a string (strSQL) that is executed using querydef.
>It then creates the following string:
>strSQL = "SELECT CG_OKGR_ID AS ZOEKVELD , CG_EFF_PERC_CR_4 FROM
>qryCreditGeld GROUP BY CG_EFF_PERC_CR_4, CG_OKGR_ID HAVING
>(CG_EFF_PERC_CR_4 = 2,238) ORDER BY CG_OKGR_ID;"
>Set qryDef = db.CreateQueryDef("myQuery", strSQL)
>In this case I want all numbers equal to 2.238
>Now, when in the countrysettings the decimalsymbol is set to a comma
>and the groupsymbol as a period (f.e. 1.234,56) the code will fail
>reporting error 3075 (Syntax error in query expression
>(CG_EFF_PERC_CR_4 = 2,238) ) regardless of what I type; 2.238 /
>2,238 or chosen from the combobox
>When I turn settings around I get the same error if I type 2,238 but
>no errors when I choose the value from the combolist or when typing
>2.238
>This behaviour can be reproduced. Clearly it has to do with the
>countrysettings and I supsect a conflict between dutch settings
>(decimalsymbol set to a comma and the groupsymbol as a period) and
>VB settings, these are american I think.
>Is there a solution to this? Can I for example tell VBA to use dutch
>settings within this database?
>Very curious whether the problem is known and whether there are
>solutions,
>thanks for thinking with me
>Henro
When you concatenate strings that you pass to the Jet engine for
evaluation/execution, try to replace the unvanted characters
MyVar = 2.238
strSQL = "SELECT CG_OKGR_ID AS ZOEKVELD , CG_EFF_PERC_CR_4 " & _
"FROM qryCreditGeld " & _
"GROUP BY CG_EFF_PERC_CR_4, CG_OKGR_ID " & _
"HAVING CG_EFF_PERC_CR_4 = " & Replace(MyVar, ",", ".") & _
"ORDER BY CG_OKGR_ID;"
--
Roy-Vidar

As the problem resides in a string output of a numeric value,
converted by the users regional settings, forcing a string before a
concatenation should work:

...
"HAVING CG_EFF_PERC_CR_4 = " & Str(MyVar) & _- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Jun 7 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.