Query: Convert String & Return Partial - getting #Error sometimes

[LordJonSnow]

Howdy from the n00b!



I am trying to get a query to pull data from a field in one table, then convert to a number, and then split the data into two fields in another table. The first part works great, the second part, not as much. I know why it's not working, but can't sort out how to handle for the occasional #Error that's returned.

So the data in Table 1 looks like this typically: "P123456/001" or "P123456 A/001", and I want the first part ("P123456") in one field as text, and the second part ("001") in the second field as numeric/double. That's fine, got that down, and it's working great!

The problem is happening when occasionally there is an entry in Table 1 that looks like this "0012345/" (still a text field); again, first part is ok ("0012345"), but the second part returns the #Error that I'm seeing.

So I know that the problem occurs because there is nothing after the "/" in the source field. Can anyone help determine how to compensate for this? I've tried IsNull, IsError, and IsNumeric, all with the same result.

Here is my formula:

IIf(IsNull[ShippingSchedule]![Supplier Reference],Null,CDbl((Mid([ShippingSchedule]![Supplier Reference],InStr([ShippingSchedule]![Supplier Reference],"/")+1))))

Here is how I tried with IsNull, IsError, and IsNumeric:

IIf(IsNull([ShippingSchedule]![Supplier Reference]),Null,IIf(IsNumeric(CDbl(Mid([ShippingSchedule]![Supplier Reference],InStr([ShippingSchedule]![Supplier Reference],"/")+1))),CDbl(Mid([ShippingSchedule]![Supplier Reference],InStr([ShippingSchedule]![Supplier Reference],"/")+1)),0))

Thanks in advance to anyone who can help!

[ranman256]

Make a query that creates these 2 fields for you.
[myCode], left([myCode],Instr([[myCode],"/")-1) as part1, mid(myCode],Instr([[myCode],"/")+1) as part2

This will break up the fields and let you see them AND possibly find errors.
THEN perform your actions off this query, instead of making extremely complicated IF statements.

[LordJonSnow]

Yep... did a plain select query (thanks for the tip).

Using simple formulas, as you suggested, returned no #Error.

So I took the next step and added back just the conversion to Double on the second part, and the #Error came back.

Formulas:

Left([ShippingSchedule]![Supplier Reference],InStr([ShippingSchedule]![Supplier Reference],"/")-1)

CDbl(Mid([ShippingSchedule]![Supplier Reference],InStr([ShippingSchedule]![Supplier Reference],"/")+1))

Only on the second part.

So normal numbers convert from/to this:

P123456/001 to (two fields) P123456 | 1

And the abnormal ones convert from/to this:

0012345/ to (two fields) 0012345 | #Error

I just want that to be Null where it says #Error

[LordJonSnow]

No other ideas??



EDIT:
Very strange. Tried it again this morning, and it worked perfectly!

IIf(IsNull([WERPShippingSchedule]![Supplier Reference]),Null,CDbl(Mid([WERPShippingSchedule]![Supplier Reference],InStr([WERPShippingSchedule]![Supplier Reference],"/")+1)))

Thanks again for the reminder to take it basic to find the error, ranman!