views:

34

answers:

2

I have created the database in Microsoft Access

with this query

 SELECT * from booking WHERE BOOK_DATE = DATE() order by book_time*

it returns 3 records which has BOOK_DATE = today date

but...

when i tried to place it in the Visual Basic with Data Control and DBGrid, i enter the query in the RecordSource property, but the data didnt come out when i execute the program

what did i miss about DATE() doesn't it support in vb, shall i replace it with something else?

WHERE BOOK_DATE = DATE()

A: 

VB6 knows about DATE() as I have used it recently. I suspect that the data control can't handle a built in function such as DATE() in the RecordSource. Can you change the RecordSource in the code, such as in the forms Open event or whatever approriate, to put in the date? Something like:

Control.RecordSource="SELECT * from booking WHERE BOOK_DATE = " & _
     "#" &  DATE() & "# order by book_time* 

Note that # are date delimiters similar to how ' or " (quotes or double quotes) are delimiters. Assuming that the data control works the same as I'm used to in Access. Also note though that you sometimes don't need those but you should be aware of them.

Also if any systems are running anything but mm/dd/yy format, or if there's even the slightest possibility this could happen then you should be ware of the following.

SQL statements require that the dates be either completely unambiguous or in mm/dd/yy, or mm/dd/yyyy format. Otherwise Access/Jet will do it's best to interpret the date with unknown results depending on the specific date it is working with. You can't assume that the system you are working on is using those date formats. Thus you should use the logic at the following web page. Return Dates in US #mm/dd/yyyy# format http://www.mvps.org/access/datetime/date0005.htm

Tony Toews
Perhaps you did not see all the comments? :)
Remou
You're correct. I didn't notice the hidden comments.
Tony Toews
CHEMlSTRY
A: 

After I tried several ways i figure out that actually it was my fault. But the new problem occurs

the query SELECT * from booking WHERE BOOK_DATE = date() is actually work in VB

the reason why it wasn't working because there is no records which contains BOOK_DATE = today date But how could this happen because there are records which has BOOK_DATE = today date but VB just couldn't find it.

There is a possibility either the database i've made in MS Access or the VB itself has the error. The same table shows in Access and VB is even different

let me explain. I have created the database in MS Access for a while with some records in it (i'm talking to only specific table 'booking') so these records contain various date range.

but once i work with the VB, i thought that i want to try showing the data which has the 'today date' in the DBGrid, so i go back to my MS Access and edit some of the records, changing their date to either 12 Oct or 15 Oct 2010.

then i switch back to VB, i tried showing the data with the simple query SELECT * from booking. But the records that show in the table were the previous data before i've changed the date(12,15).

This is what i curious that why the data in Access that i've edited and saved but it didn't update in VB DBGrid. It turns out whether how much more i change the information in the Access, the vb wont update the table but keep displaying the old one.

alt text

CHEMlSTRY
it turns out that i've saved the same database in 2 different directory with the same name. what a epic.
CHEMlSTRY
spending whole night to figure out this :( sad
CHEMlSTRY