Could I get some help with a string DLookup?

[JDaly]

This bombs on the StPL= line. I want it to look up a string value from a table with a criterion of matching another string. In immediate mode the form evaluates correctly with ? Form![wo complete]![4Item] as TEST but it returns a null in when executed. I have tinkered with syntax for way too long and would like to get past this part of a project. The lines with """"TEST"" all return a value stored under TEST in the file.

Private Sub WO_AfterUpdate()
    Dim stDate As Date
    Dim stCust As String
    Dim stOrd As String
    Dim stItem As String
    Dim stQty As Integer
    Dim stPL As String
    Dim ststdum As String
    Dim ststdprice As String
    Dim ststdcost As String
 
    stDate = DLookup("[WODate]", "[WO table]", "[WO]=[woid]")
    stCust = DLookup("[Customer]", "[WO table]", "[WO]=[woid]") & " "
    stOrd = DLookup("[salesord]", "[WO table]", "[WO]=[woid]") & " "
    stItem = DLookup("[itemno]", "[WO table]", "[WO]=[woid]")
    stQty = DLookup("[qty]-nz([scrapqty])", "[WO table]", "[WO]=[woid]")
 
 
    [Forms]![wo complete]![1Date] = stDate
    [Forms]![wo complete]![2Cust] = stCust
    [Forms]![wo complete]![3Ord] = stOrd
    [Forms]![wo complete]![4Item] = stItem
    [Forms]![wo complete]![5Qty] = stQty
    stPL = DLookup("[ProductLine]", "[IM1_InventoryMasterfile]", "[ItemNumber] = 'Forms![wo complete]![4Item]'")
    ststdum = DLookup("[StdUM]", "[IM1_InventoryMasterfile]", "[ItemNumber] = ""TEST""")
    ststdprice = DLookup("[StdPrice]", "[IM1_InventoryMasterfile]", "[ItemNumber] = ""TEST""")
    ststdcost = DLookup("[StdCost]", "[IM1_InventoryMasterfile]", "[ItemNumber] = ""TEST""")
    [Forms]![wo complete]![ProductLine] = stPL
    [Forms]![wo complete]![StdUM] = ststdum
    [Forms]![wo complete]![StdPrice] = ststdprice
    [Forms]![wo complete]![StdCost] = ststdcost
 
 
End Sub
 

Thank you for your consideration and help.

[Rabbit]

'Forms![wo complete]1[4Item]'

You have a 1 in there instead of a !.

[JDaly]

I changed it in the code and it still doesn't work, and edited the post to reflect the change.
Thank you for looking through the code and taking the time to help. I have been beating my head against a wall and retyping this with different syntaxes for some time.

[Rabbit]

Remove the single quotes.

[JDaly]

Removed the single quotes. Still gives a null error.
Thanks again.
I wounder if IM1_InventoryMasterfile being a linked file which is read only is affecting this? It evaluates correctly when I substitute in the ""TEST"" and reads the test data but doesn't seem to work with any of the standard string methods even though it evaluates in Ctrl-g as ? [Forms]![wo complete]![4Item] being equal to TEST.

[Rabbit]

What's the full text of the error message? If you're getting a message on null, Try the Nz function.

[JDaly]

Run time error '94':
Invalid use of null
And that error message was after I changes the [Forms]![wo complete]![4Item] to Nz([fORMS]![wo complete]![4Item])

In immediate mode ? Nz[4Item]
evaluates to test as it should but doesn't in the expression.

[Rabbit]

Well, you need to specify what to use instead of Null. Use, Nz([fORMS]![wo complete]![4Item], "")

[JDaly]

I tries somethink that worked. I still don't understand why it worked but I changed the line to
stPL = DLookup("[ProductLine]", "[IM1_InventoryMasterfile]", "[ItemNumber] = [4Item]") & Mid(stItem, 1, 0)
It may be kludgy, and I haven't checked to see what kind of performance hit it gives but for the time being I can move along. Thank you for all the help. I was stuck in a rut and really needed some new ideas on how to approach the problem. It seems to be an odd way to force the criterion into a string mode where all the text book answers didn't work. I really appreciate the feed back and your time. This is one of the wierdest problems I have seen in a while.