• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Parameters in a query

Hi,

I have a query, in sql : SELECT Project.Projectnr, Project.Projectomschrijving FROM Project WHERE (((Project.Projectnr)=getloc()));

The  code for getloc is

Public Function getLoc() As integer
On Error GoTo Err_Fout
    Dim dbs As DAO.Database
    Dim rsT As DAO.Recordset
    Set dbs = CurrentDb()
    Set rsT = dbs.OpenRecordset("tbdsettings", dbOpenTable)
    With rsT
        .MoveFirst
        If !Locatie = 0 Then
            getLoc = 0
        Else
            getLoc = !Locatie
        End If
    End With

Exit_Fout: On Error Resume Next
    rsT.Close
    dbs.Close
    Set rsT = Nothing
    Set dbs = Nothing
    Exit Function

Err_Fout:
    MsgBox err.Description, vbCritical, err.Number
    Resume Exit_Fout

End Function

When !Locatie is 0 i want all the records selected in the query else only the value of !Locatie.
What i want is that getloc is >0.

How can i do that?

Tx
0
sonmic
Asked:
sonmic
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..

SELECT Project.Projectnr, Project.Projectomschrijving
FROM Project
WHERE (getloc() = 0) OR (Project.Projectnr=getloc());
0
 
HainKurtSr. System AnalystCommented:
try this

declare @loc int=getloc();

SELECT Project.Projectnr, Project.Projectomschrijving 
FROM Project 
WHERE (@loc = 0) OR (Project.Projectnr=@loc);

Open in new window

0
 
sonmicAuthor Commented:
Hi,

I have find a solution.
Criteria in my query "Between getLoc() And getLoc()"
and vba
        If !Locatie = 0 Then
            If Not sw Then
                getLoc = !Locatie
                sw = Not sw
            Else
                getLoc = 9999
                sw = Not sw
            End If
        Else
            getLoc = !Locatie
        End If
and it works.

Tx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now