DLookup part of a field

[msjeb007]

I need a DLookup but for only part of a field in an access table.

 

[jdraw]

More info and/or example needed.

This sort of structure works (from one of my tables testing)

?Dlookup("fld","aircraftraw","mid(fld,10,4)='" & "N568" &"'")
Aircraft N5684

or

?Dlookup("fld","aircraftraw","mid(fld,10,4) like '*" & "68" & "*'")
Aircraft N5684

AircraftRaw table

Rec Date	ID	fld
	2	Aircraft N3456
	3	
12-Sep-12	4	456723-101 FD34566
14-Sep-15	5	566788-303 GS23445
	6	
	7	Aircraft N5684
	8	
06-Sep-13	9	566788-303 GS23445
21-Sep-12	10	456723-101 GD23478
29-Sep-13	11	566788-303 GS45444
20-Sep-09	12	434545-444 JI345345

 

[msjeb007]

I have a field in an access table with 9 characters. I only want to look up 2-5.

 

[msjeb007]

Is this correct?
DLookup("doc_num_cd", "Parts A", "mid(doc_num_cd,2,6))

 

[jdraw]

Is this correct?
DLookup("doc_num_cd", "Parts A", "mid(doc_num_cd,2,6))

No.

Dlookup


What are you trying to do --in plain English?
An example would help.

 

[msjeb007]

I have document numbers in a table named "Parts". The filed named "doc_num_cd" consists of the following format: AUUUUU-DDDD-SSSS
AUUUUU is the code that identifies who is ordering the part (identification code)
DDDD is the Julian date
SSSS is the serial number
I want to DLookup only the UUUUU

I need to then replace any erroneous UUUUU data.

 

[Mark_]

I need a DLookup but for only part of a field in an access table.

To be very clear, do you want to search by part of the field OR return only part of the field?

 

[MarkK]

I need a piece of rope, but I'm not sure how long. Can anybody help?
Mark

 

[Mark_]

I need a piece of rope, but I'm not sure how long. Can anybody help?
Mark

What is your neck size?

 

[msjeb007]

Yes I want to return part of the field

 

[MarkK]

What is your neck size?

Awesome!
Mark

 

[Mark_]

Yes I want to return part of the field

Then you would use Left, Mid, or Right (as needed) on what is RETURNED by DLookup. DLookup itself will return the entire value.

As an example you could do something like the following:

DIM asReturn as String
asReturn = DLookup([Your],[Values],"Go in here")
msgbox "DLookup returned " & asReturn ' Just to make sure we get something
asReturn = Mid(asReturn, 2, 4) 'Returns just characters 2-5
msgbox "Substring is " & asReturn

Once you get all that working right, you will eventually change it to
asReturn = Mid(DLookup([Your],[Values],"Go in here"), 2, 4)