Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

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
   
0
bolox
Asked:
bolox
  • 4
  • 3
1 Solution
 
TimCotteeHead of Software ServicesCommented:
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
0
 
boloxAuthor Commented:
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

0
 
TimCotteeHead of Software ServicesCommented:
Looks like I typed that too quickly the actual syntax for dateadd is the other way around:

DTPicker3.Value = DateAdd("d", myado2!max_num, DTPicker1.Value)

DTPicker3.Value = DateAdd("m", myado2!max_num, DTPicker1.Value)

DTPicker3.Value = DateAdd("yyyy", myado2!max_num, DTPicker1.Value)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
boloxAuthor Commented:
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


0
 
boloxAuthor Commented:



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
0
 
boloxAuthor Commented:
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








0
 
TimCotteeHead of Software ServicesCommented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now