Help with my Select wildcard statment?

G

galantis

Registered User.
Local time
Tomorrow, 04:21
Joined
Feb 10, 2005
Messages
32
Hi,

I need help with my select statment. I can't seem to select any rows when using wildcards embeded in ADOX statement.

Can someboyd help me?

Code: 
Set rst = New ADODB.Recordset
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
    rst.Open " SELECT UCase([F3]) AS Cap, tblFluorCableSch.* " & _
                " FROM tblIntermidImport INNER JOIN tblFluorCableSch ON tblIntermidImport.F39 = tblFluorCableSch.CableNo " & _
                " WHERE (((UCase([F3])) Like '" & "*C*" & "'))", CurConn, , , adCmdText


thankyou,
Galantis
 
Galantis,

Too many parentheses for me, but it looks OK. Do you get any data without
the Where clause?

You're not connecting to SQL Server are you?

Wayne
 
WayneRyan,

Sql statment call works fine without WHERE clause. No, I am not connecting to SQL server, just connecting to plain Access.

however, I need to select column F3 with anything that has the letter C.

Copying and paste straight into Query QBE works find as well, so I am puzzled at the moment.

any ideas?

cheers,
Galantis
 
Galantis,

I don't see anything wrong. Can you post a sample?

Wayne
 
Attached sample File

Hi WyaneRyan,

I have attached a sample of my database.
My intention is to Append tblIntermidImport table into tblCableSch table, but if [tblIntemidImport].[F3] contains the letter 'C' (changes), I will need to find a match in tblCableSch using [F39] and replace it.

If you can have a look at it, I appreciate it.

cheers,
Galantis.
 

Attachments

Galantis,

This works:

Code: 
SELECT tblIntermidImport.F3
FROM tblCableSch INNER JOIN tblIntermidImport ON tblCableSch.CableNo = tblIntermidImport.F39
WHERE (((tblIntermidImport.F3) Like "*C*"));

Wayne
 
thanks but emm, how do I enquote the WHERE clause to rst.open?

Code: 
rst.Open " SELECT [tblIntermidImport].[F3] FROM [tblCableSch] INNER JOIN [tblIntermidImport] ON [tblCableSch].[CableNo] = [tblIntermidImport].[F39] WHERE ((([tblIntermidImport].[F3]) Like " & """" & "*C*" & """" & "))", CurConn, , , adCmdText

galantis
 
Galantis,

I changed it to DAO and it worked fine.

Code: 
Private Sub Command52_Click()
On Error GoTo Err_UpdateChangeCable
    Dim rst As DAO.Recordset
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim db As DAO.Database
    Dim lngCounter As Long
    Dim strCompare1 As String, strCompositeSize As String, strCableNo As String, strFromTag As String, strToTag As String, strOwner As String, strRev As String, strRevType As String, strComments As String

    lngCounter = 0
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT tblIntermidImport.F3, tblCableSch.* " & _
                               "FROM tblCableSch INNER JOIN tblIntermidImport ON " & _
                               "       tblCableSch.CableNo = tblIntermidImport.F39 " & _
                               "Where [F3] Like '*C*'")

    Set rst1 = db.OpenRecordset("Select * From tblCableSchOld")
    
    With rst
        Do Until .EOF
            rst1.AddNew
            rst1![Owner] = rst![Owner]
            rst1![Rev] = rst![Rev]
            rst1![CableNoNormalised] = rst![CableNoNormalised]
            rst1![CableNo] = Nz(rst![CableNo])
            rst1![RevType] = Nz(rst![RevType])
            rst1![CableType] = Nz(![CableType])
            rst1![CableCodeNo] = Nz(rst![CableCodeNo])
            rst1![Volt] = Nz(rst![Volt])
            rst1![Core] = Nz(rst![Core])
            rst1![Size] = Nz(rst![Size])
            rst1![LoadKW] = Nz(rst![LoadKW])
            rst1![CompositeSize] = Nz(rst![CompositeSize])
            rst1![FromMod] = Nz(rst![FromMod])
            rst1![FromTagStriped] = Nz(rst![FromTagStriped])
            rst1![FromTag] = Nz(rst![FromTag])
            rst1![FromDesc] = Nz(rst![FromDesc])
            rst1![ToMod] = Nz(rst![ToMod])
            rst1![ToTagStriped] = Nz(rst![ToTagStriped])
            rst1![ToTag] = Nz(rst![ToTag])
            rst1![ToDesc] = Nz(rst![ToDesc])
            rst1![NumberSize] = Nz(rst![NumberSize])
            rst1![FromModNormalised] = Nz(rst![FromModNormalised])
            rst1![ToModNormalised] = Nz(rst![ToModNormalised])
            rst1![Length] = Nz(rst![Length])
            rst1![ConnectDrawing] = Nz(rst![ConnectDrawing])
            rst1![Drawing] = Nz(rst![Drawing])
            rst1![FromGland] = Nz(rst![FromGland])
            rst1![ToGland] = Nz(rst![ToGland])
            rst1![SystemNo] = Nz(rst![SystemNo])
            rst1![CWP] = Nz(rst![CWP])
            rst1![ContractNo] = Nz(rst![ContractNo])
            rst1![SubSystemNo] = Nz(rst![SubSystemNo])
            rst1![SiteNo] = Nz(rst![SiteNo])
            rst1![Unit] = Nz(rst![Unit])
            rst1![Area] = Nz(rst![Area])
            rst1![Description] = Nz(rst![Description])
            rst1![EngStatus] = Nz(rst![EngStatus])
            rst1![Comments] = Nz(rst![Comments])
            rst1![FromGlandQty] = Nz(rst![FromGlandQty])
            rst1![ToGlandQty] = Nz(rst![ToGlandQty])
            rst1![Route1] = Nz(rst![Route1])
            rst1![Route2] = Nz(rst![Route2])
            rst1![Route3] = Nz(rst![Route3])
            rst1![Route4] = Nz(rst![Route4])
            rst1![Route5] = Nz(rst![Route5])
            rst1![Route6] = Nz(rst![Route6])
            rst1![Route7] = Nz(rst![Route7])
            rst1![Route8] = Nz(rst![Route8])
            rst1![Route9] = Nz(rst![Route9])
            rst1![Route10] = Nz(rst![Route10])
            rst1![Route11] = Nz(rst![Route11])
            rst1![Route12] = Nz(rst![Route12])
            rst1![Route13] = Nz(rst![Route13])
            rst1![Route14] = Nz(rst![Route14])
            rst1![Route15] = Nz(rst![Route15])
            rst1![Route16] = Nz(rst![Route16])
            rst1![Route17] = Nz(rst![Route17])
            rst1![Route18] = Nz(rst![Route18])
            rst1![Route19] = Nz(rst![Route19])
            rst1![Route20] = Nz(rst![Route20])
            rst1![Route21] = Nz(rst![Route21])
            rst1![Route22] = Nz(rst![Route22])
            rst1![Route23] = Nz(rst![Route23])
            rst1![Route24] = Nz(rst![Route24])
            rst1![Route25] = Nz(rst![Route25])
            rst1![Discipline] = Nz(rst![Discipline])
            rst1![DateAdded] = Nz(rst![DateAdded])
            rst1.Update
            db.Execute "DELETE tblCableSch.CableNo FROM tblCableSch WHERE (((tblCableSch.CableNo)= '" & rst![CableNo] & "'))"
            .MoveNext
        Loop
    End With
    DoCmd.RunSQL " INSERT INTO tblCableSch ( Owner, Rev, RevType, CableType, CableCodeNo, CableNo, FromMod, FromTag, FromDesc, ToMod, ToTag, ToDesc, Volt, LoadKW, NumberSize, Length, ConnectDrawing, Drawing, FromGland, ToGland, SystemNo, CWP, ContractNo, CableNoNormalised, FromModNormalised, ToModNormalised, FromTagStriped, ToTagStriped, Comments, Flag, FromGlandQty, ToGlandQty, Route1, Route2, Route3, Route5, Route6, Route7, Route8, Route9 ,Route10 ,Route11 ,Route12 ,Route13 ,Route14 ,Route15,Route16 ,Route17,Route18 ,Route19 ,Route20 ,Route21,Route22 ,Route23 ,Route24 ,Route25,Discipline )" & _
                        " SELECT tblIntermidImport.F1, tblIntermidImport.F2, tblIntermidImport.F3, tblIntermidImport.F4, tblIntermidImport.F5, tblIntermidImport.F6, tblIntermidImport.F7, tblIntermidImport.F8, tblIntermidImport.F9, tblIntermidImport.F10, tblIntermidImport.F11, tblIntermidImport.F12, tblIntermidImport.F13, tblIntermidImport.F14, tblIntermidImport.F15, tblIntermidImport.F17, tblIntermidImport.F18, tblIntermidImport.F19, tblIntermidImport.F20, tblIntermidImport.F21, tblIntermidImport.F22, tblIntermidImport.F23, tblIntermidImport.F24, tblIntermidImport.F32, tblIntermidImport.F34, tblIntermidImport.F35, tblIntermidImport.F36, tblIntermidImport.F37, tblIntermidImport.F39, tblIntermidImport.Flag, " & _
                        " tblIntermidImport.F40 , tblIntermidImport.F41, tblIntermidImport.F42, tblIntermidImport.F43, tblIntermidImport.F44, tblIntermidImport.F45, tblIntermidImport.F46, tblIntermidImport.F47, tblIntermidImport.F48, tblIntermidImport.F49, tblIntermidImport.F50, tblIntermidImport.F51, tblIntermidImport.F52, tblIntermidImport.F53, tblIntermidImport.F54 , tblIntermidImport.F55, tblIntermidImport.F56, tblIntermidImport.F57, tblIntermidImport.F58, tblIntermidImport.F59, tblIntermidImport.F60, tblIntermidImport.F61, tblIntermidImport.F62, tblIntermidImport.F63, tblIntermidImport.F64, tblIntermidImport.F65, tblIntermidImport.F66 " & _
                        "FROM tblIntermidImport"
    MsgBox "finished"
    
Exit_UpdateChangeCable:
    
    Exit Sub

Err_UpdateChangeCable:
    If Err.Number = 3021 Then
        
    Else
        MsgBox Err.Number & " : " & Err.Description
        Stop
        Resume
        'Resume Exit_UpdateChangeCable
    End If

End Sub

Wayne
 
You must log in or register to reply here.

Similar threads

X
Compile Error Help
Replies
4
Views
1,165
theDBguy
theDBguy
C
Option Button Values causing issues with Audit Trail code
Replies
6
Views
1,130
Cark
C
B
Audit Table/ Log Help
2
Replies
23
Views
1,420
NauticalGent
NauticalGent
A
Complie error:User defined type not defined
Replies
10
Views
1,117
The_Doc_Man
The_Doc_Man
V
A little help
Replies
2
Views
365
vagelisr
V

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom