?
Solved

Parameters in a query

Posted on 2012-08-28
3
Medium Priority
?
321 Views
Last Modified: 2012-08-31
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
Comment
Question by:sonmic
3 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 750 total points
ID: 38343038
Give this a whirl..

SELECT Project.Projectnr, Project.Projectomschrijving
FROM Project
WHERE (getloc() = 0) OR (Project.Projectnr=getloc());
0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 750 total points
ID: 38343201
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
 

Author Comment

by:sonmic
ID: 38344386
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question