Setting up Sql Search to Sql Server backend using Connections (2 Viewers)

[Geirr]

Hi all.

Found out that I want to go from linked tabled on sql server backend to sql connections within code.
I've got a test up and running, but struggling with sql queries which are getting the Where criteria from the forms combo boxes.

The actual SQL Query is:

strSql = "SELECT dbo.UserLog.ID, dbo.UserLog.UserProjectNo, dbo.UserLog.UserEmail, dbo.UserReg.EmpName, dbo.UserReg.EmpLevelA, " _
& "dbo.UserLog.UserIn, dbo.UserLog.UserOut " _
& "FROM dbo.UserReg INNER JOIN dbo.UserLog ON dbo.UserReg.EmpEmail = dbo.UserLog.UserEmail " _
& "WHERE (((dbo.UserLog.UserProjectNo) Like '*' & [Forms]![CableAdminTool]![NavigationSubform].[Form]![cboSelectProject] & '*') " _
& "AND ((dbo.UserLog.UserEmail) Like '*' & [Forms]![CableAdminTool]![NavigationSubform].[Form]![cboSelectUserEmail] & '*'))" _
& "ORDER BY dbo.UserLog.ID"

First, on linked tables, all references to the linked tables where dbo_ Testing with a simple query I found that I need to remove the _ and replace it with .
But, when adding the Where clause, I'm really stuck (Of course, this query works fine with linked table, but with dbo_ etc).
I've replaces the & [Forms..... ]![cboSelectProject] with any version I can come up with, including using Me.cboSelectProject, and also getting value from the combobox to a variable, and put the text variable into the Query with '" & [txtVariable] & "'. Also testet both type with all kinds of different setup wit single and double quota...

The error message have been Error near ' or ! or &, Error due to mismatch, and error due to varchar mismatch (Btw, text column in sql table are nvarchar(nn)).

What is it I am missing or doing wrong?

To prove the connection work, the image is without the WHERE in the query string above...

Edit: Yes, I'm using navigation form as main form with subforms.
And yes, I'm almost always make queries with the Query Designer so I'm sure it works before I copy sql code into forms/reports where that is most convenient. But for the same reason I want to stop using linked tables, I also want to hardcode queries into forms/reports which today are around 50% as separate queries and 50% hardcoded in forms/reports.

Thank you!
Best regards, Geirr.

 

[dalski]

I know nowt about SQL Server, but I am aware that if a calculated field in your query relies on an Alias which was created in that query SQLServer will not recognize it but Access will; just incase this helps out (CheekyBuddha taught me [thanks]).

Mismatch usually indicates a type error I believe, so maybe check datatypes. I know SQLServer has some different datatypes.

 

[RonPaii]

Hi all.

Found out that I want to go from linked tabled on sql server backend to sql connections within code.
I've got a test up and running, but struggling with sql queries which are getting the Where criteria from the forms combo boxes.

The actual SQL Query is:

strSql = "SELECT dbo.UserLog.ID, dbo.UserLog.UserProjectNo, dbo.UserLog.UserEmail, dbo.UserReg.EmpName, dbo.UserReg.EmpLevelA, " _
& "dbo.UserLog.UserIn, dbo.UserLog.UserOut " _
& "FROM dbo.UserReg INNER JOIN dbo.UserLog ON dbo.UserReg.EmpEmail = dbo.UserLog.UserEmail " _
& "WHERE (((dbo.UserLog.UserProjectNo) Like '*' & [Forms]![CableAdminTool]![NavigationSubform].[Form]![cboSelectProject] & '*') " _
& "AND ((dbo.UserLog.UserEmail) Like '*' & [Forms]![CableAdminTool]![NavigationSubform].[Form]![cboSelectUserEmail] & '*'))" _
& "ORDER BY dbo.UserLog.ID"

First, on linked tables, all references to the linked tables where dbo_ Testing with a simple query I found that I need to remove the _ and replace it with .
But, when adding the Where clause, I'm really stuck (Of course, this query works fine with linked table, but with dbo_ etc).
I've replaces the & [Forms..... ]![cboSelectProject] with any version I can come up with, including using Me.cboSelectProject, and also getting value from the combobox to a variable, and put the text variable into the Query with '" & [txtVariable] & "'. Also testet both type with all kinds of different setup wit single and double quota...

The error message have been Error near ' or ! or &, Error due to mismatch, and error due to varchar mismatch (Btw, text column in sql table are nvarchar(nn)).

What is it I am missing or doing wrong?

To prove the connection work, the image is without the WHERE in the query string above...
View attachment 121946

Edit: Yes, I'm using navigation form as main form with subforms.
And yes, I'm almost always make queries with the Query Designer so I'm sure it works before I copy sql code into forms/reports where that is most convenient. But for the same reason I want to stop using linked tables, I also want to hardcode queries into forms/reports which today are around 50% as separate queries and 50% hardcoded in forms/reports.

Thank you!
Best regards, Geirr.

You need double quotes around the *

strSQL = "SELECT dbo.UserLog.ID, dbo.UserLog.UserProjectNo, dbo.UserLog.UserEmail, dbo.UserReg.EmpName, dbo.UserReg.EmpLevelA, " _
& "dbo.UserLog.UserIn, dbo.UserLog.UserOut " _
& "FROM dbo.UserReg INNER JOIN dbo.UserLog ON dbo.UserReg.EmpEmail = dbo.UserLog.UserEmail " _
& "WHERE (((dbo.UserLog.UserProjectNo) Like '*'" & [Forms]![CableAdminTool]![NavigationSubform].[Form]![cboSelectProject] & "'*') " _
& "AND ((dbo.UserLog.UserEmail) Like '*'" & [Forms]![CableAdminTool]![NavigationSubform].[Form]![cboSelectUserEmail] & "'*'))" _
& "ORDER BY dbo.UserLog.ID"

 

[arnelgp]

try replacing * with percent (%) symbol.

or if the query is within MS Access:

strSql = "SELECT dbo.UserLog.ID, dbo.UserLog.UserProjectNo, dbo.UserLog.UserEmail, dbo.UserReg.EmpName, dbo.UserReg.EmpLevelA, " _
& "dbo.UserLog.UserIn, dbo.UserLog.UserOut " _
& "FROM dbo.UserReg INNER JOIN dbo.UserLog ON dbo.UserReg.EmpEmail = dbo.UserLog.UserEmail " _
& "WHERE (((dbo.UserLog.UserProjectNo) Like '*" & [Forms]![CableAdminTool]![NavigationSubform].[Form]![cboSelectProject] & "*') " _
& "AND ((dbo.UserLog.UserEmail) Like '*" & [Forms]![CableAdminTool]![NavigationSubform].[Form]![cboSelectUserEmail] & "*'))" _
& "ORDER BY dbo.UserLog.ID"

 

[Geirr]

Thank you guys! Step one now works...
The soultion where as arnelgp wrote using % and in conjuction with '%" & - - & "%' (placing the single and double quota).

Now I have to find a new way to get around the Me.Refresh in the comboboxes After Update to update the .recordset which not working as earlier with recordsource...

Found it, using the same code as used in form_load in the Me.cbo.. AfterUpddate()
Not as tidy as Me.Refress, but it works...