Solved

SQL Query

Posted on 2001-06-18
6
162 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 52

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 52

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
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…
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…

732 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