Returning part of a string 1

[flaviooooo]

Hi,

we have a textfield that contains a certain text.
Now I would like to create a query that will check if the field contains the word 'INVOICE ', and then it would take the 5 characters behind it and put it into another field.

For example :

Textfield = "INVOICE 12345 + order 123" -> return 12345
Textfield = "Order 2 = INVOICE 12346" -> return 12346
Textfield = "No Invoices" -> return nothing


I guess this will be fairly simple, but I just need some help.

Kind regards,
flaviooooo

 

[rjoubert]

Try this...

SELECT InStr(1,[YourTable]![YourField],"INVOICE ",1) AS Expr1, Mid([YourTable]![YourField],InStr(1,[YourTable]![YourField],"INVOICE ",1)+8,5) AS Expr2
FROM YourTable
WHERE (((InStr(1,[YourTable]![YourField],"INVOICE ",1))>0));

 

[PHV]

SELECT Textfield, IIf(Textfield Like '*INVOICE *',Mid(Textfield, 8+Instr(Textfield,'INVOICE '),5),'') AS Invoice
FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[flaviooooo]

That's awesome, but I've just noticed an annoyance in my table... Some invoices are longer than 5 characters...

Is there a way to take all characters behind INVOICE, until a Space is reached?

Thanks in advance
flaviooooo

 

[PHV]

SELECT Textfield, IIf(Textfield Like '*INVOICE *',Val(Mid(Textfield, 8+Instr(Textfield,'INVOICE '))) & '','') AS Invoice
FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rjoubert]

Nicely done PHV, but what's with the & "," at the end?

 

[PHV]

& '' : coerce the return value to string
,'') : return a ZeroLengthString in the False part of the IIf function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[flaviooooo]

Hey, for some reason this gives some strange results. For example

invoice 403732 21/09/2004 gives me 40373221

while
invoice 301171 - 06/10/2003 works correct -> 301171

Any ideas?

 

[PHV]

And this ?
SELECT Textfield, IIf(Textfield Like '*INVOICE *',Val(Mid(Replace(Textfield,' ','!'), 8+Instr(Textfield,'INVOICE '))) & '','') AS Invoice
FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[flaviooooo]

Same problem unfortunately :-(

 

[flaviooooo]

Wait, I lied! It works. i had misread the Replace function, putting '' instead of ' '

Thanks a bunch!