UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Comma Delimiter Of Number Generating Error In SQL String, Access 2007    
 
   
Alogon
post Oct 25 2017, 05:40 AM
Post#1



Posts: 23
Joined: 21-November 11



Hello everybody

I use Portuguese language configuration, so ms access uses coma (not point) to decimal delimiter so 15,23 in portuguese means 15.23 in english.

This procedure below is in the module of the frmAuditoria form and serves to open a frmTransmission form already filtered based on values assumed in controls of frmAuditoria.
My question lies in the fact that my codes generates error when the ValorDIR txtbox control of the frmAuditoria assumes fractional value, eg 15675,02 (means 15675.02 in English configuration). But it works if the value is integer, eg 15675,00. See below the critical section of the subroutine.
Thanks so much for any help or comment.

CODE
'String SQL will be used like WhereCondition argumento of OpenForm method and serves to open frmTransmissăo already filtered.
strFrmTransmisFiltro = "tblMalaInspetor.CPF_Intimado = " & Me.CPF_Intimado _
                        & " AND tblMalaInspetor.ValorDIR = " & Me.ValorDIR _
                        & " AND tblMalaInspetor.AnoBase = " & Me.AnoBase

'Note that frmTransmissăo form data source is tblMalaInspetor table.
'This line of code generates runtime error
DoCmd.OpenForm FormName:="frmTransmissăo", View:=acNormal, WhereCondition:=strFrmTransmisFiltro,  WindowMode:=acDialog
Go to the top of the page
 
Jeff B.
post Oct 25 2017, 07:40 AM
Post#2


UtterAccess VIP
Posts: 9,885
Joined: 30-April 10
From: Pacific NorthWet


What error/message is generated?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Alogon
post Oct 26 2017, 06:50 AM
Post#3



Posts: 23
Joined: 21-November 11



Hy Jeff Boyce

Thanks for your reply.

By the way this code run at Windows 10 and Access 2017.

Below, the error message that was generated by the runtime error I have mentiond:

Runtime error '3075'
Sintaxe error (comma) in the query expression
'tblMalaInspetor.CPF_Intimado = 2411849591 AND
tblMalaInspetor.ValorDIR = 139156,2 AND
tblMalaInspetor.AnoBase = 2014'


Note that the problem ocurrs because the value assumed by the txtbox control frmAuditoria.ValorDIR (139156,2) has a comma decimal delimiter. I say that because if frmAuditoria.ValorDIR had assumed a integer number (e.g. 139156), this runtime error would not occur.
Go to the top of the page
 
Alogon
post Oct 26 2017, 06:53 AM
Post#4



Posts: 23
Joined: 21-November 11



ERRATA

Where you see:
By the way this code run at Windows 10 and Access 2017.

Replace by:
By the way this code run at Windows 10 and Access 2007.
Go to the top of the page
 
cheekybuddha
post Oct 26 2017, 06:59 AM
Post#5


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


You might test whether using the .Value property of the textbox gives a different result:
CODE
' ...
strFrmTransmisFiltro = "tblMalaInspetor.CPF_Intimado = " & Me.CPF_Intimado _
                        & " AND tblMalaInspetor.ValorDIR = " & Me.ValorDIR.Value _
                        & " AND tblMalaInspetor.AnoBase = " & Me.AnoBase
Debug.Print strFrmTransmisFiltro
' ...


If that doesn't change anything you can maybe work around the issue by doing something like:
CODE
' ...
strFrmTransmisFiltro = "tblMalaInspetor.CPF_Intimado = " & Me.CPF_Intimado _
                        & " AND tblMalaInspetor.ValorDIR = " & Replace(CStr(Me.ValorDIR), ",", ".") _
                        & " AND tblMalaInspetor.AnoBase = " & Me.AnoBase
Debug.Print strFrmTransmisFiltro
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
orange999
post Oct 26 2017, 07:07 AM
Post#6



Posts: 1,714
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Alogon,

You say had assumed a integer number (e.g. 139156), this runtime error would not occur. .

Are you really dealing with integers and this was bad data?
You might try integer divide to ensure integers.

eg:
?123456.2\1 '<-----Integer divide "\"
123456

--------------------
Good luck with your project!
Go to the top of the page
 
cheekybuddha
post Oct 26 2017, 07:14 AM
Post#7


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Hi orange,

The issue seems to be regional formatting.

SQL does not understand that the comma in the number represents a decimal point.

So, I think the fractional portion is desired.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
orange999
post Oct 26 2017, 07:24 AM
Post#8



Posts: 1,714
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Agree David that's probably the requirement. Just wanted confirmation.

--------------------
Good luck with your project!
Go to the top of the page
 
Alogon
post Nov 25 2017, 06:27 AM
Post#9



Posts: 23
Joined: 21-November 11



Hy everybody

Orange, indeed the issue is about regional formatting.

Cheekybuddha, your second sugestion works fine.
Thanks a lot.

Best regards. thumbup.gif
Go to the top of the page
 
cheekybuddha
post Nov 25 2017, 06:31 AM
Post#10


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


yw.gif

Glad we all could help!

Continued success with your project!

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 07:14 PM