Solved Dlookup still showing #error on new record line (1 Viewer)

[sspreyer]

Hi ,
cant work out why #error is still showing on new record line i thought Nz() would supress it ?

=Nz(DLookUp("sumoftotal","[Query1]","[CV_Order_ID_TXT] = " & [Forms]![Manual_Order_FRM]![CV_Order_ID_TXT]),0)

thanks in advance

shane

 

[theDBguy]

Hmm, try adding one more Nz(), just in case.

=Nz(DLookUp("sumoftotal","[Query1]","[CV_Order_ID_TXT] = " & Nz([Forms]![Manual_Order_FRM]![CV_Order_ID_TXT],0)),0)

Hope that helps...

 

[plog]

With a name like [CV_Order_ID_TXT] I am guessing the datatype is not a number but text. When you build criteria strings that themselves contain a string you must escape around it by using single quotes:

....,"[CV_Order_ID_TXT] = '" & [Forms]![Manual_Order_FRM]![CV_Order_ID_TXT] & "'"),0)

Its hard for a human eye to see, but I put single quotes inside the double quotes before and after inserting the variable into the criteria sring.

 

[sspreyer]

Hmm, try adding one more Nz(), just in case.

=Nz(DLookUp("sumoftotal","[Query1]","[CV_Order_ID_TXT] = " & Nz([Forms]![Manual_Order_FRM]![CV_Order_ID_TXT],0)),0)

Hope that helps...

spot on !! thank you

 

[sspreyer]

With a name like [CV_Order_ID_TXT] I am guessing the datatype is not a number but text. When you build criteria strings that themselves contain a string you must escape around it by using single quotes:

....,"[CV_Order_ID_TXT] = '" & [Forms]![Manual_Order_FRM]![CV_Order_ID_TXT] & "'"),0)

Its hard for a human eye to see, but I put single quotes inside the double quotes before and after inserting the variable into the criteria sring.

no its a number field txt is just referance for the text box

 

[theDBguy]

spot on !! thank you

Hi. Glad to hear it worked for you. @plog and I we're happy to assist. Good luck with your project.