Runtime 3075 syntax error (comma) in query expression (1 Viewer)

[anb001]

I have written a simple piece of code using DLookup to find a value in a query, but keeps getting above error. I have used it with text in the passed (slightly different code) where it worked fine.

Code is used in a After update expression in a combobox:

Me.txtUllageVolume = Nz(DLookup("M3", "qryUllage", "Ullage=" & Me.cboUllage))

Basically all is updated as "numbers" as data type. In my regional settings the comma is used as a decimal separator.

If I select any value in the combobox (e.g. '0,42'), I get this error:

Run-time error '3075':

Syntax error (comma) in query expression 'Ullage=0,42'.

I tried to change above code, so it says "0.42" (using a dot, not comma). Then it works for some reason. However all data in the table, which the query is based on use comma's.

Can anyone tell me what to do?

 

[sneuberg]

Try using using the full reference like:

Me.txtUllageVolume = Nz(DLookup("M3", "qryUllage", "Ullage= Forms![TheNameofTheForm]!cboUllage"))

I've seen these work without the full reference like:

Me.txtUllageVolume = Nz(DLookup("M3", "qryUllage", "Ullage= cboUllage"))

too, but I'm not sure if that always works.

 

[The_Doc_Man]

If that field is numeric (using data type Currency, e.g.) then you might be able to pick up the value and reformat it. If the field is of type String (so that the comma is actually expressed in the string rather than added by internal formatting), you SHOULD be able to use mixed double-quote and single-quote in your lookup.

I.e. if the value is internally numeric, you should be able to use

Me.txtUllageVolume = Nz(DLookup("M3", "qryUllage", "Ullage=" & Me.cboUllage))

If the value is internally in text format, you should be able to use

Me.txtUllageVolume = Nz(DLookup("M3", "qryUllage", "Ullage='" & Me.cboUllage & "'"))

To be honest, I've not worked with databases having the comma as the decimal marker before, so I'm a bit surprised that this problem occurred if the values were numeric. Which makes me think something else might be going on. What format & data type are the cboUllage and M3 fields that are part of that lookup?

 

[sneuberg]

To be honest, I've not worked with databases having the comma as the decimal marker before, so I'm a bit surprised that this problem occurred if the values were numeric. Which makes me think something else might be going on. What format & data type are the cboUllage and M3 fields that are part of that lookup?

If you google something like, "MS Access regional settings decimal separator problem" you can find lengthy threads with this problem that typical end with some sort of hack using the Val function. What's not commonly known is that with DLookup (and probably the other aggregate functions) is that you don't have the concatenate in the values of form controls into the criteria. When you just put it in the full reference to the control like:

"Ullage= Forms![TheNameofTheForm]!cboUllage"

Then it doesn't make any difference what the data type of Ullage is. I can be a date, text, or numeric. They all work. Access figures it out. That's why I'm betting it would fix this problem.

 

[boerbende]

Comma issues are known to me (Norway)

When I put numbers in a query with VBA, I use
YourQuerystring & Replace(Cstr(YourNumber), ",”,".")

So actually a double action, force the value to string and replace the comma to point.

 

[anb001]

Gents,

I tried with below, and that did the trick:

"Ullage= Forms![TheNameofTheForm]!cboUllage"

All data type/formatting is numeric in the table/combo's/text box used.

Thanks a million for the help

 

[gemma-the-husky]

Me.txtUllageVolume = Nz(DLookup("M3", "qryUllage", "Ullage= cboUllage"))

is this fixed?

you need a value if null. such as these depending on the type of the field being looked up.


Me.txtUllageVolume = Nz(DLookup("M3", "qryUllage", "Ullage= cboUllage"),0)

Me.txtUllageVolume = Nz(DLookup("M3", "qryUllage", "Ullage= cboUllage"),"")

 

[sneuberg]

Me.txtUllageVolume = Nz(DLookup("M3", "qryUllage", "Ullage= cboUllage"))

is this fixed?

you need a value if null. such as these depending on the type of the field being looked up.


Me.txtUllageVolume = Nz(DLookup("M3", "qryUllage", "Ullage= cboUllage"),0)

Me.txtUllageVolume = Nz(DLookup("M3", "qryUllage", "Ullage= cboUllage"),"")

The problem that involved a comma as a decimal separator was solved according to the OP. His use of the Nz function wasn't an issue but this web page says

value_if_null
Optional. It is the value to use when the variant is a null value. If this parameter is omitted and the variant is a null value, the Nz function will return a zero or a zero-length string.

So maybe the OP doesn't need to specify a value but since it "will return a zero or a zero-length string" I wonder what determines which is returned when nothing is specified.

Edit: ?Nz(null) in the immediate window returns an empty string which I determine by ?nz(null) = vbnullstring which returns true.

 

[CJ_London]

?nz(null) = vbnullstring

>nz(null)=0 will also return true

 

[sneuberg]

Interest

?nz(null) = vbnullstring and nz(null) = 0

returns True also.

?VarType(Nz(Null))

returns 0 which is Empty (uninitialized) according to this web page.

This doesn't make sense to me. Anyone know why this is like this?

 

[CJ_London]

I seem to remember a big discussion a few years ago about the nz function and nulls because I said (I think it was me) nz returns 0 or zls depending on the context in which it is being used. Although this is the effect, it is not correct explanation - how can nz know the context?

But this is what it says here

https://support.office.com/en-us/ar...7fd9f4c69b6c?ui=en-US&rs=en-US&ad=US&fromAR=1

I've tried to find that discussion, but without success - I think Markk was one of the respondents

 

[anb001]

This solution works as well:

Me.txtUllageVolume = Nz(DLookup("M3", "qryUllage", "Ullage= cboUllage"),0)
Me.txtUllageVolume = Nz(DLookup("M3", "qryUllage", "Ullage= cboUllage"),"")

Thank you again for the suggestions.

 

[gemma-the-husky]

actually the result is quite useful to me.

I have tied myself in knots sometimes trying to use a common sub to return a nz that may be 0 or "". I didn't realize it was optional.

 

[The_Doc_Man]

I wonder less about the value returned from NZ and more about the fact that VBA is intimately involved here. NZ is a function that will normally return a VARIANT and you know that because it can return EITHER a "" or a 0 as possible answers. The only way to be able to return both is to return via a variant.

The more important part of that is that there is usually a VBA context for which automatic variable-type adjustment can occur. If the NZ is part of an expression and returns a value according to its second argument (meaning the first argument was in fact null), the VBA expression manager "promotes" the variant to whatever it needs to be. You would still run into problems if you forced a "" return in a numeric expression, I think, because that would lead to a type mismatch. Ditto returning 0 to a string expression.

The case where the second argument was not specified means (or I take it to mean) that the VBA expression context simply converted that variant, which is either empty or zero by default, into whatever it needed for the expression context. And in the specific case of "variable = NZ(arg1,optional-arg2)" the presence of the equals-sign GUARANTEES conversion to the type of the variable on the left.

nz(null)=0 will also return true

?nz(null) = vbnullstring which returns true

In both cases your problem is that there is a relational operator in context with the NZ component, so you cannot tell from those tests what data type actually came back. VBA gets there first. Sort of a "programmer's Heisenberg Principle" in action here - the act of testing something in VBA context can interfere with the test.

I know that in some languages you can overdefine your function names so that if they get a text argument they return one thing whereas if they get a numeric argument, they return something else. I know for a fact that Ada does this. Don't know if the implementation language behind the scenes for VBA would do it, but if so, it would go a long way towards clarifying - or perhaps muddying - this question.