Extract numbers from a text string and keep format as Number?

[jset818]

Hi everybody, beginner Access user here. I’m trying to extract numbers from a text string and keep the results as number. Please take a look at the screenshot below.

As you can see from the first column, I was able to extract the numbers that I want by using the function Right([Event ID],4). However, the results it return for me is in text format. I need those numbers to be in number format so I can use them as a join for another table. Or else every time I try to run it with the join, it would say "Data type mismatch" (I think that is what's causing the error). I have tried using function like this, Format([expr 1],"General Number") and changing the format of the Table in Excel but to no success.

Basically I have two tables, one with Event IDs like the first column and another with Event IDs like the 2nd column from the screenshot. I want them both to have 4 digit Event IDs (like 1st column) so I can link them together. Any suggestions on how I can make this work?



Thanks,
J

[June7]

Format() and FormatNumber() always return a text value no matter what parameter is used.

Try CInt(Right([Event ID],4)) or Val(Right([Event ID],4)).

[jset818]

Format() and FormatNumber() always return a text value no matter what parameter is used.

Try CInt(Right([Event ID],4)) or Val(Right([Event ID],4)).

The CInt(Right([Event ID],4)) function worked. Thanks!