Database Date Variable

If I use a statement like the one below I can open my database.

SELECT myFields
FROM myTable
WHERE dDate = DateValue('9/22/2000')

How can I open it with a value entered in a textbox. I tried
myDate = Text1.Text
SELECT myFields
FROM myTable
WHERE dDate = DateValue(myDay)
but it didn't work. How do I do it?


[Edited by kokopeli on 09-22-2000 at 02:41 PM]

try this:

Code:

---

myDate = Text1.Text
"SELECT myFields
FROM myTable
WHERE dDate = " & chr$(34) & DateValue(myDate) & chr$(34)

---

Its also a good idea to use Format (text1.text, "dd/mmm/yyyy") on the date before you pass it into the query. If you are working against a server it may not have the date format set up the same as the client.
ie, US vs European date formats.

The statment is coming to the Debug window like this.
WHERE dDate = "6/29/00"
but it won't open the DB. I get "Data type mismatch in criteria expression" I have the textbox dataformat set to date, I Dimmed myDay as Date, I set it myDay = Format(Text1.text,m/d/yy) , my DB has field set to Date/Time format shortdate.

try this:

make sure you are setting a reference to the
DAO 3.51 Object Library

Code:

---

dim sSQL as String
sSQL = "SELECT myFields " & _
 "FROM myTable WHERE dDate = " & chr$(34) & text1.text & chr$(34)

set db = opendatabase("My Database")
set rs = db.openrecordset(sSQL)

while not rs.eof
  list1.additem rs!myFields
  rs.movenext
wend

set rs = nothing
set db = nothing

---

[Edited by larryn on 09-22-2000 at 05:32 PM]

Here is my code.

Code:

---

Dim strSQL As String
    Dim myDay As Date
    myDay = Format$(frmSales!Text1.Text, "M/d/yy")
   
   
    strSQL = "SELECT dDate, "
    strSQL = strSQL & "BrandPrem, "
    strSQL = strSQL & "BrandReg , "
    strSQL = strSQL & "CLRetailDiesel, "
    strSQL = strSQL & "CLRackPrem, "
    strSQL = strSQL & "RedDye, "
    strSQL = strSQL & "MasterSlaveDiesel, "
    strSQL = strSQL & "CLRackReg "
    strSQL = strSQL & "FROM Inventory "
    strSQL = strSQL & "WHERE dDate = DateValue('6/27/00')"
      '& Chr$(34) & DateValue(myDay) & Chr$(34)
   
    If moRS.State = adStateOpen Then
        moRS.Close
    End If
    Debug.Print strSQL
    moRS.Open strSQL, goConn, _
                adOpenKeyset, adLockPessimistic, adCmdText
    moRS.MoveLast

---

It works like this, but I want to be able to choose whatever date I want.

Its like this if anyone wants to know.

Code:

---

myDay = Text1.Text
"SELECT myFields
FROM myTable
WHERE dDate = " & chr$(35) & myDay & chr$(35)

---

I can't believe all I needed was #myDay#