Date format in QueryDef parameters? 
Author Message
 Date format in QueryDef parameters?

VB4.0 standard (sorry) ...

I have a querydef which looks like this:

        PARAMETERS StartDate DateTime, EndDate DateTime;
        SELECT Day.dDayDate, Event.dTime, sEvDescription, sEvRemarks
        FROM Day LEFT JOIN Event ON Day.dDayDate=Event.dDayDate
        WHERE Day.dDayDate>=[StartDate] AND
        Day.dDayDate<=[EndDate];

The procedure which sets up the query has two date format variables; in
what format do I assign them to the parameters?  I've tried

        qdEvents.Parameters(0) = dStartDate

.. which gives me "3001 - invalid argument" at the Openrecordset method.
I also tried various variants of

        qdEvents.Parameters(0) = format(dStartDate, "some date format")
        qdEvents.Parameters(0) = "'" & format(dStartDate, "...") & "'"
        qdEvents.Parameters(0) = "#" & format(dStartDate, "...") & "#"

which variously produce 3001's and "invalid datatype" messages.  I'm
clean out of ideas now ... can someone help?  


Manchester, England



Sat, 18 Sep 1999 03:00:00 GMT  
 Date format in QueryDef parameters?

Quote:

> VB4.0 standard (sorry) ...

> I have a querydef which looks like this:

>         PARAMETERS StartDate DateTime, EndDate DateTime;
>         SELECT Day.dDayDate, Event.dTime, sEvDescription, sEvRemarks
>         FROM Day LEFT JOIN Event ON Day.dDayDate=Event.dDayDate
>         WHERE Day.dDayDate>=[StartDate] AND
>         Day.dDayDate<=[EndDate];

Hi
Try this date format

" WHERE Day.dDayDate between #" & Month(StartDate) & "/" &
Day(StartDate) _
 & "/" & Year(StartDate) & "# " _
 & " AND      #" & Month(EndDate) & "/" & Day(EndDate) & "/" &
Year(EndDate) & "# "

If you have many queries you can put  the date formatting in a sub.

Odd Haugum



Sun, 19 Sep 1999 03:00:00 GMT  
 Date format in QueryDef parameters?

Quote:

>The procedure which sets up the query has two date format variables; in
>what format do I assign them to the parameters?  I've tried

I vaguely recall reading somewhere that the dates must be in the
US month-date-year style if they're in a SQL statement.

Joe

Never underestimate the power of a WAG.

http://www.citilink.com/~jgarrick/vbasic/



Tue, 21 Sep 1999 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Setting a date Parameter, how do I format?

2. Parameter QueryDef Not Working

3. Help? QueryDef parameters don't work

4. querydef with Parameter problem

5. Question for anyone who has used parameter queries with querydef objects in VB

6. ADO: Settting a text parameter to an Access 97 Select querydef

7. _SLOW_ QueryDef.Parameters call

8. QueryDef Parameters in VB3

9. Help! Cannot specify parameters using QueryDef in ODBCDirect

10. To Few Parameter, Expecting XX in Querydef object

11. QueryDef.Parameters

12. Error - data has changed on setting querydef parameter

 

 
Powered by phpBB® Forum Software