QueryDef problem

[Andy Reed]

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?

 

[Gerald Stanley]

Try
Between #Format([Forms]![Form1].[Combo2], "dd/mm/yyyy")#
And #Format([Forms]![Form1].[Combo2]+6), "dd/mm/yyyy")#

Hope This Helps
Gerald Stanley MCSD

 

[Gerald Stanley]

A typo error on my part - dates have to be entered in US
format. Try
Between #Format([Forms]![Form1].[Combo2], "mm/dd/yyyy")#
And #Format([Forms]![Form1].[Combo2]+6), "mm/dd/yyyy")#

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Unfortunately got a message "The expression you entered

has an invalid date value" - thanks for trying.

 

[Tim Ferguson]

Between #Format([Forms]![Form1].[Combo2], "mm/dd/yyyy")#

... does not make sense. This does:

"... BETWEEN " & _
Format(Forms!Form1!Combo2, "\#mm\/dd\/yyyy\#") & _
"... "

Calling a SQL command from VBA bypasses the expression evaluation service
altogether, and Jet knows nothing about forms or controls: therefore you
need to insert to actual value, properly delimited. Alternatively, using
the params collection is a good way of passing in the value, and has the
advantage of using a real DateTime value, and takes out the messing about
with US date strings.

I believe the problem lies in the addition: since the combo box will be
returning a string value, the second half of the criterion looks like

.Parameters(2) = "12/05/2004" + 6

which at best concatenates the "6" and at worst simply fails to convert to
any data type. A DateAdd function just might work; or just doing the thing
right in the first place.

WHERE Forms!Form!Combo2 <= MyDateField
AND MyDateField -6 -1 < Forms!Form!Combo2

which also caters for non-midnight values of MyDateField correctly. It
would also be safer to insert a line like this at the top of the query:

PARAMETERS Forms!Form!Combo2 DATETIME;
SELECT ....

so that Jet is left in no doubt about what it is meant to be doing.

Hope that helps


Tim F