What is wrong with this?

[DawnTreader]

when my form loads it runs code to determine the status of the product that the issue is about. in the load procedure i have declared the variable ProductStatus as a string. then i try to give it it's value. here is the code that does that:

ProductStatus = Nz((DLookup("PRunStatus", "qryProductStatusFinder", "ProductID = " & Me.ProductID)), "Running")

when running the form access gives me the message that:

runtime error '2447', there is an invalid use of the . (dot) or ! operator or invalid parentheses.

What? where? I dont understand what is wrong as this code worked a few days ago...

any and all help appreciated.

 

[Ken Snell]

Not sure if this is causing the error, but you have an unneeded extra set of
parentheses inside the Nz function. Try this:

ProductStatus = Nz(DLookup("PRunStatus", "qryProductStatusFinder",
"ProductID = " & Me.ProductID), "Running")

 

[Vacuum Sealed]

Not sure if this is causing the error, but you have an unneeded extra set of
parentheses inside the Nz function. Try this:

ProductStatus = Nz(DLookup("PRunStatus", "qryProductStatusFinder",
"ProductID = "& Me.ProductID), "Running")

Further to Ken's suggestion, is the productID numeric or text as Nz()
returns a zero(0) should it be empty.

On the other hand if it is text, and Ken will most likely correct me if
I have the syntax wrong, but would something more like this be better
suited.

ProductStatus = IF(ISNULL(DLookup("PRunStatus", "qryProductStatusFinder",
"ProductID = " & Me.ProductID)), "Running")

purely a "Food for Thought suggestion.

HTH
Mick.

 

[Ken Snell]

Mick -

I didn't focus on the text/numeric issue only because the poster said that
the code had worked previously. It's possible the poster made a change to
the ProductID field's datatype, but that usually would have resulted in an
"Undefined expression" error from the DLookup.

Your suggested expression would need to change (can't use IF this way), and
I would sugggest this:

ProductStatus = IIf(DLookup("PRunStatus", "qryProductStatusFinder",
"ProductID = " & Me.ProductID) IS NULL, "Running", DLookup("PRunStatus",
"qryProductStatusFinder", "ProductID = " & Me.ProductID))

But that isn't really an improvement to using the Nz function, which should
work just fine.


Vacuum -

What may be the problem otherwise could be caused by your code running in
the form's Open event. If that is where you have the code, move it to the
form's Load event. During the Open event, many controls and their bound
fields are not instantiated and thus cannot be referenced via code. The Load
event occurs later, and should be fine for this use.

Alternatively, make sure your value for Me.ProductID doesn't contain
undesired characters (number or letters) that might cause the concatenation
to yield a weird result for the third argument of the DLookup function.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

 

[DawnTreader]

when my form loads it runs code to determine the status of the product that the issue is about. in the load procedure i have declared the variable ProductStatus as a string. then i try to give it it's value. here is the code that does that:

ProductStatus = Nz((DLookup("PRunStatus", "qryProductStatusFinder", "ProductID = " & Me.ProductID)), "Running")

when running the form access gives me the message that:

runtime error '2447', there is an invalid use of the . (dot) or ! operator or invalid parentheses.

What? where? I dont understand what is wrong as this code worked a few days ago...

any and all help appreciated.

thanks gents, got it sorted. the query wasnt returning good values.