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




How To Use DTPicker For Start Date - End Date Queries For Generating Reports.


I am using two DTPicker Controls on my form, one for Start Date and the second one for End Date parameter. On running the project it is giving me errorata type mismatch.I have checked the datatype in my database(MSAccess) as Date. I am using control names in the SQLQuery(ie DTPicker1.Value and DTPicker2.Value) for getting values from the DTPicker Controls.




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Generating A Date Range From Start And End Date
I'm trying to figure out how to automatically generate an array of dates from a specified start and end date in excel. For example, if the user specified two months--January 2007 through May 2007--Excel would build an array such as:

Jan-07
Feb-07
March-07
April-07
May-07

Could anyone point me in the right direction?

Thanks for your help,
Casey

Generating Access Reports Based On Date.....
hello there! is there anyway for me to be able to generate reports in MS Access but manipulate the SQL statement in VBA coz i need to be able to generate reports based on the date keyed in by the user. Thanks!!!

Generating Reports On A Date Range, Anyone Know Anything????[still Not Solved!!]
in my system i must generate reports- in my reports form in vb the user can select 4 diff types of report to generate and they can select a date range for which they want to view these reports and all of this information comes back to a data grid on the screen which they can copy and paste to excel if desired.

Also they can click on a preview report button which causes access to open up and opens the required report(by means of a macro) except this report is based on a query I have created in access and selects all information but does not deal with the date range, i.e it wont compare to the information in the data grid as this will be based on a specified date,

Is there any way you think that I could get the date to work in my paper report in access,,, it sounds a little impossible to me, but maybe someone might be able to help!!

Generating Reports By Calling Queries In Access
hi,
I'm a student.I'm usind VB and Access.My problem is I want to genrate reports based on queries.I have written some queries in access and its workign fine.Now I want to access it from the VB to generate reports.I need group headers in the report.Is it possible to have group headers without Shape command.Pls help me.
Thnks in Advance
Nebu

Generating Reports By Calling Queries In Access
Hi
I'm a student.I'm using vb6.0 and access 2000.I have written queries in acceess.I want to accesss it through VB and want to geneate data reports in vb.For that i want to pass two dates dynamically.pls help me
Thnx
Nebu

Quetion On Correct Code For Start Date To End Date
hi guys i need to noe the correct coding for me select start date to end date of my database i posted on the database forum but theres only 3 ppl online but i need a fast response so hope you guys could help thanx alot


adodc1.RecordSource="select * from training where trim(course_fm_date)="1/4/1999" to trim(course_fm_date)="31/3/2000"


could u guys tell me what went wrong?? and the correct code...
i really need this.... thanx

DtPicker : Can We Show Blank Date And Time In DtPicker
Thanks for great help rpovided by you.


I am displaying order date and time using DtPicker.

I want to show spaces / blank as default value of DtPicker

How shall I do it ?

With regards
Vikas Athavale

Start Date + End Date = Amount Of Days
Hi Guys

I have a simple question that i cant quite figure out, what i have is.

3 text boxes namley "Text2" "Text3" and "Text4" Text2 gets a start date say
01/10/2005 and text3 an end date say 30/10/2005 then in Text4 the amount of days displayed is 30. I just dont know how to get the 30 displayed in Text4.
It should be pritty easy but i have hit a blank, please help.

kind regards

Date And DTPicker
Hi!

I would like to use something where my users can choose between either to give a specific date (Year, month, day) or not give a specific day only (Year and month). Is this possible with the DTPicker and save it in a SQL2000 dB in dateTime field?

Or must I have a Textbox and then check if it's OK and then format it or even save is a string in the database?

Ragards

How Can I Get The Date Using The DTPicker
How can i get the date using the DTPicker control
i am new to VB
thanx in advance.

DTPicker Date Format Changes
I am using a DTPicker - I have set up the format to be dd/MMM/yyyy and it more or less works fine. The user can input the date they wish. The data is stored in the database correctly (Access). When the DTPicker loses focus on the form it maintains the format. Once you change the record and then go back to your original the day and month has reversed.... ie you enter 12/Jan/2004 - leave the record and then come back and it displays as 01/Dec/2004. You check the database and the date is still 12/Jan/2004. It is just the display of the data in the DTPicker when you come back to it... and obviously only for days between 1 and 12. My Access, DTPicker and system date are all set up with the same format so I have no idea why this is happening. Please help if you can!

DTpicker Increasing The Date
Hi,

I need to increase the date of the dtpicker by x days. I know how to set the value. what i want to be able to do is for the days to incremnt and for it to automaticallt adjust the mouth year.

Thanks

How Can I Retrieve Each Date From Dtpicker
hi all,

i want to retrive all the dates from the selected month of the datepicker to the list view. clearly, if i pick any month from the datepicker eg: January it has to display the whole 31 dates of that month in the listview. if i pick february ... this has to display only 28 dates of that month in the list view

any help can be appreciated

saj

DTPicker Changes System Date
can anyone tell how to stop DTPicker from changing the system date on my pc

DTPicker Or Other Date Control
I am currently using the DTPicker control on my forms and I really like it. But anyone know of a way to select more than 1 date, either in a row with the 'Shift - Left Click' or with the 'Control - Left Click' for picking non adjacent days?

Thanks

Dave

DTPicker, Date Problem
Hi All



Using VB as front and SQL 2000 server as back end.

The following query is generating the error, listed after it.



query = "Select max(flght_no) from flgh_sch" & _

" where date_flgh = '" & DTpicker.value & "'"

Set objrs = .Execute(query)



ERROR: The conversion of char data type to smalldatetime resulted in an
out-of-range smalldatetime value.



Seeking for solution



Thanks



RAJIV KUMAR THAKUR

Kullu (Himachal Pradesh)- India

How To Check The Date In Dtpicker?
hi,
i am using dtpicker to store date.
I Need to display a message if the date is >-Jun-08, I used the follwing code:-------
If (dtpicker1.value>="07-Jun-08")then

msg"..........."
else

..........

..........
end if
In the above condition it is identifying only day value (i.e. 07), it is not considering month and year values......
if i enter 07-Jun-07 then also it is displaying the message box...
Please help to solve this.....
thank you,

MADHU

DTPicker (Date Picker)
I was wondering if the format of the Date or time in the DTPicker depends on your computer's settings. For example, on my computer the shortdate iin a DTPicker s formated as dd/mm/yyyy. Is it possible that on some computers it would be mm/dd/yyyy?



Edited by - Marce22 on 5/8/2005 1:51:39 PM

DTPicker Error 6.7.2004 14:15, Want To Use Only Date
Hi all

I use DTPicker
when I upgrade database and write from DTPicker into field/Date/Time
It write Date and Time 6.7.2004 14:15

How can I only write Date

When I write Data into Listview then save into database then it write only Date 6.7.2004 like I am traing to do

When I edit the data I write and upgrate then it Write 6.7.2004 14:15

How can I fix this ????

DTPicker And Funky Date Formatting
Problems with days between 1 and 12.
I'm using a DTPicker so there is no room for user data error.
I'm using an ADO Jet 4 connection to an Access database and the dtpicker is bound to the database that way.
I have the dtpicker format set to short date - "dd/mm/yyyy"
I have the access field set to short date - "dd/mm/yyyy"
I have the regional settings for my computer set to English Canada - short date as "dd/mm/yyyy"
When the user enters the date 12/01/2004 (meaning Jan 12, 2004) that is the way it is stored in the database. When the dtpicker loses focus the date remains as 12/01/2004. When you change records and come back to it the date will read as 01/12/2004 - but funnily enough it is equal to Dec 1, 2004 so technically it is still day month year, but the day and month switch on their own. The other funny thing is that if you go back into Access and look at the record there, the date still shows 12/01/2004 as it should.
Any ideas?

Editing The Date In DTPicker By Ketboard.
I have a form with a DTPicker on it. I want to enter te date into the dtpicker box by the keyboard also. When I enter the the two digit day the cursor will skip automatically to the month field and then to the year field. After the year is entered, cursor would skip to day field or to a textbox. Thanks you all.

Formatting Date On Dtpicker To Dd/mm/yyyy
once again my subject says it all does anyone know how to format the date pickers date to uk format. Ive selected the dpCustom under the format property but that does not allow me to specify my format or does it? any one?

Is There A Control Better Than Dtpicker To Display Date?
I am using vb6 with server as back end.

I now have a form use dtpickers to let user input eff. date and end date. I set end date to "12/31/07" when form load because most user use this date as end date. But if user want to set to some day in 2007, he/she has to click many times to get to year 2007. Is there any other control I can use on Vb that user can select year/month?

Thanks a lot.

Datagrid Date Display At Dtpicker
how to display datagrid date display at dtpicker?
example:when i click at the datagrid row data will display at textbox and dtpicker.

Private Sub DataGrid1_Click()
DataGrid1.SetFocus
DataGrid1.MarqueeStyle = dbgHighlightRow

'DTPpurchase = DataGrid1.Columns(2)
Txtidnumber.Text = DataGrid1.Columns(4)
Txtprice.Text = DataGrid1.Columns(5)
Txtstatus.Text = DataGrid1.Columns(6)
DataGrid1.Refresh
End Sub

Monthview Or Dtpicker Date Mark
i post this thread;
using monthview control, i can put a multiselect by setting True on its MultiSelect property and by by clicking it..

but how can i put a mark online just like the Today? (encircle with red color) in my project(Reservation System), If the day like for example 9/02/2006, 9/20/2006, 9/27/2006 etc., it will encircle the monthview or dtpicker so that the user will know that date is already reserved?
they reply me;
don't believe you can with the standard monthview control.

so all i want to happen now how to put a mark on a date (just ask a mark) indicating that date is already reserved.. just coloring it with red or encircling with black just like the red default circle..

kindly help me pls

DTPicker Date Range Question
ok i have a record that i need search with a date range, here's the record that im working with:

Roe,3:48:09 PM,3:49:09 PM ,Website,Leather,Bring in Spouce,No,,5/25/2006

right now its working when searching for one date, now i need it to search by date range.

im alse doing some other searches in the string.

here's the code:

VB Code:
tmpDate = DTPicker1    Do While Not EOF(5)         Line Input #5, strLine               If InStr(strLine, tmpDate) > 0 And InStr(strLine, nameSelect) > 0 _        And InStr(strLine, "No") > 0 _         Then             Print #6, strLine            Else            End IfLoop

can a date range be done with the inStr function?

im not using excel or access, its all on a flat file
Thanks

Setting The DTPicker Control's Max Date
Help

How do I code a DTPicker to restrict the max date based on the current date? I know how to set the max date in the properties section, thats not the problem. I need the latest date allowed on the DTPicker to be 2 days later than the current date. It needs to change everyday, based on the current date.

I appreciate any help anyone may offer.

DTPIcker Date Format Problem
Hello Guys,,,,

I am using vb6 with SQL

In one of my form I am using DTPicker control.
Here i am setting Custom format MMMM d,yyyy ----> october 15,2003
But when it store in databse, it is in a format of 10/15/2003

rs!date=format(DTdate,"MMMM d,yyyy") I am using this code..It has to work. But ididn't


where is err?????
sangeetha

Doing Queries Based On A Date
Hi, I have looked through most of the tutorials on ADO, but I cant seem to create an ADO recordset based on a query from an access database.

This is all done in VB6.

I have an activex control that uses a datasource to populate a diary.

I can query the database to create a dataset based in the ID number ( SELECT * FROM Table1 WHERE ID = 1 )
But... I am trying to get it so that it only shows a selected day's appointments in the calendar.
I was assuming the code would be:
SELECT * FROM Table1 WHERE Date = 21/10/2002

But it doesnt show any results, even though those dates are listed in the database.

could anyone give me any help on how to get this working?

I am guessing that it is just me messing up the formatting of the query.

Thanks.

Matt


Oh, another thing - I am guessing the format of dates outputted from access 2000 would be dd/mm/yyyy right? (q - are the /'s included?)

Date Queries Access & Vb6
hi.

i am trying to do some [simple] sql queries with the "where" clause filtering certain dates.

i have a calendar, which populates the varDateStart variable. This search is then performed:

sqlStr = "SELECT * FROM tbl_job " & _
         "WHERE JobStartDate>=#" & varDateStart & "#"

I have 2 jobs. One is 01/05/2004, the other 01/09/2004. If I use the above it, should, return only the latter one. It returns both!!
If I change to "<=", it returns nothing!!!!

Access fields are "datetime" and the the vb6 variable is of type "date".

any suggestions? i have tried using apostrophies, no apostrophies, brackets, Format()...

jingo_man

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?

DTPicker And Storing The Date In Access 2000 DB
Ok like many of the posts I have seen I am having the same problem.

A type mismatch is the error when I try to store a date from the date picker to the Access DB.

I tried formatting the DTpicker to yyyy/mm/dd but the month only would show 00 for whatever reason.

Does any one know how EXACTLY to over come this problem.

I am attaching my code, the form is fmChangeEmployeeInfo where the DTpicker is. Please help

My email is winterwendigo@chartertn.net or just post the code as a reply either way. I will appreciate the help greatly

DTPicker, Did Not Select Date In Popup Calendar
When using the DTPicker control is there a way not to change the date unless the user clicks/selects a date from the calendar.

For example if a user selects the DTPicker control and scrolls the popup calendar backwards or forward a month and decides not to select/change a date, by clicking outside of the calendar area, I do not want the control to set its Value property to the date that the user scrolled to.

AlanG

DtPicker Formatting The Date Like January 2003
Hi all,
Here im doing one application in VB.Im using DTPicker control.
I want to format the date in the DTPicker like(Month/Year)
for example: May 2003

I am able to get the format like 03/2003 that is month and year both in digit but what i want is month should be in string and year in digit like(May 2003)

please help me out

Thanx
Mahesh

How To View The Date &amp; Time In A Single Textbox Using DTPicker?
Dear Friends,

How can I get the Date & Time like in the .JPG file when I click on the DTPicker (on clicking any date)?

Thanks and regards,

vblearner6

Problem In Displaying Current Date In Dtpicker Control?????
hi,

iam working with an application petty cash book.iam using

dtpicker as my date control for selection and display of date.

the properties i set up for dtpicker is

with dtpicker1
.format=3
.custom format="dd/mm/yyyy"
end with

in form load event iam wrote like these
dtpAccDate.Value = Format(date, "dd/mm/yyyy")

but the value displayed is 26/00/2003

iam getting 00 in place of month,02..

is there any other property i should set or should i format the date in another way..


could any body please help me with the solution...

regards
varma

Every Record Has A Date Field. I Want To Find A Date And From This Date, I Want To Read Records To E
Every record has a date field. I want to find a date and from this date, I want to read records to eof. What is the code? - Thanks

DTPicker And MySQL Date Field - Insert/Update A Record?
Hi,

Im trying to insert a DTPicker value into a MySQL Date field. In the past I've chopped up the DTPicker value and reordered it as a variant and shoved it into the DB that way, but now I simply have too many dates to do this with and to be honest it feels like such a cowboy way of doing things I need to get to the bottom of this once and for all.

I've messed about with the dtpCustom mask, but this is for input, but I need to manipulate the output into YYYY-mm-dd as this is how MySQL likes it.

I've dabbled with .CustomFormat = "yyy-MM-dd" but this chucks up errors and I cant get around them.

Here's my code:


Code:
dbconn.Execute ("INSERT INTO scaffold(up_date) VALUES ('" & dtpScaffUp.CustomFormat = "yyy-MM-dd" & "')")
I've also tried this code (originally):


Code:
dbconn.Execute ("INSERT INTO scaffold(up_date) VALUES ('" & dtpScaffUp.value &"')")
If anyone can help I'd really appreciate it - surely its time these sort of things were a little more accessible or logically designed so I dont have to pull my hair out just to get what should have a been a 5 minute job done, but instead has taken me all night and I still dont have the answer, aargh!

Cheers fellas

DATE Problem - Formatting US Date MM/DD/YY To European Date Format DD/MM/YY
I have a problem whereby the user must enter a date into a field...

The system changes the date format to US standard which is MM/DD/YY
but I need to change the format to DD/MM/YY (European standard)


I have done this using the Format function, but I still have a slight problem
Format(mydate,"dd/mm/yy")

doesnt account for all dates...

for example...
if I enter 31/03/04 this is fine, it will accept it as that

However, if I enter 02/04/04 (todays date - 2nd April 04), it will change it to 04/02/04



Any ideas how I can counter this from happening?

Query With Date Ranges And Return Only Closest Date To 'to Date'
I am using an access db...

I am trying to pull back due dates for credit reports for clients. I have 4 tables I am working with for this query...

    [Client_info]
    [Dispute_track_eq]
    [Dispute_track_exp]
    [Dispute_track_trans]

[Client_info] has the basic client information....ID, SSN, name, telephone_numbers, yadd yadda..
    each of the other tables hold the fields I am pulling from(other fields as well but these are the ones i need)...

    [Dispute_track_eq].eq_due_date
    [Dispute_track_exp].exp_due_date
    [Dispute_track_trans].trans_due_date

Here is my problem...
    I have variable that are set by dtpickers...'from_date' and 'to_date'. I am trying to get back each of these due dates that fall between the ranges set along with the [client_info] data linked by the SSN field. The problem is that sometimes these clients will have multiple due_dates(multiple rows) between the range. What the end result needs to be is this, I need to only have one record to come back with one of each due_date from each table, but only give me back the closest due_date to the 'to_date'(set by dtpicker) as well as the data from the [Client_info] table. And if there are no records for one or two but not the thirs field, an empty fields is ok(using a datagrid). But I can only pull back one row with the [client_info] fields, and one of each of the 3 due date fields. I have a query already but it doesnt even come close to working, it gives me many duplicates and doesnt give me the closest due_date to the 'to_date'.

Here is my query, but it doesnt work. Any help would be greatly appreciated, I am in dire need of help on this one.

Code:Private Sub Command4_Click() '***Due dates report grid date
    to_date = dt2.Value
    from_date = dt1.Value
    If from_date = vbNull Then
            MsgBox "Please select a FROM DATE"
            Exit Sub
        ElseIf to_date = vbNull Then
            MsgBox "Please select a TO DATE"
            Exit Sub
    Else
    Dim ID_new As String
    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL As String
    Dim ID_pass As String
    Dim active As String
    
    active = "Active-Negotiations"
   
    Set rs = New ADODB.Recordset
    
    
        strSQL = "Select" & vbNewLine
        strSQL = strSQL & " [client_info].[FirstName], [client_info].[LastName], [client_info].[HomePhone], [client_info].[MobilePhone], [client_info].[Client_status], [client_info].[Category], [client_info].[Appt_notes],[client_info].[Appt_date],[client_info].[Appt_date_time], " & vbNewLine
        strSQL = strSQL & " [dispute_track_eq].[eq_due_date], [dispute_track_exp].[exp_due_date], [dispute_track_trans].[trans_due_date]" & vbNewLine
        strSQL = strSQL & " FROM (dispute_track_exp INNER JOIN (dispute_track_eq INNER JOIN client_info ON [dispute_track_eq].[SSN] =[client_info].[SSN]) ON [dispute_track_exp].[SSN] =[client_info].[SSN]) INNER JOIN" & vbNewLine
        strSQL = strSQL & " dispute_track_trans ON [client_info].[SSN] =[dispute_track_trans].[SSN]" & vbNewLine
        strSQL = strSQL & " Where (dispute_track_trans.trans_due_date > #" & from_date & "# And" & vbNewLine
        strSQL = strSQL & " dispute_track_trans.trans_due_date < #" & to_date & "#)" & vbNewLine
        strSQL = strSQL & " OR (dispute_track_eq.eq_due_date > #" & from_date & "# And" & vbNewLine
        strSQL = strSQL & " dispute_track_eq.eq_due_date < #" & to_date & "#)" & vbNewLine
        strSQL = strSQL & " OR (dispute_track_exp.exp_due_date > #" & from_date & "# And" & vbNewLine
        strSQL = strSQL & " dispute_track_exp.exp_due_date < #" & to_date & "#) AND NOT NULL"


    Set cn = New ADODB.Connection
    With cn
           .Provider = "Microsoft.Jet.OLEDB.4.0"
           .ConnectionString = "Data Source= " & db & " "
           .open
    End With
    
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .Source = strSQL
        .open
    End With
        
        If rs.RecordCount = 0 Then
            MsgBox "Sorry, no one matched that status, try again", vbOKOnly, "Credit Matrix"
            Set DataGrid1.DataSource = rs
        Else
            Set DataGrid1.DataSource = rs
    
        End If
        End If

    I would be forever in your debt for helping me as I am almost out of hair....FROM PULLING IT OUT!!
    



Edited by - ronmegga on 4/1/2004 5:26:03 PM

DATE Problem - Formatting US Date MM/DD/YY To European Date Format DD/MM/YY
I have a problem whereby the user must enter a date into a field...

The system changes the date format to US standard which is MM/DD/YY
but I need to change the format to DD/MM/YY (European standard)


I have done this using the Format function, but I still have a slight problem
Format(mydate,"dd/mm/yy")

doesnt account for all dates...

for example...
if I enter 31/03/04 this is fine, it will accept it as that

However, if I enter 02/04/04 (todays date - 2nd April 04), it will change it to 04/02/04



Any ideas how I can counter this from happening?

Calendar Start Date.....
I am unsure about how to modify the calendar selection so that when the user opens up the calendar it opens on the current date. Can somebody please identify where in the code I need to make this change? Many thanks:

'VB script to be used for modifying the date within the DI-Diver script
'Created by Darren Carr
'25/09/03

Option Explicit

Private Const SEARCH_STRING As String = """Advert_Insert_Date BETWEEN """
Private Const DATE_FORMAT As String = "YYYY-MM-DD"
'Input file property
Private Property Get TemplateFilename() As String
Dim strPath As String
strPath = App.Path
If Not (Right$(strPath, 1) = "") Then
strPath = strPath & ""
End If
TemplateFilename = strPath & "liverpool.int"
End Property
'Output file property
Private Property Get NewFilename() As String
Dim strPath As String
strPath = App.Path
If Not (Right$(strPath, 1) = "") Then
strPath = strPath & ""
End If
NewFilename = strPath & "liverpool1.int"
End Property

Private Sub cmdLoadile_Click()
Dim intFile As Integer
Dim strData As String
Dim strFile As String
intFile = FreeFile
Open TemplateFilename For Input As #intFile
Do While Not EOF(intFile)
'Input #intFile, strData
Line Input #intFile, strData
strFile = strFile & strData & vbCrLf
Loop
Close #intFile
strFile = Replace$(strFile, "<<DATE_FROM>>", Format$(dtpFrom.Value, DATE_FORMAT))
strFile = Replace$(strFile, "<<DATE_TO>>", Format$(dtpTo.Value, DATE_FORMAT))
If Not (Dir(NewFilename) = vbNullString) Then
Kill NewFilename
End If
intFile = FreeFile
Open NewFilename For Output As #intFile
Print #intFile, strFile
Close #intFile
txtNewSearch.Text = strFile
MsgBox "New int (Integrator Extract) file saved at: " & NewFilename
End Sub


Private Sub Command1_Click()
frmDateRange.Visible = False
End Sub

Grouping By Date Where Date Field Is Date/time
I have an SQL7 database storing datetime data, and totaliser reading, new records are added every 5 minutes.

I have written the following sql to give me the the min and max totaliser reading for each day - which works fine - except that the resultant Date field is a text field not a date field so the order statement generates an incorrect listing if the data selected sarts in one month and ends in the next, eg.

select left(Date, 12) as Date, Min(Reading) as Day_Start, Max(Reading) as Day_End from Steam where tag_no = 2 and (Date <= '24-November-2005 00:00:00' and Date >= '22-October-2005 00:00:00') Group By left(Date, 12) order by Date asc

Is there another function I can use to extract just the date from the date/time field as a date data type?

I cannot alter the database as its structure is predetermined by the remote field device supplying the data.

Count No. Of Days With Start And End Date In DD/MM/YYYY Format
<html><div style='background-color:'><DIV>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">Dear all,</P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">Thank you very much especially to<SPAN style="mso-spacerun: yes">  </SPAN>Vikrams16, Michael Dísa,<SPAN style="FONT-SIZE: 8.5pt; COLOR: black; FONT-FAMILY: Tahoma"> </SPAN><SPAN style="COLOR: black; mso-bidi-font-size: 8.5pt">Pswalia</SPAN> , Tata and <SPAN style="mso-spacerun: yes"> </SPAN>Chris McDonald<SPAN style="mso-spacerun: yes">  </SPAN><SPAN style="mso-spacerun: yes"> </SPAN>for your prompt assistance.<SPAN style="mso-spacerun: yes">  </SPAN>The problem on excluding Sunday from working day is solved now.<SPAN style="mso-spacerun: yes">  </SPAN>But there is another<SPAN style="mso-spacerun: yes">  </SPAN>problem related to number of days counting for a long period.<SPAN style="mso-spacerun: yes">  </SPAN>For instance, with the start date<SPAN style="mso-spacerun: yes">  </SPAN>1 April 2003 and end date 30 April 2003, the number of days appears as 99 days.<SPAN style="mso-spacerun: yes">  </SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt"> <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">I suspect it is caused by my date format dd/mm/yyyy.<SPAN style="mso-spacerun: yes">  </SPAN>However, I have already converted it in my code.<SPAN style="mso-spacerun: yes">   </SPAN>For your info, the number of days calculation works perfectly if I use the date format in mm/dd/yyyy.<SPAN style="mso-spacerun: yes">  </SPAN>Could someone there enlighten me by reviewing the following codes.<SPAN style="mso-spacerun: yes">  </SPAN>Many thanks in advance.<SPAN style="mso-spacerun: yes">  </SPAN></P>
<P class=MsoNormal style="MARGIN: 0in 0in 0pt">

Problem With Setting Start Date For A Task Scheduled.
I am using :Win32_ScheduledJob Class for scheduling a certain task using Visual Basic .I am able to schedule the task with the parameters specified in the class.But I am not able to set the start date for the task created.How to set the start date and end date of a task in Visual Basic program?

Problem With Setting Start Date For A Task Programatically-URGENT
I am using :Win32_ScheduledJob Class for scheduling a certain task using Visual Basic .I am able to schedule the task with the parameters specified in the class.But I am not able to set the start date for the task.How to set the start date for a task in Visual Basic program, while creating the task? Is there any other way to create a task in visual basic other than Win32_ScheduledJob Class where we can easily set start date?

Return The Date Before And The Date After The Date Entered
I have a table of Required Dates that contains: TransNo; OrderNumber; RequiredDate.
What I want to do is returned the Record with the Date before and the date on/after the date I specify for each Order Number.
i.e.
TNo; OrdNo; Date;
001; 19002; 01/03/2004
002; 19002; 20/03/2004
003; 19002; 29/03/2004
004; 19002; 01/04/2004
005; 19004; 11/03/2004
006; 19004; 20/03/2004
007; 19004; 29/03/2004
008; 19012; 05/03/2004
009; 19012; 11/04/2004
010; 19012; 19/04/2004
011; 19012; 21/04/2004

Given the above data. If I enter 19/03/2004 I want to return:
001; 19002; 01/03/2004 ' Date before 19/03
002; 19002; 20/03/2004 ' Date After the 19/03
005; 19004; 11/03/2004
006; 19004; 20/03/2004
008; 19012; 05/03/2004
009; 19012; 11/04/2004

I want the SQL statement that will do this.

Sort By DATE Using Crystal Reports
OS = Windows XP SP2; Visual Basic 6 - Enterprise Edition; Crystal Reports XI - Developers Edition

I am using VB 6 to create a front-end program that creates some data-sets and calls Crystal Reports to print the contents of the data sets. One of the datasets is called "InventoryHistory".
TableName: InventoryHistory
fields:
dbPartNumber
dbLocation
dbQtyOnHand
dbEnterDate
dbEnterTime
etc.

In the VB application, I am using the sql "Insert" statement to populate the InventoryHistory table.
Insert into InventoryHistory(dbPartNumber, dbLocation, dbQtyOnHand, dbEnterDate , dbEnterTime, etc...) values (....SQL & "'" & Format(Date, "MM/DD/yyyy") & "', "'" & Format(Time, "hhmmss") & "', " ...);

My problem is, The crystal report detail section must be sorted according to TIME within DATE. The TIME does not show on the report.

01/04/2008 (160825) Part#45 Location-Bin 10
01/04/2008 (125845) Part#82 Location-Bin 6
01/02/2008 (013022) Part#5 Location-Bin 123
12/31/2007 (182233) Part#34 Location-Bin 13
12/31/2007 (144532) Part#14 Location-Bin 23
12/31/2007 (051054) Part#8 Location-Bin 32
12/20/2007 (152933) Part#100 Location-Bin 1

In the example above, the time is shown within the "(...)" (160825) = 4PM, 8 minutes, 25 seconds; (125845) = 12 noon, 58 minutes, 45 seconds, etc.

How can I sort these so that the records appear in the correct order?

I tried "Record Sort Expert" on dbEnterDate (Desc), dbEnterTime (Desc). It didn't work. The 2007 dates printed first before the 2008.

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