dakota97

Ok...looks great...one problem on my end though...I'm going to have to go through my code again and adjust for the changes in the DB fields, and how the program originally inserts the employee information and time data. I'll play around with it throughout the day and let you know the status hopefully by this evening. Again, I can't thank you enough for the time you've put into helping me get through this. I'll let you know how I make out. Thanks, Chris

Just a quick note that it is indeed working on mine now as well. I've made a few minor modifications to the SQL to get it to only display the 3 most important fields (DateIn, StartTime, and EndTime), but it is working here. One thing that I do want to do after this is ironed out is be able to make changes to the DB from the datagrid as well. I'm going to use the same setup on the EDIT form as the VIEW form, so it shouldn't be too difficult....I hope. I await your modifications

That was actually something that I was going to work on after I got this to work, but yes if you could incorporate that I won't complain...lol

It's a work project. I'm the General Manager of a local computer store, and I'm trying to come up with a simple program to keep track of our employee hours. Any suggestions would be greatly appreciated, so feel free to make any changes you would like, and I can work arouind them.

Again, thank you very much for your help. I'll be waiting for your response Chris

Ok, here's the DB in 2000 formatnewdb2000.zip

Ok, here is a new DB with the two tables that I have and fields. Thank you for your diligence and help. I will remember this in the future. Chris newdb.zip

I am still getting the error. I've doublechecked everything, and still cannot find the problem. There is no DB password, the DB is in the right directory, and it still won't work. This is getting very frustrating, but I guess that comes with the teritory. Any other suggestions?

Ok, thank you. One problem though. I'm getting an error during runtime on the following line: myDA.Fill(ds, "TimeIn") I made sure my DB was in the right location

ok...now where do I put that code in the form?

Ok, I'm lost on this one. I've zipped the form that I'm working with in hopes that you may be so kind as to let me look at the code and see what you're doing with it. If you would, please guide me in the right direction. Thanks, Chris [edit]Please don't post binaries in zips you attach[/edit]

thanks for the replies millenniasp....what I'm looking to do is have my form show a textbox and 2 datepickers...I want to have the user click a button and have the information displayed in a datagrid based on my query...I understand your code, but how could I implement this into what I'm trying to accomplish? Thanks in advance, Chris

Ok, I have a connection and query working so that I can populate a litbox that I have, but what I'd like to do is populate a datagrid instead. Thee working code that I have is as follows, but hwo I can incorporate this into creating a dataset and datagrid instead? Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strdate As String = DateTimePicker1.Value.ToShortDateString Dim enddate As String = DateTimePicker2.Value.ToShortDateString 'Create the database connection Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= timeclock.mdb") MyConnection.Open() 'Query the database to find matching record Dim MyCommand As New OleDbCommand("SELECT * FROM Timein WHERE DateIn between #" & strdate & "# and #" & enddate & "# AND EmpID='" & TextBox1.Text & "'", MyConnection) Thanks in advance, Chris

Worked perfectly. Thanks Roey. It was driving me crazy.

I have a form consisting of a textbox, two datepickers, and three listboxes. What I'm trying to do is search the db to find records which include the text from the textbox that are between two dates chosen from the datepickers. I am able to retrieve the data and display it in the listboxes with no problem. The problem comes when I want to display a message if there are no records meeting the criteria. I've tried using an IF....THEN statement, but I must be putting it in the wrong place or something, because when I run the code, it doesn't display the data. If I take out the IF...THEN, it works fine, but doesn't display the message if no records are found. The code that works is as follows: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strdate As String = DateTimePicker1.Value.ToShortDateString Dim enddate As String = DateTimePicker2.Value.ToShortDateString 'Create the database connection Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= timeclock.mdb") MyConnection.Open() 'Query the database to find matching record Dim MyCommand As New OleDbCommand("SELECT * FROM Timein WHERE DateIn between #" & strdate & "# and #" & enddate & "# AND EmpID='" & TextBox1.Text & "'", MyConnection) 'Declare a variable to read the database information Dim MyReader As OleDbDataReader = MyCommand.ExecuteReader() Do While MyReader.Read Dim datein As Date = MyReader("Datein") Dim stime As Date = MyReader("StartTime") Dim etime As Date = MyReader("EndTime") Dim din As String = datein.ToShortDateString Dim intime As String = stime.ToShortTimeString Dim otime As String = etime.ToShortTimeString 'Add items to the listboxes with the data from the datareader ListBox1.Items.Add(din) ListBox2.Items.Add(intime) ListBox3.Items.Add(otime) Loop MyConnection.Close() MyReader.Close() MyCommand.Dispose() End Sub Any ideas on how I can display a message if no records are found matching the criteria? Thanks in advance, Chris

That's the right concept, but not quite how I'd like to do it. I've played around with some code, and have accomplished to convert the datepicker value to match the value of the field in my DB with this code: Dim strdate As String = DateTimePicker1.Value.ToShortDateString Dim enddate As String = DateTimePicker2.Value.ToShortDateString Now what I need to do is find all of the records that have a DateIn value between the seleted dates. My idea was coding it something like the following, although it's not working: 'Create the database connection Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= timeclock.mdb") MyConnection.Open() 'Query the database to find matching record Dim MyCommand As New OleDbCommand("SELECT * FROM Timein WHERE DateIn between '" & strdate & "' and '" & enddate & "'", MyConnection) 'Declare a variable to read the database information Dim MyReader As OleDbDataReader = MyCommand.ExecuteReader() Am I at least on the right track?

Hi all, I have an Access DB that has the fields EmpID, DateIn, StartTime and EndTime. I need to retrieve all records from the DB that have a value in their DateIn field that are between 2 specific dates that the user selects from a datepicker control on my form. How can I do this? I've tried searching the forums, but can't really find anything on this topic. Any suggestions will help. Thanks in advnace, Chris

Here's a sample of code from a program I wrote that will hopefully give you an idea of how to do what you want: 'Create your database connection Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\timeclock.mdb") MyConnection.Open() 'Query the database to find matching record Dim MyCommand As New OleDbCommand("SELECT * FROM Employees WHERE EmpID ='" & TextBox15.Text & "'", MyConnection) 'Declare a variable to read the database information Dim MyReader As OleDbDataReader = MyCommand.ExecuteReader() If MyReader.Read Then 'Populate your textboxes with the data from the datareader TextBox15.ReadOnly = True TextBox1.Text = MyReader("Fname") TextBox2.Text = MyReader("mi") TextBox3.Text = MyReader("Lname") TextBox4.Text = MyReader("Address1") TextBox5.Text = MyReader("Address2") TextBox6.Text = MyReader("City") TextBox7.Text = MyReader("State") TextBox8.Text = MyReader("zip") TextBox9.Text = MyReader("Phone1") TextBox10.Text = MyReader("Phone2") TextBox11.Text = MyReader("Econtact") TextBox12.Text = MyReader("Ephone") TextBox13.Text = MyReader("SSN") TextBox14.Text = MyReader("Rate") TextBox15.Text = MyReader("EmpID") ComboBox1.Text = MyReader("Per") CheckBox1.Checked = MyReader("Admin") MyConnection.Close() MyCommand.Dispose() Else MsgBox("Employee not found", , "Error") MyReader.Close() MyCommand.Dispose() End If

I used the first suggestion, and it worked perfectly. Thanks a million Chris

Hi all, What I'm trying to do is have a menu item disabled on Form1, and have a user click another enabled menu item to bring up Form2. When the user passes authentication, I want to close Form2 and enable the menu items on Form1. My code for Form1 to display Form2 is as follows: Private Sub MenuItem17_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem17.Click Dim Admin As New Admin() Admin.Showdialog() End Sub And my code on Form2 is as follows: Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= timeclock.mdb") MyConnection.Open() Dim MyCommand As New OleDbCommand("SELECT * FROM Employees WHERE EmpID ='" & TextBox1.Text & "' AND Password='" & TextBox2.Text & "'", MyConnection) Dim MyReader As OleDbDataReader = MyCommand.ExecuteReader() If MyReader.Read Then Form1.MenuItem5.Enabled = True Form1.MenuItem11.Enabled = True Me.Close() Else MsgBox("Employee does not have Administrative privilages", , "Error") MyConnection.Close() MyCommand.Dispose() TextBox1.Select() End If Any help would be greatly appreciated. Thanks in advance, Chris

Shirtster I don't want an exact match....if the user doesn't include (say) the area code, or doesn't include the -'s I want it to be able to pick the record....I'll try the suggestions and see what happens....thanks to the both of you

sorry Grimfort....brain lapse...a_jam you were right...I wasn't thinking about the INSERT...I used the UPDATE and it worked fine...thanks

It's when I run the program. Apparently when it comes to my SQL statement it finds the error.

Hi all, I'm getting an error in the following code when I run it. It's telling me that I'm missing a ; somewhere, and I can't figure out where. Any Ideas? 'Add the employee's clock out time to the database Dim MyConnection2 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= timeclock.mdb") MyConnection2.Open() Dim strdate As String strdate = Date.Now.ToString Dim MyCommand2 As New OleDbCommand("INSERT Into TimeIn (EndTime) VALUES ('" & strdate & "') WHERE EmpID ='" & TextBox1.Text & "' AND EndTime = '""'", MyConnection2) MyCommand2.ExecuteNonQuery() Thanks in advance, Chris

Worked perfectly. Thanks for all your help Chris