Foreign Currency in SQL statement

[Vikki]

I am working on a database that is used in Germany. The code was written with
Regional settings in English (United States) and it worked fine. The
Regional settings were then changed to German(Germany) and retested and it
all went south. I had to "format" the dates because of the conversion
(thanks to a tip from this board), but it appears that I have the same type
of problem with the currency. I am getting--

Run-time error ‘3075’:
Syntax error (comma) in query expression “Sum(sales.retail) Between 0,01 and
259,99.

The code is attached. I assume I need to format the variables LowVal and
HighVal but what about the "Sum(sales.retail)? Also, I am unsure at this
point what that format statement might look like, but I am still scouring the
help files/internet. Can anyone help or at least point me in the proper
direction?

The code is:
Function TotConsCumm(ByVal LowVal As Currency, HighVal As Currency, InvMonth
As Integer, InvYear As Integer) As Long

Dim BeginDate As Variant
Dim EndDate As Variant
Dim SQLString As String
Dim intI As Integer
Dim ConsCnt As Long

' Set the dbs to current database
Set dbs = CurrentDb

BeginDate = Format(DateValue(InvMonth & "/" & InvYear), "\#mm\/dd\/yyyy\#")
EndDate = Format(LastOfMonth(InvMonth, InvYear), "\#mm\/dd\/yyyy\#")

TotConsCumm = 0
ConsCnt = 0

' Get the monthly sales data in one row each for consultants
SQLString = "SELECT Sales.consultant_id, SUM(Sales.retail) FROM Sales " _
& " WHERE Sales.transaction_date Between " + CStr(BeginDate) + "
AND " + CStr(EndDate) + " " _
& " GROUP BY Sales.consultant_id" _
& " HAVING SUM(Sales.retail) Between " + CStr(LowVal) + " AND " +
CStr(HighVal) + " "

Set rstTemp = dbs.OpenRecordset(SQLString)

If rstTemp.RecordCount > 0 Then
rstTemp.MoveLast
rstTemp.MoveFirst
ConsCnt = rstTemp.RecordCount
TotConsCumm = ConsCnt
End If

 

[RoyVidar]

I am working on a database that is used in Germany. The code was
written with Regional settings in English (United States) and it
worked fine. The Regional settings were then changed to
German(Germany) and retested and it all went south. I had to
"format" the dates because of the conversion (thanks to a tip from
this board), but it appears that I have the same type of problem
with the currency. I am getting--

Run-time error ‘3075’:
Syntax error (comma) in query expression “Sum(sales.retail) Between
0,01 and 259,99.

The code is attached. I assume I need to format the variables LowVal
and HighVal but what about the "Sum(sales.retail)? Also, I am
unsure at this point what that format statement might look like, but
I am still scouring the help files/internet. Can anyone help or at
least point me in the proper direction?

The code is:
Function TotConsCumm(ByVal LowVal As Currency, HighVal As Currency,
InvMonth As Integer, InvYear As Integer) As Long

Dim BeginDate As Variant
Dim EndDate As Variant
Dim SQLString As String
Dim intI As Integer
Dim ConsCnt As Long

' Set the dbs to current database
Set dbs = CurrentDb

BeginDate = Format(DateValue(InvMonth & "/" & InvYear),
"\#mm\/dd\/yyyy\#") EndDate = Format(LastOfMonth(InvMonth,
InvYear), "\#mm\/dd\/yyyy\#")

TotConsCumm = 0
ConsCnt = 0

' Get the monthly sales data in one row each for consultants
SQLString = "SELECT Sales.consultant_id, SUM(Sales.retail) FROM
Sales " _ & " WHERE Sales.transaction_date Between " +
CStr(BeginDate) + " AND " + CStr(EndDate) + " " _
& " GROUP BY Sales.consultant_id" _
& " HAVING SUM(Sales.retail) Between " + CStr(LowVal) + "
AND " + CStr(HighVal) + " "

Set rstTemp = dbs.OpenRecordset(SQLString)

If rstTemp.RecordCount > 0 Then
rstTemp.MoveLast
rstTemp.MoveFirst
ConsCnt = rstTemp.RecordCount
TotConsCumm = ConsCnt
End If

Jet operates with US dates and numbers, so you only need to format
the dates and numbers when concatenating them into a string passed
to Jet, so

....SUM(Sales.retail) Between " & Replace(CStr(LowVal), ",", ".") & ...