Query/Report Issue

[Wulf13]

Here is the statement in my query:

ItemType: Left([itemNumber],InStr(1,[itemNumber],"-")-1)

It takes the following string "M4-1234567" and displays everything before the hyphen. Yet when someone doesn't type in a hyphen it gives me an error. This prevents me from generating my report. How can I fix this other than beating the person(s) who didn't type in the hyphen?

 

[RuralGuy]

You could check for the "-" first and just use a fixed number of characters if the dash is not there. I kind of like the beating option but my guess is it would not be too popular with anyone else.

 

[neileg]

I like the beating people option, too. Much easier to debug.

If the bit before the hyphen is always 2 chars then you don't need InStr() so I guess you won't always have 2 chars. So RG's option won't work.

If you can determine some other way how many characters to strip off, you don't need the hyphen, either, so I guess this won't work.

So you're back to beating people, aren't you?

 

[Wulf13]

Let the beatings commence...

...I shall start at their head. Unfortunately, you fine folks are spot on. It's not always 2 characters before the hypen.

Is it possible to debug a Query on error??

 

[Wulf13]

OK so I got creative and here is what I came up with:

ItemType: IIf(InStr(1,[itemNumber],"-")>0,Left([itemNumber],InStr(1,[itemNumber],"-")-1),[itemNumber])

This looks for the hyphen and if not found it just displays the itemNumber as a whole, and if the hyphen is found it strips of the previous characters like I need it too. This way I can still pull up my report (albeit a ugly looking thing).

At least I still get to beat people for a messed up report.

 

[boblarson]

Why not do this -

In a standard module (not a form's module) create a function to eliminate the hyphen:

Public Function StripHyph(strString) As String
    Dim varSplit As Variant

    varSplit = Split(strString, "-", , vbTextCompare)
    StripHyph = varSplit(0) & varSplit(1)
End Function

Then feed the field into the function within the query to return the non-hyphenated version.

 

[RuralGuy]

Wulf,
That looks like as close as you can get without using the whipping stick. Good decision.