Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    67

    Red face Unanswered: stop SQL from turning off "Show" for Fields

    I am trying to create a Query using SQL code in VBA. When I create the Query with a constant for a field, everything works fine. When I substitute a variable for the constant, the Query created does not show fields with variables. Following are the two SQL statements that are driving me crazy!!

    'First Statement with the constant 11/7/2008 (this code works fine). Note that "IncurredSelectedDate" is a variable that is not messing up the Query.
    SQL = "SELECT ID, MbrNum, EeRel, PaidClaims, IncurredDate, PaidDate, BirthDate " & _
    " FROM " & SourceTable & _
    " WHERE IncurredDate>=" & IncurredSelectedDate & " AND PaidDate<=#11/7/2008#;"

    'Second Statement, replacing the above 11/7/2008 with the variable "PaidSelectedDate" (this code unchecks the Query "show" field for both the "IncurredDate" and "PaidDate" fields.
    SQL = "SELECT ID, MbrNum, EeRel, PaidClaims, IncurredDate, PaidDate, BirthDate " & _
    " FROM " & SourceTable & _
    " WHERE IncurredDate>=" & IncurredSelectedDate & " AND PaidDate<=" & PaidSelectedDate & ";"

    'This is the statement that I use to create the Query
    Set qdef = CurrentDb.CreateQueryDef("qryTemp", SQL)

    Any help would be greatly appreciated!!

    Thank you.

    Jim

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure this is the answer to your problem but when you deal with Date variables and use them to build a SQL statement you should use the Format function to be sure the date is in mm/dd/yyyy format.

    Ex:
    11/7/2008 = July 17, 2008 in Europe
    11/7/2008 = November 7, 2008 in the USA

    What happens when you have 30/7/2008 (valid in Europe) ?

    Moreover keep in mind that you're dealing with Date/Time data type. When you pass something like 11/7/2008 it means 11/7/2008 0:00. If the fractional part (the time part) is stored in the database it can make a difference when you use comparison operators in a WHERE clause: you only see the DATE part but the TIME part is present and is used in the comparison.

    Have a nice day!

  3. #3
    Join Date
    Feb 2004
    Posts
    67
    Thank you. Yes, the date format was my problem. I needed to surround my variables with "#".

    Thank you for your help.

Posting Permissions

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