VB 2005 MS Access Time question

[abhisdoc]

I am a newb on VB 2005 and I am trying to rework a DB app an ex colleague had made before.

There is an ACCESS backend with a field tstmp (Timestamps) I need to make a simple query with two Textboxes and the Text Boxes take the users input in the MM/DD/YYYY hh/mm/ss format.

The code I have is :

Dim Dt as datetime
Dim Dt1 as datetime

dt = TextBox1.Text
dt1 = TextBox2.Text


"select * from table1 where tstmp between '" & dt & "' and'" & dt2 & "'

this query generates an error. Could anyone please help ?

[Dököll]

I am a newb on VB 2005 and I am trying to rework a DB app an ex colleague had made before.

There is an ACCESS backend with a field tstmp (Timestamps) I need to make a simple query with two Textboxes and the Text Boxes take the users input in the MM/DD/YYYY hh/mm/ss format.

The code I have is :

 
Dim Dt as datetime
Dim Dt1 as datetime
 
 dt = TextBox1.Text
dt1 = TextBox2.Text
 
 
"select * from table1 where tstmp between  '" & dt & "'  and'" & dt2 & "'
 
 

this query generates an error. Could anyone please help ?

What is the error you are getting, abhisdoc!

It looks like you are not telling VB Express the database name, though you did tell it the table name...

[abhisdoc]

Hello,

Thank you so much for helping me out. The database is connecting to the frontend because it is giving a report, just its not perfect, for example if I search for times between 6:00 PM and 9:00 PM, I may get something but I wont get something in a different time. I am thinking it is a problem in my query. I will post the query again...

dim dt as string
dim dt2 as string

dt = TextBox1.Text
dt2 = TextBox2.Text

res.Open("select distinct wccode, ldno from ccui where tstmp between CONVERT(DateTime,'" & dt & "') and CONVERT(DateTime,'" & dt2 & "')", con, 1, 2)

res.MoveFirst()

The error I am getting is EOF or BOF = True. Con is the active connection and it works as I have been able to retrieve data using the same connection but a different query.

The DB is Access 2007 and the field tstmp is storing the time stamps. Also the tstmp field is set as memo, I tried to set it as datetime it still didnt help. Am I querying right ?

Regards
Abhijit

[Denburt]

Hello,

Thank you so much for helping me out. The database is connecting to the frontend because it is giving a report, just its not perfect, for example if I search for times between 6:00 PM and 9:00 PM, I may get something but I wont get something in a different time. I am thinking it is a problem in my query. I will post the query again...

dim dt as string
dim dt2 as string

dt = TextBox1.Text
dt2 = TextBox2.Text

res.Open("select distinct wccode, ldno from ccui where tstmp between CONVERT(DateTime,'" & dt & "') and CONVERT(DateTime,'" & dt2 & "')", con, 1, 2)

res.MoveFirst()

The error I am getting is EOF or BOF = True. Con is the active connection and it works as I have been able to retrieve data using the same connection but a different query.

The DB is Access 2007 and the field tstmp is storing the time stamps. Also the tstmp field is set as memo, I tried to set it as datetime it still didnt help. Am I querying right ?

Regards
Abhijit

I see errors in both posts...

If the field in the table is a Date/Time then the query above should work with a few tweaks, due to the positioning of your quotes.

 res.Open("select distinct wccode, ldno from ccui where tstmp between CONVERT(DateTime,'" & dt & "') and CONVERT(DateTime,'" & dt2 & "')", con, 1, 2) 

However if your field is a Memo field (strange but O.K.) Then I would use the query from the other post, with the following corrections. Although make sure the declared variables coincide with the variables in the query string.

 res.Open("select * from table1 where tstmp between '" & dt & "' and '" & dt2 & "'", con, 1, 2)

[timber910]

I'm having a silimar problem to this one above that you solved.

The error I'm getting is "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

Here is my query.

"SELECT * from table WHERE entereddate BETWEEN CONVERT (DateTime, ' "& startDate &" ') AND CONVERT(DateTime, ' "& endDate &" ') "

startDate and endDate are my variables, but i've even tried hard coding dates in just to get the query working.

entereddate is added by an ASP page with the DateAdd ("h", 2, now())) to an access DB 2003 and the column has the "Date/Time" data type with General Date as the Format.

Am I not able to pull this because of the hh:mm:ss aspect? I tried using access to see if I can pull something like this and it works with the SQL that access rights. Example:

SELECT entereddate
FROM table
WHERE (((ib_amps_project.entereddate) Between #6/4/2007# And #6/10/2007#));