Help building a SQL Statement

[jamesburkett]

I have a table with userIDs that are formatted LL##### ie PL12345 . I
would like to create a statement that will get just the numbers 12345.
I don't want to update the table but store the value as a variable to
be used in a hyperlink. Is that possible... I am doing this with a JET
database. Thanks!

 

[Brendan Reynolds]

If there are always exactly two letters in front of the digits, then you can
use the Mid function. Something like ...

SELECT Mid$([UserID], 3) AS UserNumber FROM YourTable

 

[jamesburkett]

yes, there are always exactly two letters. Thanks!

If there are always exactly two letters in front of the digits, then you can
use the Mid function. Something like ...

SELECT Mid$([UserID], 3) AS UserNumber FROM YourTable

--
Brendan Reynolds
Access MVP

I have a table with userIDs that are formatted LL##### ie PL12345 . I
would like to create a statement that will get just the numbers 12345.
I don't want to update the table but store the value as a variable to
be used in a hyperlink. Is that possible... I am doing this with a JET
database. Thanks!

 

[jamesburkett]

I just want to make sure i'm on the same page as you... when you say AS
UserNumber you are defining a variable, UserNumber, as the 5 digit
number?

Will I have to set a variable = to the SQL statement or will I just be
able to call UserNumber to get the value? Thanks.

If there are always exactly two letters in front of the digits, then you can
use the Mid function. Something like ...

SELECT Mid$([UserID], 3) AS UserNumber FROM YourTable

--
Brendan Reynolds
Access MVP

I have a table with userIDs that are formatted LL##### ie PL12345 . I
would like to create a statement that will get just the numbers 12345.
I don't want to update the table but store the value as a variable to
be used in a hyperlink. Is that possible... I am doing this with a JET
database. Thanks!

 

[Brendan Reynolds]

'UserNumber' is just an alias for the calculated column in the query. If you
don't give it an alias, it will default to something like 'Expr1', which
isn't very meaningful or memorable.

Just what you do next now that you have your SQL statement I can't really
say, as I'm not sure exactly what you have in mind when you say 'store the
value as a variable to be used in a hyperlink'.

--
Brendan Reynolds
Access MVP

I just want to make sure i'm on the same page as you... when you say AS
UserNumber you are defining a variable, UserNumber, as the 5 digit
number?

Will I have to set a variable = to the SQL statement or will I just be
able to call UserNumber to get the value? Thanks.

If there are always exactly two letters in front of the digits, then you
can
use the Mid function. Something like ...

SELECT Mid$([UserID], 3) AS UserNumber FROM YourTable

--
Brendan Reynolds
Access MVP

I have a table with userIDs that are formatted LL##### ie PL12345 . I
would like to create a statement that will get just the numbers 12345.
I don't want to update the table but store the value as a variable to
be used in a hyperlink. Is that possible... I am doing this with a JET
database. Thanks!