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
> Issue Converting String To Number, Any Version    
 
   
damian.green
post Feb 21 2020, 03:46 PM
Post#1



Posts: 171
Joined: 24-October 18



I have a .text document that I've linked to my database.

All of the data is formatted with a # and then .PNG.
Field1
1.PNG
10.PNG
101.PNG
102.PNG

I was thinking that I could use InStr and format the resulting # as a "General Number"

CODE
Field: Format(Left([Field1],InStr(1,[Field1],".")-1),"General Number")


and then via a query, use that result to link to other # fields in the database but I'm getting a type mismatch error.

CODE
SELECT t_Organization.Org_Child_ID, t_Organization.Organization, t_Organization.Organization_Long_Name, q_Pictures_Org_Icon.Field
FROM t_Organization LEFT JOIN q_Pictures_Org_Icon ON t_Organization.Org_Child_ID = q_Pictures_Org_Icon.Field
WHERE (((q_Pictures_Org_Icon.Field) Is Null));


I would like to not make tables in order to resolve this issue.

Thoughts?
This post has been edited by damian.green: Feb 21 2020, 03:46 PM
Go to the top of the page
 
theDBguy
post Feb 21 2020, 04:04 PM
Post#2


UA Moderator
Posts: 77,725
Joined: 19-June 07
From: SunnySandyEggo


Hi. Unfortunately, the Format() function returns a String data type. If you want a number, try using the Val() function instead.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Feb 21 2020, 04:06 PM
Post#3


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


Hi: Format produces a string rather than a number. Try ...

VAL(Field1)

Or put CInt in front of what you already have.

Just an educated guess, since I am not at a computer right now to test.

HTH
Joe

from phone.

Ah, I see theDBguy has the faster fingers today ...

--------------------
"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
 
damian.green
post Feb 21 2020, 04:18 PM
Post#4



Posts: 171
Joined: 24-October 18



VAL worked on the text string to get the # properly formatted but …

Ran into an invalid procedure call when I joined the VAL(Field) to the number field in a query to try the find matches.
Go to the top of the page
 
cheekybuddha
post Feb 21 2020, 04:22 PM
Post#5


UtterAccess Moderator
Posts: 12,620
Joined: 6-December 03
From: Telegraph Hill


Remember, we can't see what you did! Perhaps the SQL?

However, my guess is that substituting VAL(Field) with CLng(Field) might work.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
damian.green
post Feb 21 2020, 04:33 PM
Post#6



Posts: 171
Joined: 24-October 18



Here's the SQL for the conversion using CInt

CODE
SELECT CInt([Field]) AS ORG_Child_ID
FROM q_Pictures_Org_Icon;


then I take the resulting query and try a left join on the # field, at this is where the query fails.

CODE
SELECT t_Organization.Org_Child_ID, t_Organization.Organization, t_Organization.Organization_Long_Name, q_Pictures_Org_Icon_Number.ORG_Child_ID
FROM t_Organization LEFT JOIN q_Pictures_Org_Icon_Number ON t_Organization.Org_Child_ID = q_Pictures_Org_Icon_Number.ORG_Child_ID
WHERE (((q_Pictures_Org_Icon_Number.ORG_Child_ID) Is Null));


I tried the VAL() and ended up with the same results.
This post has been edited by damian.green: Feb 21 2020, 04:34 PM
Go to the top of the page
 
cheekybuddha
post Feb 21 2020, 05:33 PM
Post#7


UtterAccess Moderator
Posts: 12,620
Joined: 6-December 03
From: Telegraph Hill


Remember, joins must be made on the same datatype.

I'm guessing that t_Organization.Org_Child_ID is a Long Integer.

If so, follow my suggestion to canst as a Long
CODE
SELECT CLng([Field]) AS ORG_Child_ID
FROM q_Pictures_Org_Icon;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
damian.green
post Feb 21 2020, 06:53 PM
Post#8



Posts: 171
Joined: 24-October 18



Yes, the ORG_Child_ID is a long integer.
I changed the Field conversion to CLng.
CODE
SELECT CLng([Field]) AS ORG_Child_ID
FROM q_Pictures_Org_Icon;

When I brought the query in join it with the table it automatically connected (which didn't happen before). THINGS ARE LOOKING PROMISING.....
I adjusted the join from an inner join to a left join.
AND
I got an invalid call procedure error.

CODE
SELECT t_Organization.Org_Child_ID, t_Organization.Organization, t_Organization.Organization_Long_Name, q_Pictures_Org_Icon_Number.ORG_Child_ID
FROM t_Organization INNER JOIN q_Pictures_Org_Icon_Number ON t_Organization.Org_Child_ID = q_Pictures_Org_Icon_Number.ORG_Child_ID
WHERE (((q_Pictures_Org_Icon_Number.ORG_Child_ID) Is Null));
Go to the top of the page
 
RJD
post Feb 22 2020, 09:03 PM
Post#9


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


Hi: Looks like this is still hanging ... is that correct?

I had an observation ... in your query ...

SELECT t_Organization.Org_Child_ID, t_Organization.Organization, t_Organization.Organization_Long_Name, q_Pictures_Org_Icon_Number.ORG_Child_ID
FROM t_Organization INNER JOIN q_Pictures_Org_Icon_Number ON t_Organization.Org_Child_ID = q_Pictures_Org_Icon_Number.ORG_Child_ID
WHERE (((q_Pictures_Org_Icon_Number.ORG_Child_ID) Is Null));


It looks like you are limiting the results to only those with a Null in q_Pictures_Org_Icon_Number.ORG_Child_ID, while also using that field as the relationship i the JOIN. Did you mean to do that? How did you expect to link from q_Pictures_Org_Icon_Number ON t_Organization.Org_Child_ID to a Null?

Or am I missing something here?

HTH
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
 
damian.green
post Feb 22 2020, 09:40 PM
Post#10



Posts: 171
Joined: 24-October 18



Yes I'm still having issues with getting the # to be a # that matches up and doesn't error out "Invalid Call Procedure".

To just ensure that I'm not crazy, I've taken the query results and put them in a table with ORG_Child_ID as a Number (Long Integer), modified the below query to use it as the data source and it works.

My conversion of the text to a number.

Query 1: get just the #. from a text string (Example: 101.JPEG)

CODE
SELECT Left([Field1],InStr(1,[Field1],".")-1) AS Field
FROM t_Pictures_Organization_Icon;


Query 2: convert to long integer. I've previously tried VAL() and CInt() in addition to the below.

CODE
SELECT CLng([Field]) AS ORG_Child_ID
FROM q_Pictures_Org_Icon;


Good catch. It was a left join previously.

Step 3: FAILURE.
CODE
SELECT t_Organization.Org_Child_ID, t_Organization.Organization, t_Organization.Organization_Long_Name, q_Pictures_Org_Icon_Number.ORG_Child_ID
FROM t_Organization LEFT JOIN q_Pictures_Org_Icon_Number ON t_Organization.Org_Child_ID = q_Pictures_Org_Icon_Number.ORG_Child_ID
WHERE (((q_Pictures_Org_Icon_Number.ORG_Child_ID) Is Null));

This post has been edited by damian.green: Feb 22 2020, 09:40 PM
Go to the top of the page
 
RJD
post Feb 23 2020, 02:11 PM
Post#11


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


Hi: Perhaps at this point we will need to see your db (at least a cut-down version with relevant objects and example data) to help you sort this. Could you provide that? If so, remember to compact and zip the file, and the zip must be less than 2 MB.

No doubt with the db in hand we can help you get this working to your satisfaction.

HTH
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
 
damian.green
post Feb 23 2020, 05:34 PM
Post#12



Posts: 171
Joined: 24-October 18



Here's a miniaturized version of the database that shows the current issue.
Attached File(s)
Attached File  NumberFormat.zip ( 28.99K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Feb 23 2020, 07:54 PM
Post#13


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


Hi: Several things ...

- You have two records with no numeric value in the field (I fixed that with logic, making these fields Null)
- You have no records in t_organizations (I added some)
- You are using a reserved word as a field name, in this case [Field], which is a very bad choice (I changed the field name)
- Note that if you use the extracted number from Field1 as the link and only retrieve those that are Null (your criteria), you will never get any matches (I removed that criteria)

I still do not understand, however, why you are linking an Autonumber in one table to a derived number in the other, but that is for you to figure out.

I eliminated query2 and included everything you need in query1, and adjusted query3 accordingly (see the logic there). I also added, separately, qryAltMethod to show you how this can be done completely within the join link, in one query ...

CODE
SELECT t_Organizations.ORG_Child_ID, t_Organizations.Name, Organization_Icon.Field1
FROM t_Organizations LEFT JOIN Organization_Icon ON t_Organizations.ORG_Child_ID = IIf(isnumeric(Left(Organization_icon.Field1,1)),Val(Organization_Icon.Field1),Null);


See if this is what you are trying to do, or if we need to look at this more.

HTH
Joe
Attached File(s)
Attached File  NumberFormat_Rev1.zip ( 21.97K )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
 
damian.green
post Feb 23 2020, 09:12 PM
Post#14



Posts: 171
Joined: 24-October 18



Thanks for the feedback. This has been on my punch list for a year.

Can you tell me why the IIF(IsNumeric(Left()),VAL(),Null) works and the other approaches didn't?

A little information on what I'm doing...
I have a folder of pictures of Organization's logos. I link the pictures to the records I'm looking at.
The file is saved using the autonumber found in the database (e.g. 1.PNG = IBM's logo = ORG_Child_ID #1 which was generated by the autonumber.)
Off and on, I want to spend some time figuring out which organizations are in the database but don't have a picture in the folder.
I run a .bat query to get the file names and save it to a text file.
The text file is linked to the database. I use this query to tell me where I don't have a matching number.
This post has been edited by damian.green: Feb 23 2020, 09:30 PM
Go to the top of the page
 
tina t
post Feb 23 2020, 11:14 PM
Post#15



Posts: 6,451
Joined: 11-November 10
From: SoCal, USA


QUOTE
I use this query to tell me where I don't have a matching number.

if the purpose of this whole thing is just to check for logo files, and if you're either 1) storing the relevant filenames in a table, or 2) can construct the filenames from existing data, then you can just loop through the relevant records in VBA, using Dir to check for each file.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
RJD
post Feb 24 2020, 08:28 AM
Post#16


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


QUOTE
Can you tell me why the IIF(IsNumeric(Left()),VAL(),Null) works and the other approaches didn't?

Frankly, I am not at all sure. Simply said, the link did not like the 0 produced by the Val when there are no numeric values leading the field. But it tolerated the Null. I got this through testing alternatives but even after examining/testing it for a while, I have no answer - just the results. And the Is Null now works as criteria to spot the unmatched links.

Perhaps someone else who knows the inner workings of the linkage and Val could shed some light on this. I would also like to know for future reference.

And thanks for the explanation. I think I get it now...

HTH
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
 
cheekybuddha
post Feb 24 2020, 08:32 AM
Post#17


UtterAccess Moderator
Posts: 12,620
Joined: 6-December 03
From: Telegraph Hill


My guess is that because there is more than one record returning 0 it blows up because it doesn't know which record to display on the right side.

Need to think about this one some more. iconfused.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
RJD
post Feb 24 2020, 09:07 AM
Post#18


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


Hi David:

QUOTE
My guess is that because there is more than one record returning 0 it blows up because it doesn't know which record to display on the right side.

Hmmm ... I just tested that with two values that include the same numbers ... and it works fine, just gives two record results in the output.

Still a bit baffled. iconfused.gif

I know there must be an explanation somewhere, just deciding whether to spend more time on this when a method exists to get the desired results... In my old age I tend to tolerate a few unknowns better.

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
 
cheekybuddha
post Feb 24 2020, 09:20 AM
Post#19


UtterAccess Moderator
Posts: 12,620
Joined: 6-December 03
From: Telegraph Hill


>> In my old age I tend to tolerate a few unknowns better. <<

For me, my tolerance level is inversely proportional to to how much I want to do what I'm actually meant to be doing!

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    4th April 2020 - 06:55 AM