Programming Forums > Application Development > Visual Basic

GoO · Jul 6th, 2005, 6:18 AM

i want to generate a report that ranges from certain dates. example: from July 1 2005 to July 7 2005.

in access, the database field is named "LogDate" and the data type is "Date/Time" and I selected "Short Date" from the menu below. the format for this date is 7/1/2005, or m/d/yyyy.

in VB 6, i used the SQL Statement:
"SELECT * FROM TableName WHERE LogDate = " & Date
if today is july 6, then it would show "SELECT * FROM TableName WHERE LogDate = 7/6/2005.

i have records that are on july 6, but the program does not see it as if the statement was invalid. if i place 7/6/2005 in single quotes (ex. LogDate = '7/6/2005') it says data type mismatch.

How do you compare dates in VB? what data type should i use in access as well as in VB?

any suggestions? thanks...

Ooble · Jul 6th, 2005, 9:31 AM

Try using this:

(Toggle Plain Text)

... WHERE LogDate = 2005-07-06

The international standard date format is YYYY-MM-DD - I believe that's the best way to do it. The code you used could be mistaken for D/M/YYYY instead of M/D/YYYY, which is why it would have returned nothing.

GoO · Jul 6th, 2005, 6:02 PM

thanks,... ill try that

GoO · Jul 7th, 2005, 8:05 AM

well... that suggestion didnt work. thanks though...

but i finally figured it out... and i thought i would share it with you..

references:
http://www.ozzu.com/ftopic31951.html
http://support.microsoft.com/default...b;en-us;296653

apparently, we have to use the "#" symbol instead of using/not using a single quote:

(Toggle Plain Text)

variable = "SELECT * FROM TableName WHERE LogDate = #" & thedate & "#"

in string form:

(Toggle Plain Text)

SELECT * FROM TableName WHERE LogDate = #7/5/2005#

Ooble · Jul 7th, 2005, 1:10 PM

Ah... the joys of Access. How lovely...

Rory · Jul 8th, 2005, 7:38 PM

Yeah that's your standard run of the mill VB Date literal. I'm suprised the long date didn't work though - how about epoque time?

GoO · Jul 12th, 2005, 11:46 AM

yeah long date didnt work. i tried all of them (i think) using the "FormatDateTime" function in vb. um... sorry i dont know what epoque time is...

...i want to edit the title... but i cant edit the thread title.. how do u do it? i want to add "date and time comparison" cause i have another question related to this...

How do you get the number of hours in between 2 given time values that have different dates. i mean i want to get the number of hours, for example, between july 6 2005 10:00 PM and july 7, 2005 6:00 am. is there a function that computes this? i found 1 way but its not that perfect. any suggestions? its a login/logout thing and i have to compute the no of hrs he was logged in...

John · Jul 15th, 2005, 11:33 PM

hi you can compare date by using format(fieldname,"dd/mmm/yyy")
so in acces it will not confused between date and month
so your query will be
"where LogDate =#" & format(06/07/2005,"dd/mmm/yyyy")
trythis out and reply me

Rory · Aug 1st, 2005, 2:16 PM

Quote:

Originally Posted by GoO

How do you get the number of hours in between 2 given time values that have different dates. i mean i want to get the number of hours, for example, between july 6 2005 10:00 PM and july 7, 2005 6:00 am. is there a function that computes this? i found 1 way but its not that perfect. any suggestions? its a login/logout thing and i have to compute the no of hrs he was logged in...

Try this:
NoOfHoursTillChristmas = DateDiff("h", Now, "25/12/2005")

Jul 6th, 2005, 6:18 AM	#1
GoO Newbie Join Date: Nov 2004 Posts: 17 Rep Power: 0	vb with access DB: comparing dates i want to generate a report that ranges from certain dates. example: from July 1 2005 to July 7 2005. in access, the database field is named "LogDate" and the data type is "Date/Time" and I selected "Short Date" from the menu below. the format for this date is 7/1/2005, or m/d/yyyy. in VB 6, i used the SQL Statement: "SELECT * FROM TableName WHERE LogDate = " & Date if today is july 6, then it would show "SELECT * FROM TableName WHERE LogDate = 7/6/2005. i have records that are on july 6, but the program does not see it as if the statement was invalid. if i place 7/6/2005 in single quotes (ex. LogDate = '7/6/2005') it says data type mismatch. How do you compare dates in VB? what data type should i use in access as well as in VB? any suggestions? thanks...

Jul 6th, 2005, 9:31 AM	#2
Ooble I eat cake for breakfast. Join Date: Jul 2004 Location: In my box. Posts: 4,434 Rep Power: 9	Try using this: (Toggle Plain Text) ... WHERE LogDate = 2005-07-06 ... WHERE LogDate = 2005-07-06 The international standard date format is YYYY-MM-DD - I believe that's the best way to do it. The code you used could be mistaken for D/M/YYYY instead of M/D/YYYY, which is why it would have returned nothing. __________________ Me :: You :: Them

Jul 7th, 2005, 8:05 AM	#4
GoO Newbie Join Date: Nov 2004 Posts: 17 Rep Power: 0	well... that suggestion didnt work. thanks though... but i finally figured it out... and i thought i would share it with you.. references: http://www.ozzu.com/ftopic31951.html http://support.microsoft.com/default...b;en-us;296653 apparently, we have to use the "#" symbol instead of using/not using a single quote: (Toggle Plain Text) variable = "SELECT * FROM TableName WHERE LogDate = #" & thedate & "#" variable = "SELECT * FROM TableName WHERE LogDate = #" & thedate & "#" in string form: (Toggle Plain Text) SELECT * FROM TableName WHERE LogDate = #7/5/2005# SELECT * FROM TableName WHERE LogDate = #7/5/2005#

Jul 7th, 2005, 1:10 PM	#5
Ooble I eat cake for breakfast. Join Date: Jul 2004 Location: In my box. Posts: 4,434 Rep Power: 9	Ah... the joys of Access. How lovely... __________________ Me :: You :: Them

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Jul 6th, 2005, 6:02 PM	#3
GoO Newbie Join Date: Nov 2004 Posts: 17 Rep Power: 0	thanks,... ill try that

Jul 8th, 2005, 7:38 PM	#6
Rory Expert Programmer Join Date: Jan 2005 Location: London Posts: 542 Rep Power: 4	Yeah that's your standard run of the mill VB Date literal. I'm suprised the long date didn't work though - how about epoque time?

Jul 12th, 2005, 11:46 AM	#7
GoO Newbie Join Date: Nov 2004 Posts: 17 Rep Power: 0	yeah long date didnt work. i tried all of them (i think) using the "FormatDateTime" function in vb. um... sorry i dont know what epoque time is... ...i want to edit the title... but i cant edit the thread title.. how do u do it? i want to add "date and time comparison" cause i have another question related to this... How do you get the number of hours in between 2 given time values that have different dates. i mean i want to get the number of hours, for example, between july 6 2005 10:00 PM and july 7, 2005 6:00 am. is there a function that computes this? i found 1 way but its not that perfect. any suggestions? its a login/logout thing and i have to compute the no of hrs he was logged in...

Jul 15th, 2005, 11:33 PM	#8
John Newbie Join Date: Apr 2005 Posts: 6 Rep Power: 0	hi you can compare date by using format(fieldname,"dd/mmm/yyy") so in acces it will not confused between date and month so your query will be "where LogDate =#" & format(06/07/2005,"dd/mmm/yyyy") trythis out and reply me

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Application Development > Visual Basic

vb with access DB: comparing dates