Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

rekcut01

Programmer
Jun 19, 2014
14
US

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
 
What is Ca ?

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

Code:
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
 
What is the data type of [Branch Number:] ?

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

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

BR341
BR342
BR343

Etc....

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

Code:
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.
 
So, use this:
Code:
rs.Open "Select * from Data where [Branch Number:]='" & Replace(Ca.Value, "'", "''") & "'", cn

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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
 
Just to know, did you try my last suggestion ?
 

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
 


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

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

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

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
 
Seems like you didn't use my suggestion time stamped 1 Jul 14 14:01
 
Try both: mine and PHV's suggestions.

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

Try this:
Code:
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:
Code:
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.
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top