Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query

Posted on 2001-06-18
6
Medium Priority
?
167 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 53

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 53

Accepted Solution

by:
Ryan Chong earned 200 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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

636 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