I had a friend of mine that was having a problem performing a join. Some of the information he downloads from the web and pastes into an excel spreadsheet that he then imports into IDEA. He was doing the join based on the persons name.
When I looked at the two files and compared the name fields they looked exactly the same. The field was in the format of First Name Last Name. I then though that maybe one of the fields had spaces at the end that might have been screwing up the join so I used the view spaces function in IDEA to check it out. Turns out that there were no spaces at the end but I noticed in one of the files the space between the first and last name was not showing up as a space, interesting!! So I created a virtual field and used the following function to see what character that looked like a space actually was: @Ascii(@Mid(FIELD,4,1)), I used 4 as I knew that several of the records had that non-space in position 4. Well it turns out it returned 160 as the ASCII code, go figure, definitely not a space. So I then to the internet to find out what it is and it turns out it is a non-breaking space, so it looks exactly like a space but for IDEA it is a totally different character. To fix the problem I created another virtual field and used the following formula: @Replace(FIELD, @chr(160), " "), this replaces the non-breaking space with a regular space. So he was now able to do the join with the virtual field.
I guess the moral of the story is that when you see a join not working as expected you might have to dig a bit deeper to find out what the real problem might be.
I have a problem with table joins. To make the union of two tables with just coincidence, taking a common key field in both tables, the union successfully performed, when I copy the script generated at the record and then run the script by changing parameters in the criteria ... and all it performs excellent.
The problem is that I am associating a Script dialog box, but it has an error on the line:task.PerformTask dbName, "" WI_JOIN_MATCH_ONLYWith an error message saying that a valid equation is needed.
Criterion parameter is:task.Criteria = "NU_CUENTA> =" & Chr (34) & CTA1 & Chr (34) & ".AND. NU_CUENTA <=" & Chr (34) & CTA2 & Chr (34) & ".AND. FECHA_FECHA =" & Chr (34) & Date & Chr (34) &
I have previously defined input dialogues:
CTA1 = InputBox ("Enter Original Account")CTA2 = InputBox ("Enter Final Account")Date = InputBox ("Enter the date (YYYYMMDD)")
What who tell me how to fix this error?
Hi Katherin and welcome to the site.
I see one problem, you use:
Date = InputBox ("Enter the date (YYYYMMDD)")
Date is actually a reserved word in visual basic so I suggest you use something like sDate as you will get the current date and not the date that is from the inputbox.
For the task.Criteria you end with a & so I am not sure if you are missing part of the equation or if that should be removed as it will also cause an error.
Brian