Using form field value in query criteria

[Andy Mc]

Hi.

I have a query named "ClientWrkHrs":

SELECT ClientCal.Abbr, ClientCal.Start_Date, ClientCal.StartTime, ClientCal.EndTime, DateDiff("n",[StartTime],[EndTime])/10+1 AS Slots
FROM ClientCal
WHERE (((ClientCal.Abbr) Like "*" & "jo" & "*"))
ORDER BY ClientCal.StartTime;

This returns the records that I want, limited to those with "jo" in the field Abbr.

However, I want to make the criteria less specific, so I tried inserting a reference to a form field:

SELECT ClientCal.Abbr, ClientCal.Start_Date, ClientCal.StartTime, ClientCal.EndTime, DateDiff("n",[StartTime],[EndTime])/10+1 AS Slots
FROM ClientCal
WHERE (((ClientCal.Abbr) Like "*" & [Forms]![ClientAppt]![Cname] & "*"))
ORDER BY ClientCal.StartTime;

If I run the query in isolation and enter "jo" in the prompt it works fine. However, If I run the query from a command button on the form I get an error: "Too few parameters. Expected 1". The command button calls a module and it fails at line:

Set RS = db.OpenRecordset("ClientWrkHrs")

Does anyone have any ideas what could be wrong?

Thanks

 

[Gasman]

Read https://accessmvp.com/thedbguy/codes.php?title=generic

 

[Andy Mc]

Thanks Gasman. I couldn't get that to work for me (my bad probably) but it did get me on to a work around.

 

[ebs17]

A DAO method such as Openrecordset passes the query directly to the database engine (Jet/ACE). This understands SQL, but cannot do anything with variables and objects such as form text fields, which is why the query occurs.
In the simplest case, you make a replacement

Forms.ClientAppt.Cname => Eval(Forms.ClientAppt.Cname)

Eval evaluates the content of the text field so that the content is passed directly to processing.

Other ways to pass parameters:

Query to find records not between two dates

In a nutshell, I'm trying to create a report showing who hasn't made a payment between a selected date range, that's it. I don't know what the actual field names are, but the logic is: < Date1 OR > Date2 OR Payment Amount ISNULL (because these people didn't make any payment at all)

www.access-programmers.co.uk

 

[Gasman]

You could try a TempVar?

 

[Pat Hartman]

Dim db    As     DAO.Database
Dim qd    As    DAO.Querydef
Dim rs  As  DAO.Recordset

    Set db = Currentdb()
    Set qd = db!querydef!yourqueryname
        qd.Parameters![EnterCustID] = Me.CustID
        qd.Parameters![EnterStartDT] = Me.StartDT
    Set rs = qd.OpenRecordset

When I create parameters I named them [Entersomething] so I know if the prompt is intentional or accidental