Problem with query dates

Hi

My records date range in the database is  Betwene 20/05/20010 and  
22/05/20010 .

When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the 
records less than this date, OK. But if I change the month, this date to 
24/04/2010 00:00:00 shows the same records. That is even if I change the 
month the records that appear are always less than the 24 days without 
following month. There lies my problem.

Note: My date style is dd-mm-yyyy and time is hh: mm: ss

I really need to fix this.Seabra

 

Dim Q1, SQL As String
Dim DateTime1 As Date
Dim ConnString As String = "Provider=SQLOLEDB;Data 
Source=HENRIQUE-PC;Initial Catalog=Winergest;Integrated Security=SSPI"
Dim dateString = DateTimePicker1.Text & " " & DateTimePicker3.Text 
Dim dateObject As DateTime = DateTime.ParseExact(dateString, "dd-MM-yyyy 
HH:mm:ss", New Globalization.CultureInfo("pt-PT")) 

DateTime1 = dateObject
Q1 =" SELECT contadores.Data_Hora as Data_Hora, "

Q1 = Q1 +"ID_Contador as Contador,Nome as Nome, E_SVazio as H_SVazio, 
E_Vazio as H_Vazio, E_Cheias as H_Cheias, E_Ponta as H_Ponta, "

Q1 = Q1 +"(E_SVazio+ E_Vazio+ E_Cheias+ E_Ponta) as Total "

Q1 = Q1 +", Convert(varchar, contadores.Data_Hora, 105) as horas"

Q1 = Q1 & " FROM Contadores JOIN DadosContadores ON Contadores.ID_Contador = 
DadosContadores.ID "

Q1 = Q1 & " WHERE Convert(varchar(10), contadores.Data_Hora, 104) < '" & 
dateObject & "'"

Q1 = Q1 & " AND E_SVazio<>0 AND E_Vazio<>0 AND E_Cheias<>0 AND E_Ponta<>0 "

Q1 = Q1 & "ORDER BY "

Q1 = Q1 & "contadores.Data_Hora Asc "

TextBox1.Text = Q1

 Using Con As New OleDb.OleDbConnection(ConnString) 
Con.Open()

 Dim Tabela As New DataTable() 
SQL = Q1

Dim Da As New OleDb.OleDbDataAdapter(SQL, Con) 
Dim ocb As New OleDbCommandBuilder(Da) 
Da.Fill(Tabela)
DataGridView1.DataSource = Tabela
End Using


0
Utf
6/11/2010 5:09:49 PM
dotnet.languages.vb 830 articles. 0 followers. Follow

3 Replies
989 Views

Similar Articles

[PageSpeed] 28

Am 11.06.2010 19:09, schrieb Henrique:
> Hi
> 
> My records date range in the database is  Betwene 20/05/20010 and  
> 22/05/20010 .
> 
> When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the 
> records less than this date, OK. But if I change the month, this date to 
> 24/04/2010 00:00:00 shows the same records. That is even if I change the 
> month the records that appear are always less than the 24 days without 
> following month. There lies my problem.
> 
> Note: My date style is dd-mm-yyyy and time is hh: mm: ss

Date style of what?

> I really need to fix this.Seabra

Use the OleDbCommand's Parameters property instead of concatinating
Strings. Mind the correct data types.

> Q1 = Q1 & " WHERE Convert(varchar(10), contadores.Data_Hora, 104) < '" &
> dateObject & "'"

Why do you convert to a string? The < operator is also defined for the
date field type - I guessing it's type is date.


> Using Con As New OleDb.OleDbConnection(ConnString)

Why not SqlConnection?


-- 
Armin
0
Armin
6/11/2010 6:04:19 PM
Why so difficult,

Change in this sample SQLClient to OleDB.

http://www.vb-tips.com/SQLParameters.aspx

Be aware that the whole EU uses the same date time format, with the 
exception of UK and Eire where a 12 hour clock is used.

(The punctuation can be different, with what I've seen here the most 
exceptional Norway)

Cor

"Henrique" <Henrique@discussions.microsoft.com> wrote in message 
news:54DBD271-4590-4553-9F97-E8506B46CE74@microsoft.com...
> Hi
>
> My records date range in the database is  Betwene 20/05/20010 and
> 22/05/20010 .
>
> When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the
> records less than this date, OK. But if I change the month, this date to
> 24/04/2010 00:00:00 shows the same records. That is even if I change the
> month the records that appear are always less than the 24 days without
> following month. There lies my problem.
>
> Note: My date style is dd-mm-yyyy and time is hh: mm: ss
>
> I really need to fix this.Seabra
>
>
>
> Dim Q1, SQL As String
> Dim DateTime1 As Date
> Dim ConnString As String = "Provider=SQLOLEDB;Data
> Source=HENRIQUE-PC;Initial Catalog=Winergest;Integrated Security=SSPI"
> Dim dateString = DateTimePicker1.Text & " " & DateTimePicker3.Text
> Dim dateObject As DateTime = DateTime.ParseExact(dateString, "dd-MM-yyyy
> HH:mm:ss", New Globalization.CultureInfo("pt-PT"))
>
> DateTime1 = dateObject
> Q1 =" SELECT contadores.Data_Hora as Data_Hora, "
>
> Q1 = Q1 +"ID_Contador as Contador,Nome as Nome, E_SVazio as H_SVazio,
> E_Vazio as H_Vazio, E_Cheias as H_Cheias, E_Ponta as H_Ponta, "
>
> Q1 = Q1 +"(E_SVazio+ E_Vazio+ E_Cheias+ E_Ponta) as Total "
>
> Q1 = Q1 +", Convert(varchar, contadores.Data_Hora, 105) as horas"
>
> Q1 = Q1 & " FROM Contadores JOIN DadosContadores ON Contadores.ID_Contador 
> =
> DadosContadores.ID "
>
> Q1 = Q1 & " WHERE Convert(varchar(10), contadores.Data_Hora, 104) < '" &
> dateObject & "'"
>
> Q1 = Q1 & " AND E_SVazio<>0 AND E_Vazio<>0 AND E_Cheias<>0 AND E_Ponta<>0 
> "
>
> Q1 = Q1 & "ORDER BY "
>
> Q1 = Q1 & "contadores.Data_Hora Asc "
>
> TextBox1.Text = Q1
>
> Using Con As New OleDb.OleDbConnection(ConnString)
> Con.Open()
>
> Dim Tabela As New DataTable()
> SQL = Q1
>
> Dim Da As New OleDb.OleDbDataAdapter(SQL, Con)
> Dim ocb As New OleDbCommandBuilder(Da)
> Da.Fill(Tabela)
> DataGridView1.DataSource = Tabela
> End Using
>
>
> 
0
Cor
6/13/2010 6:54:37 AM
On Jun 11, 12:09=A0pm, Henrique <Henri...@discussions.microsoft.com>
wrote:
> Hi
>
> My records date range in the database is =A0Betwene 20/05/20010 and =A0
> 22/05/20010 .
>
> When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the
> records less than this date, OK. But if I change the month, this date to
> 24/04/2010 00:00:00 shows the same records. That is even if I change the
> month the records that appear are always less than the 24 days without
> following month. There lies my problem.
>
> Note: My date style is dd-mm-yyyy and time is hh: mm: ss
>

<snip>

> Dim dateString =3D DateTimePicker1.Text & " " & DateTimePicker3.Text
> Dim dateObject As DateTime =3D DateTime.ParseExact(dateString, "dd-MM-yyy=
y
> HH:mm:ss", New Globalization.CultureInfo("pt-PT"))
>

<snip>

Why convert your date to a string and then back to a date??  Just use
the Value property:

Dim dateObject As DateTime =3D DateTimePicker.Value

And then do as Armin says and use Parameters and not string
concatenation to build your query.  You will be much happier!

Chris
0
Chris
6/29/2010 8:44:01 PM
Reply:

Similar Artilces:

Problems saving a worksheet with Links
Does anyone know how I can resolve this issue ... I have a directory which contains 129 worksheets which have links to external data (in a Master Spreadsheet) -- I need to copy these files into a New Directory, but kee the Master Spreadsheet (which they are linked to) in the original location. If I do a simple Cut & Past, the Reference Link to the Master Spreadsheet gets moved to the New Directory (where the file does not exist), but if I open the worksheet (in the original directory/location) and Save As to the New Directory, the worksheet saved in the New Directory maintains its link t...

RPC over HTTP problem #3
Hi, All! My network configuration: DC1, DC2 and MX (MS Exchange 2003, sp1). All of them Windows Server 2003. What was done: In the registry on dc1 and dc2 was created a new key: "NSPI Interface protocol sequences" with value: ncacn_http:6004. MX was promoted to be a GC. Installed RPC over HTTP windows component. MX was changed to be RPC-HTTP back-end server. On the MX Default Web Site was installed cerificate from the local authority running on DC2. On the RPC virtual directory anonymous access and integrated windows authentication were disabled. In the registry of MX the key HK...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

Send/Receive Problem
I am using Outlook 2002 on an XP platform. I cannot get Outlook to check for Email at regular intervals. I have the my Outlook set to Send and Receive all my accounts every 10 minutes but nothing happens. The only way I can receive Emails is by manually using the Send/Recv button or pressing F9. Can anyone offer any help. In case it is relevant I am using Norton Internet Security 2003. PWS Not sure it it Yor problem, but Outlook has some problems with Noroton Antivirus runing and chekking e-mails. As far as I know, Outlook may stop recieving e-mails from POP3 servers due to very le...

Excel problem #3
I am attaching an excel file where i have a problem In the file are 2 sheets, Main & second I want to get data from second sheet to the main sheet by a formula by which the amount in the total column will be posted in the second sheet falling under various dates. I have done for 6 sept 2003 by way of example I do not know any formula by which i can do this automatically Please help me Attachment filename: example.xls Download attachment: http://www.excelforum.com/attachment.php?postid=444742 --- Message posted from http://www.ExcelForum.com/ Hi one way: ...

Hot Dates
Hi, I have a Year-at-a-Glance worksheet into which users enter important dates and associated actions to be taken. I now want to use Conditional Formatting to highlight specific entries in the worksheet when their date matches today's date. The problem I have is that I need to convert data from three independent cells into a date to compare it with 'TODAY()'. The first cell (formatted as a number) carries the day, e.g. 2, 17, 23, etc. The second cell (formatted as General) carries the month, e.g. January, March, September, etc. The third cell (formatted as a num...

Sync net folder problem
I am sharing my calendar to my workmate with net folder. My PC is Win XP and Office 2000 and my workmate's is Win 98 and also Office 2000. I always find that My calender can't be updated from my workmate when I return office after I've taken my notebook for a few days. Can't net folder sync. data offline? Thx. your attention. Ken So Net Folders uses e-mail messages to send updates between computers so naturally you would have to be connected to your e-mail to get any updates. When you leave the office and are not connected you won't get any updates but as soon as ...

chart line style problem
I am making a scatter chart (with lines) in Excel 2007 under Vista. I can select a line style, for example, long dashes. However, if I try to change the axis (change from "automatic" to "fixed" on the horizontal axis), the line on the chart immediately becomes solid again. The legend still shows the proper dashing. I can get the dashing partly back by making the line thinner, but only where the variation is fastest - regions where the derivative is near zero are still solid even for thin lines. I'll appreciate any help! frank I was not able to reproduce this. Can you...

auto forward problems
I setup a 'contact' for 5 existing users in Exchange 5.5 Administrator. I give the contacts the desired SMTP address where they want their mail forwarded to. I set the corresponding 'contact' as the 'alternate recipient' for each of the 5 as detailed in Q255697. 2 of 5 work, the other 3 do not. When sending to each of the 5, 3 return undeliverable stating "A configuration error in the e-mail system caused the message to bounce between two servers or to be forwarded between two recipients." Any ideas? -adam Adam SK wrote: > I setup a 'con...

Exchange 2003 with SP2 problems...
I installed the SP2 for exchange 2003 server tonight, and I'm getting some problems. I added the registry key to increase the DB size to 30GB per the instructions, but I don't get a confirmation in eventid 1216 as it says I should. In fact, the message in eventid 1216 looks mised up. See insert: The Exchange store '16384' is limited to First Storage Group\Mailbox Store (ATL-SBS) GB. The current physical size of this database (the ..edb file and the .stm file) is %3 GB. If the physical size of this database minus its logical free space exceeds the limit of First Storage Gro...

Active Directory/Exchange problem
All, Before I joined my current employer the admin here upgraded from Exchange 5.5 to Exchange 2000(Box A) and then added another Exchange 2000 box to the organisation(Box B)and migrated the data in Box A to Box B. Box B is now the working exchange server and Box A is no longer used. The problem is that if I actually shut down Box A I can add a new user to Active Directory but I am unable to modify a users email/smtp details. All mail can still be transferred with no problems which would lead me to believe that Exchange is Ok but there is some sort of Active Directory link between the two bo...

small problem
hi every body; i wrote a program that it has error ;plz help me :( using System; namespace ConsoleApplication45 { class Program { static void Main(string[] args) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("***********"); for (int i = 0; i < 1008; i++) { Console.BackgroundColor = ConsoleColor.DarkCyan; Console.Write(" "); } move(); // ***********error is for here***********************************************...

Insert Static Date as part of Macro
I've built a nice macro, but I want it to insert the date it is run in an empty cell within the macro's range. When working within a spreadsheet, Ctrl +; returns the system date, but I can't figure out how to put that command into a macro. The macro recorder editor shows that the system recognizes the entry as the current date, not a function that returns the current date. The now() or today() functions are both dynamic ... they change with the system date. That's not acceptable in this situation. I need to seen when the macro was run. Thanks for any help, Ralph di...

save as version 2003 problem
I'm working in vba in Access to create and save an Excel file. All's good except that one of the workstations this is runnign on is using Office 2007. I'm developing in 2003 and all the other workstatiosn they have are using 2003. It's very important that the files be saved in 2003 format. When I do this, it runs fine and saves as 97/95 objExcelBk.SaveAs sTempPath & sExcelFileName, xlExcel9795 ','56 = xl 2000/2003 I read online in a forum post that "56" is the correct code for saving as 2003 but that's when the code is written in 20...

How can I keep track of when (date and time) data is entered into.
I am trying to create a spreadsheet for a high school class. I need to be able to track when a student has entered data into specific cells of the spreadsheet. Any ideas? In the code behind the worksheet, enter (eg) Private Sub Worksheet_Change(ByVal Target As Range) Cells(1, 1).Value = Now() End Sub This will enter in Cell A1 the date and time at which any entry is made in that worksheet. If you need the location of the time-stamp to vary according to which cell is changed then you can test the value of Target and vary the destination cell accordingly. -- Return email address is n...

Font problem with Office 2004 for mac
I have Tiger, whenever I try to Launch any Office program, as the the menu loads to 'optimizing font menu performance' It pops up with 'The font " " has been corrupted and should be removed'. It does this with MANY fonts, many of which I don't even have in my font folder. It does this every time during the program start, and most times even if I go through clicking ok 40-60 times it will sometimes freeze up anyway. Anyway to fix, get around this problem? Please email me at madefornothing@yahoo.com. Hi, this problem affects quite a few users, and there'...

zz report date truncation
We run only z reports at end of day. We will be running cashier shifts soon and would like to run z and zz reports the way they were intended. Is there any way to truncate the zz report date for each register without it running since the beginning of time and using a truckload of paper? Thanks in advance! This may not be the most graceful solution, but you could simply turn off your printer before 'printing' the zz report. Then manually delete all print tasks for that printer before turning it back on. The report won't print, but RMS will think it did. "TV hardwa...

form and query problem. please help.
All tables are linked with weak entities. However, when i enter data on the form I can't get it to let me enter more than one partipicant without access generating a new invoice id. however i need one invoice to many participants. It wont work and i have no idea what to do at this point. in addition the workshop will not let me add workshop to invoice. this is a small mdb and i'd like to email it to anyone who can assist me with the relationships as I think this is the problem but I don't know what to do. please help me. INVOICE invoiceNO - autonumber invoice prices WORKSHOP wo...

Hidden log on problem
On our XP Home laptop I have 2 users on the welcome screen, while my admin account uses (ctrl alt del) x2. All is fine until it goes into standby when I am in admin. Then on wake up the welcome screen comes back, but (ctrl alt del) x2 does nothing. I can't see a way to get back into my account without rebooting. Is there another way (opening one of the other accounts is as slow as rebooting)? Cheers, S On Mar 30, 9:26=A0am, "spamlet" <spam.mores...@invalid.invalid> wrote: > On our XP Home laptop I have 2 users on the welcome screen, while my a...

Pulling counts out of query results
I have a query that has one field Type which is set to Count The query results are used in a report. The report has the fields in the detail section as TYPE and CountOfType (1 line only in the detail section) The report looks like this when displayed AS 28 AV 17 OR 5 I need to be able to get the individual AS No (28) and add it to another number elswhere in the report. How can I do this? I have tried using a textbox with an if function (if [type]="AS",CountOfType,0 but that did not work. Any help appreciated. Ray I think you will need to do it in the query. But try addi...

Historical IV Trial Balance
When running this report in Summary, I am receiving an error when trying to run with a date range. Has anyone seen this before? are you getting a duplicate error? If you are, then what you are experiencing is a bug. To fix, you will have to apply the latest service pack. Hope this helps. Rheiner "DavidInterDyn" wrote: > When running this report in Summary, I am receiving an error when trying to > run with a date range. Has anyone seen this before? I think so. I'll try that by applying SP 4 for 10.0 "Rheiner" wrote: > are you gettin...

Excel
Hi, I would like to know if there is a way to set up when I enter the data each cells on first row, it will default the date and time stamp on first row. 2nd row should have the different date and time stamp than 1st row. I put "=now()" but it create the same date and time for 80 rows. I don't want that. I want to enter each rows and each rows should be different by one minute apart but same date - June 22. Your help would be much appreciated. Thanks Format A1:A79 as Custom dd-mmm-yy h:mm Select A1 Hold down CTRL Key and hit semi-colon key then <space> bar. Sti...

Date changing 2028....2029......1930???????
Hello! I have a slight problem, in my form i have a date section which is automated as 00/00/00. I use these for part of a report as a from this date to this date, if you put 010125 it will automatically change it to 01/01/2025. The thing I have found is that it is fine until you get to 010130 and then it saves it as 01/01/1930 :-/ any ideas? Thank You Jay -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200703/1 Assuming you're using XP, go to Regional Settings in the Control Panel, and click on the Customize button. Look on the...

Leave Balance As of Date
Urgent response requested for this query. This inquiry is pertaining to ‘Annual Leave Inquiry’ screen in GP 8.0. The “Leave Balance As of Date” values are displayed incorrectly for new employees joined in 2006. Incorrect values are appearing in the Employees Leave Balance List Report as well. The Year End Leave process for the year 2006 was conducted on January 4th. The leave calculation for all the new employees is starting from this day i.e. 4th January. However, the leave entitlement value is correct. If an employee joins in May and his annual leave entitlement is 30 then it sho...

Formula Problem #11
I have an excel sheet that has almost 4000 data rows. I need to compare the old sheet to the new sheet and if the part number is equal, I need it to show me the discount from the old sheet in a column in the new sheet. Here is the formula I came up with: =LOOKUP(A4,old!A4:A4000,old!H4:H4000) This compares the A column in the new sheet with the A column in the old sheet and then will report the discount from the H column into the column the formula is written. If I hand type the formula in ever cell changing the row number for the look up cell it works fine. However, when I try to dr...