Arrg!!! I still can't seem to contatenate correctly! (1 Viewer)

slharman1

Member
Local time
Today, 00:37
Joined
Mar 8, 2021
Messages
467
I have a table with a field named WONumber. It is input by a barcode scanner and is a Text field of the order number (long) and item number (txt) and is represented like this @@@@@-@@@@. After the dash in the field the remaining string is of variable lengths, so I am doing this to get a Dlookup on a time punch form:
=DLookUp("JobName","tblOrders","OrderNumber= " & Left([Forms]![frmTimePunch]![txtWONumber],5))
Which works perfectly!

The problem comes when I want to lookup the text field (the part of the string after the dash):
=DLookUp("Description","tblOrderDetails","[ItemNo] = '" & Mid([Forms]![frmTimePunch]![txtWONumber],7) & "'")

What the heck am I missing?
Thanks in advance for my lack of misunderstanding string contatenation.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:37
Joined
May 21, 2018
Messages
8,463
Looks correct to me. Can you test somewhere to see what is returned?
debug.print "[ItemNo] = '" & Mid([Forms]![frmTimePunch]![txtWONumber],7) & "'")
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:37
Joined
Feb 28, 2001
Messages
27,001
You said this is for a time-punch form. If this is a form, you can use VBA. It seems like this is coming from a table where the barcode has already been read, translated, and stored. You have captured the entire string (into a string field, of course) that is probably bound into the form. That means the record is already available in the Form_Current event. There, use the SPLIT function to assign the values to your fields. And you can shorten things if this code would be in your form called frmTimePunch, because you can use Me. as a shortcut.


Code:
Dim WOArray() As String

WOArray = SPLIT( Me.[txtWONumber] )
JobName = DLookup( "[JobName]", "tblOrders]", "[OrderNumber]=" & CStr( WOArray(0) ) )
Description = DLookup( "[Description]", "tblOrderDetails", "[ItemNo]= " & CStr( WOArray(1) ) )

Note that SPLIT will by default just return a 0-based array as opposed to a 1-based array.
 

slharman1

Member
Local time
Today, 00:37
Joined
Mar 8, 2021
Messages
467
It is working now, don’t understand what the problem was at first. Probably a newbie not closing the form and reopening is all I can think of. Thanks
 

slharman1

Member
Local time
Today, 00:37
Joined
Mar 8, 2021
Messages
467
You said this is for a time-punch form. If this is a form, you can use VBA. It seems like this is coming from a table where the barcode has already been read, translated, and stored. You have captured the entire string (into a string field, of course) that is probably bound into the form. That means the record is already available in the Form_Current event. There, use the SPLIT function to assign the values to your fields. And you can shorten things if this code would be in your form called frmTimePunch, because you can use Me. as a shortcut.


Code:
Dim WOArray() As String

WOArray = SPLIT( Me.[txtWONumber] )
JobName = DLookup( "[JobName]", "tblOrders]", "[OrderNumber]=" & CStr( WOArray(0) ) )
Description = DLookup( "[Description]", "tblOrderDetails", "[ItemNo]= " & CStr( WOArray(1) ) )

Note that SPLIT will by default just return a 0-based array as opposed to a 1-based array.
I have moved the code off the unbound txtField and put it in VBA. Thanks
 

Users who are viewing this thread

Top Bottom