MS Access query with ":" as last character in the column name 1

[rekcut01]

Friends,

I'm trying to help out a co-worker with his query. The person that set up the MS Access database put a ":" at the end of the column name.

"Branch Number:" so when he does his select:

rs.Open "Select * from Data where [Branch Number:] =" & Ca, cn

it doesn't work. I told him I think there is a way to select by column number but I can seem to find any examples. Can someone point me to some examples please?

Thanks in advance
Rick

 

[PHV]

What is Ca ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rekcut01]

Sub CreateAndRunQuery()
          Dim cn As Object, rs As Object
          Dim intColIndex As Integer
          Dim DBData As String
          Dim TargetRange As Range
          Dim MyValues() As Variant
          Dim i As Integer
          Dim Ca As Range
          
          
          

10        DBData = "Z:\Users\rugge\AA SKYNET AA\Morgan Stanley.accdb"

20        On Error GoTo Whoa

30        Application.ScreenUpdating = False

40        Set TargetRange = Sheets("Sheet2").Range("a6")
50        Set Ca = Sheets("sheet2").Range("a1")  <-------------------------------------------[b]Ca is the sheet range[/b]
60        Set cn = CreateObject("ADODB.Connection")

70        cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBData & ";"

80        Set rs = CreateObject("ADODB.Recordset")
90        rs.Open "Select * from Data where [Branch Number:] =" & Ca, cn


          ' Write the field names
100        For intColIndex = 0 To rs.Fields.Count - 1
110           TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
120       Next

          ' Write recordset
130      TargetRange.Offset(1, 0).CopyFromRecordset rs

LetsContinue:
140       Application.ScreenUpdating = True
150       On Error Resume Next
160       rs.Close
170       Set rs = Nothing
180       cn.Close
190       Set cn = Nothing
200       TargetRange.Clear
210       On Error GoTo 0
220       Exit Sub
Whoa:
230       MsgBox "Error Description :" & Err.Description & vbCrLf & _
             "Error at line     :" & Erl & vbCrLf & _
             "Error Number      :" & Err.Number
240       Resume LetsContinue
End Sub

Thanks
Rick

 

[PHV]

What is the data type of [Branch Number:] ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rekcut01]

I think its a char, the Branch numbers are like:

BR341
BR342
BR343

Etc....

Thanks again
Rick

 

[Andrzejek]

Also, what do you get in Immediate Window if you do this:

Dim strSQL as String

strSQL = "Select * from Data where [Branch Number:] =" & Ca
[blue]Debug.Print strSQL[/blue]
rs.Open strSQL, cn

BTW: Data is one BAD name for a table/query

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PHV]

So, use this:

rs.Open "Select * from Data where [Branch Number:]='" & Replace(Ca.Value, "'", "''") & "'", cn

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rekcut01]

Thanks Andy!

I agree with you, Data is a bad name for a table.... thankfully I didn't create it or have to support it.
I will have him give this a try.

Most appreciate the help.
Rick

 

[PHV]

Just to know, did you try my last suggestion ?

 

[rekcut01]

Andy,

Getting the following error, I suspect he's not getting one of his variables set like he thinks is it.

Error Description:No value given for one or more required parameters.
Error at line :90
Error Number :-2147217904

-- Rick

 

[rekcut01]

Yes Andy I did and I appreciate the help. Its still getting the same error

--Rick

 

[PHV]

Its still getting the same error
Which error on which line of code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rekcut01]

The above error on line 90, and the debug.print strSQL isn't printing anything but when he takes out the [Branch Name:] it returns everything as in:

rs.Open "Select * from Data (yes I know Data is a bad name, do you think that might be the problem? Is data a reserved word in MS Access?

-- Rick

 

[PHV]

Seems like you didn't use my suggestion time stamped 1 Jul 14 14:01

 

[Andrzejek]

Try both: mine and PHV's suggestions.

If this works OK:
[tt]
rs.Open "Select * from Data", cn[/tt]

Try this:

Dim strSQL as String

strSQL = "Select * from Data where [Branch Number:] = [red]'BR341'[/red]"
Debug.Print strSQL
rs.Open strSQL, cn

If this does not work, you have some other problem.

if it works OK, then try this:

Dim strSQL as String
Debug.Print "The value of Ca is *" & Ca.Value & "*"

strSQL = "Select * from Data where [Branch Number:] = '" & Replace(Ca, "'", "''") & "'"
Debug.Print strSQL
rs.Open strSQL, cn

And show us what you have after first Debug.Print statement:
[tt]The value of Ca is *[red]???[/red]* [/tt]



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[rekcut01]

Hey guys,

Your suggestions helped and he got it to run!! Thanks very much. Unfortunately he left for the day and didn't explain to me what was happening. I will get the info in the morning and will post it to you. Your help was very appreciated!

Thanks again
--Rick

 

[Andrzejek]

For future references, I would suggest to avoid using Access' reserved words for table names, query names, field names, etc.

Also, even tho Access allows you to, I would not use spaces or any special characters in field's name. Use _ instead of a Space.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.