Solved

SQL Query

Posted on 2001-06-18
6
158 Views
Last Modified: 2010-08-05
Using VB5 (sp3)
I have a form with a data control (Data1) a dbGrid control (dbGrid1), a Command Button (Command1) and 3 Text Boxes (Text1, Text2, Text3)

I have a Database structure consisting of
Field1 - Book Name     (eg B1, B2, B3 etc)
Field2 - Boot Type     (eg T1, T2, T3 ext)
Field3 - Date          (Any Date you want to enter)

The Data1 databaseName is "Book1.mdb"
The RecordType is "Dynaset"
The RecordSource is "BT" (for Book Type Table)

The dbGrid DataSource is Data1

How do I get dbGrid1 to fill with records where the Book Type Name is "T2" (or whatever booktype is entered into Text1)  and the dates are between whatever Dates are entered into text2 and text3

Thanks


0
Comment
Question by:backflash
6 Comments
 

Expert Comment

by:vinisaac
Comment Utility
Set the RecordSource Property to

"SELECT * FROM BT WHERE BookType='" & text1.text & "' AND Date>=#" & Text2.text & "# AND Date<=#" & text3.text & "#"

and it should work. It's better to validate date in text2.text is less than date in text3.text

0
 
LVL 3

Expert Comment

by:ivanc
Comment Utility
BackFlash

SQL Query

Select * from [TableName] where [BookType] = 'T2' and [Dates] > #" & Text2.text & "# and [Dates]< #" & Text3.Text & "#"

HOpe this helps
Ivanc
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
Hi, blackflash. You can use

Date Between #..# And #..#

in your SQL.

_________________________________

Select * from [TableName] where [BookType] = '" & text1.text & "' and [Dates] Between #" & format$(Text2.text,"mm/dd/yyyy") & "# And #" & format$(Text3.text,"mm/dd/yyyy") & "#"

'Hope this can solve your problem.

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:backflash
Comment Utility
Sorry to keep you people waiting but I'm having ISP problems.

I've had a look at all 3 comments and seem to have a wierd problem

I'm assuming that all your sql statements are correct and require the Date field to be a Date/Time field and not a Text Field.

The Data Record Entry's are as follows
Book  BookType Date
B1    T1       01/06/01
B2    T2       02/06/01
B3    T3       03/06/01
B4    T4       04/06/01
B5    T5       05/06/01

What happens is that when I run the query, It returns no records unless the Text3.text entry is beyond 05/06/01

A search on T2  02/06/01, with Text2.text having a Date of 01/06/01 and Text3.text having a Date of 04/06/01 produces no records, but if I change the Date in Text3.text to anything beyond 05/06/01 it returns the correct record.

Text1.text entries work ok, Its just text3.text enteries that are causing the problems.

Common sense suggests this is silly but that's what is happening, I've recreated the project and database from scratch a number of times but the results are always the same.


As a point of interest I've created a database where the Date Fields are in Text Format and tried your code in desperation but to with no success, so I Coded the sql statement to treat the Dates as text and it worked great

sqlQuery = "SELECT * FROM [BT] WHERE [Date]>='" & Text2.Text & "' and[Date]<='" & Text3.Text & "' "

But I'd still like to get the code working properly with Date fields in the database

Any sugestions or comments????

0
 
LVL 49

Accepted Solution

by:
Ryan Chong earned 50 total points
Comment Utility
Did you refresh your Data Control?

Data1.Refresh

Anyway , what is returned if using my SQL provided ?

Select * from [TableName] where [BookType] = '" & text1.text & "' and [Dates] Between #" & format$(Text2.text,"mm/dd/yyyy")
& "# And #" & format$(Text3.text,"mm/dd/yyyy") & "#"

Thanks.

0
 

Author Comment

by:backflash
Comment Utility
Your Code works lovely ryancys.

As I live in Australia I fell for the trap of converting your date format from "mm/dd/yyyy" to "dd/mm/yyyy" and must have had a typing error in the process. As it turned out I didn't have to convert it, as VB will process it either either format.

I'm still a bit puzzeled because the way the formatting insructions are written in the VB4 manual, it "seemes" to indicate that you don't need the formatting as VB has Local-Ware functions that are supposed to convert the different date formats automatically, never the less it only seems to work with formatting on the machine I'm using.

Thanks for your help vinisaac and ivanc, your code works great when you put in the date formatting that ryancys provided.

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now