How can I get this query to work with SQL tables?

[gold007eye]

I am in the process of converting my database backend from Access to SQL Server 2005. Everything was going pretty good until I hit a snag with this query. This worked fine until I moved the tables to SQL Server.

To give you an idea of what this query is supposed to do. I have a search form with 9 options for the user to search by. a Minimum of 1 search criteria is required. (See attached image taken from Access Backend version). A user can use as many or few criteria in an effort to narrow down the results.

I have tried to re-write the query to work with the SQL tables to no avail.. the best I can do is have 1 specific search criteria work, but not the others.

Could someone show me how I need to reformat this to work with the SQL tables? I keep getting OBDC Error (3146) when I try to run the query on the Front End.

Here is the code:

SELECT [Letter Information].ID, [Letter Information].[Provider Name], [Letter Information].CCN, [Letter Information].PTAN, [Letter Information].[Letter Type], [Letter Information].[Letter Date], [Letter Information].[Contact Name], [Letter Information].Analyst, [Letter Information].[Address Line 1], [Letter Information].[Address Line 2], [Letter Information].City, [Letter Information].State, [Letter Information].[Zip Code]
FROM [Letter Information] INNER JOIN [Letter Names] ON [Letter Information].[Letter Type] = [Letter Names].[Letter Name]
WHERE ((([Analyst]=[Forms]![Search Form]![Analyst] Or [Forms]![Search Form]![Analyst] Is Null)=True) AND (([Letter Type]=[Forms]![Search Form]![Letter Type] Or [Forms]![Search Form]![Letter Type] Is Null)=True) AND (([Letter Date]=[Forms]![Search Form]![Letter Date] Or [Forms]![Search Form]![Letter Date] Is Null)=True) AND (([Provider Name] Like [Forms]![Search Form]![First Name] & "*" Or [Forms]![Search Form]![First Name] Is Null)=True) AND (([Provider Name] Like "*" & [Forms]![Search Form]![Last Name] Or [Forms]![Search Form]![Last Name] Is Null)=True) AND (([PTAN] Like [Forms]![Search Form]![PTAN] & "*" Or [Forms]![Search Form]![PTAN] Is Null)=True) AND (([CCN] Like [Forms]![Search Form]![CCN] & "*" Or [Forms]![Search Form]![CCN] Is Null)=True) AND (([Provider Name] Like "*" & [Forms]![Search Form]![Provider Name] & "*" Or [Forms]![Search Form]![Provider Name] Is Null)=True) AND (([Contact Name] Like "*" & [Forms]![Search Form]![Contact Name] & "*" Or [Forms]![Search Form]![Contact Name] Is Null)=True) AND (([Letter Names].Status)=True))
ORDER BY [Letter Information].[Provider Name], [Letter Information].[Letter Type], [Letter Information].[Letter Date];

The OBDC error doesn't seem to appear until this point in the code:

WHERE ((([Analyst]=[Forms]![Search Form]![Analyst] Or [Forms]![Search Form]![Analyst] Is Null)=True) AND (([Letter Type]=[Forms]![Search Form]![Letter Type] Or [Forms]![Search Form]![Letter Type] Is Null)=True) AND (([Letter Date]=[Forms]![Search Form]![Letter Date] Or [Forms]![Search Form]![Letter Date] Is Null)=True) AND (([Provider Name] Like [Forms]![Search Form]![First Name] & "*" Or [Forms]![Search Form]![First Name] Is Null)=True) AND (([Provider Name] Like "*" & [Forms]![Search Form]![Last Name] Or [Forms]![Search Form]![Last Name] Is Null)=True) AND (([PTAN] Like [Forms]![Search Form]![PTAN] & "*" Or [Forms]![Search Form]![PTAN] Is Null)=True) AND (([CCN] Like [Forms]![Search Form]![CCN] & "*" Or [Forms]![Search Form]![CCN] Is Null)=True) AND (([Provider Name] Like "*" & [Forms]![Search Form]![Provider Name] & "*" Or [Forms]![Search Form]![Provider Name] Is Null)=True) AND (([Contact Name] Like "*" & [Forms]![Search Form]![Contact Name] & "*" Or [Forms]![Search Form]![Contact Name] Is Null)=True) AND (([Letter Names].Status)=True))
ORDER BY [Letter Information].[Provider Name], [Letter Information].[Letter Type], [Letter Information].[Letter Date];

Any help would be greatly appreciated so I can move forward with this project

 

[KenHigg]

You need to resolve all the calls to the form objects before SQL Server gets it. You can do this by first building the sql statement as a text string. Hope this makes sense...

 

[gold007eye]

Could you explain how I would do that, please? I am very new to the whole SQL thing. Are you saying I need to throw the Query by the wayside?

 

[KenHigg]

I'm not 100% sure this will work for you but here's the basics:

You have a string called something like strSQL. Then you simply build the sql statements in it:

strSQL = "SELECT * From MyTableName"

Then if you need a report to use the results of the query you simply set its recordsource to the string:

me.recordsouce = strSQL

In your case there are some pcs of the sql statement that must resolved before you set the recordsource. For example, in the above sample let’s say that you want to enter the name of the table you want to use in a text box on a form. You would do something like:

strSQL = "SELECT * From " & forms!myForm!myTextBox

This way as soon as this line of code runs it goes out to the text box and gets the table name and appends it to the string strSQL

Hope all of this is making sense and you get the idea of how to implement it in your scenario -

 

[gold007eye]

I somewhat get what you are saying. I think where my problem comes in is that my "Search Form" has a list box that get's populated.

Here are the steps.

1. User enters criteria they want to search by.
2. User Clicks the "Search" button.
3. The "Search Results" ListBox .rowsource gets set to the "Letter Search - Query"
4. The "Search Results" ListBox is populated by the "Letter Search - Query".

So the problem I am seeing with the method your suggested is I can't do a me.recordsource command for the listbox. It would have to be something like [Search Results].RowSource = strSQL.

Is that possible? I was messing around trying to do something like that earlier, but was having no luck.

Do I need to be looking at something like a parameter query? Thanks again for the help.

 

[Pat Hartman]

The query should work with the field references. You do not have to resolve them in code. Jet resolves them before sending the query off to SQL Server. You should be able to remove the "=True" at the end of each condition. That is redundant.

You should be aware that the query as written will not use any indexes to find selected records. It will do a full table scan. Remove the "Like" conditions unless they are required because users will be typing partial names.

 

[KenHigg]

Thanks Pat, I stand corrected. Any idea where the ODBC error is coming from? I found this on the error number:

http://support.microsoft.com/kb/160762

 

[Pat Hartman]

That article may identify the issue. If not, I would simplify the query and add back one piece of the selection criteria at a time until I identify the failing part.

 

[gold007eye]

@Pat: I did try removing the "=True" from each of those prior to your suggestion and everytime I did; the criteria disappeared from the query all together.

@Ken: The OBDC error is coming from the Criteria. I rebuilt the query from scratch and with no criteria it worked fine, but once I added 1 criteria (see attached image) is when I get the OBDC error.
--------------

Maybe someone could suggest some better way for me to accomplish my goal?

I need the users to be able to enter search criteria in 1 or more of the 9 fields and populate a "ListBox" with the results based on whichever criteria was used to find the letter(s). So if they used: First Name, Letter Date, & Analyst for their search criteria... the query will look for any matching data for those fields and ignore the other 6 empty (Null) criteria fields.

Does that make sense?

 

[KenHigg]

For one thing it looks like you have the is null the wrong. It should be something like:

IsNull([Frms]![Search Form]![Analyst])

???

 

[gold007eye]

@Ken

Just tried changing the code around to the IsNull([Frms]![Search Form]![Analyst]). If I leave the "True" in the criteria field it gives the OBDC Error. If I remove the True it shows all records regardless of what I enter in the "Analyst" field.

 

[KenHigg]

Duh... What was I thinking. - All of the criteria should go on the criteria row. Then if it evaluates to true the record will be returned. You don't have to put the word 'true' anywhere...

 

[gold007eye]

Yeah I had tried that already too.. what happens doing that is that it will return 0 letters unless all fields 9 fields have data in them.. I want someone to be able to use any or all of the 9 search criteria. So I need something added to the query to say "If any search criteria fields are Null then ignore them and show results based on search criteria fields that have data entered into them"

Which is what the "True" statement in the criteria field was handling before moving the tables to SQL.

Such a puzzling issue that I would have thought would have been so easy to fix. lol

 

[Pat Hartman]

The general format for each field is:
(tablename.FieldName = Forms!formname!fieldname OR IsNull(Forms!formname!fieldname))

These are then connected by AND operators.

An alternative is:
(tablename.FieldName = Forms!formname!fieldname OR IsNull(Forms!formname!fieldname)= True)