Re: QueryDef

From: Graham Mandeno (Graham.Mandeno_at_nomail.please)
Date: 05/11/04 

Date: Tue, 11 May 2004 23:41:10 +1200

Hi Andy

The combo box value is actually returning a string, which Access is clever
enough to evaluate when the query is opened from the database window.
However, when you open it from VBA it fails to do this.

Try this:
    Between CDate([Forms]![Form1].[Combo2]) And
        CDate([Forms]![Form1].[Combo2])+6 

-- 
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
"Andy" <anonymous@discussions.microsoft.com> wrote in message
news:6CDD0B1D-7A8B-4157-BB46-ECB1D601EC1A@microsoft.com...
> Sorry to post this twice (also in Programming VBA) but I think it belongs
here.
>
> I am calling zReport1 from Form1. The Report has the
> following recordset code :-
>
>     Dim db1 As Database
>     Dim rs As Recordset
>     Dim qdf As QueryDef
>     Dim prm As Parameter
>
>     Set db1 = CurrentDb
>
>     Set qdf = db1.QueryDefs("Query1")
>
>     For Each prm In qdf.Parameters
>            prm.Value = Eval(prm.Name)
>     Next prm
>
>     Set rs = qdf.OpenRecordset(dbOpenDynaset)
>
> '''''
>
> Query1 pulls back few fields from some tables and one of
> the fields, a date format field, has the ctriteria :-
>
> Between [Forms]![Form1].[Combo2] And [Forms]![Form1].
> [Combo2]+6
>
> Combo2 on Form1 is a simple list of dates in date format.
>
> The code above fails at :-
>
> Set rs = qdf.OpenRecordset(dbOpenDynaset)
>
> with an error about incompatible data types.
>
> If I change the query criteria to Between Date() and Date
> ()+6 (i.e. using system clock rather than combo list) it
> works OK
>
> Any ideas?
>
>

Relevant Pages

  • Re: ADODB Recordset Not Loading From Parameter Query
    ... The query itself runs fine when running manually and supplying the ... Dim con As ADODB.Connection ... Dim prm As ADODB.Parameter ... Set prm = cmd.CreateParameter("@LastName", adVarChar, adParamInput, 25) ...
    (microsoft.public.access.modulesdaovba)
  • Re: OpenRecordset
    ... query is taking values or criteria from fields on a form (which are treated ... Dim qdf As DAO.QueryDef ... Dim prm As DAO.Parameter ...
    (microsoft.public.access.formscoding)
  • Re: Loading query into a recordset...
    ... Set MyRecordSet = qdf.OpenRecordset ... For Each prm in qdf.Parameters ... > You don't need to use the name of the query in the OpenRecordset action. ... >> Dim MyRecordSet As DAO.Recordset>> Dim qdf As QueryDef ...
    (microsoft.public.access.modulesdaovba)
  • RE: Field selected by Combo Box
    ... Public Sub OpenTempQuery(strSQL As String) ... Dim dbs As DAO.Database ... Dim prm As DAO.Parameter ... button a query would run that would show the grouped field entries for their ...
    (microsoft.public.access.queries)
  • Re: Cannot update DAO Recordset
    ... the database window, ... Form is based on a query with 4 fields, ... Dim rs As DAO.Recordset ... Running Access 2003/SP2, Windows XP ...
    (microsoft.public.access.modulesdaovba)