Database Date Variable

[kokopeli]

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:

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.

[kokopeli]

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

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]

[kokopeli]

Here is my 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.

[kokopeli]

Its like this if anyone wants to know.

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

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