Access Query Expression - Extremely Urgent!!!

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

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.......

Can anyone help me with this

I hope this makes sense.

Thanks,
Keith
 
Assumption:
ItemNumber Starts with DI or R
Or Itemnumber has a - before the part you need to truncate.

Switch(Left(ItemNumber,2)="DI" or Left(ItemNumber,1)="R",
Right(ItemNumber,2),Instr(1,ItemNumber,"-")>4,Left(ItemNumber,5),
True,ItemNumber)

Otherwise, you need something a bit more complex than the above.
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.......

Can anyone help me with this

I hope this makes sense.

Thanks,
Keith
 
-----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-----
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.......
Click to expand...
 
Thank you for the information, I will give it a try and post back.

Keith

MGFoster said:
-----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-----
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.......
Click to expand...
Click to expand...
 
Back
Top