Problem in Date Format in SQL query

[imagetvr]

OS = Windows XP
VB6 Enterprise Edition


I am using ADODB connection to conntect ACCESS 2000 Database

DATABASE Table field name Bill_Date data format date/Time

table filed contains values 20/12/2007 , 30/07/2007,..... ie (dd/mm/yyyy) format
billno in number format in table

In form i have a textbox name txtbilldate(to enter bill date for getting records)
table name sale_master

i wish apply query to find max(billno) on particular date

select max(billno) from Sale_master where bill_date = txtbilldate

Now my query is following but i could not get results please correct my code

Call OpenDB
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT max(Billno) from Sale_Master where bill_date= "& txtbilldate &", cn, adOpenStatic, adLockPessimistic
If rs1.RecordCount <= 0 Then
txtbno.Text = 1
Elseif rs1.(0).value < 50
txtbno.Text = (rs1.Fields(0).Value + 1)
End If

[Doogle]

Dates should be enclosed in "#" eg

rs1.Open "SELECT max(Billno) from Sale_Master where bill_date= #" & txtbilldate &"#", cn, adOpenStatic, adLockPessimistic

[si_the_geek]

Thread moved to Database Development forum

For Access, yes the # character is the delimiter.

However, at least as important is the date format you use in the SQL statement, as getting it wrong will make the data worthless (but not give you any errors to warn you about it!).

No matter what format you think the database is storing, you are wrong - as a Date value doesn't actually have a format (only a display interface does, and that is what you set the format for).


For the correct way to specify a date value for each database system (including the format to use), see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)