bolox
asked on
dtpicker adding year/month
HERE IS THE CODE.............
-------------------------- ---------
Dim myconn
Set myconn2 = New ADODB.Connection
myconn2.Open "Provider=Microsoft.JET.OL EDB.4.0;Pe rsist Security Info=False;Data Source=m:\farebase\farebas e.mdb"
'Set myado2 = myconn2.Execute("SELECT farebase.* From farebase where farebase.from_1 = '" & Text1.Text & "' and to_1 = '" & Text2.Text & "'order by farebase.from_1 desc;")
query = "SELECT * From farebase WHERE ((#" & DTPicker1.Value & "#) >= (farebase.valid_from)) and ((#" & DTPicker2.Value & "#)<= (farebase.valid_to)) order By farebase.gross_ad;"
'query = "SELECT * From farebase WHERE ((farebase.valid_from) <=" & DTPicker1.Value & " and (farebase.valid_to) >=" & DTPicker2.Value & ") order By farebase.gross_ad;"
Set myado2 = myconn2.Execute(query)
MSFlexGrid1.Cols = 12
While Not myado2.EOF
If myado2!max_type = "Days" Or myado2!max_type = "Nights" Then
DTPicker3.Value = DTPicker1.Value + myado2!max_num
End If
If myado2!max_type = "Months" Then
DTPicker3.Value = DTPicker1.Value
DTPicker3.Month = DTPicker1.Month + myado2!max_num
End If
If myado2!max_type = "Years" Then
DTPicker3.Value = DTPicker1.Value
DTPicker3.Year = DTPicker1.Year + myado2!max_num
End IF
If myado2!from_1 = Text1.Text And myado2!to_1 = Text2.Text Then
If DTPicker2.Value < DTPicker3.Value Then
num = num + 1
MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.TextMatrix(num , 0) = IIf(IsNull(UCase(myado2!ai rline)), "", (UCase(myado2!airline)))
MSFlexGrid1.TextMatrix(num , 1) = IIf(IsNull(UCase(myado2!Cl ass)), "", (UCase(myado2!Class)))
MSFlexGrid1.TextMatrix(num , 2) = IIf(IsNull(UCase(myado2!fr om_1)), "", (UCase(myado2!from_1)))
MSFlexGrid1.TextMatrix(num , 3) = IIf(IsNull(UCase(myado2!ro uting_code 1)), "", (UCase(myado2!routing_air1 )))
MSFlexGrid1.TextMatrix(num , 4) = IIf(IsNull(UCase(myado2!to _1)), "", (UCase(myado2!to_1)))
MSFlexGrid1.TextMatrix(num , 5) = IIf(IsNull(UCase(myado2!jo urneytype) ), "", (UCase(myado2!journeytype) ))
MSFlexGrid1.TextMatrix(num , 6) = IIf(IsNull(UCase(myado2!va lid_from)) , "", (UCase(myado2!valid_from)) )
MSFlexGrid1.TextMatrix(num , 7) = IIf(IsNull(UCase(myado2!va lid_to)), "", (UCase(myado2!valid_to)))
MSFlexGrid1.TextMatrix(num , 8) = UCase(myado2!min_num & myado2!min_type) & "/" & UCase(myado2!max_num & myado2!max_type)
MSFlexGrid1.TextMatrix(num , 9) = UCase("£" & myado2!gross_ad)
MSFlexGrid1.TextMatrix(num , 10) = myado2!fareid
MSFlexGrid1.TextMatrix(num , 11) = Max
End If
-------------------------- ---------- --
database=
FROM_DATE
TO_DATE
max_num
max_type
IN MY DATABASE I HAVE A FIELD WHICH CAN CONTAIN :(max_type)
MONTHS
DAYS
YEARS
AND THEN A NUMBER
FOR EXAMPLE, 30,DAYS OR 28,MONTHS ETC
I AM USING THE DTPICKER (EASIER TO ADD) TO FINALIZE MY QUERY.
-------------------------- --------
MY PROBLEM IS THIS
-----------------------
If myado2!max_type = "Days" Or myado2!max_type = "Nights" Then
DTPicker3.Value = DTPicker1.Value + myado2!max_num
End If
If myado2!max_type = "Months" Then
DTPicker3.Value = DTPicker1.Value
DTPicker3.Month = DTPicker1.Month + myado2!max_num
End If
If myado2!max_type = "Years" Then
DTPicker3.Value = DTPicker1.Value
DTPicker3.Year = DTPicker1.Year + myado2!max_num
End If
IF I ADD DAYS IT IS FINE, SO :
If DTPicker2.Value < DTPicker3.Value Then
.....
END IF
WORKS FINE,
MY PROBLEM IS ADDING YEAR AND MONTHS,
IT SHOWS THE DATE FINE IN THE DTPICKER3.VALUE BUT IF I USE THE :
If DTPicker2.Value < DTPicker3.Value Then
IT DOES NOT ALWAYS WORK CORREECTLY, IF I USE A BUTTON (
AS OPPOSED TO THE ADO2 CONTROL, THE DTPICKER 2 AND 3 WORK FINE?
USING THIS.............
If DTPicker2.Value < DTPicker3.Value Then
MsgBox "2 is less than 3"
End If
BUT IN THE QUERY IT IS 50/50 IF IT WORKS? I KNOW IT IS DOWN TO FORMATTING OR SOMETHING LIKE THAT BUT WHERE? HOW? HELP ME PLEASE
BOLOX
--------------------------
Dim myconn
Set myconn2 = New ADODB.Connection
myconn2.Open "Provider=Microsoft.JET.OL
'Set myado2 = myconn2.Execute("SELECT farebase.* From farebase where farebase.from_1 = '" & Text1.Text & "' and to_1 = '" & Text2.Text & "'order by farebase.from_1 desc;")
query = "SELECT * From farebase WHERE ((#" & DTPicker1.Value & "#) >= (farebase.valid_from)) and ((#" & DTPicker2.Value & "#)<= (farebase.valid_to)) order By farebase.gross_ad;"
'query = "SELECT * From farebase WHERE ((farebase.valid_from) <=" & DTPicker1.Value & " and (farebase.valid_to) >=" & DTPicker2.Value & ") order By farebase.gross_ad;"
Set myado2 = myconn2.Execute(query)
MSFlexGrid1.Cols = 12
While Not myado2.EOF
If myado2!max_type = "Days" Or myado2!max_type = "Nights" Then
DTPicker3.Value = DTPicker1.Value + myado2!max_num
End If
If myado2!max_type = "Months" Then
DTPicker3.Value = DTPicker1.Value
DTPicker3.Month = DTPicker1.Month + myado2!max_num
End If
If myado2!max_type = "Years" Then
DTPicker3.Value = DTPicker1.Value
DTPicker3.Year = DTPicker1.Year + myado2!max_num
End IF
If myado2!from_1 = Text1.Text And myado2!to_1 = Text2.Text Then
If DTPicker2.Value < DTPicker3.Value Then
num = num + 1
MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
End If
--------------------------
database=
FROM_DATE
TO_DATE
max_num
max_type
IN MY DATABASE I HAVE A FIELD WHICH CAN CONTAIN :(max_type)
MONTHS
DAYS
YEARS
AND THEN A NUMBER
FOR EXAMPLE, 30,DAYS OR 28,MONTHS ETC
I AM USING THE DTPICKER (EASIER TO ADD) TO FINALIZE MY QUERY.
--------------------------
MY PROBLEM IS THIS
-----------------------
If myado2!max_type = "Days" Or myado2!max_type = "Nights" Then
DTPicker3.Value = DTPicker1.Value + myado2!max_num
End If
If myado2!max_type = "Months" Then
DTPicker3.Value = DTPicker1.Value
DTPicker3.Month = DTPicker1.Month + myado2!max_num
End If
If myado2!max_type = "Years" Then
DTPicker3.Value = DTPicker1.Value
DTPicker3.Year = DTPicker1.Year + myado2!max_num
End If
IF I ADD DAYS IT IS FINE, SO :
If DTPicker2.Value < DTPicker3.Value Then
.....
END IF
WORKS FINE,
MY PROBLEM IS ADDING YEAR AND MONTHS,
IT SHOWS THE DATE FINE IN THE DTPICKER3.VALUE BUT IF I USE THE :
If DTPicker2.Value < DTPicker3.Value Then
IT DOES NOT ALWAYS WORK CORREECTLY, IF I USE A BUTTON (
AS OPPOSED TO THE ADO2 CONTROL, THE DTPICKER 2 AND 3 WORK FINE?
USING THIS.............
If DTPicker2.Value < DTPicker3.Value Then
MsgBox "2 is less than 3"
End If
BUT IN THE QUERY IT IS 50/50 IF IT WORKS? I KNOW IT IS DOWN TO FORMATTING OR SOMETHING LIKE THAT BUT WHERE? HOW? HELP ME PLEASE
BOLOX
ASKER
it still does not work, if i use the button it works using the same dtpicker, but on going throygh my database it fails?
I will explain................... ...
i am developing a flight database.......
they can stay in the country for X days/months/years before returning, if X = 3 days, then
if the departure date (dtpicker1) is 20/10/2002 the MAXIMUM RETURN DATE will be in this case 23/10/2002
so if they chose (dtpicker2) their return as 24/10/2002 it would not shoe the fare, else if they chose 22/10/2002 it would show it....
Somethimes the reluts are NUL even though there should be results,
but if it is NUL and i also click a button saying if MAXRETURN DATE > dtpicker2 it works fine there and only there????
HELP
I will explain...................
i am developing a flight database.......
they can stay in the country for X days/months/years before returning, if X = 3 days, then
if the departure date (dtpicker1) is 20/10/2002 the MAXIMUM RETURN DATE will be in this case 23/10/2002
so if they chose (dtpicker2) their return as 24/10/2002 it would not shoe the fare, else if they chose 22/10/2002 it would show it....
Somethimes the reluts are NUL even though there should be results,
but if it is NUL and i also click a button saying if MAXRETURN DATE > dtpicker2 it works fine there and only there????
HELP
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, it is still displaying the date corectly in the dtpicker3 box, and the button on press,
If DTPicker2.Value < DTPicker3.Value Then
MsgBox "2 is less than 3"
End If
works like it should, how come as i go through the list of fields and enter them into the msflexgrid they do not work??
-------------------------- ---------- ---------- ----
FULL CODE TO FOLLOW IN NEXT COMMENT
FULL CODE TO FOLLOW IN NEXT COMMENT
FULL CODE TO FOLLOW IN NEXT COMMENT
If DTPicker2.Value < DTPicker3.Value Then
MsgBox "2 is less than 3"
End If
works like it should, how come as i go through the list of fields and enter them into the msflexgrid they do not work??
--------------------------
FULL CODE TO FOLLOW IN NEXT COMMENT
FULL CODE TO FOLLOW IN NEXT COMMENT
FULL CODE TO FOLLOW IN NEXT COMMENT
ASKER
MSFlexGrid1.Cols = 12
MSFlexGrid1.Clear
MSFlexGrid1.Rows = 1
MSFlexGrid1.ColWidth(0) = 1700
MSFlexGrid1.ColWidth(1) = 1500
MSFlexGrid1.ColWidth(2) = 500
MSFlexGrid1.ColWidth(3) = 500
MSFlexGrid1.ColWidth(4) = 500
MSFlexGrid1.ColWidth(5) = 900
MSFlexGrid1.ColWidth(6) = 1000
MSFlexGrid1.ColWidth(7) = 1000
MSFlexGrid1.ColWidth(8) = 1650
MSFlexGrid1.ColWidth(9) = 650
MSFlexGrid1.ColWidth(10) = 0
MSFlexGrid1.TextMatrix(0, 0) = "Airline"
MSFlexGrid1.TextMatrix(0, 1) = "Class"
MSFlexGrid1.TextMatrix(0, 2) = "From"
MSFlexGrid1.TextMatrix(0, 3) = "Via"
MSFlexGrid1.TextMatrix(0, 4) = "To"
MSFlexGrid1.TextMatrix(0, 5) = "Type"
MSFlexGrid1.TextMatrix(0, 6) = "Valid From"
MSFlexGrid1.TextMatrix(0, 7) = "Valid To"
MSFlexGrid1.TextMatrix(0, 8) = "Validity MIN/MAX"
MSFlexGrid1.TextMatrix(0, 9) = "Gross"
MSFlexGrid1.TextMatrix(0, 10) = "ID NUMBER"
MSFlexGrid1.TextMatrix(0, 11) = "MAX"
If Text1.Text <> "" And Text2.Text <> "" Then
Dim myconn
Set myconn2 = New ADODB.Connection
myconn2.Open "Provider=Microsoft.JET.OL
'Set myado2 = myconn2.Execute("SELECT farebase.* From farebase where farebase.from_1 = '" & Text1.Text & "' and to_1 = '" & Text2.Text & "'order by farebase.from_1 desc;")
query = "SELECT * From farebase WHERE ((#" & DTPicker1.Value & "#) >= (farebase.valid_from)) and ((#" & DTPicker2.Value & "#)<= (farebase.valid_to)) order By farebase.gross_ad;"
Set myado2 = myconn2.Execute(query)
'myado2.MoveFirst
num = 0 + 0
While Not myado2.EOF
DTPicker3.Value = Date
If myado2!max_type = "Days" Or myado2!max_type = "Nights" Then
DTPicker3.Value = DateAdd("d", myado2!max_num, DTPicker1.Value)
End If
If myado2!max_type = "Months" Then
DTPicker3.Value = DateAdd("m", myado2!max_num, DTPicker1.Value)
End If
If myado2!max_type = "Years" Then
DTPicker3.Value = DateAdd("yyyy", myado2!max_num, DTPicker1.Value)
End If
If myado2!from_1 = Text1.Text And myado2!to_1 = Text2.Text Then
If DTPicker2.Value < DTPicker3.Value Then
Max = DTPicker3.Value
num = num + 1
MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
End If
If myado2!from_2 = Text1.Text And myado2!to_2 = Text2.Text Then
num = num + 1
MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
End If
If myado2!from_3 = Text1.Text And myado2!to_3 = Text2.Text Then
num = num + 1
MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
End If
If myado2!from_4 = Text1.Text And myado2!to_4 = Text2.Text Then
num = num + 1
MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
MSFlexGrid1.TextMatrix(num
End If
End If
myado2.MoveNext
Wend
End If
Label8.Caption = "Total records = " & num
If num = 0 Then
MsgBox ("No Records Found!!")
End If
ASKER
DO NOT WORRY, I HAVE SORTED IT OUT, IT WAS MY INITIAL QUERY, IT SHOULD HAVE BEEN......
((#" & DTPicker1.Value & "#) >= (farebase.valid_from)) and ((#"
& DTPicker1.Value & "#)<= (farebase.valid_to))
AND NOT USING THE DTPICKER2.
THANKYOU AGAIN TIMCOTTEE, YOU HAVE SAVED THE DAY AGAIN.
SEING AS THE DATEADD() FUNCTION WAS SO HANDY, I THOUGHT THE POINTS WOULD BE YOURS.
SEE YOU SOON I HOPE, I HAVE NOW COMPLETED MY PROJEST, AND ALSO RAN OUT OF POINTS SO I WILL PESTER YOU SOON
BYE
BOLOX
((#" & DTPicker1.Value & "#) >= (farebase.valid_from)) and ((#"
& DTPicker1.Value & "#)<= (farebase.valid_to))
AND NOT USING THE DTPICKER2.
THANKYOU AGAIN TIMCOTTEE, YOU HAVE SAVED THE DAY AGAIN.
SEING AS THE DATEADD() FUNCTION WAS SO HANDY, I THOUGHT THE POINTS WOULD BE YOURS.
SEE YOU SOON I HOPE, I HAVE NOW COMPLETED MY PROJEST, AND ALSO RAN OUT OF POINTS SO I WILL PESTER YOU SOON
BYE
BOLOX
No Problem bolox, I was trying to wade through your code so am happy that it is all done now. I will be waiting for the next problem though!
If myado2!max_type = "Days" Or myado2!max_type = "Nights" Then
DTPicker3.Value = DateAdd("d",DTPicker1.Valu
End If
If myado2!max_type = "Months" Then
DTPicker3.Value = DateAdd("m",DTPicker1.Valu
End If
If myado2!max_type = "Years" Then
DTPicker3.Value = DateAdd("y",DTPicker1.Valu
End If