I need some help trying to design a form so that a user can pick 2 dates and find out the number of pieces received between those 2 dates.
I have a database that a form will access for info. As the inventory is input and updated it stores the date in a database column with the datatype datetime using the GetDate() function. I would like to have a form that the user can query the database, using the datetime picker so that a user can obtain a particualr amount of pieces delivered between the 2 dates the user chooses. I'm in a bit of a quandry with this.
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'll have to work on these a little today. The VB method might be the one I need since I'm working on VB windows project. But I need to put 2 columns in my Database? I guess I need to have a way to capture both dates in my form. I would imagine it might be better if the user manullay enters the dates through a datetime picker as opposed to have the date put in the database automatically using the GetDAte() method. I'm having a little bit of a time with the database. Do I need to make another table just for the dates related (using ProductID as the PK-FK) to my Inventory table?
You haven't posted any details about your DB schema but I assume that if you are dealing with products and dates you have some sort of transaction table. If you can post the schema (fields/datatypes) for the table(s) you wish to query I could give you a more precise answer.
ASKER
I haven't really created this particular tabel yet because I'm trying to work it out on paper first, but I was thinking something like this:
ProductID int 4
ProductName varchar
Stock int
PAR int
Received int
Date Received datetime (Default of GetDate())
Unfortunately, the row for a particular product is what my form shows and each time that product is received throughout the month, it updates the date recieved column so I only have one date to work with. Once I create the proper database, I can make the form for it. Is it possible to have a column where the date is always Jan 1 of a year and then automatically changes to the next year using the system clock? Then I could have 2 seperate dates I could query, the first of the year and the date that I choose.
ProductID int 4
ProductName varchar
Stock int
PAR int
Received int
Date Received datetime (Default of GetDate())
Unfortunately, the row for a particular product is what my form shows and each time that product is received throughout the month, it updates the date recieved column so I only have one date to work with. Once I create the proper database, I can make the form for it. Is it possible to have a column where the date is always Jan 1 of a year and then automatically changes to the next year using the system clock? Then I could have 2 seperate dates I could query, the first of the year and the date that I choose.
Dim cn As New OleDb.OleDbConnection("Pro
Dim sq As String
sq &= "SELECT Sum([quantity]) AS cnt "
sq &= "FROM(inventory) "
sq &= "WHERE (([date]>=#" & Format(Me.DateTimePicker1.
sq &= "And [date]<=#" & Format(Me.DateTimePicker2.
Dim cm As New OleDb.OleDbCommand(sq)
Dim result
cm.CommandType = CommandType.Text
cm.Connection = cn
cn.Open()
result = cm.ExecuteScalar()
cn.Close()
MsgBox(result.ToString)