Re: Access Query Expression - Extremely Urgent!!!
- From: "Keith" <notreally@xxxxxxxxxxx>
- Date: Thu, 26 Jan 2006 07:29:01 -0600
Thank you for the information, I will give it a try and post back.
Keith
"MGFoster" <me@xxxxxxxxxxx> wrote in message
news:KUSBf.5823$Hd4.1306@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> What you're really saying is you want the first occurrence of 5 numerals
> in the ItemNumber string. Other than having a really complex InStr()
> function you could write a VBA function that will extract the numerals.
> Unfortunately, it will slow down the query somewhat.
>
> Public Function GetItemNumber(ByVal varItem As Variant) As Variant
>
> Dim i As Integer
>
> i = 1
> Do Until i > Len(varItem)
> If IsNumeric(Mid$(varItem, i, 1)) Then
> GetItemNumber = Mid$(varItem,i,5)
> Exit Do
> End If
> i = i + 1
> Loop
>
> ' If no numeric value is found in the varItem
> ' then a NULL is returned
>
> End Function
>
> You could also use a Switch() function (and use an Alias on your tables
> so the code is easier to read):
>
> I'll Alias PO2_PurchaseOrderEntryLine to PO2.
>
> Expr1: Switch(Left(PO2.ItemNumber,3)="DI-", Mid$(PO2.ItemNumber, 4,5),
> Left(PO2.ItemNumber,1)="R",Mid$(PO2.ItemNumber,2,5),
> Right(PO2.ItemNumber,2)="-1",Mid$(PO2.ItemNumber,1,5),
> Right(PO2.ItemNumber,4)="-SMR",Mid$(PO2.ItemNumber,1,5),
> ... etc. ...
> 1=1,"")
>
> The final 1=1,"" means if any of the above comparisons fail return an
> empty string. The reason is: normally, if the Switch() can't find a
> match it returns a NULL. So, I'm saying: if the Switch() can't find a
> match then the last comparison is always true & will return an empty
> string. Your InPack item is concatenating the result of Expr1. If
> Expr1 is a NULL the InPack will be NULL also. Therefore, make sure the
> results of Expr1 can be successfully concatenated with another string.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQ9f6qIechKqOuFEgEQKCagCfTTcmzxHi3pu6ZKc/A1Ji1YotYeEAoOdQ
> QUh1vDatm+QPsdt6vT9pv7C+
> =QL/B
> -----END PGP SIGNATURE-----
>
> Keith wrote:
>> First of all I want to say thanks in advance for your help.
>> Ok, I have a query that pulls information from an ODBC Connection and a
>> field that gets information from an expression.
>> InPack: Trim("30085455"+[Expr1])
>> AND
>> Expr1:
>> IIf(Left([PO2_PurchaseOrderEntryLine.ItemNumber],2)="DI",Right(Trim([PO2_PurchaseOrderEntryLine.ItemNumber]),5),[PO2_PurchaseOrderEntryLine.ItemNumber])
>> I need to know how to edit Expr1 to include the following ......
>> ItemNumber starts with ..... DI-, R (Extract last 5 numbers)
>> And
>> ItemNumber Ends with (27 Items in this list)
>> IE: -DI, -1, -2, -3, -SMR, -WNG, -5919A, etc....(Extract first 5
>> numbers)
>> In other words:
>> ItemNumber = DI-12345 Expr1=12345
>> ItemNumber = R54321 Expr1=54321
>> ItemNumber = 12345-1 Expr1=12345
>> ItemNumber = 13579-SMR Expr1=13579
>> ItemNumber = 98765-WNG Expr1=98765
>> ItemNumber = 85214-591A Expr1=12345
>> Etc.......
>>
.
- References:
- Access Query Expression - Extremely Urgent!!!
- From: Keith
- Re: Access Query Expression - Extremely Urgent!!!
- From: MGFoster
- Access Query Expression - Extremely Urgent!!!
- Prev by Date: Combine multiple rows into one record
- Next by Date: Re: Re-attack
- Previous by thread: Re: Access Query Expression - Extremely Urgent!!!
- Next by thread: Re: help woith syntax..
- Index(es):
Relevant Pages
|
