Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB 6
| Search | Today's Posts | Mark Forums Read
Pro VB 6 For advanced Visual Basic coders working in version 6 (not .NET). Beginning-level questions will be redirected to other forums, including Beginning VB 6.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 2nd, 2008, 12:19 AM
Registered User
 
Join Date: Jan 2008
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problem in Date Format in query

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

 
Old February 4th, 2008, 06:03 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Dates must be delimited with #, just as strings are delimited with ". (This is unique to Access.)
Code:
    
rs1.Open "SELECT MAX(Billno) " & _
         "FROM   Sale_Master " & _
         "WHERE  bill_date = #"& txtbilldate & "#", ...
         (Incidentally, your quote after ‘txtbilldate’ resumes creating a literal string.
‘, cn, adOpenStatic, adLockPessimistic’ would be considered part of the string, not additional arguments for the .open method, and VB would supply the closing quote—just VB’s way of being helpful...)




Similar Threads
Thread Thread Starter Forum Replies Last Post
Date format problem abhishekkashyap27 C# 2005 1 June 23rd, 2008 02:25 AM
Date Format Problem cyberddindia Classic ASP Basics 2 October 23rd, 2006 01:49 AM
Date Format Problem Scripts82 Access VBA 2 March 13th, 2006 09:36 PM
Date format problem ERC Crystal Reports 1 June 15th, 2005 04:03 AM
How to query Date format in Oracle samlow82 ADO.NET 1 July 18th, 2003 04:34 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.