"Between" SQL function Problem

[kamae]

I need help in using "between" sql function. I don't know how to use it. I am comparing two time using dtpicker. The time should only be acceptable if I input between 6:00am and 6:00pm like 7am, 8am,.........6:00pm.

5am below and 7pm above are not acceptable.

In need help. . .

Regards,
Kamae

[debasisdas]

you need to convert the date and time to proper format before passing the same to database for processing through SQL statement.

[hkdani]

Your Sequel code should look something like this

Dim strSQL as string 
strSQL = _
"SELECT MyDates.* " & _  
"FROM YearlyDates " & _
"WHERE MyDates " & _
"BETWEEN  DateOne AND DateTwo"

You might have to monkey with setting up the date format: e.g. cDate("3/1/2009")

But you also need to establish an exact day for a reference point for your 6:00 AM and 6:00 PM. Which means you need a Month, a Day, and Year to make this work.

Don't know which type of Database you're using, but the above is more or less what you're looking for in a SQL string.

The DatePicker Value property is the Value you're probably looking to use in your program.

Dim DateOne as Date, DateTwo as Date

DateOne = DTPicker1.Value
DateTwo = DTPicker2.Value

That should give you a Date Value you can use.

Hank

[kamae]

Your Sequel code should look something

like this

Dim strSQL as string 
strSQL = _
"SELECT MyDates.* " & _  
"FROM YearlyDates " & _
"WHERE MyDates " & _
"BETWEEN  DateOne AND DateTwo"

You might have to monkey with setting up the date format: e.g. cDate("3/1/2009")

But you also need to establish an exact day for a reference point for your 6:00 AM and 6:00 PM. Which means you need a Month, a Day, and Year to make this work.

Don't know which type of Database you're using, but the above is more or less what you're looking for in a SQL string.

The DatePicker Value property is the Value you're probably looking to use in your program.

Dim DateOne as Date, DateTwo as Date

DateOne = DTPicker1.Value
DateTwo = DTPicker2.Value

That should give you a Date Value you can use.

Hank

Thank you so much, the sql works properly but another problem. How am I going to test it? I have a dtpicker, how am I going to test if the value of the dtpicker is between DateOne and DateTwo ? A message box will appear "you're out of range" if it is out side DateOne and DateTwo .

Let's assume that we already have database.
dtpickerInput - is a dtpicker where you will set the date to test. It is serve as the input date.

DateOne and DateTwo - These are the two dtpickers used to filter dtpickerInput to test if it is between or outside DateOne and DateTwo.

btnTest - This is a test button where the test action occurs.

public sub  btnTest_Clicked()

Dim con As ADODB.Connection
Dim rst  As ADODB.Recordset
Dim strSQL as string 
strSQL = _
"SELECT MyDates.* " & _  
"FROM YearlyDates " & _
"WHERE MyDates " & _
"BETWEEN  DateOne AND DateTwo"

---------what will be next------------?

end sub

[hkdani]

I assume you already have DateOne and DateTwo?

You just use if then logic

if (dtPickerInput >  Dateone) And (dtPickerInput < DateTwo) then
   ' Whatever you want to code, if test passes.
else 
   msgBox "Out of Range", vbInformation, "Range Error"
Endif

The word between should fit the logic above, unless you're required to include the occasion when it's >= or <=. If so, just use >= and <= for your logical comparisons.

[kamae]

I assume you already have DateOne and DateTwo?

You just use if then logic

if (dtPickerInput >  Dateone) And (dtPickerInput < DateTwo) then
   ' Whatever you want to code, if test passes.
else 
   msgBox "Out of Range", vbInformation, "Range Error"
Endif

The word between should fit the logic above, unless you're required to include the occasion when it's >= or <=. If so, just use >= and <= for your logical comparisons.

Nice, it worked. . but what about if I am going to get the value between time like 9:00am to 2:00pm? It < or > will still going to work?

[hkdani]

It < or > will still going to work?

A Date Value is a Year, Month, and Minute, second, millisecond, etc. value. The VB6 compiler can break it down into a number with double Precision:

dblDate as Double
dblDate = cDbl(Now)
Debug.Print dbldate

Try running that and the result should answer your question. Date Values begin at Zero or Saturday, December 30, 1899.

Try this in the immediate Pane:

?format(cdate(0), "long date")

?clng(cDate(0))

[kamae]

What do you mean by "break it down into a number with double Precision?" Do you mean the date will be converted to double and that is the time you use < or > to test it?

hehehe my friend, I am still new to VB and I can't get deep though easly. I hope you understand my friend. If it is OK, can I ask just a small code fragment. Is the code:

blDate as Double
dblDate = cDbl(Now)
Debug.Print dbldate

will be placed under a button? The date you illustrated a while a ago was easy but handling a time is a bit hard. =)

[hkdani]

but what about if I am going to get the value between time like 9:00am to 2:00pm? It < or > will still going to work?

I guess the simple answer is yes it will work.
The point is that a Date when used by the compiler is just a number

Here's what VB6 does in Single and Double conversion of a date value:

?cDbl(now)
39876.9616087963
?Csng(now)
39876.96

If you have no desire to delve into the deep for fear of drowning, just understand that dtpPicker.Value will return a Value that you can use with the < > logical comparators.

handling a time is a bit hard. =)

Understand that a time is actually a date. Or understand that a time can be stored in a Date Type.

So, if you pulled that 9:00 AM value from your DatePicker Control, make sure you're using dtpPicker.Value for your date: that value is a Year, Month, Day, SEcond, etc. value.

You don't have to convert it into a Double or Single number, the compiler does that for you when it does the comparison. All you have to understand is 2 things: (1) using the dtpPicker.Value property for you return value is crucial, and (2) yes it will work.

The brain is like a muscle. The more you use it, the more it grows.

[kamae]

I guess the simple answer is yes it will work.
The point is that a Date when used by the compiler is just a number

Here's what VB6 does in Single and Double conversion of a date value:

?cDbl(now)
39876.9616087963
?Csng(now)
39876.96

If you have no desire to delve into the deep for fear of drowning, just understand that dtpPicker.Value will return a Value that you can use with the < > logical comparators.

Understand that a time is actually a date. Or understand that a time can be stored in a Date Type.

So, if you pulled that 9:00 AM value from your DatePicker Control, make sure you're using dtpPicker.Value for your date: that value is a Year, Month, Day, SEcond, etc. value.

You don't have to convert it into a Double or Single number, the compiler does that for you when it does the comparison. All you have to understand is 2 things: (1) using the dtpPicker.Value property for you return value is crucial, and (2) yes it will work.

The brain is like a muscle. The more you use it, the more it grows.

Wow! Nice explanation . . . I finally get your point. . thank you soooooooooooooooooooo much friend. I will tell the result soon as I apply it. Thank you again.

[kamae]

This is my line condition:

If dtDate(0).value >= rst.Fields("sched_date") And dtTime1(0).value > rst.Fields("start_Time") And dtTime2(0).value < rst.Fields("end_time") Then
MsgBox "The time is accepted."
Else
MsgBox "The time you input is conflict with another time."
exit sub
end if

where:
---------------------------
dtDate = date
dtTime1 = start time
dtTime2 = end time
--------------------------

The condition will simply test if there are conflict on schedules on the database. Example:

Accepted Inputs:
database: 01-10-2009 9:00AM 3:00PM
input: 01-10-2009 4:00PM 6:00PM
01-10-2009 6:00AM 8:00PM
01-10-2009 4:00PM 6:00PM
01-11-2009 6:00AM 8:00PM
.
.
.
.and so on as long as the date is >= the date in the database, esle msgbox "You're out of date."

Please check my code if it is correct or not.