Checking for date in access file and compairing it to todays date...

StealthRT
01-15-2004, 01:55 AM
Hey all, i am stuck at this point in my program.

I need the program to load up my access table and check for the date of orders:

each record has a field called PickUp and its formatted like "sat 1/1/2004".

When the form loads up, i need it to check that field and the computers date and if its the same, place the record into the list view. Keep going down the list til EOF.

Any help would be great.

Thanks for your time,
David

Dennis DVR
01-15-2004, 02:32 AM
Dim strDate As String
Dim strToDay
strPickup = Right(Pickup, 10)
strToDay = DateSerial(Year(Date), Month(Date), Day(Date))
If strPickup = strToDay Then
msgbox "Same date"
End If

StealthRT
01-15-2004, 02:55 AM
duane.
The code doesnt seem to work with my database. Really it doesnt seem to do anything at all...

maybe more details will help?


Private Sub Open_Database()
Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "D:\cfaprogram\PartyTrays.mdb"
.Mode = adModeReadWrite
.Open
End With
End If
End Sub


that is the database opening on form_load.

after that, i need to check the PickUp field in my database for the dates (ex. sat 1/15/2004). and compair them to todays date.

Dont u have to use this?


Dim Today as date

today = Format(Today, "ddd mm/dd/yyyy")

strSQL = "SELECT PickUp FROM PartyTrayOrders WHERE PickUp = Today"



Hope that helps a little more,
David


Dim strDate As String
Dim strToDay
strPickup = Right(Pickup, 10)
strToDay = DateSerial(Year(Date), Month(Date), Day(Date))
If strPickup = strToDay Then
msgbox "Same date"
End If

Dennis DVR
01-15-2004, 04:23 AM
you didn't say that you will check it in your select statement


Dim strDate As String
Dim strToDay As String
Dim sqlStr as String
strToDay = DateSerial(Year(Date), Month(Date), Day(Date))
strToDay = Format(strToDay, "mmm mm/dd/yyyy")

sqlStr = "SELECT Pickup FROM PartyTrayOrders WHERE Pickup = '" & strToDay & "'"

StealthRT
01-15-2004, 11:51 AM
ok, this is what i have come up with that doesnt work.

Private Sub Form_Load()
Dim strDate As String
Dim strToDay As String
Dim sqlStr As String

Open_Database

strToDay = DateSerial(Year(Date), Month(Date), Day(Date))

strToDay = Format(strToDay, "mmm mm/dd/yyyy")

sqlStr = "SELECT Pickup FROM PartyTrayOrders WHERE Pickup = '" & strToDay & "'"
Set rst = New ADODB.Recordset
With rst
.Source = sqlStr
.ActiveConnection = conn
.LockType = adLockReadOnly
.Open
rst!FullName = Text1.Text
.Close
End With
Set rst = Nothing
End Sub


right now im just using a txt box to display the name that has the same day as the computer. I will want to put it in a listview box though with more info:

mitem.subitems(1) = full name
mitem.subitems(2) = Pick-up Date
mitem.subitems(3) = Time


Thanks for your time,
David

Dennis DVR
01-15-2004, 11:54 AM
sqlStr = "SELECT Pickup FROM PartyTrayOrders WHERE Pickup = #" & strToDay & "#"


what's the problem with my previous query?

StealthRT
01-15-2004, 12:08 PM
i get an error -2147217913
Syntax error in date in query expression 'PickUp = #Jan 01/15/2004#'

then the dubug is on the .Open.

Am i using the right set rst = in this to display the records?

All i am looking to do is setup the listview with 3 headings and then place the same dated records as the computer into the listview.

| Full Name | Pick-up Date | Time |
billy bob thr 1/15/2004 3:44
etc etc etc


Private Sub Form_Load()
Dim strDate As String
Dim strToDay As String
Dim sqlStr As String

Open_Database

strToDay = DateSerial(Year(Date), Month(Date), Day(Date))

strToDay = Format(strToDay, "mmm mm/dd/yyyy")


sqlStr = "SELECT Pickup FROM PartyTrayOrders WHERE PickUp = #" & strToDay & "#"
Set rst = New ADODB.Recordset
With rst
.Source = sqlStr
.ActiveConnection = conn
.LockType = adLockReadOnly
.Open
!FullName = Text1.Text
.Close
End With
Set rst = Nothing
End Sub



sqlStr = "SELECT Pickup FROM PartyTrayOrders WHERE Pickup = #" & strToDay & "#"


what's the problem with my previous query?

Dennis DVR
01-15-2004, 12:08 PM
am i lost here?
what really is your problem putting the record in the listview control or the query does not working as expected?

i guess the previous one is ok

StealthRT
01-15-2004, 12:12 PM
right now, im just displaying the full name inside the text box just to make sure its pulling out the name from the records. (which its not doing right now)

all the records have a ID thats autonumbered whenever a person is added to the records. This is where i am stuck. I dont know how to pick more than one record out and place it into the listview. Do u do a loop on the ID or can u just do do until EOF?

But right now i just want the FullName to display in the text box so that i know its reading it right before i go about reading any more records and placing them into the listview.

Hope this helps,
David

am i lost here?
what really is your problem putting the record in the listview control or the query does not working as expected?

Dennis DVR
01-15-2004, 12:15 PM
sqlStr = "SELECT Pickup FROM PartyTrayOrders WHERE Pickup = '" & strToDay & "'"

but is this query working or not?

StealthRT
01-15-2004, 12:20 PM
no, its showing in to error that its picking Jan 01/15/2004
It has this in the records Thu 01/15/2004.


sqlStr = "SELECT PickUp FROM PartyTrayOrders WHERE PickUp = '" & strToDay & "'"


it says that the item can not be found in the collection correspnding..
on !FullName = Text1.Text

and i know thats right in the recordset. There is a field called FullName.

I have to goto work now. But i will get back with you on all of this. Thanks for your help so far!

David

Dennis DVR
01-15-2004, 12:21 PM
not that one the first one

sqlStr = "SELECT Pickup FROM PartyTrayOrders WHERE Pickup = '" & strToDay & "'"


what happend if you use the first query?

StealthRT
01-15-2004, 12:25 PM
It seems to do fine. in that code the "Pickup" needs to be "PickUp". It does not give me an error on that line anymore, only now at the !FullName = Text1.Text line.

Ill talk to u later, thanks again,
David

StealthRT
01-16-2004, 01:49 AM
Ok, im back and i have been working on the code a little... heres what i have so far

Private Sub Form_Load()
Dim strDate As String, strToDay As String
Dim sqlStr As String, mitem As ListItem

With LV1
.ColumnHeaders.Add , , "Guest Name", (.Width / 6)
.ColumnHeaders.Add , , "Pick-Up Date", (.Width / 8)
.ColumnHeaders.Add , , "Pick-Up Time", (.Width / 9)
.ColumnHeaders.Add , , "Order(s)", (.Width / 2)
.View = lvwReport
End With
Open_Database
strToDay = Format(Now, "ddd mm/dd/yyyy")
sqlStr = "SELECT * FROM PartyTrayOrders WHERE PickUp = '" & strToDay & "'"
Set rst = New ADODB.Recordset
With rst
.Source = sqlStr
.ActiveConnection = conn
.LockType = adLockReadOnly
.Open
Set mitem = LV1.ListItems.Add(, "K" & strToDay)
mitem.Text = !NameLast & ", " & !NameFirst
mitem.SubItems(1) = !PickUp
mitem.SubItems(2) = !TimeH & ":" & !TimeM & " " & !AMPM
mitem.SubItems(3) = !QTY1 & "--" & !Item1 & vbNewLine & !QTY2 & "--" & !Item2
.Close
End With
Set rst = Nothing
End Sub


Now there are 2 things i need it to do that i just cant seem to get working. I need it to:

1) add "multilines" to the subitems(3) colum. Right now its just displaying all the info on a single line with only a || between the two items.

2) I need it to loop though the whole database and display all todays date records in the listview.

I have included a GIF of what it looks like now and what i need it to look like so u have a better idea oh what i am trying to get.

Thanks again for your help!,
David

StealthRT
01-16-2004, 01:49 PM
Do i need to use a Loop for this to go though all the records?

While Not rst.EOF
Set mitem = LV1.ListItems.Add(!PickUp(X))
X = X + 1

??

Thanks,
David

MKoslof
01-16-2004, 09:03 PM
Is that your entire While statement? If so, and if you are trying to loop until EOF you need to close your While statement with a wend and move to the next record....a slight enhancement to your last code post:




While rst.EOF = False
Set mitem = LV1.ListItems.Add(!PickUp(X))
X = X + 1
rst.movenext
Wend

StealthRT
01-16-2004, 09:26 PM
MK, This is what i want to do with it..

i need it to go though the records and find all dates that are the same as the computers on the day they run it.

This is the line that gets the computers date and finds the field in the record set that has the same date.

strToDay = Format(Now, "ddd mm/dd/yyyy")
sqlStr = "SELECT * FROM PartyTrayOrders WHERE PickUp = '" & strToDay & "'"


Now how can i place the records that DO have the right date as the computer in the ListView? So far the code below only grabs one of the records (even though there are two with the same date as the computer)

With rst
.Source = sqlStr
.ActiveConnection = conn
.LockType = adLockReadOnly
.Open
Set mitem = LV1.ListItems.Add(, "K" & strToDay)
mitem.Text = !NameLast & ", " & !NameFirst
mitem.SubItems(1) = !PickUp
mitem.SubItems(2) = !TimeH & ":" & !TimeM & " " & !AMPM
mitem.SubItems(3) = !QTY1 & " " & !Item1
LV1.ListItems.Add.SubItems(3) = !QTY2 & " " & !Item2
LV1.ListItems.Add.SubItems(3) = !QTY3 & " " & !Item3
LV1.ListItems.Add.SubItems(3) = !QTY4 & " " & !Item4
LV1.ListItems.Add.SubItems(3) = !QTY5 & " " & !Item5
LV1.ListItems.Add.SubItems(3) = !QTY6 & " " & !Item6
LV1.ListItems.Add.SubItems(3) = !QTY7 & " " & !Item7
LV1.ListItems.Add.SubItems(3) = !QTY8 & " " & !Item8
LV1.ListItems.Add.SubItems(3) = !QTY9 & " " & !Item9
LV1.ListItems.Add.SubItems(3) = !QTY10 & " " & !Item10
.Close
End With
Set rst = Nothing


So right now i am able to view one of the records in the ListView but not the other one. Thats what im trying to get it to do, but i really am out of ideas on how to loop though it and grab the ones with the correct date.

Get back with me,
David


Is that your entire While statement? If so, and if you are trying to loop until EOF you need to close your While statement with a wend and move to the next record....a slight enhancement to your last code post:




While rst.EOF = False
Set mitem = LV1.ListItems.Add(!PickUp(X))
X = X + 1
rst.movenext
Wend

Dennis DVR
01-17-2004, 01:54 PM
With rst
.Source = sqlStr
.ActiveConnection = conn
.LockType = adLockReadOnly
.Open
End With
do while not rst.eof
Set mitem = LV1.ListItems.Add(, "K" & strToDay)
mitem.Text = !NameLast & ", " & !NameFirst
mitem.SubItems(1) = !PickUp
mitem.SubItems(2) = !TimeH & ":" & !TimeM & " " & !AMPM
mitem.SubItems(3) = !QTY1 & " " & !Item1
LV1.ListItems.Add.SubItems(3) = !QTY2 & " " & !Item2
LV1.ListItems.Add.SubItems(3) = !QTY3 & " " & !Item3
LV1.ListItems.Add.SubItems(3) = !QTY4 & " " & !Item4
LV1.ListItems.Add.SubItems(3) = !QTY5 & " " & !Item5
LV1.ListItems.Add.SubItems(3) = !QTY6 & " " & !Item6
LV1.ListItems.Add.SubItems(3) = !QTY7 & " " & !Item7
LV1.ListItems.Add.SubItems(3) = !QTY8 & " " & !Item8
LV1.ListItems.Add.SubItems(3) = !QTY9 & " " & !Item9
LV1.ListItems.Add.SubItems(3) = !QTY10 & " " & !Item10
rst.Movenext
Loop
rst.close
Set rst = Nothing

StealthRT
01-19-2004, 01:47 PM
Ok, i got all of that to work now but how do u go about looping though the listview to delete out rolls that have no data in them? Sometimes theres not a QTY2-10, Item2-10 so it just prints out blank lines and then starts with the second name.


With rst
.Source = sqlStr
.ActiveConnection = conn
.LockType = adLockReadOnly
.Open
End With
do while not rst.eof
Set mitem = LV1.ListItems.Add(, "K" & strToDay)
mitem.Text = !NameLast & ", " & !NameFirst
mitem.SubItems(1) = !PickUp
mitem.SubItems(2) = !TimeH & ":" & !TimeM & " " & !AMPM
mitem.SubItems(3) = !QTY1 & " " & !Item1
LV1.ListItems.Add.SubItems(3) = !QTY2 & " " & !Item2
LV1.ListItems.Add.SubItems(3) = !QTY3 & " " & !Item3
LV1.ListItems.Add.SubItems(3) = !QTY4 & " " & !Item4
LV1.ListItems.Add.SubItems(3) = !QTY5 & " " & !Item5
LV1.ListItems.Add.SubItems(3) = !QTY6 & " " & !Item6
LV1.ListItems.Add.SubItems(3) = !QTY7 & " " & !Item7
LV1.ListItems.Add.SubItems(3) = !QTY8 & " " & !Item8
LV1.ListItems.Add.SubItems(3) = !QTY9 & " " & !Item9
LV1.ListItems.Add.SubItems(3) = !QTY10 & " " & !Item10
rst.Movenext
Loop
rst.close
Set rst = Nothing

StealthRT
01-20-2004, 12:03 PM
I know their is a lv1.listitems.selected that you can use to delete a line but the user will not have selected it. So any ideas on how to loop though it and delete them?

Thanks,
David

Ok, i got all of that to work now but how do u go about looping though the listview to delete out rolls that have no data in them? Sometimes theres not a QTY2-10, Item2-10 so it just prints out blank lines and then starts with the second name.


With rst
.Source = sqlStr
.ActiveConnection = conn
.LockType = adLockReadOnly
.Open
End With
do while not rst.eof
Set mitem = LV1.ListItems.Add(, "K" & strToDay)
mitem.Text = !NameLast & ", " & !NameFirst
mitem.SubItems(1) = !PickUp
mitem.SubItems(2) = !TimeH & ":" & !TimeM & " " & !AMPM
mitem.SubItems(3) = !QTY1 & " " & !Item1
LV1.ListItems.Add.SubItems(3) = !QTY2 & " " & !Item2
LV1.ListItems.Add.SubItems(3) = !QTY3 & " " & !Item3
LV1.ListItems.Add.SubItems(3) = !QTY4 & " " & !Item4
LV1.ListItems.Add.SubItems(3) = !QTY5 & " " & !Item5
LV1.ListItems.Add.SubItems(3) = !QTY6 & " " & !Item6
LV1.ListItems.Add.SubItems(3) = !QTY7 & " " & !Item7
LV1.ListItems.Add.SubItems(3) = !QTY8 & " " & !Item8
LV1.ListItems.Add.SubItems(3) = !QTY9 & " " & !Item9
LV1.ListItems.Add.SubItems(3) = !QTY10 & " " & !Item10
rst.Movenext
Loop
rst.close
Set rst = Nothing

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum