Create a numeric colum in a query using a string

[bugman]

Hi, I'm using Access 2002 as the back end report Database for some
Crystal reports. The Access database takes data from a PeopleSoft table
with a column that has a field called ActivityID. The ActivityID field
consists of some alphanumeric text. I only need to get the record in my
query if the last 3 or 4 characters are numeric. If they are numeric,
they are to be matched up to another table which has the a 4 digit
number. For example, 'GSS DEV 1234' is representative of the data in
the PeopleSoft table. I just want to use the '1234' to join on a number
field of another table. I have had no trouble creating a query for what
I want: Field(DPR#: Right([ActivityI],4) and Criteria {Like "*#"}. This
returns only the records with 4 numeric characters ending the
ActivityID. But the query returns the result as a string. Crystal
Reports will not let me compare strings to numbers. What is the best
way to fix this? IS there a way to 'Cast' the query result into a
number, or do I need change my original table?

 

[Cheryl Fischer]

You can use the Type Conversion functions, such as:

CInt() ' Returns an Integer
CDbl() ' Returns a Double
CLng() ' Returns a Long Integer
CCur() ' Returns Currency
CSng() ' Returns a Single

etc.


hth,