Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    querydef (Access)

    I am using the following statement to construct a query

    strSQL = "select * from qryProductionSales where tdate >= #" & txtStart & "# and tdate <= #" & txtEnd & "#
    CurrentDb.QueryDefs("qryProduction").SQL = strSQL

    I pause the program and note that the strSQL's content is

    select * from qryProductionSales where tdate >= #01/01/01# and tdate <= #01/12/30#

    however, when I open qryProduction, the criteria for the field tdate is:

    >=#01/01/01# And <=#30/01/12#

    which causes no record in the query result. My computer regional setting for the date is yy/mm/dd.

    I wonder what causes the problem (changing the date format and no record in the query result).

    Thanks

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: querydef (Access)

    I think you will find that Access expects the date in the where clause in USA format regardless of the regional settings. I use UK dates and I always have trouble getting it right.

    From experience I would suggest that you test your where clause with test dates like 01/01/01 and 02/02/02. It doesn

  3. #3
    New Lounger
    Join Date
    May 2002
    Location
    Tasmania, Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: querydef (Access)

    Rupert is right. You need to pass the date in US format to the query. Try :

    strSQL = "Select * fromqryProductionSales Where tdate >= #" & Format(txtStart, "mm/dd/yyyy") & "# and tdate <=#" & Format(txtEnd, "mm/dd/yyyy") & "#"

    Ryan

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: querydef (Access)

    Thanks guys,

    I also find out that if I use the datevalue function (instead of format), it works fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •