Dlookup with comma in field

[richard_0101]

I have a table in which I have a field that contains comma's. The field contains a salesprice stored in European format. I need to do a Dlookup where this field is part of the criteria.
If I do a lookup, I get an error 3075 (comma failure).
If I single or duoble quote I get an error 3464 (type mismatch).

Any suggestions on how to use Dlookup with numeric criteria, containing a comma (other then changing the regional settings and converting comma to dot).

See code below. The problem is in Me![CustPrice] (which contains for instance 1,25).

If Not IsNull(Me![CustPrice]) Then
If Dlookup("[Cust-id]", "Pricetable", "[Cust-id] = " & Me![Cust-id] _
& " And [CustPrice] = " & Me![CustPrice]) & _
& " And [Product-id] = " & Me![Product-id]) Then
msgbox "Already exist"
Cancel = True
End If

 

[Pat Hartman]

You need to enclose strings in single or double quotes.

& " And [CustPrice] = '" & Me![CustPrice]) & "'" & _

Wouldn't it be better to convert CustPrice to currency?

 

[CyberLynx]

And if you insist on not doing as Pat had suggested then try this:

   Dim CustPrice As Variant
   Dim CPrice As Currency
   
   CustPrice = Me![CustPrice]
   'For two decimal points
   CPrice = IIf(Mid$(CustPrice, Len(CustPrice) - 2, 1) = ",", _
                CCur(Left$(CustPrice, Len(CustPrice) - 3) & "." & _
                Right$(CustPrice, 2)), CCur(CustPrice))
   
   'For three decimal Points...
   'CPrice = IIf(Mid$(CustPrice, Len(CustPrice) - 3, 1) = ",", _
                 CCur(Left$(CustPrice, Len(CustPrice) - 4) & "." & _
                 Right$(CustPrice, 3)), CCur(CustPrice))

   If Not IsNull(Me![CustPrice]) Then
      If Nz(DLookup("[Cust-id]", "[Pricetable]", "[Cust-id] = " & _
                   Me![Cust-id] And [CustPrice] = " & CPrice) " & _
                   "AND [Product-id] = " & Me![Product-id]), 0) > 0 Then
         MsgBox "Already exist"
         Cancel = True
      End If
   End If

As Pat had suggested....far better to have CustPrice as a Currency DataType.

.

 

[richard_0101]

Thanks for the reply, but the custprice is a currency field.
As I mentioned, the database automaticly puts a comma as a decimal seperator due to regional settings.

For PAT's solution: If I put a single quote around it, Dlookup wil still complain about type mismatch (error 3464).
For CyberLynx's solution: The system wil discard the decimal point and give 125 as result in Cprice in stead of 1.25.

Any other suggestions? Maybe an other way to look for a record?

Many thanks in advance.

 

[Pat Hartman]

You could try using a decimal point instead of a comma but I would have thought that your reginal settings would take care of this.

 

[CyberLynx]

In the code I provided, try changing:

CustPrice = Me![CustPrice]

TO

CustPrice = CStr(Me![CustPrice])

.

 

[richard_0101]

Pat, If this was an option I already would have done it. The conseqeunce of adjusting the decimal separator in the regional settings would be that reports would display the decimal point i.s.o the decimal comma. This is not an option.(would require major format changes in reports)

Would it be fair to say the this is a shortcomm of Access (I am using access 2007)? I could imagine that if MS supports decimal separator values such as comma, they would also give the possibility to adjust the query parameters.

CyberLynx, this still gives a 3075 error (syntax error (comma) ...) on the cprice field.

Any thoughts on an other solution?

Many thanks in advance.

 

[namliam]

Would it be fair to say the this is a shortcomm of Access (I am using access 2007)? I could imagine that if MS supports decimal separator values such as comma, they would also give the possibility to adjust the query parameters.

Wow this does seem like a Bug to me, I have never encountered this tho

ccur('" & cPrice & "')")

    Dim x As Double
    x = 23.4
    Debug.Print cPrice, DLookup("aaa", "Table24", "AAA = ccur('" & x & "')")

This seems to work for me, Note that I did set my regional settings to Comma decimals. Despite the dot seperator in the code!
Regardless it does lookup the value in my test table...

Happy Coding

 

[richard_0101]

The ccur in the dlookup did the trick. Unfortunatly I have to convert my custprice first to a variable with a forced dotted decimal separator (like your x in the example).

Many thanks for the input.