Solved

SQL Query

Posted on 2001-06-18
6
160 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
ID: 6204654
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
ID: 6204676
BackFlash

SQL Query

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

HOpe this helps
Ivanc
0
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 6205050
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

Author Comment

by:backflash
ID: 6214114
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 50

Accepted Solution

by:
Ryan Chong earned 50 total points
ID: 6214142
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
ID: 6218072
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

776 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