Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Select Date Between Two Dates


I have two dates for ex. 03/05/2004 and 03/10/2004 (mm/dd/yyyy). I have another one date for ex. 03/07/2004. This date is come inbetween those two dates. I have to check the condition that the 3rd date is come inbetwwen those two dates or not ?
If yes then I have to print "Your input date is correct". otherwise "Not correct"


 




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
DATE Problems - Change Date Format To European, Get Current Date, Subtract 2 Dates?
Need someone to point me in the right direction/start me off with this please as I am quite clueless on it at present...

I need something that can successfully subtract two dates...


eg. calculate the difference between the current date
02/04/04 (2nd April 04) and 29/03/04 (28th March 04)
...
which will then output a result of 5 days


So what I need to know is...

1.
I need to know how to get the current date from the system clock

2.
I must somehow get my date function converted into European date format (as at present it is in USA format ie. 2nd April 04 is 04/02/04 like April 2nd 04 which is not good!)

3.
and then I must actually SUBTRACT the 2 dates (the current date and a date that is given) from each other to give an integer which I can then use (ie. a value like 5 or 15).


But I have little idea how to go about this


Can anyone please start me off?

DATE Problems - Change Date Format To European, Get Current Date, Subtract 2 Dates?
Need someone to point me in the right direction/start me off with this please as I am quite clueless on it at present...

I need something that can successfully subtract two dates...


eg. calculate the difference between the current date
     02/04/04 (2nd April 04) and 29/03/04 (28th March 04)
     ...
     which will then output a result of 5 days


So what I need to know is...

1.
I need to know how to get the current date from the system clock

2.
I must somehow get my date function converted into European date format (as at present it is in USA format ie. 2nd April 04 is 04/02/04 like April 2nd 04 which is not good!)

3.
and then I must actually SUBTRACT the 2 dates (the current date and a date that is given) from each other to give an integer which I can then use (ie. a value like 5 or 15).


But I have little idea how to go about this


Can anyone please start me off?

Select SQL Between Two Dates
I've tried the code below and it keeps giving me errors. Either operator/operand type mismatch or Missing operand.
Does anyone have a line of code that shows how to count all records between two dates?


Code:
Dim StartDt As Date
StartDt = Format(StartDate, "mm/dd/yyyy")

Set rstDateCount = New ADODB.Recordset
rstDateCount.Open "SELECT count(*) as DateMatch FROM pnotable WHERE date_stamp >= '" & StartDt & "';", Cnn1, adOpenStatic, adLockReadOnly

SQL, Select Between Two Dates.
This code is supposed to take the records out between two dates and then it is supposed to go through the dataset and accumulate balance from the records into a variable called total. After that its supposed to put the total in a label.

It keeps getting stuck on the sql statement though. I dont know whats wrong with it. Here it is if you dont feel like looking at the code. I think looking ta it might help though.

select [ID], [Pay Date], [Amount Paid] from [Payment Record] where ([Pay Date]>= #" & Format(CDate(date1.Value), "mm/dd/yyyy") & "#) AND ([Pay Date]<=#" & Format(CDate(date2.Value), "mm/dd/yyyy") & "#);

If anyone can help me I'd appreciate it greatly.



Code:Public Class payments
    Inherits System.Windows.Forms.Form
    Dim sconn As String
    Dim daV As New OleDb.OleDbDataAdapter
'-----------------------------------------------------
    Private Sub findtotal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles findtotal.Click
        Dim DsL As New DataSet
        Dim ssql As String
        DsX.Clear()
        sconn = "Provider=Microsoft.Jet.OLEDB.4.0;"
        sconn = sconn & "Data Source= " & Application.StartupPath & "Company A.mdb;"
        'Dim conX As New OleDb.OleDbConnection(sconn)
        'Format(CDate(date1.Value), "mm/dd/yyyy")
        'Format(CDate(date2.Value), "mm/dd/yyyy")
        ssql = "select [ID], [Pay Date], [Amount Paid] from [Payment Record] where ([Pay Date]>= #" & Format(CDate(date1.Value), "mm/dd/yyyy") & "#) AND ([Pay Date]<=#" & Format(CDate(date2.Value), "mm/dd/yyyy") & "#);"
        daV = New OleDb.OleDbDataAdapter(ssql, sconn)
        MsgBox(ssql)
        Try
            daV.Fill(DsL, "Payment Record")
        Catch er As Exception
            MsgBox(er.Message & " " & ssql)
        End Try

        Dim i As Int16
        Dim totals As Decimal
        'MsgBox(DsL.Tables(0).Rows.Count - 1)
        For i = 0 To DsL.Tables(0).Rows.Count - 1
            'MsgBox(CDec(DsL.Tables(0).Rows(i).Item("Amount Paid")))
            totals += CDec(DsL.Tables(0).Rows(i).Item("Amount Paid"))
        Next
        total.Text = "$" & Math.Abs(totals)
        'conX.Close()

How To Select Range Of Dates In VB App ?
Hi! I need to add date range selection component to my VB form. My chief said 2 datapickers are not good solution. I have found on web only xe date control activex for this purpose.
Can anyone propose something else ?

thanks in advance ;)

Select Record In Between 2 Dates
May i ask about, i f let say i want to display the records in between two dates, so how am i suppose to write in sql?

have a nice day n GOD Bless u~~~~~

Select Record With Certain Dates?
I am trying to change the recordsource of an ADO to select certain records that are between a certain date to a certain date. Is there a between date function in SQL or do I have to do it programmically. If so what is the code??
Here is my code.

Do
temp = txtStartdate.Text
Do

adoTime.RecordSource = "Select * from Timesheet where EMPLOYEE_INFO_Employee_Key = " & DE1.rsEmployeeDB.Fields("EmployeeKey").Value & _
" AND StartDate = " & temp 'this is where I would like to have the Between Dates statement

adoTime.Refresh
adoTime.Recordset.MoveFirst
Loop While temp &lt;&gt; txtEndDate.Text

DE1.rsEmployeeDB.MoveNext
Loop While DE1.rsEmployeeDB.EOF = false






I have heard that if you want to select dates in SQL syntax that you have to surround the date in # signs. Is this true.
Help Me!!

Select Dates From Calender Control.
Hi, how can I use the MS calender control and select either a date or a range of dates? Thanks

Using Calendar Control To Select 2 Dates
I am trying to use a calendar control to select 2 dates which I have been able to do. The 2 dates are going to be used to search a database table for all records that fall between the 2 dates
What I want it to do is after the second date has been clicked, close (or hide) the form with the calendar control on (dates stored as public variables) and call another procedure.
I have got the 2 dates I need but can't call the procedure and close the form

Any help?

Calendar form code

VB Code:
Private Sub Calendar1_Click()    If flag = False Then        date1 = Calendar1.Value        MsgBox (date1)    End If        If flag = True Then        date2 = Calendar1.Value        flag = False        MsgBox (date2)    End If    flag = True    Search_Invoice    End IfEnd Sub Private Sub Form_Load()    Dim flag As Boolean    flag = FalseEnd Sub


Procedure to be called in module

VB Code:
Public Sub Search_Invoice()    On Error GoTo ErrHandler      Set ObjConn = New ADODB.Connection        ObjConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "FFES.mdb"        'This will only work with a client side cursor!!!        ObjConn.CursorLocation = adUseClient        ObjConn.Open         Set objrec = New ADODB.Recordset         objrec.Open Update_Invoice & " WHERE Create_Date BETWEEN " & Date & " AND " & date2 & ";", ObjConn, adOpenKeyset, adLockOptimistic, adCmdText         If objrec.RecordCount > 0 Then            With frmAll                .Show                .SetFocus                .fraList.Caption = "Search Invoice"                .txtJobDescription.Visible = False                .txtSubTotal.Visible = False            End With            Set frmAll.dgdAll.DataSource = objrec            DatagridFill            With frmAll.dgdAll                .Columns(12).Width = 1200                .Columns(13).Visible = False                .Columns(14).Visible = False            End With            SelectAll        Else            MsgBox "No Records Found!"        End IfErrHandler:     Exit SubEnd Sub

I also need to clear the variables for the next time 2 dates need to be chosen

SQL Select Statement With Dates (British!!)
I am using VB and Access Database. I am using ADO to connect the two.

The select criteria is based on a datepicker control, and because the computer has a british set up, the value of this control is stored as day/month/year.

However, when I use this value in an SQL statement it does not work. I have disovered I can only get the desired effect by first changing it to month/day/year (the american format).

My question is then does the SQL language only work with American dates and VB work with whatever the settings are specified by the operating system.

SQL Select Statement Based On Dates
I have the following line of code:

Code:sql = "Select * from tblNews where ClientID = " & CurrentClientID & " And ((MessageExpiryDate > " & Date & ") OR (MessageExpiryDate IS Null)) order by MessageDate DESC, MessageTime DESC"

That should basically select news items for a particular ClientID if the MessageExpiryDate field is either Null (i.e. No expiry date set so item is always valid) or the value in MessageExpiryDate field is in the future. The MessageExpiryDate field only contains a date, not the time as well as its not relevent.

However, when run, today for example, an record with a MessageExpiryDate of 21/07/03 was returned even though the Date function correctly returned 22/07/03

I have tried enclosing the Date in "#" signs to give this code

Code:sql = "Select * from tblNews where ClientID = " & CurrentClientID & " And ((MessageExpiryDate > #" & Date & "#) OR (MessageExpiryDate IS Null)) order by MessageDate DESC, MessageTime DESC"

But this only returned records with a Null MessageExpiryDate, and not a record with a vlaue of 31/07/03 in the MessgaeExpiryDate field.

Hope this makes sense, and any help is appreciated.

I connect to the database using Microsoft Jet OLEDB 4.0
      

Multi Select Dates In Calendar Control
Hey folks, I need an expert on the ActiveX Calendar Control in Access 2003. I know the basics of the calendar control to populate a date text box. I'm wondering if it's possible to select multiple dates in one calendar control object and have it display your selected dates when you go back into the form.

Let's say a database is built to track customers and the database users need to select multiple dates when the customer is scheduled for service calls. Say a customer is to receive a service call on June 6 and then on June 30, could the Calendar Control be configured so those specific dates appear selected on the control? I've seen web based calendars that seems to manage this (although I have no idea how). Also, i'm guessing the best way to track this would be to have the control on a subform/table that specifically tracks the "service date". I can't think of another way they could be stored.

Finally, is there a way to have the calendar control appear as a small icon next to the text box, then when clicked it would maximize to full view?

Hope I'm making sense. I've searched the forum and the internet for this, but nothing. Thanks in advance.

Problem SELECT In Getting Maximum Dates In SQL Server
I have a table with 3 fields which are lnumber, ltransactionNo, ldate.

These are tables that can have multiple records with the same date but different ltransactionNo. lnumber is a foreign key.

What I want is to get is the maximum date in 3 cutoffs.  which is 2, 12 and 22. This means that records with the latest ldate cutoff of March 2 2003, Feb 22 2003, Feb 12 2003 and NOT with the records containing Feb 2, Jan 12 and Jan 22 2003 will be displayed.

I tried using

"Select max(ldate) as ldate, lnumber, ltransactionNo from myTran
group by ldate, lnumber, ltransactionNo
order by ldate DESC"

It only returns records with maximum ldate on top of the heap, but still the recordset contains the other records with the unwanted dates.

Please help me out.  Thanks

Derick

(Solved)select Record Between 2 Dates In Recordset?
Hi all,
i have a recordset containing date a field.
i tried to show only those between 2 dates in my report

i tried to use for example
Code:rsQsambungan.filter between "Date1='" & txtdate1.Text & "' and "Date2='" & txtdate1.Text & "'
(i´m using msaccess and i know this feature is psible in MSaccess)
but i dont know how to do that in VB
how is the right code?
or how should i do in a different way to get those record?
if i have to use sql statement, how do i code the rsQsambungan containing an SQL statement?
this is how i used to write to connect to my table or query
Code:
With rsSetup
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open "Tabel_setup", conAVB, , , adCmdTable
End With
please help




Edited by - kjoephi on 8/5/2004 5:11:21 AM

Still Having Probs With Select Statement Where Date = Todays Date
Hi Guys

I am using vb6.0 connecting to a SQL database (runningMSDE)

I am trying to load all contacts where the recontactdate is today
I have tried the following but am getting the error - syntax error converting datetime to character string - please help!

my code -
onload - dtpicker1.value = date
(tblcontacts.dtmrecontactdate is date/time)

Set adoduecallbacks = New Recordset

adoduecallbacks.Open "Select * from tblcontacts where dtmrecontactdate = ' " & DTPicker1.Value & "%" & " ' ", db, adOpenStatic, adLockOptimistic

- Please help!!!

Thanks

Compare Dates Multiple Fields Select Earliest
In a table that is named Budget  I have Six different dates in Six Different fields.  Well, the fields can vary whether or not they are null for each record.  Each one of the fields could be null.  Actually, I glanced quickly at the 32,000 records and noticed that one of the records had all 6 fields as being null.  What is the best way to capture the earliest date of the six fields when comparing all of the dates within one record.  Maybe we could create a seventh field to contain the earliest date?

Example: a record in the Budget table contains:

Field 1        01/01/2004
Field 2        04/31/2002
Field 3        02/04/2000
Field 4        null
Field 5        null
Field 6        01/05/1999

I want to capture the earliest date which is in Field 6 (01/05/1999) after comparing all of the dates in the record.  What is the best way to do this?


Need A Random Date Between 2 Dates
I need to generate some random dates that fall between two other dates.

Peet posted some code on another thread but I am not clear on how I would adapt this to my purposes or even if I could


PHP Code:




Private Sub Command1_Click()
    Randomize
    MsgBox Date + Rnd * 365
End Sub








I do have to get the dates from two text boxes txtBeginDate.Text and txtEndDate.Text but my book merely says that I can do this and is not very clear on how. I get type mismath error 13 even though the text in the boxes is 01012003 and 12312003 to start with. But I will keep trying that part.

Thanks in advance for any helps - wish I could load a full version of VB on my machine here at work but I am not even supposed to have this little version loaded (but shhhhhh, don't tell no one). Then again I don't want to create 2000 random dates manually either. So I do not have access to MSDN or the object browser.

Date Listing: Too Many Dates!
I have several actions and the times they were carried out stored on several tables.  I need to list all actions in a report ordered by date, my problem being there are several date fields relating to only their own table.  What I need is to have one list of dates which for each day can call up several actions each from seperate tables.

Randomize A Date BETWEEN 2 Dates
i am working in a random record generator to fill a sql server database and i been having problems in generating dates.
well if someone can help i would be greatfull.

Date - Working The Difference Between 2 Dates
hi

i am trying to work out the difference between two dates to see how many weeks there are between the two dates

for example if i had a date 12/4/2005 and a date 19/4/2005 it should work out that theres a week difference

thanks

angel

Dates And Date Literals Construction
I read in another post that its not posible to create date literals ie: #20/12/02# from variables.

This is a probem for me as my date data comes from variables so I was hoping to make the date from them like so.

MyDate = "#" & MonthVariable & "/" & DayVariable & "/" YearVariable & "#"

Date literals seem to be the only way I can make my code perform the same way if I change regional settings.

Dim BirthDate As Date, CurrentDate As Date
BirthDate = #10/12/2001#
txtDaysAlive.Text = "You have been alive " & Date - BirthDate & " days."

gives 1268 days on either USA or Australian settings. (which is right)

But I cant make a literal from a string ...
I suppose what I am really asking is how can I make the same code work with variables.

If I use

Dim BirthDate As Date, CurrentDate As Date

BirthDate = 10/12/2001 'note no literal # markers
txtDaysAlive.Text = "You have been alive " & Date - BirthDate & " days."

Routine returns 1268 days on the USA setting and 1209 on the Australian regional setting.



Using the code below just gives syntax errors

Dim BirthDate As Date, CurrentDate As Date

Dim MonthVariable
Dim DayVariable
Dim YearVariable

MonthVariable = "10"
DayVariable = "12"
YearVariable = "2001"

BirthDate = "#" & MonthVariable & "/" & DayVariable & "/" YearVariable & "#"

txtDaysAlive.Text = "You have been alive " & Date - BirthDate & " days."


Really scratching my head on this one !

Using Date To Calculate Future Dates Every Month..
hi guys

this is what i need to do:
i have 3 types of "credit cards" the user can choose from.
each one recieve a date when the user entering the data to a access DB.
from this date, i need to calculate when will the user recieve the money from the credit card company. for example:

card "X" - if the user entered the amount from 1st of each month to 15th, then the money will come at the 2nd of next month.

card "X" - if the user entered the amount from 16th to 31st, then the money will come at 15th of next month...

can someone help me with this?...

thanks

Get The Latest Date From All Dates In An Ms Access Table
how to get the latest date in an ms access table and compare it to date entered?

for example, if i have

12/15/2005
12/31/2005
1/15/2005
1/31//2005

and i enter 2/15/2005

how do i get 1/31/2005 to be compared against 2/15/2005 and return the date difference (which of course, i'd be using datediff)

tnx

Error In SQL Query Containing Dates When Invalid Date Is Entered
ok i have an SQL statement that generates an error if a non-existant date (ie. 2/30/2005) is entered in to the field text2


VB Code:
sSQL = "select patdob,insurance,vaccine,Dose,dov,type,datediff('m',patdob,dov),vaccine from vfcrec Where type='" & Combo1.Text & "' and dov between #" & Text1.Text & "# and #" & Text2.Text & "# and vaccine= '" & DBCombo1.Text & "' order by datediff('m',patdob,dov)asc ;"


Is there any sort of statment like notdate or something I can use to tell the system that if the date can not exist on a calander to ignore the value?

thanks tons =)

Setting DatePicker So No Dates Previous To System Date Are Accessible
Hello,
My subject says it all really, im trying to set the datepicker so that users are not allowed to see dates prior to the system date my application is not concerned with previous dates. Any one helplease

How To Search A Range Of Date With A Given Starting And Ending Dates Through Text Box
Hi,

I just would like to ask how to search a range of date with the starting and ending dates specified in a textbox each and display them in a DBGrid or a Flexgrid.

Please help me!

Thank you!

Select Between Date
can friend help to solve this prob,i developed room booking system and want to check whether a range booking date is conflict with another range of booking date.what sql statement or anything i can use .

thanks

SELECT && Date
Hi all,

With this statement......


Code:
sKeuze = "SELECT * FROM orders WHERE datecomplete = 0 "
......I'm trying to select all Orders that have no completion date i.e. where the field "datecomplete" (short date) is empty. However, this doesn't work. The recordset stays empty. I've searched the forum, but haven't found the solution yet.
Does someone have a suggestion?

Thanks in advance,

Wimchester

Help With Date Select
I have a date field like 25/02/2002 'dd/mm/yyyy' ; I would like to extract the month and year so I can build a select statment on them. e.g.

If month is 02 then ...
Select * from table where month = 02
End if

if year is 2002 then
Select * from table where year = 2002
End

The only problem is that my date field in database is one field which is call_date and it is datefield like 'dd/mm/yyyy'.

How can I make the select statment search for only month or only year in that field?

Thank for all of your for your help

Select Date
Is there any ocx control or such that let you select a date like the way you do it in the Outlook calander?

/Anders

Select Oldest Date
I hav a table with a date column

I want to select the oldest date for use with a calendar control

is there a way to return the oldest date value from a table??



ID | someField | DATE | otherFields

0 blah 01/12/2004 ...
2 blah 07/18/2003 ...
3 blah 03/21/2002 ...
7 blah 05/17/2003 ...


I want this value: 03/21/2002

Date And The SELECT Statement
Sub FilterByDate1_click()
Dim Filter as date
Filter=inputbox("Enter Date of Transaction","","")
with Data1
.recordsource="Select * from Table1 where [Date1] = " & Filter
.refresh
end with
end sub
The program is designed to ask the user to enter a date into variable Filter using the Inputbox. The entered date (Filter) is then used in the SQL Select statement to filter Table1. That is, only records with [Date1]= Filter will populate Data1.

When I run the above, a "Too few parameters. 1 expected" error occurs. I believe I have a syntax error (probably something missing). Please take note that Filter is a Date variable and [Date1] is a Date/Time field in the Access Table.

HELP!

Select ID With No Smaller Date
Hello,

I'm trying to run a query against a table that has a one on many relationship with an other table.

The table is a timesheet table, so 1 person can have multiple timelines (each timeline is a new record).

So I have a many of the same personID's against different dates.

I'm trying to run a query that returns only those personID's that have a record with a date larger then 2 years ago, but have non before...

Can someone shine a little on my cause I don't even now where to start here...

Thanks in advance

Filip

Select String Like As Date
Hello,
I have an access database and a row as string.
But I want select this row like a date.

sql="Select * from table where StringDate Between 'first date' and 'second date'"

or

sql="Select * from table where StringDate > 'first date' and StringDate < 'second date'"

I hope i can understand what i need.

Please help me.

Best Regards

Select Statment Where = Date
Hi,

WIN XP - Australian.

I have an access datebase which I am Selecting records where the ISSUE_DATE field = todays date from a SQL 7 database.

The ISSUE_DATE field is formated as DateTime in SQL Length of 8. Looking in SQL the ISSUE_DATE data is formated like 29/01/2007

With the below code it is only the select statement part. I know the rest of the code work because if I change the statement to look for a FIRST_NAME it works. But using DATES nothing is selected, even though there is data there with the current date.

I also get no errors so I have no idea what is wrong. My first thought was SQL must be in American Date Format, but this made no difference when I formated the date as this.


Dim SearchDate As Date
'When set as date it is always reformated to Australian format.

SearchDate = Date

'SearchDate = Format(Date, "mm/dd/yyyy")
'Tried to format date as American


Set MyConn = New ADODB.Connection

MyConn.Provider = "SQLOLEDB"

Conn = "Server=127.0.0.1;Database=Master;User Id=sa;Password="

MyConn.Open Conn
Set MyRecSet = MyConn.Execute("SELECT EMPLID , LAST_NAME , FIRST_NAME , ISSUE_DATE FROM Student WHERE ISSUE_DATE = " & SearchDate)












Thanks for any help.

Select Date Between .. And In Dbase
how to select data in dbase according to date between .. and?

select * from customer where joindate between #1/1/05# and #31/1/05#

this query return an error

How To Select Date Range In VB ???
Hi! I need to add Date Range Selection to our corporate tool. 2 DTPickers are not good enough
I found XE-Date Control on
http://www.xe-soft.com

It is pretty good, but probably someone knows better solution ?

** Resolved ** IIF In SELECT With Date Value
I'm having trouble getting my SELECT to work using Access. I want to inspect a date field in my SELECT statement, and if it is a certain date, I want to get a blank, but if it is not a certain date, I want to display the field date. Here is what I have so far:

   SELECT Firstfield, (IIF([Receive_Header.RReceived] = "1/1/1900", "", [Receive_Header.RReceived]) as SecondField FROM Receive_Header

I seem to be having trouble with the correct syntax, particularly with "1/1/1900". I have tried all sort of formats, such as # 1/1/1900 #, and '" & 1/1/1900 & "' among many others, but no luck!

Any ideas anyone?

Thanks

Murray



Edited by - rock on 6/7/2007 11:34:02 PM

Select Between Two Fields Where Is Date
plz help i useing sql commands with ACCESS DATA BASE i have two FIELDS
FIELD 1 IS NAME ( FROM DATE) FIELD NUMBER 2 IS NAME (TO DATE)
if i have in field[from date] the date ' 1/5/2003' andfield [ to date] the date 30/6/2003
I WANT SELECT BETWEEN THIS TWO FIELDS WHERE THE DATE BETWEEN #10/5/2003#
# 25/5/2003 #

I USE THIS CODE :
" SELECT * FROM [table]"
   "WHERE [date field 1] and [date field 2] Between #" & textbox1.Text & "# AND "
#" & textbox2 & "#"

'********************************************

" SELECT * FROM [table]" _
& "AND [datefiled1]>= #" & textbox1.Text & "# AND" _
   & "[datefiled2]<= #" & textbox2.Text & "#"

'**********************************************

 thank you and good luck for all

Query Dbf, SELECT Date....
Hi everyone

I have problems to filter records by date in a dbf file.

If you can check the attachment code in VB6, I really appreciate it.

Thks

this is the code for conection

Code:
FOXC.ConnectionString = _
 "Driver=Microsoft Visual Foxpro Driver; " & _
 "UID=;SourceType=DBF;SourceDB= " & App.Path & "DBFS"



and this are some test I made, the complete project and one dbf file are attachment to this message.

Code:
Private Sub cmdPruebaFechas_Click()

Screen.MousePointer = 11
 
 'CFECHAALTA TYPE IS DATE
 
 ' I TRYED SEVERAL WAYS FOR THIS STATEMENT BUT IT FAILS¡¡
 Sql = "SELECT * FROM MGW10002 WHERE CFECHAALTA > #01/01/2007#"
'it says that the odbc doesnt admit the requets property
'what can i do please help??

 FOXC.Open
 RS.Open Sql, FOXC, adOpenDynamic, adLockBatchOptimistic
 Set Grid.DataSource = RS
 Grid.ColWidth(0) = 300
 lblCont = Grid.Rows - 1
 RS.Close
 FOXC.Close
   
 Screen.MousePointer = 0
  
End Sub

Private Sub Command1_Click()
 
 'all records
Screen.MousePointer = 11
 
 Sql = "SELECT * FROM MGW10002 ORDER BY CFECHAALTA DESC"
 
 FOXC.Open
 RS.Open Sql, FOXC, adOpenDynamic, adLockBatchOptimistic
 Set Grid.DataSource = RS
 Grid.ColWidth(0) = 300
 lblCont = Grid.Rows - 1
 RS.Close
 FOXC.Close
   
 Screen.MousePointer = 0

End Sub

Private Sub Command2_Click()
 
 'FILTER BY TEXT FIELD
 
 Screen.MousePointer = 11
 
 Sql = "SELECT * FROM MGW10002 WHERE CRFC = 'SN'"
 
 FOXC.Open
 RS.Open Sql, FOXC, adOpenDynamic, adLockBatchOptimistic
 Set Grid.DataSource = RS
 Grid.ColWidth(0) = 300
 lblCont = Grid.Rows - 1
 RS.Close
 FOXC.Close
   
 Screen.MousePointer = 0
 
End Sub




Edited by - Abdel on 3/25/2008 4:37:40 PM

SELECT DATE FOXPRO
Having problem when attempting to use ODBC select statement against a foxpro 2.6 dbf. The problem only occurs with the date field and I get every form of useless error message based on how I attempt to construct the sql against the date field Please help this is driving me nuts and unfortunately I can not update the table to a visual fox format.

VB6 And SQL Server "Select Between Dates"
Hello every one.

I'm selecting records between 25/04/2006 and 29/04/2006. = 4days

The records showing is the dates between the 25th and the 29th of 2005 and 2006
The year I require is 2006.

Any reason why it reads the DAY's and not the month and years.

This is the code:-

& "Where MachBranch = '" & (frmIncome.DataCombo1) & "'" _
& "And (CONVERT(varchar, MachPeriod, 103) " _
& "Between '" & frmIncome.DTPicker1.Value & "'AND '" & frmIncome.DTPicker2.Value & "')" _
& "Order by MachPeriod DESC"

The select in quering the records is (CONVERT(varchar(20), MachPeriod, 103)

Thanks
Pieter

AH! After two hours
Persistence pays off.

After placing this post i went back and changed the code many times as I knew I was there but have not yet arived.

'-------------------------------------------------------------------------------------------------------------------------
To format the display of the DATE, one uses the:- (CONVERT(varchar(20), MachPeriod, 103). OK!
To query records between two dates one uses the:-(CONVERT(DateTime, MachPeriod, 103). GOT IT!

'-------------------------------------------------------------------------------------------------------------------------
& "Where MachBranch = '" & (frmIncome.DataCombo1) & "'" _
& "And (CONVERT(DateTime, MachPeriod, 103) " _
& "Between '" & frmIncome.DTPicker1.Value & "'AND '" & frmIncome.DTPicker2.Value & "')" _
& "Order by MachPeriod DESC"

Thanks
Pieter
 



Edited by - pieterw0 on 4/30/2006 2:53:29 PM

Help Out A Beginner... {select Records By Date}
I am a beginner programmer and have written a few simple programs like calculators and text programs. I am trying to build a program for my dad's business, Vinny's Garage so it will alert him and print out a reminder that he can send to a customer when it is almost time for they're next checkup. I have the database code running correctly, but i still have some trouble with the reminders. Is there anything better than the timer control to check the date? With the calendar or monthview controls, can I access my database and have the dates highlighted that are scheduled to give a reminder? Also, I use VB6, is there any way to get windows xp styles with the programs?Thanks

Please! Help Me To Find Something To Select Date Range In VB!
I need to add possibility to select date range on the form. 2 DTPickers are not too good.

Select Using Date Field - No Matches
Hi. I'm trying to use vb code to select data from a table based on a date field. I'm not sure why its not working. If the Date field in the table is of type date, does it matter what format my date variable is in when I SELECT?

i.e. Date in table is in the format : 05 November, 2002
myDate is 11/05/02

SELECT * FROM myTbl WHERE myTbl.Date = myDate

returns no records.

If necessary, how do I format myDate to match myTbl.Date?

p.s. there's a date for every day of 2002 so I know theres data that should match.

Thanks in advance for your help.

Mike I.

Wreally having trouble

SQL Select Iwth Date Problem
I am using SQL within Excel to drag information out of a database. My code is:


Code:
rstRecordset.Open _
Source:="Select * from availability where thedate BETWEEN (#" & (startDate + 1) & "#) and (#" & endDate & "#) and engName = '" & engName & "' ORDER BY THEDATE", _
ActiveConnection:=cnnConnect, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly, _
Options:=adCmdText

startDate and endDate are both set correctly. Re results are ordered correctly and only include those which apply to engName.
HOWEVER, it seems to be ignoring the date parameters entirely. Instead of only returning the values between the two date variables it's returning them all.

Can anyone see the error of my ways? Please!
*pulls hair out in frustration*

Thanks!

Select Record With Most Recent Date
Hi. I have an Access query that selects the top 4 records per lane (origin and destination) based on the lowest to highest rate (dollar) total. For example, if I have the following records in a table:

ORIGIN DESTINATION RATE CARRIER EFFECT DATE
Atlanta Cleveland $250 ABC 1/11/03
Atlanta Cleveland $300 123 1/11/03
Atlanta Cleveland $350 XYZ 1/11/03
Atlanta Cleveland $400 456 1/11/03
Atlanta Cleveland $500 456 8/30/03
Atlanta Cleveland $600 DDD 1/11/03
Atlanta Boston $315 ABC 1/11/03
Atlanta Boston $385 123 1/11/03
Atlanta Boston $420 456 1/11/03
Atlanta Boston $505 XYZ 1/11/03
Atlanta Boston $625 XYZ 9/15/03

My SQL for is:

Code:
SELECT q.ORIGIN, q.DESTINATION, q.CARRIER, q.RATE, q.[EFFECT DATE]
FROM TL AS q
GROUP BY q.ORIGIN, q.DESTINATION, q.CARRIER, q.RATE
HAVING (((q.RATE) In (Select Top 4 [RATE] From TL Where
[ORIGIN]=[q].[ORIGIN] AND [DESTINATION]=[q].[DESTINATION]Order By [RATE])))
ORDER BY q.[ORIGIN], q.[DESTINATION], q.[RATE], q.[EFFECT DATE] DESC;
In this case the query will return 8 records. The 4 records with rates of $250, $300, $350 and $400 for Atlanta to Cleveland and 4 records with rates of $315, $385, $420 and $505 for Atlanta to Boston.

However, now I'd like the query to return the top 4 rates per lane and carrier based on the later effect date. So, Atlanta to Cleveland would return records with rates of $250, $300, $350 and $500 because carrier 456 has 2 records but the $500 rate has a more recent effect date than the $400 rate. And Atlanta to Boston would return $315, $385, $420 and $625

Will all that said, can anybody help me with this? Thanks in advance

DateTime Picker Select Many Date
How can i put a mark on a DTPicker if i have 2 or more days?? because in a normal DTPicker, i will encircle the date today?? how about to a specified day?? like for example 8/26/2006, 8/27/2006 and 8/28/2006??
kindly help me pls?

Select Week, Display Date... How?
How do I do in such a way where i choose the week and the date within that week is displayed?

Copyright 2005-08 www.BigResource.com, All rights reserved