473,854 Members | 1,862 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

filtering on a date field in Access using VB6

9 New Member
Hi all,

I am having trouble with VB6 (running in windows7) talking to an Access database, and was hoping someone
could help me locate the error.

the database contains a "DateIn" field of type "Date" (short Date)
I am reading the table into a recordset called "rsDiv"

I am trying to read only records for the financial year, between [1 Jul (name the year)] to [30 Jun] the next year.

my code is as follows.....

Expand|Select|Wrap|Line Numbers
  1.    FY = InputBox("Financial Year starting:-  Jul, ", "Enter Financial Year")
  2.    st = "DateIn > '30/06/" & CStr(FY) & "' and DateIn < '1/07/" & CStr(FY + 1) & "'"
  3.    rsDiv.Filter = st
  4.  
I have tried using CDate(construct ed date), CStr(DateIn), even #constructed date#.
so far, the filter allows all records through.

any sugggestions, apart from "give it away" will be appreciated.

Many thanks
Phil
Aug 13 '12
10 12690
Phil Frankel
9 New Member
Thank you both. The BETWEEN syntax works fine as part of a SQL statement, but in VB6 (using DAO), it doesn't seem to be accepted as part of a Filter.
Anyway, I have resolved it to my satisfaction now, and once again, thanks for your input and thought provoking ideas.
cheers
Phil
Aug 15 '12 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

3
23825
by: usenet | last post by:
I have inherited a table where date information was saved from PHP as a VARCHAR. (Sigh.) This means that the so-called date fields look like this : August 1, 2005, 9:09 am EDT October 13, 2004, 12:28 pm EDT This makes them essentially useless for sorting and logical evaluation. Understandbly, the MySQL date functions don't seem able to convert the field to a "real" date field. This yields a blank (or maybe null) "realdate" :
3
4889
by: Fran Zablocki | last post by:
I have a process that exports an Access table to a comma-delimited text file. One of the fields that is exported shows the date it was exported, using the Date() function. In the Access table, the date appears in the records in mm/dd/yyyy format, but after exporting, the date appears as mm/dd/yyyy 0:00:00. For example, today is 11/06/2003. It shows 11/06/03 in the Access table, but the exported text file shows 11/6/03 0:00:00. I thought...
2
2324
by: Norbert Lieckfeldt | last post by:
I am setting up a database for a friend who's an Optician, using MS Access 2002. All seems to be working well, but I have hit a snag. There's a calculated field both in a form and a query which calculates the date for the next appointment from a date field and the number of months to the next appointment. That works fine. For reminder letters, I need to have a query which allows me to select a subsection of dates between dd/mm/yyyy...
4
5296
by: S. van Beek | last post by:
Dear reader, By a Date field with Now() as default value the content of the field is date plus time. As I need a filter in a query on date only (excluding time) I invented the following solution:
1
1846
by: Graham Feeley | last post by:
I have a date field named rcdate it is a general date eg: 12/08/2006 3:30:00 PM 12/08/2006 3:00:00 PM I used to update another empty field named rdate with the with using in a update query " Left(Ratings!RCDate,10)" This now does not work for some reason gives me the error "undefined function in left function"
2
21509
by: Igor | last post by:
Can someone tell me sql query for filtering date field for current day, not last 24hours but from 00:00 to current time?
8
6422
by: Ragbrai | last post by:
Howdy All, I have a query that is used for filtering results to be used in a combo box. The query needs to test fields from both a table and then unbound text boxes on the form that also contains the combo box. The fields I am working with are date fields that are formated as Short Date. I have written an IIF statement and placed it within the Criteria section of the Date field that I am trying to filter by. The current IIF statement works...
7
6753
by: Katherine | last post by:
I'm trying to filter the records on the mainform (MailingList) of my database using a field contained in a subform (Donations). I was basing my code off Allen Browne's Access Tips page (here: http://allenbrowne.com/ser-28.html) since it has worked so well in the past. However, now I'm trying to filter by date instead of by a string or a number, and I'm getting nowhere. My goal is to have users type a date into the unbound combo box and...
7
10206
by: sheri | last post by:
I have a field called date, the date is in the field like this 70925 (which means Sept. 25, 2007). I have another field called day, it is a text field. How do I write a query to populate the day field from the date field? Thanks for your help.
1
1254
by: ndeeley | last post by:
Okay... I've created a drop down list fed by a table. I want my users to select an item in the list, say `Awaiting Repairs`, then fill in a single date text field. When a command button is pressed Access takes this date and fills in one of six disabled text boxes below (ie the one relating to the item in the list). I can do this for one item in the list, but I can't get Access to recognise the change of item in the list box - say to...
0
9902
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10691
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10764
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10371
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7920
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7083
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5753
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5945
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3192
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.