473,320 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
4 3112
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
"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
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
@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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Craig Keightley | last post by:
I have a mysql database with a list of companies who supply specific products tblSuppliers (simplified) sID | sName | goodsRefs 1 | comp name | 1,2,3,4,5 2 | company 2 | 2,4
8
by: Joseph | last post by:
I have a textBox that people writes stories in it. They can use for format. I have Aspell installed on the server, so people can make correction to their text. Sometimes, they forget to add a...
15
by: AK | last post by:
Once upon a time there was a table: CREATE TABLE VENDOR(VENDOR_ID INT, NAME VARCHAR(50), STATE CHAR(2))@ in a while the developers realized that a vendor may be present in xseveral states, so...
1
by: John Wheeler | last post by:
Hi, We upgraded the Windows ADC V8.1 to fixpack 5 on some Windows 2000 (Dutch Version) professional machines and something strange happened. When we select decimal columns in our queries the...
12
by: Serve Laurijssen | last post by:
Is code like the following allowed? I am talking about the comma after the last function in the initializer. void f(void) {puts("f");} void g(void) {puts("g");} struct Funcs { void...
2
by: John Buell | last post by:
When I generate an html email to send via SmtpMail, it will (apparently) randomly remove a period in the filename for some image and anchor tags, but not all For example, in the email body, i may...
10
by: Ryan Dahl | last post by:
Hi, I'm working on a simple performance-program, where I need to extract information from the 2 newest periods for every performance-indicator - And from there calculate a trend between these...
3
by: SMusic | last post by:
Can anyone find me a solution. I want to import csv file into mysql database. I have the code to insert.But the rows that contains comma in data field is not getting inserted and is skipped.All the...
1
by: udaypawar | last post by:
Hi All, I have one problem here with mysql stored procedures. I have a list of ids seperated by comma e.g., (" 'A', 'B', 'C' "). I am passing the same to mysql stored procedure as a parameter....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.