Custom function with Pass-Through in Access

[zezo2021]

Hello friends
Have a great day
this function work with normal query inside access

WHERE (Projects.Client=CName() or CName()="")

I want to convert this code to work with Pass-Through
I tried many what but all not work

For example1

 

[FrankRuperto]

First you have to tell us the target db server the query is going to be passed to because each db server has specific SQL syntax, e.g. SQL-Server uses T-SQL syntax.

 

[zezo2021]

Hello

SQL SERVER

 

[FrankRuperto]

Okay, you only provided part of the sequel statement you wish to pass through. Can you provide the full statement?

 

[Minty]

What is CName() ?
If it's a local VBA function SQL Server won't recognise it at all.

And it won't like "" - You would use '' as an empty string in T-SQL

 

[zezo2021]

Here is the function
Public CName_ As String

Public Function CName() As String
CName = CName_
End Function

 

[zezo2021]

CName is place holder to pass the value to query
if you don't sign a value to CNAME
Should display all records

 

[cheekybuddha]

WHERE (Projects.Client='" & CName() & "' OR '' = '" & CName() & "')"

 

[zezo2021]

cheekybuddha​

Incredible

Incredible



Thank you so much

 

[cheekybuddha]

You're welcome.

If your changing the PASS-THRU SQL in VBA you can probably do it even more simply, but it would be easier to see your whole VBA code.

 

[FrankRuperto]

@zezo2021 - but what does the rest of your sequel statement look like?... i.e. the SELECT/FROM part. I ask because there's syntax also involved when specifying column and table names, plus we might be able to further optimize your PT query.

 

[zezo2021]

FrankRuperto​

@FrankRuperto
it very simple select statement the trick in where clause

Thank you so much for your interest

 

[Pat Hartman]

Keep in mind that
anyfield = quotequote (sorry, getting an error trying to post quote quote)
Is looking for a ZLS which is completely different from Null so make sure your function returns a ZLS rather than a Null when the the function finds the name to be null.

 

[cheekybuddha]

The function is posted in Post #6 and returns a string based on a module-level string variable (can not return Null).

However the code to set the PassThru SQL might be better more along the lines of:

Dim strClient As String

strClient = Replace(CName, "'", "''")
With CurrentDb.QueryDefs("ptQuery")
  .SQL = "SELECT * FROM Projects" & IIf(Len(strClient), " WHERE Client = '" & strClient & '", vbNullString)
  .ReturnsRecords = True
  Set rs = .OpenRecordset
End With

No need to bother with OR '' = ''