Solved

dtpicker adding year/month

Posted on 2002-05-22
7
392 Views
Last Modified: 2008-03-03
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
Comment
Question by:bolox
  • 4
  • 3
7 Comments
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
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
 
LVL 1

Author Comment

by:bolox
Comment Utility
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
 
LVL 43

Accepted Solution

by:
TimCottee earned 100 total points
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:bolox
Comment Utility
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
 
LVL 1

Author Comment

by:bolox
Comment Utility



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
 
LVL 1

Author Comment

by:bolox
Comment Utility
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
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now