Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100

    Unanswered: Simple date problem??

    I have an unbound text box on a form that is used as a parameter in a SQL statement.

    The statement is ("SELECT Invoice_ID FROM TBL_Invoice WHERE Invoice_Payment_date = #" & me.txt_payment_date & "#")

    it has been working fine but all of a sudden it's not returning any data (when I know it's there!)

    Also, originally I had the statement like this:

    ("SELECT Invoice_ID FROM TBL_Invoice WHERE Invoice_Payment_date = " & me.txt_payment_date & "")

    ie without the "#" and it worked fine but then fell over so I changed it.

    I've checked all the date settings (I'm on the European date) and also for any missing references but I'm stumped - anyone any idea?
    Access XP & WinXP Pro

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Arrow

    Make your own function converting the date to SQL-Date
    PHP Code:
    Function CSQLDate(varDate As Variant) As String
    'This funktion changes any given date format 
    to a string that used in the SQL-Statment.

      If IsDate(varDate) Then
         CSQLDate = "#" & Month(varDate) & "/" & _ 
            Day(varDate) & "/" & Year(varDate) & "#"
      Else
          CSQLDate = ""
      End If
    End Function 

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    To use it in your application

    SELECT Invoice_ID FROM TBL_Invoice WHERE Invoice_Payment_date
    CSQLDate(Me.txt_payment_date)

  4. #4
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    thanks for that, changing the date to mm/dd/yyyy works fine, any idea why it stopped working with my original statement?
    Access XP & WinXP Pro

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Can you make a MessageBox before you execute the DoCmd to see the date format. I think it is not the american one.

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    I think it was only the Date format

Posting Permissions

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