CODEWITHSUNDEEP
sqlms-access
Sergio
Sergio

Reputation: 109

Query in MS Access

I have a problem with my query in MS Access 2000. I need to select by the current date in database. I put either function date() or now and it doesn't work. Here is my query:

SELECT     TOP 100 PERCENT dbo.КАРНЕТ.НомКарнета, dbo.ФИРМА.НаимПредпр
 FROM         dbo.ФИРМА INNER JOIN
                  dbo.КАРНЕТ ON dbo.ФИРМА.КодПредпр = dbo.КАРНЕТ.КодПредпр
  WHERE     (dbo.КАРНЕТ.ДтСдачи = CONVERT(DATE, 'DATE()', 102))
  GROUP BY dbo.КАРНЕТ.НомКарнета, dbo.ФИРМА.НаимПредпр;

Upvotes: 1

Views: 1682

Answers (4)

user7351900
user7351900

Reputation:

You can format dates any number of ways.

Format (#17/04/2004#, "Short Date") Result: '17/04/2004'

Format (#17/04/2004#, "Long Date") Result: 'April 17, 2004'

Format (#17/04/2004#, "yyyy/mm/dd") Result: '2004/04/17'

Sub Createdate()
myDate = Format(Date, "Short Date")
End Sub

?myDate 12/29/2016

Upvotes: 0

Taryn
Taryn

Reputation: 247610

You should be able to use Date() without the single quotes:

WHERE dbo.КАРНЕТ.ДтСдачи = Date()

The Date() function will return the current system date.

If your dbo.КАРНЕТ.ДтСдачи has time included in it, then you might need to format that and you can also format the Date() value the same:

WHERE Format(dbo.КАРНЕТ.ДтСдачи, "yyyy.mm.dd") = Format(Date(), "yyyy.mm.dd")

Note: I just tested the above line in MS Access 2003 and it returned records. The code should be the same for MS Access 2000.

Upvotes: 4

glh
glh

Reputation: 4972

  1. Try using now() or today() or date() without the quotes.

  2. You may have no data for that day.

  3. Also convert your field to a date format without the time, convert(...) or format() before you compare it to the date() function.

Upvotes: 2

Justin
Justin

Reputation: 9724

Try this:

SELECT FORMAT(DATE(),'DD.MM.YYYY')

Result in MS Access:

01.02.2013

Upvotes: 2

Related Questions