syntax error missing operator

[AlvaroCity]

Hello everyone.

I have a problem developing my query. This is to be used in a search form.

The source of the query is just 1 table with 5 fields + 1 calculated.

When I try to insert some criteria in the calcultated field

(FormatCurrency(Dsum("Total","tblOrderDetail","[OrderID]="&[OrderID]))

this does not allow me and it shows an error message with the description: syntax error missing operator.

I think that is because i formated the calculated field with FormatCurrency. When I dont put it it does work but the thing is that I want to show the Euros character.

I have been trying to find out what the problem is after so many hours and I am helpless.

 

[jdraw]

In plain English what are you searching for?
We can't provide focused help until we understand the problem in the business context.

So the more you can tell us about your business, the things involved, and what you're searching for, the sooner you are likely to get some meaningful responses.

Good luck

 

[sneuberg]

I think I'm seeing three opening parenthesis but only two closing in this expression

(FormatCurrency(Dsum("Total","tblOrderDetail","[OrderID]="&[OrderID]))

albeit when I put it in an immediate window the compiler says it expected a ")" and nothing about syntax.

 

[MarkK]

(FormatCurrency(Dsum("Total","tblOrderDetail","[OrderID]="&[OrderID]))

Probably your reference to [OrderID] is returning a Null, so your criteria evaluates to "OrderID=".

You can cause this error with a query text like...

SELECT * FROM YourTable WHERE SomeField =

 

[AlvaroCity]

Hello everyone.
First of all, thank you for all your replies.
The thing is when I dont put the expression "FormatCurrency", the criteria works...
There must be something.
To make it clearer i posted a video on Youtube.

https://youtu.be/oB3f1e9eDPM

I hope this will help. Sorry for my English and i hope you can understand it although the DB is in Spanish.


Thank you.

 

[sneuberg]

I don't know why this is causing a data type mismatch but one way of working around this is just to have two expressions. One without the FormatCurrency in which you put the criteria and another that you use for display. If this query is going to be the record source of a report and won't be displayed directly I suggest doing the formatting in the report.

 

[JHB]

Have you tried to use the CCur function instead of FormatCurrency?
Because there is a difference between the type of value returned,
FormatCurrency return a string type, (value is left-aligned in the field), while CCur return a number type, (value is right-aligned in the field).
As FormatCurrency return a string type and you put in the criteria as number type, you'll a "Data type mismatch in criteria expression" error.

 

[sneuberg]

As FormatCurrency return a string type and you put in the criteria as number type, you'll a "Data type mismatch in criteria expression" error.

I tried that and by itself FormatCurrency doesn't seem to cause that. Somehow it seems that Nulls being returned by the DSum are contributing to the problem. I suspect if the OP adds Nz something like:

TotalPedidoc:  FormatCurrency(Nz(DSum("PrecioTotal", "tblPedidioDetaile", "[PedidoID] = " & [PedidoID]),0))

the problem will go away. Of course if 0 isn't acceptable for these cases then a IIF function could be used.

 

[AlvaroCity]

Thank you sneuberg for your solution

It worked!!!

You saved me. Now i see that there are some values returning 0 euros so that might be the error that it was happening.

thank you everyone for your replies!!

 

[sneuberg]

Another thing to consider is that aggregate functions (DSum, DCount, etc) should be avoided in queries as they can slow the query down. I suggest trying an aggregate query on the details table to do the total and then join that with the orders table. You would just group by the order id and sum the total. Maybe this would solve this problems with the nulls too.

 

[AlvaroCity]

Thank you for the advice.

I'll take the word!!