sql statement for current date record

[umesh.sahni.35]

i have record saved in access database. with few fields, like item,rate,qty,total, p_date. i need to generate report of system date record only. suggest me that this sql statement will work or not, select*from table1 where p_date=date$;

[AndreRet]

Nope, it will not work...

Assuming that you are using vb6 (looks almost like php...), try the following -

SELECT * FROM table1 WHERE p_date ='" & date$ & "'"
''All depends on what/where your date$ is coming from!!!

[Nutster]

There are three functions that can give Date/Time information in VB6: Now (which gives date and time), Date (which gives only the date), and Time (which gives only the time of day). Putting the $ on the end forces the result to be a string instead of a Date type. Hopefully your DB server and your local machine are using the same locale settings for the date, you might think you are asking for January 4, but instead get records for April 1. Happy April Fool's day. Which date is 1/4/12? April 12, 2001?

One way around this issue is to format the date into an unambigious form.
strSQL = "SELECT * FROM table1 WHERE p_date = '" & Format$(Now, "MMMM d, yyyy") & "'"
Using this format is unambigously a date. No mistaking what January 4, 2012 means.