Question Error : Invalid column name

[zregalado]

Hi Everyone,
I'm new here as member, but I did get a lot help here just by reading some of the threads/post.. need help/advice on below.

I have this code below which work on my other MS access 2007 database, it opens the report based on my filtered form, however it did'nt work on my database with an SQL backend, it returns an error "Invalid column name...."

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report", acViewPreview, , strWhere

Thanks in advance.
Sonny

 

[Pat Hartman]

Conceptually this is fine. Print the contents of the filter and post them please.

 

[zregalado]

Thanks Pat.
See attached error screen shot. You'll see that the filter is doing its job except that it cannot open the report.
Without filter is not a problem, the report is opening.

 

[Pat Hartman]

You didn't print the filter but I'm going to guess that the problem is caused by a column name with embedded spaces. That's why Access thinks it is a "multi" part filter.

If you have a spare hour or two, now would be as good a time as any to clean up your naming scheme. Get rid of embedded spaces and special characters. Use CamelCase or the_underscore to make names readable. Also change any names that duplicate the names of properties, methods, or functions such as Name, Date, Month, Open, Flag, etc.

 

[zregalado]

Thanks again Pat for your reply & concern..
I basically do not have spaces or embedded spaces on column or field name, I found 3 reserved names (i.e. Type, Description & Report) and changed it but still getting the error -Invalid column name 'the name of record in the column'... can't figure out...

 

[gemma-the-husky]

try opening the reports query directly. is there something wrong there?

secondly in your code, if the filteron is false, your strwhere is set to a nullstring I think. would that be an issue maybe?

 

[Pat Hartman]

What is "Vendor Quality Issues.ProjectName"? It contains embedded spaces.

Please post the filter.

 

[info@pinetreeit.com]

Hi,
Try putting square brackets around the field names, thats worked for me in the past.

Thanks
PinetreeIT
AccessLifecycleManager the build & release management tool for Microsoft Access © developers & teams

 

[MSAccessRookie]

Hi,
Try putting square brackets around the field names, thats worked for me in the past.

Thanks
PinetreeIT ...

What is "Vendor Quality Issues.ProjectName"? It contains embedded spaces.

Please post the filter.

In regards to the Statements made by Pat Hartman and PinetreeIT, and based on Context Clues, I am going to speculate that "Vendor Quality Issues" is a Table/Query in your Database, and "ProjectName" is a Field in that Table/Query. If this is the case, then PinetreeIT's advice is exactly correct and you need to use the Brackets, since the Spaces are special characters and cannot be used without them.

 

[zregalado]

Hi guys thanks a lot for your concerns.
I do not have fields with spaces right now so no use of the bracket and Vendor Quality Issues is a Table name. What I've noticed now is when I apply the filter on the columns that contain number or with the number/int data type, the code works! the report opens what was filtered, in other words if I filter on other data types (i.e. varchar, nvarchar, ntxt) the code doesn't work, or it reads only numbers, Im pretty sure everyone knows what im trying to say and everybody has an idea now. I know what is the problem but I do not know how to solve it. Hope to hear from you guys soon..
Thanks al lot guys,
Sonny

 

[Pat Hartman]

Dates are delimited with the pound sign - #05/30/12#
Strings are delimited with single or double quotes - 'My Name' or "Joe's Name" - double quotes are required if the string may contain single quotes as most name fields can.
Numbers are not delimited - 234.567

 

[zregalado]

Pat, can you tell me what should I do now?, anything to change in my database or to the code?

 

[Pat Hartman]

Look at the data types of the fields you are filtering on and make sure the variables you are using to filter them delimit their values correctly.