Not working correctly

My aim is to select all invoices done by a certain user and done 'today' using system date.

In my sql statement, when i use the date it returns nothing. When i remove the date section of the statement it works fine BUT selects everything.

VB Code:

Public Function GetData() As ADODB.Recordset
   Dim strsql As String
   ' Dim r As ADODB.Recordset '
   Dim c As ADODB.Connection
   Dim multipledate
    raised = FrmInvoice.StatusBar1.Panels(3).Text
  multipledate = Date
   Set c = New ADODB.Connection
   c.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False; Data Source=.\Invoice.mdb;"
   c.Open
 
   strsql = "SELECT * from Invoice WHERE RaisedBy = '" & raised & "' AND [Date] = '" & multipledate & "' "
   'strsql = strsql & "WHERE Raisedby = '" & raised & "' "
   'Set rsquery = New ADODB.Recordset
   
   
      rsquery.Open strsql, c, adOpenDynamic, adLockPessimistic
 
   Set GetData = rsquery
End Function

Don't you need to format the date string appropriately for your database?

e.g. {d '2005-02-02'}

Thats what i thought originally.

Then i found a web site that said-

date = date is same as

date = format(date, "dd-mm-yyyy")
I tried it in a textbox and it was right.

Unless i'm getting american dates and british dates mixed up somewhere.

So, if you do a Debug.Print on strsql what do you get?

On my PC Date() returns "02/02/2005 ". For SQL Server this should be {d '2005-02-02'}

VB Code:

"{d '" & Format(Date,"yyyy-mm-dd") & "'}"

access data base store dates mm/dd/yy pattern, so you may need to format your date to that pattern to make it match

rgds pete

have you also seen the replies in the other almost identical thread of yours

p.

Lol, i have tried everything that has already been mentioned apart from the sql part. Thats why i posted it. It seems really wierd. I assumed its something wrong in the sql statement.

See i even tried puting the date in directly-

[Date] = '02/02/2005'

exactly as it appears in access. It's printing blank invoices. Can't work this one out. seems really wierd.

Quote:

---

access data base store dates mm/dd/yy pattern, so you may need to format your date to that pattern to make it match

---

I don't have MSAccess on this PC, but I have no reason to disbelieve Pete. Do you need to use "02/02/05" instead of "02/02/2005"?

I worked it out.

It's cause i cant use ' ' i have to use # #. they are date delimiters

as replies in the other thread indicated you need to enclose a date in # date #
characters

Did it? I must of forgot that. I didn't know that sql sees ' ' as text, numbers have nothing and dates have # #.

So i just put in [Date] = #" & multipledate & "#

if you look at the list of threads in this forum you will see that aprox the 7th one down is also by you with the same title as this thread and i and hack answered you some time ago

rgds pete

well my bad. Didn't remember it so didn't look at it.