whats best way to remove last digit?

[pduncan]

I have a field that has a 1 to 4 digit number that is concatenated with a single letter (A-H)
For example 1A, 123B, 3331H, etc.
I need to do a lookup based on the number only – for example (based on above) 1, 123, 3331, etc.
In my query I have this:

DLookUp(tblNationalPrice!Code,"tblNationalPrice",(tblInvoice!PartID=tblNationalPrice!NationalPriceID))

From the code section above, I am talking about tblInvoice!PartID – this needs the letter removed from the last digit. (but only for this query!) I need to leave the letter there for future use.
I know there are functions like right, left, len, etc – but having a time figuring out their application here – and also – can I use them inside of the dlookup function above?
If so, can someone please provide me an example?

Thank you


PDUNCAN - MEMPHIS, TN

 

[hymn]

Have you tried LIKE thrn your number

Hope this helps
Hymn

 

[pduncan]

no - but if I did would this be the correct format?

DLookUp(tblNationalPrice!Code,"tblNationalPrice",(tblInvoice!PartID like tblNationalPrice!NationalPriceID))

PDUNCAN - MEMPHIS, TN

 

[PHV]

DLookUp(tblNationalPrice!Code,"tblNationalPrice",(tblInvoice!PartID=tblNationalPrice!NationalPriceID))
I don't think you may use two different tables (tblNationalPrice and tblInvoice) with DLookUp.

Anyway, given the samples posted, just use the Val function.

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

 

[pduncan]

thanks PHV - VAL is a new one for me. I'll work on that and see what I can come up with - thanks

PDUNCAN - MEMPHIS, TN

 

[dhookom]

You must have quotes around parts of all arguments:
DLookUp([red]"[/red]Code[red]"[/red],[red]"[/red]tblNationalPrice[red]"[/red],[red]"[/red]NationalPriceID=[red]"[/red] & Val(tblInvoice!PartID))

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[HitechUser]

pduncan,

I believe this may work.

Original Entry in [NationalPriceID]: "322H"
Returned by Expression: 322
Expression: Expr: Left([NationalPriceID],Len(RTrim([NationalPriceID]))-1)

If I keyed this in correctly this should return the result you desire. Just insert into your DLookUp.

NOTE: This assumes that there is only ONE character at the end of your string. You may have to convert the string to numeric (Val, etc.) if your lookup table has been defined as numeric.

Good Luck