Run consecutive co-dependent queries 5

[cutestuff]

hi,

Is there a more efficient way to do the following:

I have 6 queries that are dependent on each other. What I mean is: i need to run the first query to feed the second query and so on. I can run four queries ok but as soon as i get to the fifth one, it says "not enough disk space" and it takes a while.

Can someone please tell me how to get around it? Or is there a more efficient way to do it?

The data tables change daily as it is fed from excel.

thanks in advance!

 

[MajP]

For sure you want to look at cleaning up the data before or after you get it. Likely it is easier after you get it because you may not have business rules to change how people give you the data.

If the culprit is spaces, the best is to clean up the data

A space could be only one of many special characters, and trim will do nothing for things like carriage returns. Check this site for FAQs on removing special characters from strings.

 

[MajP]

There are lots of these. Here is one

Public Function StripSpecialCharacters(ByVal sIn As String) _
As String

Dim sWkg As String, sOrigString As String, sNewString As String

Dim lLen As Long
Dim lCtr As Long, lCtr2 As Long
Dim sChar As String

lLen = Len(sIn)
'create buffer

sOrigString = Space$(lLen)
sOrigString = sIn
sNewString = sOrigString
lCtr2 = 1

For lCtr = 1 To lLen
    sChar = Mid(sOrigString, lCtr, 1)
    If Asc(sChar) < 128 And Asc(sChar) > 31 Then
        Mid(sNewString, lCtr2, 1) = sChar
        lCtr2 = lCtr2 + 1
    End If
Next

If lCtr2 > 1 Then
    sNewString = Left(sNewString, lCtr2 - 1)
Else
    sNewString = ""
End If

StripSpecialCharacters = sNewString
End Function

 

[cutestuff]

I'm not sure the space is the problem but I will try your suggestions.

 

[MajP]

Test it by simply linking the table to itself where fldone = fldtwo. You should get a result that looks like below. If you do not, then you know the in fact the data is not the entered the same in both fields.

B B
C C
D D
E E
...

 

[cutestuff]

Can you please show me the qry design view of qryColOne? I just want to make sure that's not what's wrong with mine.

thanks for your patience!
j

 

[MajP]

No problem.

http://www.4shared.com/file/166277883/5bdd4746/twocolumn.html

 

[cutestuff]

Thanks so much everyone for your help (especially you, Majp!).
Appreciate it!