UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Syntax For Isnumeric After Comma Char, Access 2016    
 
   
jysharp2003@yaho...
post Apr 25 2020, 07:10 AM
Post#1



Posts: 50
Joined: 23-January 19



Hi Experts! I am struggling with detecting data that has a comma then number after (some have space, some not). If data string has an alpha char after comma then don't detect. Here is a sample of data plus the query I started with.
11.1.4.6 Report disclosure, COREP, and internal reporting OK
11.1.5.1 Manage credit risk, 11.1.5.1 Manage credit risk (PCF 17463) NOT OK and want to display in query.

SELECT UBER_temp.ObjectID, UBER_temp.ParentID, UBER_temp.ProcessName, UBER_temp.[WF PCF Activity Alignment (SL4)]
FROM UBER_temp LEFT JOIN IGRAFX_ImultipleTaxonomyStrings ON UBER_temp.[WF PCF Activity Alignment (SL4)] = IGRAFX_ImultipleTaxonomyStrings.IGRAFX_L4
WHERE (((UBER_temp.[WF PCF Activity Alignment (SL4)]) Like "*,*"))
ORDER BY UBER_temp.[WF PCF Activity Alignment (SL4)];

Attaching visual. I appreciate your time and knowledge check.
Go to the top of the page
 
June7
post Apr 25 2020, 07:55 AM
Post#2



Posts: 1,517
Joined: 25-January 16
From: The Great Land


Consider:

IsNumeric(Mid([WF PCF Activity Alignment (SL4)], InStr([WF PCF Activity Alignment (SL4)], ",")+1, 2))
This post has been edited by June7: Apr 25 2020, 07:57 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
jysharp2003@yaho...
post Apr 30 2020, 06:38 AM
Post#3



Posts: 50
Joined: 23-January 19



thanks. Trying to work that in. I will let you know.
Go to the top of the page
 
jysharp2003@yaho...
post Apr 30 2020, 09:40 AM
Post#4



Posts: 50
Joined: 23-January 19



Trying a few different ways but getting that Data Mismatch error. I have since this fixed by changing the type in the table. Hmmm. My data is Short Text which is correct I think. Below are some records to sample with. Can you table those up and help with the Mismatch return please? OK and Detect values are my notes so disregard that value. Can you make the syntax work with the few records below?
Attaching a screenshot showing the error. I appreciate your expertise.

10.2.4.4 Manage safety, security, and access to sites OK
10.3.2.1 Schedule work, 10.3.2.7 Monitor maintenance performance against targets/contracts Detect
10.3.3.1 Perform preventative asset maintenance, 10.3.3.2 Perform routine asset maintenance, 10.3.3.4 Identify unplanned maintenance requirements, 10.3.3.5 Perform unplanned maintenance and repairs Detect
10.3.3.2 Perform routine asset maintenance, 10.3.3.2 Perform routine asset maintenance (PCF 19254) Detect
11.1.1.1 Determine risk tolerance for organization, 11.1.1.2 Develop and maintain enterprise risk policies and procedures, 11.1.1.3 Identify and implement enterprise risk management tools, 11.1.1.4 Coordinate the sharing of risk knowledge across the orga Detect

Attached File(s)
Attached File  dataMisMatch.png ( 34.81K )Number of downloads: 5
 
Go to the top of the page
 
RJD
post Apr 30 2020, 10:29 AM
Post#5


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but your comparison of field to criteria is not correct. You are comparing a TRUE/FALSE criteria (Is the value after the comma numeric?) to the text field. Instead you should put your current criteria as a separate field in the query and set the criteria to True. This will select the records that have a numeric value after the comma in the field.

CODE
SELECT UBER_temp.[WF PCF Activity Alignment (SL4)], IsNumeric(Mid([WF PCF Activity Alignment (SL4)],InStr([WF PCF Activity Alignment (SL4)],",")+1,2)) AS CheckNumeric
FROM UBER_temp
WHERE (((IsNumeric(Mid([WF PCF Activity Alignment (SL4)],InStr([WF PCF Activity Alignment (SL4)],",")+1,2)))=True));

The CheckNumeric field (alias) can be removed from the SELECT if you want, but leave it in the WHERE.

See the demo attached and see if this is what you are trying to do.

Couple notes:

One of the fields you posted is 261 characters, too long for a Short Text. I used Memo (Long Text) instead.
You should revisit your field names. Remove the spaces and the special characters. You could use _ in the field name, but I would not use any other. Special characters can cause you problems in many cases.

HTH
Joe
Attached File(s)
Attached File  IsNumericAfterComma.zip ( 17.88K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
jysharp2003@yaho...
post May 4 2020, 07:02 AM
Post#6



Posts: 50
Joined: 23-January 19



Hi Joe, Thanks for the sample database, it works. With the large set of data I have though there are problems. In order to normalize the field I had to make a query "uber_distinct_base" to trim out the field issues. SL4 is the alias and I am running your query against this distinct output.
It seems to lose it's logic and returns all 1232 records whether there is a comma or not (running without a T or F criteria). Attaching your database back with my inserts (copy of **) where the copy of qrygetnumericaftercomma does not seem to detect the comma char but does show a select return (meaning it does not return all 1232 in table records but partial).
Any idea's on this? I appreciate your help.


Removed attachment per request. Doug
Go to the top of the page
 
RJD
post May 4 2020, 09:47 AM
Post#7


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


[attachment=94074:IsNumeri...mma_Rev1.zip]
QUOTE
With the large set of data I have though there are problems.

That's because the examples you posted are not representative of your actual data table. In all cases in your examples, a comma can be found in the field. In your actual table, however, there are some fields that do not contain any commas. So that has to be checked before applying the IsNumeric search logic after a comma.

This has been accommodated in the db you posted. See attached.

CODE
SELECT UBER_temp.[WF PCF Activity Alignment (SL4)],
IIf(InStr([WF PCF Activity Alignment (SL4)],",")>0,IsNumeric(Mid([WF PCF Activity Alignment (SL4)],InStr([WF PCF Activity Alignment (SL4)],",")+1,2)),False) AS CheckNumeric
FROM UBER_temp
WHERE IIf(InStr([WF PCF Activity Alignment (SL4)],",")>0,IsNumeric(Mid([WF PCF Activity Alignment (SL4)],InStr([WF PCF Activity Alignment (SL4)],",")+1,2)),False)=True;

HTH
Joe


Removed attachment per request. Doug

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
jysharp2003@yaho...
post May 5 2020, 08:46 AM
Post#8



Posts: 50
Joined: 23-January 19



Works! Thanks for the lesson Joe.
Go to the top of the page
 
RJD
post May 5 2020, 08:49 AM
Post#9


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


You are welcome. Glad that works for you. thumbup.gif

Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th July 2020 - 04:24 AM