Solved

dtpicker adding year/month

Posted on 2002-05-22
7
398 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
ID: 7027061
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
ID: 7027138
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
ID: 7027180
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 1

Author Comment

by:bolox
ID: 7027192
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
ID: 7027198



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
ID: 7027277
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
ID: 7027299
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

809 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