Link to home
Start Free TrialLog in
Avatar of bolox
bolox

asked on

dtpicker adding year/month

HERE IS THE CODE.............
-----------------------------------
Dim myconn
Set myconn2 = New ADODB.Connection
myconn2.Open "Provider=Microsoft.JET.OLEDB.4.0;Persist Security Info=False;Data Source=m:\farebase\farebase.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!airline)), "", (UCase(myado2!airline)))
MSFlexGrid1.TextMatrix(num, 1) = IIf(IsNull(UCase(myado2!Class)), "", (UCase(myado2!Class)))
MSFlexGrid1.TextMatrix(num, 2) = IIf(IsNull(UCase(myado2!from_1)), "", (UCase(myado2!from_1)))
MSFlexGrid1.TextMatrix(num, 3) = IIf(IsNull(UCase(myado2!routing_code1)), "", (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!journeytype)), "", (UCase(myado2!journeytype)))
MSFlexGrid1.TextMatrix(num, 6) = IIf(IsNull(UCase(myado2!valid_from)), "", (UCase(myado2!valid_from)))
MSFlexGrid1.TextMatrix(num, 7) = IIf(IsNull(UCase(myado2!valid_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
   
Avatar of TimCottee
TimCottee
Flag of United Kingdom of Great Britain and Northern Ireland image
Use this instead, it should properly add time periods to a date value:

If myado2!max_type = "Days" Or myado2!max_type = "Nights" Then
DTPicker3.Value = DateAdd("d",DTPicker1.Value, myado2!max_num)
End If
If myado2!max_type = "Months" Then
DTPicker3.Value = DateAdd("m",DTPicker1.Value, myado2!max_num)
End If
If myado2!max_type = "Years" Then
DTPicker3.Value = DateAdd("y",DTPicker1.Value, myado2!max_num)
End If
Avatar of bolox
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

ASKER CERTIFIED SOLUTION
Avatar of TimCottee
TimCottee
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bolox
bolox

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


Avatar of bolox
bolox

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.OLEDB.4.0;Persist Security Info=False;Data Source=m:\farebase\farebase.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;"
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, 0) = IIf(IsNull(UCase(myado2!airline)), "", (UCase(myado2!airline)))
MSFlexGrid1.TextMatrix(num, 1) = IIf(IsNull(UCase(myado2!Class)), "", (UCase(myado2!Class)))
MSFlexGrid1.TextMatrix(num, 2) = IIf(IsNull(UCase(myado2!from_1)), "", (UCase(myado2!from_1)))
MSFlexGrid1.TextMatrix(num, 3) = IIf(IsNull(UCase(myado2!routing_code1)), "", (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!journeytype)), "", (UCase(myado2!journeytype)))
MSFlexGrid1.TextMatrix(num, 6) = IIf(IsNull(UCase(myado2!valid_from)), "", (UCase(myado2!valid_from)))
MSFlexGrid1.TextMatrix(num, 7) = IIf(IsNull(UCase(myado2!valid_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


  If myado2!from_2 = Text1.Text And myado2!to_2 = Text2.Text Then
    num = num + 1
    MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.TextMatrix(num, 0) = IIf(IsNull(UCase(myado2!airline)), "", (UCase(myado2!airline)))
MSFlexGrid1.TextMatrix(num, 1) = IIf(IsNull(UCase(myado2!Class)), "", (UCase(myado2!Class)))
MSFlexGrid1.TextMatrix(num, 2) = IIf(IsNull(UCase(myado2!from_2)), "", (UCase(myado2!from_2)))
MSFlexGrid1.TextMatrix(num, 3) = IIf(IsNull(UCase(myado2!routing_code1)), "", (UCase(myado2!routing_air1)))
MSFlexGrid1.TextMatrix(num, 4) = IIf(IsNull(UCase(myado2!to_2)), "", (UCase(myado2!to_2)))
MSFlexGrid1.TextMatrix(num, 5) = IIf(IsNull(UCase(myado2!journeytype)), "", (UCase(myado2!journeytype)))
MSFlexGrid1.TextMatrix(num, 6) = IIf(IsNull(UCase(myado2!valid_from)), "", (UCase(myado2!valid_from)))
MSFlexGrid1.TextMatrix(num, 7) = IIf(IsNull(UCase(myado2!valid_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

   If myado2!from_3 = Text1.Text And myado2!to_3 = Text2.Text Then
    num = num + 1
    MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.TextMatrix(num, 0) = IIf(IsNull(UCase(myado2!airline)), "", (UCase(myado2!airline)))
MSFlexGrid1.TextMatrix(num, 1) = IIf(IsNull(UCase(myado2!Class)), "", (UCase(myado2!Class)))
MSFlexGrid1.TextMatrix(num, 2) = IIf(IsNull(UCase(myado2!from_3)), "", (UCase(myado2!from_3)))
MSFlexGrid1.TextMatrix(num, 3) = IIf(IsNull(UCase(myado2!routing_code1)), "", (UCase(myado2!routing_air1)))
MSFlexGrid1.TextMatrix(num, 4) = IIf(IsNull(UCase(myado2!to_3)), "", (UCase(myado2!to_3)))
MSFlexGrid1.TextMatrix(num, 5) = IIf(IsNull(UCase(myado2!journeytype)), "", (UCase(myado2!journeytype)))
MSFlexGrid1.TextMatrix(num, 6) = IIf(IsNull(UCase(myado2!valid_from)), "", (UCase(myado2!valid_from)))
MSFlexGrid1.TextMatrix(num, 7) = IIf(IsNull(UCase(myado2!valid_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

   If myado2!from_4 = Text1.Text And myado2!to_4 = Text2.Text Then
    num = num + 1
    MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.TextMatrix(num, 0) = IIf(IsNull(UCase(myado2!airline)), "", (UCase(myado2!airline)))
MSFlexGrid1.TextMatrix(num, 1) = IIf(IsNull(UCase(myado2!Class)), "", (UCase(myado2!Class)))
MSFlexGrid1.TextMatrix(num, 2) = IIf(IsNull(UCase(myado2!from_4)), "", (UCase(myado2!from_4)))
MSFlexGrid1.TextMatrix(num, 3) = IIf(IsNull(UCase(myado2!routing_code1)), "", (UCase(myado2!routing_air1)))
MSFlexGrid1.TextMatrix(num, 4) = IIf(IsNull(UCase(myado2!to_4)), "", (UCase(myado2!to_4)))
MSFlexGrid1.TextMatrix(num, 5) = IIf(IsNull(UCase(myado2!journeytype)), "", (UCase(myado2!journeytype)))
MSFlexGrid1.TextMatrix(num, 6) = IIf(IsNull(UCase(myado2!valid_from)), "", (UCase(myado2!valid_from)))
MSFlexGrid1.TextMatrix(num, 7) = IIf(IsNull(UCase(myado2!valid_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

End If
    myado2.MoveNext
  Wend
       End If
Label8.Caption = "Total records = " & num

If num = 0 Then
MsgBox ("No Records Found!!")

End If
Avatar of bolox
bolox

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








Avatar of TimCottee
TimCottee
Flag of United Kingdom of Great Britain and Northern Ireland image
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!