• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

SQL Query

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


1 Solution
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


SQL Query

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

HOpe this helps
Ryan ChongCommented:
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.

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

backflashAuthor Commented:
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????

Ryan ChongCommented:
Did you refresh your Data Control?


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") & "#"


backflashAuthor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now