Text to Numbers.

[PuddinPie]

Hello.

I have a query that pulls some info eg. P4352342-1. I need to do a greater than or less than on this field and am trying to figure out the best way to do it.

I've heard about text being converted into numerical values as well as using spacing as well so that the criteria only look at certian characters eg. P'4352342'-1 and it would only look at the '4352342' portion. I've been checking online and can't seem to find a way of doing either. Can someone please help.

Thank you.

 

[jzwp22]

If the text in the field is ALWAYS in the same formatxxxxxxx-1, then you can use some of the built-in functions to extract and convert the xxxxxxx to a number. You will have to use nested functions:

cLng(mid(yourtextfieldname,2,7))

The mid() function will extract from the text field the portion of the string starting at the second character for a length of 7 characters. The cLng() function converts that extracted string to a long number integer.

 

[PuddinPie]

I'm getting a Data Type Mismatch in criteria expression error.

 

[jzwp22]

Can you post the SQL text of the query that gave the error?

 

[raskew]

Hi -

Take a look at the RemoveAlphas() function at this Microsoft site: http://support.microsoft.com/kb/210537

To use the code to convert your P4352342-1 to 4352342, you could try something like (from the debug (immediate) window):

x = "P4352342-11"

? removealphas(iif(instr(x, "-")>0, left(x, instr(x, "-")-1), x))

4352342

HTH - Bob

 

[PuddinPie]

Here is the SQL

UPDATE tblSeg_SDN SET tblSeg_SDN.ImportDate = [tblSeg_SDN]![ImportDate], tblSeg_SDN.Product = [tblSeg_SDN]![Product], tblSeg_SDN.NonRes = [tblSeg_SDN]![NonRes], tblSeg_SDN.Flag = [tblSeg_SDN]![Flag], tblSeg_SDN.Advisor = [tblSeg_SDN]![Advisor], tblSeg_SDN.ClientID = [tblSeg_SDN]![ClientID], tblSeg_SDN.Lang = [tblSeg_SDN]![Lang], tblSeg_SDN.Registered = [tblSeg_SDN]![Registered], tblSeg_SDN.Spousal = [tblSeg_SDN]![Spousal], tblSeg_SDN.LockedIn = [tblSeg_SDN]![LockedIn], tblSeg_SDN.ClientName = [tblSeg_SDN]![ClientName], tblSeg_SDN.Address1 = [tblSeg_SDN]![Address1], tblSeg_SDN.Address2 = [tblSeg_SDN]![Address2], tblSeg_SDN.Address3ALL = [tblSeg_SDN]![Address3ALL], tblSeg_SDN.DOB = [tblSeg_SDN]![DOB], tblSeg_SDN.Sex = [tblSeg_SDN]![sex], tblSeg_SDN.Policy = [tblSeg_SDN]![Policy], tblSeg_SDN.Contract = [tblSeg_SDN]![Contract], tblSeg_SDN.PYD = [tblSeg_SDN]![PYD], tblSeg_SDN.[FC#] = [tblSeg_SDN]![FC#], tblSeg_SDN.[FSA#] = [tblSeg_SDN]![FSA#], tblSeg_SDN.FSAName = [tblSeg_SDN]![FSAName], tblSeg_SDN.FSAPhone = [tblSeg_SDN]![FSAPhone], tblSeg_SDN.SPLHandling = [tblSeg_SDN]![SPLHandling], tblSeg_SDN.UserID = [tblSeg_SDN]![UserID], tblSeg_SDN.DepartmentNameE = [tblSeg_SDN]![DepartmentNameE], tblSeg_SDN.DepartmentNameF = [tblSeg_SDN]![DepartmentNameF], tblSeg_SDN.Autosignature = [tblSeg_SDN]![Autosignature], tblSeg_SDN.FirstName = [tblSeg_SDN]![FirstName], tblSeg_SDN.Signature = [tblSeg_SDN]![Signature], tblSeg_SDN.LastName = [tblSeg_SDN]![LastName], tblSeg_SDN.ERA = [tblSeg_SDN]![ERA]
WHERE (((tblSeg_SDN.Policy)=CLng(Mid("Policy",2,8))));

For the removealphas instruction. I don't know where you are meaning to input that in a query?

 

[jzwp22]

Your are going to have to explain exactly what you are doing. Are you attempting to run an update query using data in controls on a form? Are you updating an existing record or are you really creating a new record? From where are you executing this query?

 

[PuddinPie]

I have a make table query that dumps data and then an update query that that I want to pull out certian records based on a condition. So the data gets dumped and then updated so that only records with condition p063000-9 or greater get a change to the ERA column. anything below gets no change to the column. Right now I'm just trying to get it to sort the data to make sure the sort is working but im getting errors.

 

[jzwp22]

I'm confused. If you only want to update 1 field in the table, then why are you updating the long list of fields. Also, I do not know where you are getting the new info for the update. I am guessing that you want something like this:

UPDATE tblSeg_SDN SET tblSeg_SDN.ERA =somevaluefromsomesource
WHERE Clng(Mid(tblSeg_SDN.Policy,2,8))>63000

 

[PuddinPie]

It is not displaying anything. even if I put in =1159906 which I know is in there it still does not display anything.

 

[jzwp22]

I missed an error in your expression

You had this
UPDATE tblSeg_SDN SET tblSeg_SDN.ERA =somevaluefromsomesource
WHERE Clng(Mid(tblSeg_SDN.Policy,2,8))>63000

The length was 7 not 8, so it should be: Clng(Mid(tblSeg_SDN.Policy,2,7)=1159906

Now that I am looking at this again, the p063000-9 you posted earlier does not fit the format since the 063000 is only 6 characters not 7 so the function will return 63000- which the function will interpret as -63000.

So I have to ask, are all policies in have 7 numeric characters between the P and the -?

 

[raskew]

Referring back to my post #5, the code provided is not dependent on a specific number of characters between the 'P' and the '-'.

Try copying/pasting the function cited to a new module, then experiment using the one-liner, i.e.:

? removealphas(iif(instr(x, "-")>0, left(x, instr(x, "-")-1), x))

...replacing x with your field name.

Bob